-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|