3 Replies Latest reply: Dec 17, 2012 10:30 PM by Sachin Srivastava RSS

    How to pass the value of CLOB in procedure & get data display sysrefcursr

    Sachin Srivastava
      Hi ,

      I am executing the below code in Toad. I want to pass the value in CLOB

      Value will be of

      I_ITEM_NO := '01234567/01234568/01234569/.... up to 100 reoccurnces.

      requirement is to show the value in refcursor at toad and how to pass the above value in CLOB.



      DECLARE
      I_ITEM_NO CLOB;
      I_ITEM_NAME VARCHAR2(200);
      I_HFB VARCHAR2(200);
      I_PRODUCTTYPE VARCHAR2(200);
      I_DWPTYPE VARCHAR2(200);
      I_FROMPRODDATE VARCHAR2(200);
      I_TOPRODDATE VARCHAR2(200);
      I_DWPVERSION VARCHAR2(200);
      I_SUPPLIERNO VARCHAR2(200);
      I_SUPPLIERNAME VARCHAR2(200);
      I_PACKGINGREQ VARCHAR2(200);
      I_PACKAGINGDRAWINGS VARCHAR2(200);
      I_USERTYPE NUMBER;
      I_TSO_NO VARCHAR2(200);
      O_REF_CURSOR SYS_REFCURSOR;

      BEGIN
      -- I_ITEM_NO := NULL; Modify the code to initialize this parameter
      I_ITEM_NAME := NULL;
      I_HFB := NULL;
      I_PRODUCTTYPE := NULL;
      I_DWPTYPE := NULL;
      I_FROMPRODDATE := NULL;
      I_TOPRODDATE := NULL;
      I_DWPVERSION := NULL;
      I_SUPPLIERNO := NULL;
      I_SUPPLIERNAME := NULL;
      I_PACKGINGREQ := NULL;
      I_PACKAGINGDRAWINGS := NULL;
      I_USERTYPE := NULL;
      I_TSO_NO := NULL;
      -- O_REF_CURSOR := NULL; Modify the code to initialize this parameter

      SEL_DWP_REQ_EXPORT_INFO ( I_ITEM_NO, I_ITEM_NAME, I_HFB, I_PRODUCTTYPE, I_DWPTYPE, I_FROMPRODDATE, I_TOPRODDATE, I_DWPVERSION, I_SUPPLIERNO, I_SUPPLIERNAME, I_PACKGINGREQ, I_PACKAGINGDRAWINGS, I_USERTYPE, I_TSO_NO, O_REF_CURSOR );

      :rc0_O_REF_CURSOR := O_REF_CURSOR;

      DBMS_OUTPUT.Put_Line('');


      END;


      any sugestion welcome .. Thanks in advance

      Regards
      Sachin