This discussion is archived
8 Replies Latest reply: Jan 20, 2010 4:51 AM by SachinAhuja RSS

Passing Parameter from Unix to SQL Script

SachinAhuja Newbie
Currently Being Moderated
Hi All,

Not sure if this is the right place to put my issue, but thought as it's somewhere related too.

I am calling SQL Script from Unix Shell Script.

From Unix Shell we are also passing parameters to SQL Script, but when there is space in some parameter then it's not preserving the space, it's splitting one parameter to two parameters.

Like if i pass "Account Payables" from Unix then it's making "Account" as one parameter & "Payables" as another parameter. How can I preserve it as one parameter.

Thanks
Sachin Ahuja
  • 1. Re: Passing Parameter from Unix to SQL Script
    BluShadow Guru Moderator
    Currently Being Moderated
    Usually this is achieved by putting single or double quotes around the string. Depends on what exactly you are doing, so if you were to show us an example of the script we could probably help some more.
  • 2. Re: Passing Parameter from Unix to SQL Script
    bluefrog Expert
    Currently Being Moderated
    does this work:
    SOME_STR="Account Payables"
    sqlplus apps/some_password@oracle_sid <<EOF
    set serveroutput on
    begin
      dbms_output.put_line('${SOME_STR}');
    end;
    /
    exit
    EOF
    ?
  • 3. Re: Passing Parameter from Unix to SQL Script
    SachinAhuja Newbie
    Currently Being Moderated
    Hi,

    I have something like below in my Shell Script

    sqlplus -s $APPSUSER/$APPSPASS @$XX_TOP/sql/xyz.sql 'Account Payable' 'Receivable'

    now in sql I am capturing value in variable something like as below

    DECLARE

    l_var1 varchar2(100):='&&1';
    l_var2 varchar2(100):='&&2';

    BEGIN
    do something;
    END;

    When I am running shell script it's not able to preserve the space.

    Account is going as 1st parameter & Payable is going as 2nd parameter and Receivable is getting lost.

    Please suggest.

    Thanks
    Sachin
  • 4. Re: Passing Parameter from Unix to SQL Script
    bluefrog Expert
    Currently Being Moderated
    The same principle as per my first post should apply! Does the first script I posted work in your shell, since I ran it in the korn shell.

    Your script could be something like this:
    VAR1="Account Payable"
    VAR2="Receivable"
    sqlplus -s $APPSUSER/$APPSPASS @$XX_TOP/sql/xyz.sql <<EOF
    set serveroutput on
    declare
    
      l_var1 varchar2(100):='${VAR1}';
      l_var2 varchar2(100):='${VAR2}';
    
    begin
      dbms_output.put_line(l_var1);
      dbms_output.put_line(l_var2);
    end;
    /
    exit
    EOF
    
    lonss00138:ostuat19$ r 818
    . test_str.ksh
    Account Payable
    Receivable
  • 5. Re: Passing Parameter from Unix to SQL Script
    SachinAhuja Newbie
    Currently Being Moderated
    Thanks a lot for your response.

    But it's still same. Can you try passing Variables as parameter to SQL Script rather then trying to capture value of variable in SQL.
  • 6. Re: Passing Parameter from Unix to SQL Script
    bluefrog Expert
    Currently Being Moderated
    not sure what you want, but it seems rather simpler than what I had initially thought!
    lonss00138:ostuat19$ more test_str.sql
    set serveroutput on
    declare
     
      l_var1 varchar2(100):='&&1';
      l_var2 varchar2(100):='&&2';
     
    begin
      dbms_output.put_line(l_var1);
      dbms_output.put_line(l_var2);
    end;
    / 
    exit
    
    
    lonss00138:ostuat19$ more test_str.ksh
    VAR1="Account Payable"
    VAR2="Receivable"
    sqlplus -s apps/n3wsys@dolnsgc3 @test_str.sql $VAR1 $VAR2
    
    
    lonss00138:ostuat19$ . test_str.ksh
    old   3:   l_var1 varchar2(100):='&&1';
    new   3:   l_var1 varchar2(100):='Account Payable';
    old   4:   l_var2 varchar2(100):='&&2';
    new   4:   l_var2 varchar2(100):='Receivable';
    Account Payable
    Receivable
  • 7. Re: Passing Parameter from Unix to SQL Script
    SachinAhuja Newbie
    Currently Being Moderated
    Below is my Shell

    VAR1="Account Payable"
    VAR2="Receivable"
    sqlplus -s apps/apps @try.sql $VAR1 $VAR2

    Then below is my SQL

    SET SERVEROUTPUT ON
    DECLARE
    v_val1 varchar2(100):='&&1';
    v_val2 varchar2(100):='&&2';
    BEGIN
    dbms_output.put_line('v_val1 is '||v_val1);
    dbms_output.put_line('v_val2 is '||v_val2);
    END;
    /
    exit


    And below is my O/p

    UNIXPROMPT$ sh try.sh
    old 2: v_val1 varchar2(100):='&&1';
    new 2: v_val1 varchar2(100):='Account';
    old 3: v_val2 varchar2(100):='&&2';
    new 3: v_val2 varchar2(100):='Payable';
    v_val1 is Account
    v_val2 is Payable

    PL/SQL procedure successfully completed.

    Input truncated to 4 characters


    Account & Payable is getting split on two parameters.
  • 8. Re: Passing Parameter from Unix to SQL Script
    SachinAhuja Newbie
    Currently Being Moderated
    Ahhh, It worked now. Thought to post it, might benefit someone too.

    Changed my Shell to something like as below, placed double quotes & single quotes. And now working as expected.

    VAR1="Account Payable"
    VAR2="Receivable"
    echo $VAR1
    echo $VAR2
    sqlplus -s apps/apps @try.sql "'$VAR1'" "'$VAR2'"


    Thanks to all for your kind help

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points