Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Shell arrays in oracle stored procedure

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Shell arrays in oracle stored procedure
# 1  
Old 06-08-2005
Shell arrays in oracle stored procedure
Is it possible to pass unix shell arrays in Oracle stored procedure?
Is yes, how?
Thanks
# 2  
Old 06-08-2005
The answer is: that depends.

Not taking into account command line restrictions, which may prevent large arrays from being expanded to the command line without error;

further not taking into account if an interface for such a huge amount of passed variables is possible to realize in sqlplus or whatever you use to trigger the stored procedure;

you could use the variable expansion of the ksh to expand the content of an array:

Code:
# myarr[1]="foo"
# myarr[2]="bar"
# print - ${myarr[*]}
foo bar
#

bakunin
# 3  
Old 06-09-2005
Hi bakunin
Thanks for the response but I am looking for the syntax on how to pass unix shell arrays in Oracle stored procedure
# 4  
Old 06-09-2005
How are you currently executing your stored procedures via shell? Are you using a Here document ( << EOF .... EOF). If so, you might be able to use the syntax noted above
# 5  
Old 06-09-2005
Bottom line is that you are not going to be able to pass the array by making a simple assignment to an Oracle stored procedure parameter or variable.

You can do one of the following:
  1. Pass the array as stated previous, which is essentially a space delimited string. The strored procedure or an anonymous PL/SQL block can then parse sting into an Index-by table (PL/SQL table).
    Code:
    # myarr[1]="foo"
# myarr[2]="bar"
# sqlplus user/pwd@db <<EOF
BEGIN
    MyVar VARCHAR2(100) := '${myarr[ * ]}'; -- The star is separated by spaces because the code block was getting horked up
    -- The previous line is expanded as follows:
    -- MyVar VARCHAR2(100) :='foo bar';
    YourStoredProcedure (MyVar);
END;
/
EOF

  2. Insert the array elements into a table and the stored procedure can then retrieve the values from the table. This can be a global temporary table since the data would be temporary in nature.

Thomas
# 6  
Old 06-09-2005
Another way to do this:
1. write the contents of the array to something like a pipe-delimited file
2. Write a PL/SQL wrapper for your SP that calls UTL_FILE to read in the file, and create an array out of it, then pass the array (table actually) to the SP.

There is no shortcut.
# 7  
Old 06-09-2005
Quote:
Originally Posted by jim mcnamara
Another way to do this:
1. write the contents of the array to something like a pipe-delimited file
2. Write a PL/SQL wrapper for your SP that calls UTL_FILE to read in the file, and create an array out of it, then pass the array (table actually) to the SP.

There is no shortcut.
Oh yeah, there is this way too. I avoid using UTL_FILE. I usually want to minimize load time and UTL_FILE is notroiously slow, involves DBAs who resist helping developers (I am a DBA by the way Smilie ), and is counter intuitive when reading the input lines.

Thomas
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

ksh and Oracle stored procedure output in logfile

Friends, I pass some runtime arguments (date, number) through ksh script to Oracle procedure, use input value and pass it on to procedure. Oracle procedure gets input value, run query and logs everything in the logfile. I'm facing with couple of challenges 1. Even though I pass all... (5 Replies)
Discussion started by: homer4all
5 Replies

2. Shell Programming and Scripting

Calling Oracle stored procedure from ksh script

Friends, I'm newbie with ksh so wanting some help.... 1. I'm trying to call oracle stored procedure from ksh script by taking variable value from runtime, feed into script and execute procedure. 2. Put name1 and name2 value from script run replacing $3 & $4 I'm trying to put name1 in... (4 Replies)
Discussion started by: homer4all
4 Replies

3. Shell Programming and Scripting

Need to run Oracle stored procedure from UNIX env

Hi Everyone, I want to create a script where i need to run the oracle stored procedure from unix script and get the output(sequence number ) into a variable which i will pass in my datastage job. Below is my stored procedure:- DECLARE P_TRANSTYPE VARCHAR2(20); ... (4 Replies)
Discussion started by: prasson_ibm
4 Replies

4. Shell Programming and Scripting

Call and redirect output of Oracle stored procedure from unix script

Hi, Can you assist me in how to redirect the output of oracle stored procedure from unix script? Something similar to what i did for sybase isql -U$MYDBLOG -D$MYDBNAME -S$MYDBSVR -P$MYDBPWD -o$MYFILE<< %% proc_my_test 8 go %% Thanks in advance - jak (0 Replies)
Discussion started by: jakSun8
0 Replies

5. Shell Programming and Scripting

how to call oracle stored procedure from unix shell

Hi i want to call a oracle stored procedure from unix (using bash shell). consider this is my oracle stored procedure with parameter create procedure testproc(name IN varchar, age IN Number, id OUT Number ) AS begin id=1; dbms_output.put.line('successfull validation') end;... (6 Replies)
Discussion started by: barani75
6 Replies

6. Shell Programming and Scripting

how to pass the values to unix shell from the oracle stored procedure.

Hi i am calling a stored procedure from unix shell like this call test_proc('0002','20100218'); the stored procedure was giving output like this dbms_output.put_line(' processed earlier'); i want to see the output in the unix shell where i called. Thanks barani (6 Replies)
Discussion started by: barani75
6 Replies

7. Shell Programming and Scripting

Invoking Oracle stored procedure in unix shell script

Here's a shell script snippet..... cd $ORACLE_HOME/bin Retval=`sqlplus -s <<eof $TPDB_USER/april@$TPD_DBCONN whenever SQLERROR exit 2 rollback whenever OSERROR exit 3 rollback set serveroutput on set pages 999 var status_desc char(200) var status_code... (1 Reply)
Discussion started by: hidnana
1 Replies

8. Shell Programming and Scripting

Calling an Oracle Stored Procedure from Unix shell script

hai, can anybody say how to call or to execute an oracle stored procedure in oracle from unix... thanks in advance.... for ur reply.... by, leo (2 Replies)
Discussion started by: Leojhose
2 Replies

9. Shell Programming and Scripting

Execute an Oracle stored procedure from a shell scrip

Here is a snippet of my code: if then echo "\n Deleting all reports older than 24 hours. \n" >> $logfile ls -l $FileName >> $logfile ... (1 Reply)
Discussion started by: mh53j_fe
1 Replies

10. UNIX for Advanced & Expert Users

Oracle stored procedure.

I am using sqlplus. I have the stored procedure name. How can i print the stored procedure content? (2 Replies)
Discussion started by: kamil
2 Replies
Login or Register to Ask a Question