How to Pass Value of Variable From PL/SQL Procedure to Shell Script
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How to Pass Value of Variable From PL/SQL Procedure to Shell Script

Hybrid View

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    does anyone know how to do this, for example

    Code:
    declare 
    fec date;
    begin
    select sysdate
    into fec
    from dual;
    end;
    /
    I then have to get variable fec to a shell variable
    I know how if I do shell variable="" where "" is the whole chunk of above (using EOF)

    Code:
    #!/bin/ksh
    
    export fec=`sqlplus -s << EOF
    loop/loop@loop
    set pagesize 0
    set feed off
    set serveroutput on
    variable fec varchar2(20)
    begin
    select sysdate
    into :fec
    from dual;
    dbms_output.put_line(:fec);
    end;
    /
    exit
    EOF`
    
    echo $fec
    but is there another cleaner way?

    I dunno how :(

    [Edited by pando on 11-12-2001 at 07:52 AM]

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    the only way I know to do this is to use a script with EOF as you said, but I do not think there is another way to do this since you cannot interface your shell and sqlplus using another way :/

  3. #3
    Join Date
    Apr 2001
    Posts
    219
    You can call your script from PL/SQL, if this fits:

    http://www.dbasupport.com/forums/sho...threadid=14440
    ______________________
    Applications come and go,
    but the data remains!

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hm no I want to get those variables in shell so I can export them in the environment and used by sub-processes called by more shell scripts

  5. #5
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    note : you can do it a bit simpler than what you used Pando :

    Code:
    #!/bin/ksh
    
    fec=$(sqlplus -s loop/loop@loop << EOF
    set pagesize 0
    set feed off
    set serveroutput on
    select sysdate
    from dual;
    EOF)
    
    echo $fec

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by pando

    but is there another cleaner way?

    I dunno how :(

    [Edited by pando on 11-12-2001 at 07:52 AM]
    That's the only way I know to do it. You could omit the dbms_output call and just issue a select statement, but it's basically the same method.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    oops I put that code coz it was simpler to read but the values I am getting should come from more complex pl/sql blocks
    So this is the only way... hmm

    Btw pipo if you put l/p@connect_string after
    Code:
     << EOF
    people cant see them if they do ps

    [Edited by pando on 11-12-2001 at 10:26 AM]

  8. #8
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    you're right for the user/pwd

    note that you can also do :

    Code:
    sqlplus -s << EOF | while read VAR1 VAR2
    user/pwd
    ...
    EOF
    do
    echo $VAR1
    echo $VAR2
    done
    of course if you are bringing complex values, you can either awk the result or use a delimiter and cut the returned string

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