Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Using a variable in sqlplus
Top Forums Shell Programming and Scripting Using a variable in sqlplus Post 302090394 by MadHatter on Monday 25th of September 2006 11:06:53 AM
Old 09-25-2006
Using a variable in sqlplus
Hello,

I'm trying to write a script that will loop a sql statement through an external list. Basically, the script enters a loop and runs the sql statement for each entry in the list file. Currently, the script will stop at a cursor where I can then manually enter the SQL statment. This is what I have so far:

Port_count.ksh
Quote:
PRTFLIST='/home/oracle/PortConversion/tradePort.list'
DATADIR='/home/oracle/PortConversion'


# Source the login information
. $HOME/Dear/jobs/DRU/login_DRU.ksh

cd /home/oracle/chris/PortConversion
$ORACLE_HOME/bin/sqlplus -s $DBO_USER/$DBO_PASS

#get the row count for each portfolio
while read $PRTFLIST; do



select count (*) from dear_trade_snapshot where id_prtf_stars = '{$prtf}'
;
done < $PRTFLIST
tradePort.list
Quote:
1MB
1AK
1AP
Can anyone help?

Thanks,
Chris
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to pass variable to SQLPLUS in a ksh script?

Hi, I am writing a ksh script which will use sqlplus to run a sql and pass 2 variables as the SQL request. In the ksh script, I have 2 variables which are $min_snap and $max_snap holding 2 different numbers. Inside the same script, I am using SQLPLUS to run an Oracle SQL script,... (6 Replies)
Discussion started by: rwunwla
6 Replies

2. UNIX for Advanced & Expert Users

How to pass unix variable to SQLPLUS

hi fellows, can any body tell me how to pass unix variables to oracle code is... #! /bin/ksh echo ENTER DATE VALUE's read START_DATE END_DATE sqlplus xyx/abc@oracle select * from table1 where coloumn1 between $START_DATE and $END_DATE; is this is correct way........... Thanks in... (1 Reply)
Discussion started by: chiru
1 Replies

3. Shell Programming and Scripting

Passing the unix variable to sqlplus

Hi, I am writing a script which creates an external table using a shell script. My requirement is like this. Usage: . ./r.ksh <table_name> - this should create an external table. e.g . ./r.ksh abc - this should create an external table as abc_external. How do i achieve this? Please... (5 Replies)
Discussion started by: Anaramkris
5 Replies

4. Shell Programming and Scripting

store sqlplus output in variable

hi how can i store sqlplus output to a variable in sh script (not bash) Thanks MM (1 Reply)
Discussion started by: murtymvvs
1 Replies

5. Shell Programming and Scripting

Sending sqlplus output to a shell variable

I am trying to import a sqlplus output into a shell variable but it doesnt seem to be working. set -x export DEPENDENT_CR_NO=`sqlplus -s /nolog <<EOF conn username/passwd set heading off select dependency from custom_patches where patch_name='PATCH.zip'; exit; EOF` echo $DEPENDENT_CR_NO ... (2 Replies)
Discussion started by: beginer314
2 Replies

6. Shell Programming and Scripting

awk with variable from sqlplus

Hi, I'm a it stuck on the below code where a variable is pulled from sqlplus and used in awk. It runs with no errors but still pulls back all records in the input file. It should pull the max reference from sql plus and then only print those records where the reference value in column 1 is... (4 Replies)
Discussion started by: jonathanb30
4 Replies

7. Shell Programming and Scripting

Scirpt to fetch the variable from sqlplus

Hi Gurus, I am stuck with the step where i need to fetch the location & sales from the below procedure by taking it from table field using the for loop. any idea how this can be done in unix. From one column both the location and sales are taken out. create or replace procedure newyork... (2 Replies)
Discussion started by: arun888
2 Replies

8. Shell Programming and Scripting

Passing sqlplus output to shell variable

Hi , I am using below code : for i in `ps -ef|grep pmon|awk {' print $8 '}|cut -f3 -d'_'|grep -v '^grep'` do ORACLE_SID=$i export ORACLE_SID; dest=`sqlplus "/ as sysdba" <<EOF set heading off feedback on verify off select DESTINATION from v\\$archive_dest where target in... (5 Replies)
Discussion started by: admin_db
5 Replies

9. UNIX for Beginners Questions & Answers

Sqlplus variable UNIX

hi guys i have a sqlplus : sqlplus -s username/password << EOF @mysql.sql EOF in mysql.sql there is a count of a table, i want to write in a variabile unix. how can i do? Thanks a lot Regards Francesco. (3 Replies)
Discussion started by: Francesco_IT
3 Replies

10. Shell Programming and Scripting

Pass a VARIABLE to sqlplus script

Hi Team, I am trying to run a sqlplus script against several databases via a FOR/LOOP and also passing the loop variable to a sqlplus script I am calling, as follows: #!/bin/bash export ORACLE_SID=plgc1 export ORACLE_HOME=/opt/oracle/product/11.2.0.2/db_1 export... (1 Reply)
Discussion started by: jonnyd
1 Replies

LEARN ABOUT X11R4

break

break(1)                                                           User Commands                                                          break(1)

NAME

       break, continue - shell built-in functions to escape from or advance within a controlling while, for, foreach, or until  loop

SYNOPSIS

   sh
       break [n]

       continue [n]

   csh
       break

       continue

   ksh
       *break [n]

       *continue [n]

DESCRIPTION

   sh
       The break utility exits from the enclosing for or while loop, if any. If n is specified, break n levels.

       The continue utility resumes the next iteration of the enclosing for or while loop. If n is specified, resume at the n-th enclosing loop.

   csh
       The  break  utility  resumes  execution after the end of the nearest enclosing foreach or while loop. The remaining commands on the current
       line are executed. This allows multilevel breaks to be written as a list of break commands, all on one line.

       The continue utility continues execution of the next iteration of the nearest enclosing while or foreach loop.

   ksh
       The break utility exits from the enclosed for, while, until, or select loop, if any. If n is specified,  then  break  n  levels.  If  n  is
       greater than the number of enclosing loops, the outermost enclosing loop shall be exited.

       The  continue utility resumes the next iteration of the enclosed for, while, until, or select loop. If n is specified then resume at the n-
       th enclosed loop. If n is greater than the number of enclosing loops, the outermost enclosing loop shall be used.

       On this man page, ksh(1) commands that are preceded by one or two * (asterisks) are treated specially in the following ways:

       1.  Variable assignment lists preceding the command remain in effect when the command completes.

       2.  I/O redirections are processed after variable assignments.

       3.  Errors cause a script that contains them to abort.

       4.  Words that follow a command preceded by ** that are in the format of a variable assignment are expanded with the same rules as a  vari-
           able  assignment.  This means that tilde substitution is performed after the = sign, and also that word splitting and file name genera-
           tion are not performed.

ATTRIBUTES

       See attributes(5) for descriptions of the following attributes:

       +-----------------------------+-----------------------------+
       |      ATTRIBUTE TYPE         |      ATTRIBUTE VALUE        |
       +-----------------------------+-----------------------------+
       |Availability                 |SUNWcsu                      |
       +-----------------------------+-----------------------------+

SEE ALSO

       csh(1), exit(1), ksh(1), sh( 1), attributes(5)

SunOS 5.10                                                          17 Jul 2002                                                           break(1)
All times are GMT -4. The time now is 08:12 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy