8 Replies Latest reply: Jan 20, 2010 6:51 AM by SachinAhuja RSS

    Passing Parameter from Unix to SQL Script

    SachinAhuja
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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