4 Replies Latest reply: Oct 2, 2012 9:26 AM by dkoracle RSS

    Passing values to sql file

    dkoracle
      Hi Team,

      I need your help in fixing one issue.

      Need to pass multiple values non-interactively while executing SQL script. Example one like given below

      Requirement:

      After executing one sql script it will prompt these
       Enter value for read_only_mode: 
      Enter value for enter_ledger_id:
      Enter value for start_gl_date:
      Enter value for end_gl_date:
      I would like to pass values non-interactively so i framed like one but resulted in error..pls help me on this

      My Prepared Code :
      #!/usr/bin/bash
      P1="N"
      P2="2021"
      P3="`date '+%Y%m%d'`"
      P4="`date '+%Y%m%d'`"
      sqlplus -s apps/apps <<!
      @del_orphans_xla_120.sql
      ${P1}
      ${P2}
      ${P3}
      ${P4}
      commit;
      exit
      !
      OUTPUT:
       sh -x del_orphans.sh
      + P1=N
      + P2=2021
      ++ date +%Y%m%d
      + P3=20121001
      ++ date +%Y%m%d
      + P4=20121001
      + sqlplus -s apps/apps
      Enter value for read_only_mode: Enter value for enter_ledger_id: Enter value for start_gl_date: Enter value for end_gl_date: declare
      *
      ERROR at line 1:
      ORA-06502: PL/SQL: numeric or value error: character string buffer too small
      ORA-06512: at line 3
      
      
      
      Commit complete.
      Regards,
      Dilip

      Edited by: dkoracle on Oct 2, 2012 1:15 AM
        • 1. Re: Passing values to sql file
          Catch-22
          For security reasons you should not specify the username/password at the command prompt since everyone can see it using the ps command. Using the exclamation mark (!) as a "here document" marker is not recommended. Sqlplus exit includes commit. You will have to export the variables to use them in the environment of a child process.

          Perhaps the following will work:

          <pre>
          export p1="N"
          export p2="2021"
          export p3="`date '+%Y%m%d'`"
          export p4="`date '+%Y%m%d'`"
          sqlplus /nolog <<-EOT
          connect apps/apps
          @del_orphans_xla_120.sql
          $p1
          $p2
          $p3
          $p4
          exit
          EOT
          </pre>
          • 2. Re: Passing values to sql file
            dkoracle
            Hi Dude,

            Thanks for the help..

            But still it throws same error

            CODE:
            export p1="N"
            export p2="2021"
            export p3="`date '+%Y%m%d'`"
            export p4="`date '+%Y%m%d'`"
            sqlplus /nolog <<-EOT
            connect apps/apps
            @del_orphans_xla_120.sql
            $p1
            $p2
            $p3
            $p4
            exit
            EOT
            ERROR:
            SQL*Plus: Release 10.1.0.5.0 - Production on Tue Oct 2 07:07:51 2012
            
            Copyright (c) 1982, 2005, Oracle.  All rights reserved.
            
            SQL> Connected.
            SQL> Enter value for read_only_mode: Enter value for enter_ledger_id: Enter value for start_gl_date: Enter value for end_gl_date: declare
            *
            ERROR at line 1:
            ORA-06502: PL/SQL: numeric or value error: character string buffer too small
            ORA-06512: at line 3
            
            
            SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
            Regards,
            Dilip
            • 3. Re: Passing values to sql file
              Catch-22
              No idea. The following example works:
              $ cat prompt.sql
              accept value char format a30 prompt 'Enter value: '
              select &value from v$instance;
              
              $ cat passvar 
              #!/bin/bash
              export p1=status
              sqlplus -s /nolog <<-EOT
              connect / as sysdba
              @prompt
              $p1
              EOT
              
              $ ./passvar
              Enter value: old   1: select &value from v$instance
              new   1: select status from v$instance
              
              STATUS
              ------------
              OPEN
              • 4. Re: Passing values to sql file
                dkoracle
                Hi Dude,

                This one working fine slightly modified from parent code of yours :)
                export p1="N"
                export p2="2021"
                export p3="`date '+%d-%^h-%Y'`"
                export p4="`date '+%d-%^h-%Y'`"
                sqlplus /nolog <<-EOT
                connect apps/apps
                spool del_orphans.log
                @del_orphans_xla_120.sql $p1 $p2 $p3 $p4
                commit;
                spool off
                exit
                EOT
                Thanks a lot Dude :)

                Regards,
                Dilip