How to get Unix Environment variables into pl/sql session

[Muskaan]

Hi all,

I am running a PL/SQL block in which I need the values of some environment variables. Does anyone know how this can be done ?

Thanks in advance,
Muskaan

 

[parbhani]

Hi there ,

I have used it in this way . Suppose there is a env var $MYNAME which you want to refer in PL/SQL , write a shell script like this ,

#!/usr/bin/ksh # or whatever

sqlplus -s uname/pass <<EOF
declare
v_emp emp%ROWTYPE ;
begin
select * into v_emp from emp where
name = '$MYNAME' ;
end;
/
EOF

Thats it .... give permissions and run it .
Do not bother about the single quotes (') around the veriable .

Cheers

 

[Muskaan]

Hi parbhani,

I have a HUGE pl/sql block defined within a *.sql file, I cannot possibly move it to the shell script, nor can I pass so many variables as argument. Surely there must be a way ?

Thanks for your time!

Most problems are nothing but lack of ideas.

 

[SantaMufasa]

Muskaan,

The method that I use for passing external data into PL/SQL is by getting the data into a flat file (in Unix you can pipe the contents of the system variables into a file), then read the flat file using PL/SQL flat-file-handling capabilities. Are you familiar with flat-file-handling mechanisms in PL/SQL?

Dave

 

[parbhani]

Hi Muskaan ,

I am not passing anything as parameters to the shell script.
If u see my script ,

1 #!/usr/bin/ksh # or whatever
2
3 sqlplus -s uname/pass <<EOF
4 declare
5 v_emp emp%ROWTYPE ;
6 begin
7 select * into v_emp from emp where
8 name = '$MYNAME' ;
9 end;
10 /
11 EOF

No parameter is passed from command line .
What I have to do is , just add one line between the first line ( line no. 2 ) and sqlplus is ,

export MYNAME=&quot;MUSKAAN&quot;

I have used this in a PL/SQL code more than 2000 lines long and I was to pass more tha 10 parameters.

The final script will look like this ,

1 #!/usr/bin/ksh # or whatever
2 export MYNAME=&quot;MUSKAAN&quot;
3 sqlplus -s uname/pass <<EOF
4 declare
5 v_emp emp%ROWTYPE ;
6 begin
7 select * into v_emp from emp where
8 name = '$MYNAME' ;
9 end;
10 /
11 EOF

Have a carefull look , THERE IS NO COMMAND LINE PARAMETER PASSING. All magic is done by <<EOF syntex .

Cheers.

 

[sem]

Dave, I suppose that in many cases some extra sql*plus capabilities, such as formatting queries, bind/lexical variables, at last spooling are in use, so sql*plus script in fact is something more than sql script and it can not be invoked via dynamic sql.

Muskaan, I suppose you may easily add a couple of lines to any sql*plus script to get executable from it. But can you clarify your task a bit? Do you need to invoke this script from client machine to read server side environment variables? Or both sql*plus and database work within the same environment?

Regards, Dima

 

[Muskaan]

Hi Dima,
The task I need to perform involves invoking a pl/sql script on server side to read server(same) side environment variables. What I need to implement is quite complex but the simplified form of the problem is:
there is an environment variable age_in_months. I need to use the value of this variable inside a pl/sql block.

Dave, I know how to parse a file using the utl_file package. Maybe that is what I will eventually have to do. You were also suggesting utl_file, weren't you ?

Parbhani, I know your suggested code does not have any command line parameters, but your sql block is written in a ksh script, which I cannot do.
Thanks,
Muskaan


Most problems are nothing but lack of ideas.

 

[SantaMufasa]

Yes, Muskaan, My suggestion (if applicable for you) is to store in a flat file, the environment-variable value(s) you need, then use utl_file functionality to read the flat-file values into your program.

Dave

 

[carp]

And if/when you upgrade to Oracle 9i, you will be able to use this flat file to populate an external table (which you may then query upon directly).

 

[Muskaan]

Thanks everyone, for your time and suggestions. I am implementing the utl_file solution. We do not have the 9i yet, but when that happens I will look at the solution that carp suggested.
thanks again
Muskaan

***Most problems are nothing but lack of ideas***

 

[dsanchez2]

Why don't you just create an &quot;environment&quot; table and store this information in it? For example:

Create table environment
(
variable_name varchar2(100),
value varchar2(100)
);

Then you will only need to query this table to obtain the information that you need.

If you require that the value of the variable to be different for different users, then add a &quot;username&quot; column to the table.

 

[Muskaan]

Hi dsanchez2, I could do that, only one would have to maintain the env table up to date. These env variables are anyway maintained for other shell scripts, they will now be parsed by pl/sql block from a flat file. Values for the flat file will be picked from env during every run, so there is no botheration of maintaining these values at two places. Thoughts ?
Thanks,
Muskaan

***Most problems are nothing but lack of ideas***