Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Passing argument to a pl/sql block

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Passing argument to a pl/sql block
# 1  
Old 05-02-2008
Passing argument to a pl/sql block
Hi,

How can I pass an argument to a pl/sql block through perl/unix shell scripting.
# 2  
Old 05-02-2008
shell script version:
Code:
var="42"
printf  "'%s'" "THIS IS A STRING" | read string_var
sqlplus -s user/passwd@somedb <<EOF
DECLARE
    mynum NUMBER(4):=$var;
    mystring VARCHAR2(32):=$string_var;
BEGIN
    UPDATE MYTABLE
          SET FLD1=mystring WHERE FLD2=mynum;
    COMMIT;
END;
/
EOF

perl has a DBI package for Oracle that allows you code what amounts to a PL/SQL script in perl.
# 3  
Old 05-02-2008
What if I have a file ex.txt with contents

23423
5645
45646
567567
56756
5675
567567
345345

and I want to pass each of these as an argument (ARG_I_ORD_KEY) to PL/SQL block.

DECLARE
RetVal INTEGER;
ARG_I_ORD_KEY NUMBER;

BEGIN
ARG_I_DOC_KEY := NULL;

RetVal := DBPROD.PILOT_Subdoc_Mgr.ONLINEORDAPPROVED ( ARG_I_ORD_KEY );
END;
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Passing a second argument

I am trying to pass a second argument like so: if ] then export ARG2=$2 else message "Second argument not specified: USAGE - $PROGRAM_NAME ARG1 ARG2" checkerror -e 2 -m "Please specify if it is a history or weekly (H or W) extract in the 2nd argument" fi however, it always goes... (4 Replies)
Discussion started by: MIA651
4 Replies

2. Shell Programming and Scripting

Passing sql file name as an argument

Hi all, I have a a file which contains an oracle query. If this file is pushed into a shell script such as the below, it of course works fine. #!/usr/bin/sh su - oracle -c 'sqlplus -s / as sysdba <<EOF @/home/cron/ORA/sql/T-ORA006-OracleSessions.sql exit; EOF . What i am after... (2 Replies)
Discussion started by: nms
2 Replies

3. Shell Programming and Scripting

Passing the result of an anonymous pl/sql block to a shell script

Hello, Here is the code i have written to get the count of a plsql query back to the unix. function checkforCOIDs { countcheck=`sqlplus -s $1/$2@$3 whenever oserror exit sql.oscode rollback whenever sqlerror exit sql.sqlcode rollback set serverout on size 2000; set head off feedback off... (2 Replies)
Discussion started by: santosh2eee
2 Replies

4. Shell Programming and Scripting

Argument passing

How to pass the alphabet character as a argument in case and in if block? ex: c=$1 if a-z ]] then echo "alphabet" case $1 in a-z) echo "the value is a alphabet" edit by bakunin: please use CODE-tags. We REALLY mean it. (9 Replies)
Discussion started by: Roozo
9 Replies

5. Shell Programming and Scripting

Help with passing argument

Hi, I have a script that is scheduled with cron and runs every night. The cron part looks like this: 00 20 * * 0,1,2,3,4,5,6 /usr/local/bin/BACKUP TBTARM HOT DELETE My issue is with the 3rd parameter. Somewhere in the script, i want to tell the script to delete some files if the 3rd... (7 Replies)
Discussion started by: dollypee
7 Replies

6. Programming

Passing argument to a program!!

Hi friends, I have a small query, hope u will help me with it. I want to write a small program which would take an integer as an argument, then I want to save that argument in a variable of type int. Could you please help me with it. Here is my program #include <stdio.h> int main(int... (4 Replies)
Discussion started by: gabam
4 Replies

7. Shell Programming and Scripting

Help required in passing multiple arguments from a shell script to a pl/sql block

Hi, hope everyone are fine. Please find my issue below, and I request your help in the same In a configuration file, i have a variable defined as below TEST = 'One','Two','Three' I am trying to pass this variable in to a sql script which is define in a pl/sql block as follows, In the... (1 Reply)
Discussion started by: ramakanth_burra
1 Replies

8. Shell Programming and Scripting

Using GET, passing argument to bash

Hi guys! So, I use GET ( Simple user agent using LWP library. ) on a remote text file that is then passed to bash and executed. However, I need to pass that bash script a single argument, and so far nothing that I have tried has worked, although I have learned quite a bit about input/output... (5 Replies)
Discussion started by: Rhije
5 Replies

9. Shell Programming and Scripting

passing Argument

Hi All, i have script like below.. echo "1) first option" echo "" echo "2) second option" echo "" echo "*) please enter the correct option" read select case $select in 1) echo "first option selected" ;; 2) echo "second option selected" ;; *) echo "please enter the correct... (4 Replies)
Discussion started by: Shahul
4 Replies

10. Shell Programming and Scripting

Problem with Argument Passing

Greetings, I am wrapping the monitoring commands like vmstat, sar, iostat and call via arguments I want ./unix_stats.sh -v vmstat -p <SEC> -d <Duration> to give vmstat values, and similarly iostat etc.,. Also if I give ./unix_stats.sh -v vmstat -i iostat -p <SEC> -d <Duration> should give... (4 Replies)
Discussion started by: A_Rod
4 Replies
Login or Register to Ask a Question