0

I have a stored procedure with an input parameter of type CLOB. This parameter is used as follows in the procedure to insert data into the table.

PROCEDURE sp_Proc1(ret_Num1 OUT INTEGER, tmpData IN CLOB)
AS
BEGIN

    INSERT INTO MyTable  
    WITH S AS (SELECT XMLTYPE(tmpData) AS XML FROM dual)
    SELECT Col1, Col2, Col3 
    FROM S, 
    XMLTABLE('/AllData/Data'
    PASSING S.XML
    COLUMNS 
    Col1 VARCHAR2(250 BYTE) PATH '/Data/Col1',
    Col2 VARCHAR2(250 BYTE) PATH '/Data/Col2',
    Col3 NUMBER PATH '/Data/Col3'
    ) SDATA;

END sp_Proc1;

I have used this approach as I need to insert multiple records which are passed as XML created in string variable in C#.Net. When this procedure is called from C#.Net code tmpData is assigned using this string variable and not the clob created using dbms_lob.createtemporary.

When a few times this procedure is executed I run into

ORA-01652: unable to extend temp segment by 32000 in tablespace TEMP.

I tried to use dbms_lob.freetemporary(tmpData); at the end of the procedure but it fails to compile with

PLS-00363: expression 'TMPDATA' cannot be used as an assignment target

Can anybody shed some light on how to free table space used by the input parameter?

Thank you for your help and time.

Saurabh
  • 26
  • 4

0 Answers0