Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    6

    Unanswered: simple substitution variables in sql script?

    hi...

    I used to do this all the time, but now in Oracle 10 it doesn't seem to want to
    work....

    Simplest possible script.... "testsql.sql"

    -- test
    set serveroutput on size 1000000
    execute test_stored_proc(&1,&2)
    /
    QUIT

    I execute as 'sqlplus acct/pw@db @testsql.sql 'abcd' 'efgh'
    Shouldn't that pass 'abcd' into &1 and 'efgh' into &2?

    But instead I get the following output:

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
    With the Partitioning, Real Application Clusters, OLAP and Data Mining options

    SP2-0552: Bind variable "2" not declared.
    Elapsed: 00:00:00.00
    SP2-0103: Nothing in SQL buffer to run.

    Why is it even mentioning bind variables? I did try them by the way... I tried...

    VAR X VARCHAR2
    VAR Y VARCHAR2

    BEGIN
    :X := &1;
    :Y := &2;

    set serveroutput on size 1000000
    execute test_stored_proc(:X,:Y)
    END;
    /
    QUIT

    Running it as above, I get the same oracle output...
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
    With the Partitioning, Real Application Clusters, OLAP and Data Mining options

    SP2-0552: Bind variable "2" not declared.
    Elapsed: 00:00:00.00

    This is very frustrating. I know I've done this before... The simple way (first example) without any problems...

    thanks
    mjr
    quine@sonic.net

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    bcm@bcm-laptop:~$ sqlplus dbadmin/admindb @test1 'A' '1'
    
    SQL*Plus: Release 10.2.0.4.0 - Production on Mon Aug 31 18:33:24 2009
    
    Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
    
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> set serveroutput on
    SQL> --00552,0, "Bind variable \"%.*s\" not declared.\n"
    SQL> --// *Cause:  The specified bind variable was not declared.
    SQL> --// *Action: Run the VARIABLE command to check that the bind variables
    SQL> --//	   you used in your SQL statement exist. Before running a SQL
    SQL> --//	   statement with bind variables, you must use the VARIABLE
    SQL> --//	   command to declare each variable.
    SQL> declare
      2  X VARCHAR2(31);
      3  Y VARCHAR2(31);
      4  begin
      5  dbms_output.enable(10000);
      6  X :='&1';
      7  Y :='&2';
      8  dbms_output.put_line('var1 = '||X|| ' var2 = ' || Y);
      9  end;
     10  /
    old   6: X :='&1';
    new   6: X :='A';
    old   7: Y :='&2';
    new   7: Y :='1';
    var1 = A var2 = 1
    
    PL/SQL procedure successfully completed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2009
    Posts
    6
    Hello...

    Thank you for the suggestion... But you appear to be making it work from INSIDE sqlplus and I must make it work from the outside... I tried modifying my script along your lines...

    -- test
    declare
    X VARCHAR2(8);
    Y VARCHAR2(8);

    BEGIN
    dbms_output.enable(1000000);
    :X := '&1';
    :Y := '&2';
    execute test_stored_proc(:X,:Y)
    END;
    /

    I still get the same result... I should point out that "test_stored_proc" is the simplest of stored procs:

    create or replace test_stored_proc (A IN VARCHAR2, B IN VARCHAR2) AS

    BEGIN
    DBMS_OUTPUT.PUT_LINE('First argument is: '||A);
    DBMS_OUTPUT.PUT_LINE('Sec argument is: '||B);

    END;
    /

    If I do nothing else but substitute literals into the sqlscript it works fine... Making the script as simple as possible:

    -- test
    set serveroutput on size 1000000
    execute test_stored_proc('abc', 'def')
    /

    then 'sqlplus acct/pw@db @testsql.sql' results in:
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
    With the Partitioning, Real Application Clusters, OLAP and Data Mining options

    First argument is: abc
    Sec argument is: def

    PL/SQL procedure successfully completed.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    She did make it work from outside. Look again she is passing arguments when invoking a script.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Aug 2009
    Posts
    6
    Hello...

    It looks like she is:

    1. executing a script from the outside, then
    2. getting the same error message from sqlplus (unbound variable) and
    3. correcting the problem by binding the variables INSIDE sqlplus (otherwise why all the SQL> lines???

    I thought this might be a clue and I tried to bind the variables INSIDE my stored proc, but that didn't work either... So..... In the end, my employer wants a working system and not necessarily the most elegant one... Since the minimal sql script I need is only 2 lines long (well 3 with the QUIT at the end), and since I'm using PERL to control the workflow and execute sqlplus handing it the sql script, I decided simply to CREATE the script on the fly with every run and that way I can pass the arguments to the stored proc as literals! At least this works!

    Thanks for all the help attempts though... Appreciate it.

    quine@sonic.net

    P.S. my very first attempt with all this was to execute the stored proc through the perl DBI. This worked but I could not find a way to recover the DBMS_OUTPUT statements I use to convey final status to the controlling work-flow -- no I don't put DBMS_OUTPUT statements in a loop, ever! :-)

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    declare
    X VARCHAR2(31);
    Y VARCHAR2(31);
    begin
    dbms_output.enable(10000);
    X :='&1';
    Y :='&2';
    dbms_output.put_line('var1 = '||X|| ' var2 = ' || Y);
    end;
    /
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •