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

Vinay Bhushan wrote:
> I need help in reconstructing the oracle stored procedure as this
> involves lot of global temporaty tables can any one help me with the
> code.
>
> ---------------------------------SQL
> Version-----------------------------
> CREATE PROCEDURE SP_STANDARDDETAIL
>
> @inq_num int,
> @view_name varchar(50),
> @pfilter_txt varchar(1000)=NULL
> AS
> BEGIN
>
> BEGIN TRANSACTION
>
> -- Get the field names
> DECLARE @strValue CHAR(1000)
> SELECT DISTINCT SEQ_NUM,INTERNL_NME, FLD_BUS_NME, VALUE = @strValue
> INTO #DATA_DEFINITION
> FROM fld_in_qry
> WHERE entity_num =(SELECT entity_num FROM inq_def
> WHERE inq_num = @inq_num)
>
> -- Get the actual values from the view
>
> declare @sSql varchar(4000)
> SELECT @sSql = 'SELECT * INTO ##VIEW FROM ' + @view_name
> IF DATALENGTH(@pfilter_txt) <> 0
> BEGIN
> SELECT @sSql = @sSql + ' WHERE Id=' + @pfilter_txt
> END
>
> exec(@sSql)
>
> DECLARE @InternalName AS VARCHAR(50)
> DECLARE @Value AS VARCHAR(50)
>
> DECLARE DetailCursor CURSOR FOR
> SELECT INTERNL_NME FROM #DATA_DEFINITION
> OPEN DetailCursor
> FETCH NEXT FROM DetailCursor INTO @InternalName
>
> WHILE @@FETCH_STATUS = 0
> BEGIN
>
> EXEC('UPDATE #DATA_DEFINITION SET VALUE = (SELECT ISnull(' +
> @InternalName + ','''') FROM ##VIEW) WHERE INTERNL_NME = ''' +
> @InternalName + '''')
>
> FETCH NEXT FROM DetailCursor INTO @InternalName
> END
> CLOSE DetailCursor
> DEALLOCATE DetailCursor
>
> SELECT SEQ_NUM,FLD_BUS_NME, VALUE /*, DATA_TYP_NUM */ FROM
> #DATA_DEFINITION ORDER BY SEQ_NUM ASC
> DROP TABLE #DATA_DEFINITION
> DROP TABLE ##VIEW
>
> COMMIT TRANSACTION
> END
>
> ------------------------------ SQL Version Ends
> ------------------------------
>
> ----------------------------- Oracle version
> -------------------------------
>
> CREATE OR REPLACE PROCEDURE SP_STANDARDDETAIL(
> INQ_NUM IN NUMBER,
> VIEW_NAME IN VARCHAR2,
> 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 DtaCurTyp IS REF CURSOR;
> DataCursor DtaCurTyp;
>
> BEGIN
>
> -- Data Table
> sqlStmt:='CREATE GLOBAL TEMPORARY TABLE DATA_DEFINITION ' ;
> sqlStmt:= sqlStmt || 'ON COMMIT DELETE ROWS ' ;
> sqlStmt:= sqlStmt || 'AS SELECT DISTINCT SEQ_NUM,INTERNL_NME,
> FLD_BUS_NME, ''DATA'' AS VALUE ';
> sqlStmt:= sqlStmt || 'FROM FLD_IN_QRY ';
> sqlStmt:= sqlStmt || 'WHERE entity_num =(SELECT ENTITY_NUM FROM
> INQ_DEF';
> sqlStmt:= sqlStmt || 'WHERE inq_num ='||INQ_NUM ||' ) ';
>
> FOR i IN 0..(LENGTH(sqlStmt)-1)/80 LOOP
> DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlStmt, (i*80) + 1, 80));
> END LOOP;
>
> EXECUTE IMMEDIATE sqlStmt USING strValue,INQ_NUM ;
>
> -- Actual Data
> sqlStmt:= 'CREATE GLOBAL TEMPORARY TABLE DATA_VIEW ' ;
> sqlStmt:= sqlStmt ||'ON COMMIT DELETE ROWS ' ;
> sqlStmt:= sqlStmt ||'AS SELECT * FROM :VIEW_NAME ';
> sqlStmt:= sqlStmt ||'WHERE ID =: PFILTER_TXT ';
>
> FOR i IN 0..(LENGTH(sqlStmt)-1)/80 LOOP
> DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlStmt, (i*80) + 1, 80));
> END LOOP;
>
> EXECUTE IMMEDIATE sqlStmt USING VIEW_NAME,PFILTER_TXT ;
>
> --UPDATE THE RETURN DATASET WITH VALUES
> OPEN DataCursor FOR 'SELECT INTERNL_NME FROM DATA_DEFINITION';
> LOOP
> FETCH DataCursor INTO InternalName;
> EXIT WHEN DataCursor%NOTFOUND;
> -- process record
> EXECUTE IMMEDIATE 'UPDATE DATA_DEFINITION SET VALUE =
> (SELECT
> NVL(' || InternalName || ','''') FROM DATA_VIEW) WHERE INTERNL_NME =
> ''' || InternalName || '''';
> END LOOP;
>
> CLOSE DataCursor ;
>
> --RETURN DATASET
> OPEN P_CV1 FOR 'SELECT SEQ_NUM,FLD_BUS_NME, VALUE FROM
> DATA_DEFINITION ORDER BY SEQ_NUM ASC ';
>
> -- DELETE TEMP TABLES
> EXECUTE IMMEDIATE 'DROP TABLE DATA_VIEW';
> EXECUTE IMMEDIATE 'DROP TABLE DATA_DEFINITION';
>
> COMMIT ;
>
> --EXCEPTION
> --WHEN OTHERS THEN
>
> -- DBMS_OUTPUT.PUT_LINE('EXEPTION');
> -- ROLLBACK ;
>
> END;
> /
>
> SHOW ERRORS;
> ------------------------------Oracle version Ends
> ---------------------------
>
> but this dosnt work can any one help regarding this.
>

Sjees - who wrote that?!?

You actually are doing an execute immediate in a loop in order to perform an update?!? All that context switching will not scale well...

You are actually creating tables on the fly? No, even worse, you create global temporary tables on the fly! You do not seem to have the slightest idea how global temporary tables work in Oracle, do you.

What do you think NVL(' || InternalName || ','''') will accomplish? Check out the difference between an empty string ('') and a NULL for character fields in Oracle: there is none.

And will a ref cursor select into a variable? Don't know.

Did you manually parse the statements to check what actually is generated?
What does "does not work" mean?

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...