Home » SQL & PL/SQL » SQL & PL/SQL » Better way of writing the code (oracle 10g,windows)
Better way of writing the code [message #328042] Wed, 18 June 2008 12:38 Go to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi,
Below is the sample code:

SQL> desc FINCL_ACTY_PRTY_INFO_OBJ_TYPE
Name Null? Type
----------------------------------------- -------- ----------------------------
INSURD_ROLE_CD VARCHAR2(3)
CLNT_REF_NBR NUMBER(7)

and the proceudre is
create PROCEDURE GET_PRTY_INFO_PROC(PLCY_NBR_IN IN VARCHAR2,
                               CO_CD_IN    IN VARCHAR2,
                               -- SRC_SYSTM_CD  IN VARCHAR2,
                               PRTY_INFO_TAB   IN OUT FINCL_ACTY_PRTY_INFO_TBL_TYPE,
                               PROC_STATUS_CD  OUT VARCHAR2,
                               PROC_ERROR_DESC OUT VARCHAR2) IS
    -- Get Insured Information
    CURSOR INSURED_PARTY_INFO_CUR IS
      SELECT 'I' || A.PRIMRY_SECDRY_LIFE_IND INSURD_ROLE_CD,
                     A.CLNT_REF_NBR INSURD_CLNT_REF_NBR
             FROM CLEAR_DAILY_FACT_VW A, CLNT_DIM B, COV_DIM C
               WHERE A.CLNT_REF_NBR = B.CLNT_REF_NBR
                 AND A.COV_DIM_ID = C.COV_DIM_ID
                 AND A.PLCY_NBR = PLCY_NBR_IN
                 AND A.CO_CD = CO_CD_IN)
       WHERE SEQ = 1
       ORDER BY INSURD_ROLE_CD;
  
  BEGIN
    FOR REC IN INSURED_PARTY_INFO_CUR LOOP
    
      PRTY_INFO_TAB.EXTEND;
      PRTY_INFO_TAB(PRTY_INFO_TAB.LAST) := FINCL_ACTY_PRTY_INFO_OBJ_TYPE(REC.INSURD_ROLE_CD,
                                                                         REC.INSURD_CLNT_REF_NBR
                                                                         );
    END LOOP;

  END;


In the above procedure I am opening a CURSOR and then populating the vallues into that object FINCL_ACTY_PRTY_INFO_OBJ_TYPE;
  But ,instead of doing the above way I want use the BULK collect clause for this.
  can any one please let me know how to use Bulk collect clause for the same.
Re: Better way of writing the code [message #328043 is a reply to message #328042] Wed, 18 June 2008 12:43 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2216

Are you unwilling or incapable of Reading This Fine Manual?
Re: Better way of writing the code [message #328044 is a reply to message #328042] Wed, 18 June 2008 12:49 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm pretty this is the whole application you have to review and tune not this procedure.

Regards
Michel
Previous Topic: To store just Time information in Oracle column
Next Topic: Capture encrypted email from POP3/IMAP server
Goto Forum:
  


Current Time: Sat Dec 10 14:55:00 CST 2016

Total time taken to generate the page: 0.04707 seconds