XML Execution in PL/SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: XML Execution in PL/SQL

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Posts
    101
    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




  2. #2
    Join Date
    Apr 2002
    Posts
    135
    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

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Gandhi,
    declare
    INSERTFLAG INT := 1;
    begin
    EXEC pkg.b_insert('XMLTABLE','1','ABC',INSERTFLAG);
    end;
    Cheers!
    Cheers!
    OraKid.

  4. #4
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Sorry!

    "EXEC" - This shld not be there

    Gandhi,
    declare
    INSERTFLAG INT := 1;
    begin
    pkg.b_insert('XMLTABLE','1','ABC',INSERTFLAG);
    end;
    Cheers!
    Cheers!
    OraKid.

  5. #5
    Join Date
    Nov 2000
    Posts
    101
    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

  6. #6
    Join Date
    Apr 2002
    Posts
    135
    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

  7. #7
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

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