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

From: Vinay Bhushan <bhushanvinay_at_gmail.com>
Date: 3 Nov 2005 09:42:34 -0800
Message-ID: <1131039754.808121.270520@g14g2000cwa.googlegroups.com>

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. Received on Thu Nov 03 2005 - 11:42:34 CST