Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: SQL not working in a for loop
Top Forums Programming SQL not working in a for loop Post 302952636 by Corona688 on Thursday 20th of August 2015 01:37:22 PM
Old 08-20-2015
Why is sqlplus in backticks?

Why not run sqlplus once for 10,000 statements instead of 10,000 times for 10,000 statements? Connection overhead can be significant.

There is no point putting cat in backticks here.

Code:
(

count=0

echo "SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF"

while read tn
do
        echo "update sv set old_ts=sysdate, status=7 where status=1 and tn='$tn';"
        echo "commit;"
        count=`expr $count + 1`
        # Printing to stderr avoids feeding this text into sqlplus
        echo "Record # $count - Setting TN=${tn} to OLD on SV" >&2
done < /home/cpac/mnaji/Resync/sv_tn.list.split1 ) | sqlplus -s ${DB_USERID}

This User Gave Thanks to Corona688 For This Post:
mrn6430
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Unix-Sql Loop error

Hi, I am getting the error "No matching <<", when i run the below. Is it that we can't execute SQL in a Unix loop. I am executing a SQL in a loop. Is it the EOF is written wrongly. . /opt/app/wlsconfigv61/domains/profiles/oracleV901.profile export DBUSER=ecdb01 set -A DBINSTANCE... (3 Replies)
Discussion started by: manu_byc
3 Replies

2. UNIX for Dummies Questions & Answers

Problem with while loop and SQL

Connected to oracle database sqlplus << EOF $CONNECTSTR set heading off set trimspool on set feedback off select ID,DATE from sysadm.TEST where VALUE = 'A' order by ID; value_id = ID value_date = DATE EOF 1. Is it possible to reference the values, ID,DATE in unix shell script. 2.... (20 Replies)
Discussion started by: nandajk
20 Replies

3. Shell Programming and Scripting

how to call shell script from pl/sql loop

Hello, I am doing a shell script which contain a pl/sql loop to search for 3 values, i would like to call another shell script inside this sql loop each time it find the values. so how can i call shell script from pl/sql using its variables, any idea? Here is idea about the code: my... (1 Reply)
Discussion started by: rosalinda
1 Replies

4. Shell Programming and Scripting

Sql in a linux loop

Hi i want to run a query in loop(unix loop) every 2 hours and spool the result to a file. i am trying but getting error here is the script $ while true do sqlplus ank/ank<<! spool ank.lst select * from v$database; exit sleep 7200 done :D (2 Replies)
Discussion started by: ankurk
2 Replies

5. Shell Programming and Scripting

Compiling n number of SQL files in loop

Trying to compile all SQL files using a shell script. But the below code is not working. Below Code works fine when for loop is not there(commenting line no: 1,2 and 9). 1. sq_lfile=`ls *.sql` 2. for current_sql_file in $sql_file 3. do 4. sqlplus uname/pass@Service>>SQLLOG << -ENDOFSQL... (3 Replies)
Discussion started by: Dip
3 Replies

6. Shell Programming and Scripting

SQL query in a loop with single sqlplus connection

Hi, I'm trying to build a shell script that reads a set of accounts from a file. For each account I need to perform a set of sql queries. So I have a loop with a set of sqlplus connections to retrieved my data. Is it possible to have a single sqlplus connection before entering the loop and... (4 Replies)
Discussion started by: lsantacana
4 Replies

7. Shell Programming and Scripting

UNIX/SQL loop to spool files!!

I want to spool files from unix using a sql script that would take values from another sql query within the KSH script.. unix loop select order_date from date_tbl for each order_Date i need to call this spool script with the value sqlplus scott/tiger@order_db @/ordspool/order_date.sql... (2 Replies)
Discussion started by: vr23
2 Replies

8. Shell Programming and Scripting

Script (with sql queries) not working using cron

Hi all, I have script, which performing sql queries and put output into file. When I run this script manually, its working fine, but when I want to schedule it with cron I am getting errors... I defined LD_LYBRARY_PATH and ,but no result. After I defined it, I am getting error: # more... (4 Replies)
Discussion started by: nypreH
4 Replies

9. Shell Programming and Scripting

Percona SQL Backup Shell Script not working

I am having one heck of a time, and I need anyone that can assist in finding my issue here.... First off, here is the script we are using: pastebin dot com slash njwUg6vd All usernames and passwords have been edited out. We are using an actual database user, and not root (tried both). I... (0 Replies)
Discussion started by: jonbeard
0 Replies

10. UNIX for Beginners Questions & Answers

UNIX variable not working in sql resultset

Hi, I am trying to extend unix variable value with mysql resultset. It's working fine in terminal but not working in shell script. #!/bin/bash export prcs_nm=$1 export tbl_nm=$2 export rslt=$( mysql -sN -u root -proot123 -e " select sql_query from retail_db.config where prcs_nm =... (3 Replies)
Discussion started by: Prasad
3 Replies

LEARN ABOUT LINUX

echo

ECHO(1) 							   User Commands							   ECHO(1)

NAME

       echo - display a line of text

SYNOPSIS

       echo [SHORT-OPTION]... [STRING]...
       echo LONG-OPTION

DESCRIPTION

       Echo the STRING(s) to standard output.

       -n     do not output the trailing newline

       -e     enable interpretation of backslash escapes

       -E     disable interpretation of backslash escapes (default)

       --help display this help and exit

       --version
	      output version information and exit

       If -e is in effect, the following sequences are recognized:

       \     backslash

       a     alert (BEL)

            backspace

       c     produce no further output

       e     escape

       f     form feed

       
     new line

       
     carriage return

       	     horizontal tab

       v     vertical tab

       NNN  byte with octal value NNN (1 to 3 digits)

       xHH   byte with hexadecimal value HH (1 to 2 digits)

       NOTE: your shell may have its own version of echo, which usually supersedes the version described here.	Please refer to your shell's docu-
       mentation for details about the options it supports.

AUTHOR

       Written by Brian Fox and Chet Ramey.

REPORTING BUGS

       Report echo bugs to bug-coreutils@gnu.org
       GNU coreutils home page: <http://www.gnu.org/software/coreutils/>
       General help using GNU software: <http://www.gnu.org/gethelp/>
       Report echo translation bugs to <http://translationproject.org/team/>

COPYRIGHT

       Copyright (C) 2010 Free Software Foundation, Inc.  License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>.
       This is free software: you are free to change and redistribute it.  There is NO WARRANTY, to the extent permitted by law.

SEE ALSO

       The full documentation for echo is maintained as a Texinfo manual.  If the info and echo programs are properly installed at your site,  the
       command

	      info coreutils 'echo invocation'

       should give you access to the complete manual.

GNU coreutils 8.5						   February 2011							   ECHO(1)
All times are GMT -4. The time now is 08:57 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy