Can we access the a environment variable in a SQL Script

[denzilrohit]

Hi there,

I have Stored Procedure on my unix box and i need to access the environment variable defined on the unix box in the Stored Procedure. I am calling this stored procedure using an SQL file.
Can I pass the environment name to my Stored Procedure using an SQL file?

For example:

We have an environment variable called '$e2DefaultEnv' that has the value as 'TECH'.
I have a Stored Porcedure called "createfilters" in my Oracle DB under a schema called "E2".

Can I call the stored procedure as below?

E2.createfilters('${e2DefaultEnv}'); exit;


Any pointers will be helpful

Thanks

[davey23uk]

nope you cant, plsql cant access the os. You would need some sort of java stored proc to do that

[oracli]

you can probaly use from your UNIX box:

Make a shell script to generate the SQL code for calling the procedure then execute that SQL code in sqlplus:

make_sql.sh:

#!/bin/bash
echo "
spool proc.log
connect user/password@db
begin
proc($e2DefaultEnv);
end;
spool off
exit
" > test.sql
sqplus /nolog @test.sql

then run the make_sql.sh which will make the SQL script and run it using SQL*Plus. The stored procedure in is called proc in the example. You will have a log of the operation in the proc.log file.

[davey23uk]

would need to wrap the env variable in quotes for that to work

make_sql.sh:

#!/bin/bash
echo "
spool proc.log
connect user/password@db
begin
proc($e2DefaultEnv);
end;
/
spool off
exit
" > test.sql
sqlplus /nolog @test.sql

[denzilrohit]

I want to just access the environment variable in the sql script can anyone help

Thanks
Denzil

[oracli]

The script above will do that, call the stored proceure proc with the envirionemnt variable as it's parameter.