-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|