Forum Stats

  • 3,854,665 Users
  • 2,264,397 Discussions
  • 7,905,749 Comments

Discussions

compiling Store procedure

745247
745247 Member Posts: 46
edited Jan 25, 2010 10:19AM in SQL & PL/SQL
Hallo together,

i am using Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production and PL/SQL Release 11.1.0.6.0 - Production.

I have a store procedure without bind variables, now I write this store procedure with bind variable.
But i can not compile it with the orcale enterprise.

I got following error message:

Error

Committing Failed: Missing IN or OUT parameter at index:: 1

Can anyone help me?


Best regards

Andreas
Tagged:

Answers

  • riedelme
    riedelme Member Posts: 3,528
    user8964782 wrote:
    Hallo together,

    i am using Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production and PL/SQL Release 11.1.0.6.0 - Production.

    I have a store procedure without bind variables, now I write this store procedure with bind variable.
    But i can not compile it with the orcale enterprise.
    Committing Failed: Missing IN or OUT parameter at index:: 1
    Are you trying to compile it with Oracle enterprise manager? Most of us use tools like TOAD, SQL*Developer, or SQL*PLUS

    Can you post the code you care trying to compile, repeating the error messages you are getting?
  • 745247
    745247 Member Posts: 46
    Hi,

    i must do it over the oracle enterprise manager is an order from my boss. But I will ask him again.
    I can´t post the whole store procedure here. But i can tell you what i have change in the store procedure.

    before:

    ...
    sqlstr := 'SELECT * FROM table WHERE column = ' || key || ' ';
    cur := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cur, sqlstr, DBMS_SQL.V7);
    DBMS_SQL.DEFINE_COLUMN(cur, 1, rID,18);
    ...


    after:

    ...
    sqlstr := 'SELECT * FROM table WHERE column = :bind_var ';
    cur := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cur, sqlstr, DBMS_SQL.V7);
    DBMS_SQL.BIND_VARIABLE( cur, ':bind_var', key );
    DBMS_SQL.DEFINE_COLUMN(cur, 1, rID,18);
    ...
  • riedelme
    riedelme Member Posts: 3,528
    user8964782 wrote:
    ...
    sqlstr := 'SELECT * FROM table WHERE column = ' || key || ' ';
    cur := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cur, sqlstr, DBMS_SQL.V7);
    DBMS_SQL.DEFINE_COLUMN(cur, 1, rID,18);
    . . .
    sqlstr := 'SELECT * FROM table WHERE column = :bind_var ';
    cur := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cur, sqlstr, DBMS_SQL.V7);
    DBMS_SQL.BIND_VARIABLE( cur, ':bind_var', key );
    DBMS_SQL.DEFINE_COLUMN(cur, 1, rID,18);
    My DBMS_SQL is a bit rusty - I try not to use it whenever possible due to its complexity. Ref cursors usually do the same thing with much less effort.

    The problem may be in the line
    sqlstr := 'SELECT * FROM table WHERE column = :bind_var ';
    Check the documentation and find out how DBMS_SQL expects you to populate ":bind_var" - you are doing this but there's something Oracle does not like; possibly you are doing itne DBMS_SQL.BIND_VARIABLE in the wrong place (does it go before the parse?). Also, find an example where this is done and look at it carefully.

    Good hunting!
  • 745247
    745247 Member Posts: 46
    riedelme wrote:
    Ref cursors usually do the same thing with much less effort.
    Have you a short example?
    Maybe it is easier to change from DBMS_SQL to Ref cursors.
  • fsitja
    fsitja Member Posts: 657 Silver Badge
    edited Jan 25, 2010 10:19AM
    From what you have posted there is no need to use dynamic SQL at all. So you could just stick to static SQL and save yourself some headaches having to debug this code.

    Static SQL in PL/SQL is automatic converted to use bind variables and will spare you the effort of doing it explicitly, as it would happen were you using dynamic SQL.
    Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
    Connected as fsitja
    
    SQL> set serveroutput on
    SQL> 
    SQL> declare
      2    cur sys_refcursor;
      3    v_bind_test number := 5;
      4    v_output varchar2(30);
      5  begin
      6    open cur for
      7      select t.table_name
      8        from all_tables t
      9       where rownum <= v_bind_test;
     10    fetch cur
     11      into v_output;
     12    loop
     13    dbms_output.put_line(v_output);
     14    exit when cur%notfound;
     15    fetch cur
     16      into v_output;
     17    end loop;
     18    close cur;
     19  end;
     20  /
     
    DUAL
    SYSTEM_PRIVILEGE_MAP
    TABLE_PRIVILEGE_MAP
    STMT_AUDIT_OPTION_MAP
    AUDIT_ACTIONS
    AUDIT_ACTIONS
     
    PL/SQL procedure successfully completed
     
    SQL> 
This discussion has been closed.