This discussion is archived
4 Replies Latest reply: Oct 2, 2012 7:26 AM by dkoracle RSS

Passing values to sql file

dkoracle Newbie
Currently Being Moderated
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
    Dude! Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    Dude! Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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