How can i pass an shell Array to sql Session ?.

How can i pass an shell Array to sql Session ?.

Post by Raj » Wed, 03 May 2000 04:00:00

     Is there anyway can i pass an Array from shell script to SQL
session and accessing all elements from SQL ?.



How can i pass an shell Array to sql Session ?.

Post by Howard Bryde » Sat, 13 May 2000 04:00:00

Let's assume Korn (or Posix) shell, and SQL*Plus for the Oracle
RDBMS.  Let the shell array be defined as

set -A x one two three

Now SQL*Plus has no direct means of dereferencing shell
environment variables (although you could exploit the Oracle
DBMS_PIPE system for this kind of thing).  Instead it sets up
variables with the DEFINE command.

So a quick hack might be to prepare a SQL*Plus script as a set of
DEFINE commands instantiating variables with the values of the
array, e.g.

print "
DEFINE x1 = ${x[0]}
DEFINE x2 = ${x[1]}
DEFINE x3 = ${x[2]}


1. How to pass parm from prompt to shell to SQL*Plus program

I'm trying to pass a parameter to a SQL*Plus program (Oracle 8i1.3)
from the unix (korn shell) prompt.  I can pass it from the shell to
the SQL*Plus program, but can't get it from the submit command to the
shell, and then to the SQL*Plus program.  I don't know which flavor of
unix we have, we are migrating to it now, Compaq True64(?).

This passes a parm (1996, a character value) to the program fine......
(in the .ksh file)

in SQL*Plus program.....
where year = &1

I want to submit the program and pass the year parm to the shell, and
can't get the code right.  This is as close as I get, and get the word
'default' erroneously being passed instead of the value(1996).  How do
I change it?

ie)...from the prompt

. program1.ksh 1996

(in the shell......)

in the SQL*Plus program....

...where year = &1                 ???

...I've tried quotes, double quotes, grave quotes, ampersands, etc...
Newbie here, in case you can't tell.
so, how do I submit it, what's the code in the shell for passing it,
and how do I refer to it in the SQL*Plus program???

2. Q: Accessing Linux partition from DOS

3. passing array variables to spawned shells

4. ps

5. Passing Shell Enviorment to Telnet Session

6. Sun3 NetBSD site in US?

7. Convert C shell array to Bourne shell

8. Bridge

9. OT: Passing Unix Variables to a PL/SQL Stored Procedure

10. How to pass a variable from a shell script to another shell script...

11. passing values from unix to pl/sql

12. ksh/sh help with exiting from SQL session