Can we access the a environment variable in a SQL Script
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Can we access the a environment variable in a SQL Script

  1. #1
    Join Date
    Dec 2005
    Posts
    3

    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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    nope you cant, plsql cant access the os. You would need some sort of java stored proc to do that

  3. #3
    Join Date
    Mar 2006
    Posts
    40
    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.
    go to the oracle clinic
    Quick and easy Oracle database support and consulting

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  5. #5
    Join Date
    Dec 2005
    Posts
    3
    I want to just access the environment variable in the sql script can anyone help

    Thanks
    Denzil

  6. #6
    Join Date
    Mar 2006
    Posts
    40
    The script above will do that, call the stored proceure proc with the envirionemnt variable as it's parameter.
    go to the oracle clinic
    Quick and easy Oracle database support and consulting

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width