DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Invalid sql statement error

  1. #1
    Join Date
    Apr 2001
    Posts
    60

    Invalid sql statement error

    Hi

    I have package/procedure which I wanted to run with the parameters in pl/sql.

    When I run the pl/sql its giving an error 'ORA-00900: invalid SQL statement'. The procedure parameters are more.

    When I call the same procedure in sqlplus,with its parameters, its working fine.

    Following is the coding in pl/sql.


    DBMS_SQL.PARSE(v_cursor, 'exec INVENTORY_PART_IN_STOCK_API.Receive_Part_With_Posting (:contract,art,:star1,:loc,:star2,:star3,1,:star4,:nrec,null,:qty,0,null,null,null,null,null,null,null,null,nul l,null,null,:qval)', DBMS_SQL.V7);

    DBMS_SQL.BIND_VARIABLE(v_cursor, ':contract', 'MBCP');
    DBMS_SQL.BIND_VARIABLE(v_cursor, 'art', 'CC-01-01');
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':loc', 'BIN-A7');
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':qty', 500);
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':qval', 5000);
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':nrec', 'NREC');
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':star1', '*');
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':star2', '*');
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':star3', '*');
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':star4', '*');
    rammi

  2. #2
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Why don't you use Native Dynamic SQL.. Its much more easy than old dynamic sql.. Which version of Oracle are you using..

    Try this

    Code:
    SQL> CREATE PROCEDURE test (aa IN VARCHAR2) IS
      2  BEGIN
      3  DBMS_OUTPUT.PUT_LINE('Val is '||aa);
      4  END;
      5  /
    
    Procedure created.
    
    SQL> BEGIN
      2  EXECUTE IMMEDIATE 'BEGIN test(''Hello''); END;';
      3  END;
      4  /
    Val is Hello
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    
    You need to embed the procedure execution in BEGIN/END block 
    as shown in bold marked statement...
    HTH

    Sameer
    Last edited by Sameer; 01-24-2003 at 09:30 AM.

Posting Permissions

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


Click Here to Expand Forum to Full Width