Thursday, May 30, 2013

PL/SQL : procedural language / Structured Query Language

HI,
 Here is simple sample client for IN OUT procedure,Say procedure name called DB_SCHEMA.DB_PACKAGE.DB_API_NAME and has 3 parameters are INOUT_CXLIST, P_STARTDATE, P_ENDDATE, P_CXCATEGORY.Assume this API return ref_cursor and it contains 14  fields.Following is the sample PL/SQL client for printing values from cursor


DECLARE
  INOUT_CXLIST SYS_REFCURSOR;
  P_STARTDATE VARCHAR2(200);
  P_ENDDATE VARCHAR2(200);
  P_CXCATEGORY VARCHAR2(200);
 
 
  TYPE MYREC IS RECORD(
    COL1  VARCHAR2(140),
    COL2  VARCHAR2(140),
    COL3  VARCHAR2(120),
    COL4  VARCHAR2(140),
    COL5  VARCHAR2(140),
    COL6  VARCHAR2(140),
    COL7  VARCHAR2(140),
    COL8  VARCHAR2(140),
    COL9  VARCHAR2(140),
    COL10  VARCHAR2(140),
    COL11  VARCHAR2(140),
    COL12  VARCHAR2(140),
    COL13 VARCHAR2(140),
    COL14  VARCHAR2(140));

  REC MYREC;

BEGIN
  P_STARTDATE := '20130201';
  P_ENDDATE := '20130202';
  P_CXCATEGORY := 'GSM';

  DB_SCHEMA.DB_PACKAGE.DB_API_NAME(INOUT_CXLIST, P_STARTDATE, P_ENDDATE, P_CXCATEGORY );
  -- COMMIT;

 LOOP
    FETCH INOUT_CXLIST
      INTO REC;
    EXIT WHEN INOUT_CXLIST%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(REC.COL1 || ',' || REC.COL2 || ',' || REC.COL3 || ',' ||
                         REC.COL4);
  END LOOP;
END;