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

"Vinay Bhushan" <bhushanvinay_at_gmail.com> wrote in message news:1131130417.192195.113750_at_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;
> /
>

Ugly, very ugly. (You usually don't need bullshit temp tables in Oracle. You rarely need temp tables in Oracle. You are now working with Oracle not SQL Server. The code you have written is a lot more complex (and unscalable) than it needs to be. Why not just select out of one table and update the other in one statement? Much faster and would use a lot less memory than what you have.
Jim Received on Fri Nov 04 2005 - 20:54:33 CST