-
Dear Friends,
I have a table as XMLTABLE(rid VARCHAR2(10), record XMLTYPE).
I am creating a package with procedure. The procedure follows:
SQL> get pkg1
1 CREATE OR REPLACE PACKAGE pkg as
2 PROCEDURE B_INSERT (TABLENAME IN VARCHAR2, REC IN VARCHAR2, XMLREC IN BLOB,
3 INSERTFLAG IN OUT INT);
4 END pkg;
5 CREATE OR REPLACE PACKAGE BODY pkg AS
6 PROCEDURE B_INSERT (TABLENAME IN VARCHAR2, REC IN VARCHAR2, XMLREC IN BLOB,
7 INSERTFLAG IN OUT INT) IS
8 BEGIN
9 EXECUTE IMMEDIATE 'INSERT INTO '||TABLENAME||
10 'VALUES (:bREC, :bXMLREC)
11 USING REC,XMLREC;
12 INSERTFLAG := 1;
13 EXECPTION
14 WHEN DUP_VAL_ON_INDEX THEN
15 EXECUTE IMMEDIATE 'UPDATE '||TABLENAME||
16 'SET XMLRECORD = :bxmlrec WHERE RECID= :brec'
17 INSERTFLAG := 0;
18 END;
19* END B_INSERT;
20 .
When i execute a procedure it shows the following erorr.
SQL> EXEC pkg.b_insert('XMLTABLE','1','ABC');
BEGIN pkg.b_insert('XML','1','ABC'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'B_INSERT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Could u pls help on this
Thanks
gandhi
OCP-DBA
-
hello gandhi(i like your name),
you have four arguments and the fourth argument being an IN OUT one.
so ,,
you should pass four arguments to the procedure obviously
cheers,
-
Gandhi,
declare
INSERTFLAG INT := 1;
begin
EXEC pkg.b_insert('XMLTABLE','1','ABC',INSERTFLAG);
end;
Cheers! :)
-
Sorry!
"EXEC" - This shld not be there
Gandhi,
declare
INSERTFLAG INT := 1;
begin
pkg.b_insert('XMLTABLE','1','ABC',INSERTFLAG);
end;
Cheers!
-
Hi Sona, Balaji,
I did the same and the result is also the same.
SQL> declare
2 INSERTFLAG INT := 1;
3 begin
4 pk.b_insert('XMLTABLE','1','ABC',INSERTFLAG);
5 end;
6 /
pk.b_insert('XMLTABLE','1','ABC',INSERTFLAG);
*
ERROR at line 4:
ORA-06550: line 4, column 1:
PLS-00306: wrong number or types of arguments in call to 'B_INSERT'
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
I excluded the INSERTFLAG in the procedure. Then i executed with three arguements, i am getting the same result.
Thanks Sona for your comments, i born on OCT-2nd.
Thanks
gandhi
-
hai i tried you procedure ,
as far as i tried,
if you change the clob i/p parameter to varchar and try ,the procedure call works perfectly.
I do know exactly about passing a clob type to a stored procedure.
but you can do like this if you want to insert clob into the table
create table t ( x int, y clob );
create or replace procedure p( p_x in int, p_new_text in varchar2 )
as
l_clob clob;
begin
insert into t values ( p_x, empty_clob() ) returning y into l_clob;
dbms_lob.write( l_clob, 1, length(p_new_text), p_new_text );
end;
make necessary changes to adopt to your procedure
-