need help??
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: need help??

  1. #1
    Join Date
    Oct 2000
    Posts
    144

    Question

    Hi everyone,
    When I run the shell script to execute the SQL script so it can pick up the value
    from the loopcount. The SQL script does not recognize the $. How can I make it
    to work. Below is the sample script.

    shell script:
    loopcount=5

    SQL script:
    DEFINE apple = $loopcount


  2. #2
    Join Date
    May 2002
    Posts
    42
    you can pass the var into sqlplus with the &n (n being the number of the argument)

    ex
    ksh
    i=1
    while [ $i -lt 10 ]
    do
    echo $i
    sqlplus -s @ $i
    ((i=$i+1))
    done

    in you will have a &1. &1 will = $i

  3. #3
    Join Date
    May 2002
    Posts
    42
    html did not show my less then and greater then chars


    while [ $i -lt 10 ]
    do
    echo $i
    sqlplus -s @myscript.sql $i
    ((i=$i+1))
    done

    in myscript.sql you will have a &1. &1 will = $i

  4. #4
    Join Date
    Oct 2000
    Posts
    144
    Hi Alenz,
    I want to thank you for your help. I tried your method and I'm not sure if I did it
    correctly. It didn't work. My sql scripts are below. It didn't recognize the $.

    ksh script:
    i=1
    sqlplus -s @myscript.sql $i

    sql script:
    SET SERVEROUTPUT on
    DEFINE loopcount = $i

    DECLARE
    v_test NUMBER := &loopcount;
    BEGIN
    DBMS_OUTPUT.PUT_LINE(v_test);
    END;
    /

  5. #5
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250
    I think this should work :
    this is the shell script
    i=1;export i
    sqlplus -s system/complex < set serverout on;
    define loopcount=$i
    declare
    v number :=&loopcount;
    begin
    dbms_output.put_line(v);
    end;
    KN

  6. #6
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250
    oh it missed out some characters let me try again basically there is an exclamation mark after two less than signs at the end of the sqlplus line and an exclamation mark at the end of the file:
    i=1;export i
    sqlplus -s system/complex < set serverout on;
    define loopcount=$i
    declare
    v number :=&loopcount;
    begin
    dbms_output.put_line(v);
    end;
    /
    !
    KN

  7. #7
    Join Date
    Oct 2000
    Posts
    144
    Hi Knarayan,
    thanks for your assistance but it didn't work. My OS is HP and I used
    kornshell script. It didn't work. It kept saying DEFINE and DECLARE not found.
    Below is my kornshell script.

    userid=apple
    export i=1

    $ORACLE_SQLPLUS -s $apple/$apple < set serverout on;

    DEFINE loopcount = $i

    declare
    v_test number := &loopcount;
    begin
    dbms_output.put_line(v_test);
    end;
    /

  8. #8
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250
    I think that is bcoz your shell script is incorrect.
    What is oracle_sqlplus??
    KN

  9. #9
    Join Date
    Oct 2000
    Posts
    144
    oracle_sqlplus is where i defined to get the sqlplus tool to run.

    shell script:
    export oracle_sqlplus=/opt/app/oracle/....

  10. #10
    Join Date
    May 2002
    Posts
    42
    Originally posted by Trina
    Hi Alenz,
    I want to thank you for your help. I tried your method and I'm not sure if I did it
    correctly. It didn't work. My sql scripts are below. It didn't recognize the $.

    ksh script:
    i=1
    sqlplus -s @myscript.sql $i

    sql script:
    SET SERVEROUTPUT on
    DEFINE loopcount = $i

    DECLARE
    v_test NUMBER := &loopcount;
    BEGIN
    DBMS_OUTPUT.PUT_LINE(v_test);
    END;
    /

    change
    DEFINE loopcount = $i
    to
    DEFINE loopcount = &1

    does this work?

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