Re: Can some body help me with changing to the oracle sp.

From: Vinay Bhushan <bhushanvinay_at_gmail.com>
Date: 4 Nov 2005 10:53:37 -0800
Message-ID: <1131130417.192195.113750@g49g2000cwa.googlegroups.com>

Well here is some improvement i made on it. It works now but not how i intended it to work like.

but i couldnt get the temp tables working on it its realy bull shit when you compare to sql server, its much beautifull in the sence you know in what context the table is writen to the disk and when it gets deleted and much flexible for handling things.

even with a table type i cannot do a slect

for ex: select * from DATA_TABLE should give results as a table, as it has enough information of what a table needs. but its crap altogether it dosnt work like that. and you cant open a cursor for that perticular table.

CREATE OR REPLACE PROCEDURE SP_TEST_DETAIL(

P_INQ_NUM       IN   NUMBER,
P_VIEW_NAME     IN   VARCHAR2,
P_PFILTER_TXT   IN   VARCHAR2 DEFAULT NULL,
P_CV1         IN OUT PK_COM_DEFS.CV_TYP

)
AS

   debug NUMBER(10,0) := 0; -- 0 = Debug off : 1 = Debug on

   strValue       VARCHAR2(2000);
   sqlStmt        VARCHAR2(2000);
   InternalName   VARCHAR2(50);

  TYPE DATA_RECORD_TYPE IS RECORD (
SEQ_NUM FLD_IN_QRY.SEQ_NUM%TYPE DEFAULT NULL, INTERNL_NME FLD_IN_QRY.INTERNL_NME%TYPE DEFAULT NULL, FLD_BUS_NME FLD_IN_QRY.FLD_BUS_NME%TYPE DEFAULT NULL );    TYPE DATA_TABLE_SET IS TABLE OF DATA_RECORD_TYPE ;    DATA_TABLE DATA_TABLE_SET;
   DATA_RECORD DATA_RECORD_TYPE;  BEGIN  DELETE FROM TEMP_DP_DATA_TABLE ;  INSERT INTO TEMP_DP_DATA_TABLE ( SEQ_NUM,INTERNL_NME, FLD_BUS_NME )  SELECT FIQ.SEQ_NUM,FIQ.INTERNL_NME, FIQ.FLD_BUS_NME  FROM FLD_IN_QRY FIQ
 WHERE entity_num in ( SELECT ENTITY_NUM FROM INQ_DEF WHERE INQ_DEF.INQ_NUM = P_INQ_NUM )
 ORDER BY SEQ_NUM ASC;   SELECT DISTINCT SEQ_NUM,INTERNL_NME, FLD_BUS_NME   BULK COLLECT
  INTO DATA_TABLE
  FROM TEMP_DP_DATA_TABLE ;   FOR i IN DATA_TABLE.FIRST .. DATA_TABLE.LAST

     LOOP
        DATA_RECORD := DATA_TABLE(i);
		EXECUTE IMMEDIATE (' UPDATE TEMP_DP_DATA_TABLE SET VALUE = (SELECT  '


||	DATA_RECORD.INTERNL_NME ||' FROM ' || P_VIEW_NAME  ||	'  WHERE  ID =

'|| P_PFILTER_TXT ||') WHERE INTERNL_NME = '''


||	DATA_RECORD.INTERNL_NME ||'''' )  ;

  END LOOP;  OPEN P_CV1
 FOR
 SELECT DISTINCT DT.SEQ_NUM, DT.FLD_BUS_NME , DT.VALUE  FROM TEMP_DP_DATA_TABLE DT ;                    DELETE FROM TEMP_DP_DATA_TABLE ;    COMMIT;   END;
/ Received on Fri Nov 04 2005 - 12:53:37 CST