-
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,
Good Judgement comes from Experience.
Experience comes from Bad Judgement
-
Gandhi,
declare
INSERTFLAG INT := 1;
begin
EXEC pkg.b_insert('XMLTABLE','1','ABC',INSERTFLAG);
end;
Cheers!
Cheers!
OraKid.
-
Sorry!
"EXEC" - This shld not be there
Gandhi,
declare
INSERTFLAG INT := 1;
begin
pkg.b_insert('XMLTABLE','1','ABC',INSERTFLAG);
end;
Cheers!
Cheers!
OraKid.
-
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
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|