Home » SQL & PL/SQL » SQL & PL/SQL » Unable to Insert CLOB (RHEL 4, Oracle 10g)
Unable to Insert CLOB [message #380200] Fri, 09 January 2009 08:36 Go to next message
srikanth2384
Messages: 4
Registered: December 2008
Location: Pune
Junior Member

Hi All,

Following is my simple procedure which will insert data into a table based in IN parameters to the Sproc. I am Passing a string which is declared as CLOB. All other data are getting inserted except the CLOB. Please check and let me know what is wrong .. !!

CREATE OR REPLACE PROCEDURE STORE_ASYNC_REQUEST

(

    i_REQUEST                IN OUT   CLOB,

    i_REQUEST_URL            IN       VARCHAR2,

    i_PROCESSOR_CODE         IN       VARCHAR2,

    i_TRANSACTION_ID         IN       VARCHAR2,

    i_PRODUCT_CODE       	 IN       VARCHAR2,

    i_DB_CALL           	 IN       VARCHAR2,

    o_STORE_ID           	 OUT      NUMBER,

    o_OUTPUTSTATUS           OUT      NUMBER,

    o_OUTPUTMESSAGE          OUT      VARCHAR2

)

AS

    v_ErrorFlag                      NUMBER:=0;

    v_ID_Seq                         VARCHAR(50);

    v_STORE_ID                       NUMBER;



BEGIN



    SELECT STORE_ID_SEQ.NEXTVAL INTO v_STORE_ID FROM DUAL;

    

    INSERT INTO STORE_FORWARD

    (STORE_ID, PROCESSOR_CODE, TRANSACTION_ID, REQUEST, REQUEST_URL, STATUS, PRODUCT_CODE, DB_CALL)

    VALUES 

    (v_STORE_ID, i_PROCESSOR_CODE, i_TRANSACTION_ID, EMPTY_CLOB(), i_REQUEST_URL, 'SYNC_INPROCESS', i_PRODUCT_CODE, i_DB_CALL);

    

    SELECT REQUEST

	INTO i_Request

	FROM STORE_FORWARD

	WHERE STORE_ID = v_STORE_ID

	FOR UPDATE;

    

    o_STORE_ID := v_STORE_ID;    

    o_OutputStatus      := 0;

    o_OutputMessage     := 'SUCCESS';



EXCEPTION

    WHEN OTHERS THEN

        o_OutputStatus      :=   -1;

        o_OutputMessage     := 'Procedure STORE_ASYNC_REQUEST Failed at step No = '||v_ErrorFlag||' SQL Error :- '||SUBSTR(SQLERRM,1,100) ;

        ROLLBACK;

END ;

/



~Srikanth
Re: Unable to Insert CLOB [message #380208 is a reply to message #380200] Fri, 09 January 2009 08:48 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where do you put something in your CLOB?
And remove this stupid WHEN OTHERS part.

Regards
Michel
Previous Topic: DISABLE CONSTRAINTS
Next Topic: Determine Hash Size for DBMS_UTILITY.GET_HASH_VALUE
Goto Forum:
  


Current Time: Mon Dec 05 19:22:43 CST 2016

Total time taken to generate the page: 0.08616 seconds