Can we access the a environment variable in a SQL Script

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

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

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.

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

Code:

---

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

---

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

Thanks
Denzil

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