Going from SQLServer 7 to Oracle 8

From: Robert Vabo <robert.vabo_at_gecko.no>
Date: Mon, 28 Aug 2000 13:51:30 GMT
Message-ID: <C7uq5.10964$541.2248865_at_juliett.dax.net>

I wrote a message some days ago and I got answers that helped me on that matter. I am going to convert some SQL Server SP's to Oracle and I really need help.

This proc (sp_seek) is going to help me return eg. 20 and 20 records to a client but the seach criteria given can return app. 1000 records. It works fine in SQLServer but I don't know so much about Oracle and I ran into some serious problems.

Here is the proc:
CREATE PROCEDURE sp_Seek (
_at_MaxRecords integer,
_at_SQL_SelectCols nvarchar(200) ,
_at_SQL_WhereStatement nvarchar(200) ,
_at_SQL_OrderByStatement nvarchar(200) = '' ,
_at_TableName char(30),
_at_TempTableName nvarchar(30) ,
_at_ID_ColName nvarchar(30),
_at_PageSize integer ,
_at_FromRec integer,
_at_AntRecsLeftinteger OUTPUT ,
_at_TotalRecs integer OUTPUT

    )
AS

SET NOCOUNT ON

DECLARE _at_TotNum integer
DECLARE _at_FromRecLoc int
DECLARE _at_LastRec int

/* Variabler brukt for å kjøre parametre som kommer inn */

DECLARE _at_strCreateTable nvarchar(200)
DECLARE _at_strDropTable nvarchar(200)
DECLARE _at_strFillTempTable nvarchar(200)
DECLARE _at_strGetNumPages nvarchar(200)
DECLARE _at_strGetReturn nvarchar(200)
DECLARE _at_strGetRecords nvarchar(200)

SET _at_FromRecLoc = @FromRec

SET ROWCOUNT _at_MaxRecords

if not exists (select * from sysobjects where id = object_id(_at_TempTableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1)   BEGIN

      /* Make "temp" table */
      SELECT _at_strCreateTable = 'CREATE TABLE ' + @TempTableName + ' (ID_COL
int IDENTITY, INPUT_ID int ) '
      EXEC(_at_strCreateTable)

      /* Fill table with data */
      SELECT _at_strFillTempTable = 'INSERT INTO ' + @TempTableName + '

(INPUT_ID) SELECT ' + _at_ID_ColName + ' FROM ' + @TableName + ' WHERE ' + _at_SQL_WhereStatement + ' ' + @SQL_OrderByStatement

      EXEC(_at_strFillTempTable)
    END
else
  BEGIN
    if exists (select * from sysobjects where id = object_id(_at_TempTableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1) AND _at_FromRecLoc = 1

      BEGIN
        /* Delete temp table */
        SELECT _at_strDropTable = 'DROP TABLE ' + @TempTableName
        EXEC(_at_strDropTable)
        /* Make temp table */
        SELECT _at_strCreateTable = 'CREATE TABLE ' + @TempTableName + '
(ID_COL int IDENTITY, INPUT_ID int ) '
        EXEC(_at_strCreateTable)

        /* Fill temp table with data */
        SELECT _at_strFillTempTable = 'INSERT INTO ' + @TempTableName + '

(INPUT_ID) SELECT ' + _at_ID_ColName + ' FROM ' + @TableName + ' WHERE ' + _at_SQL_WhereStatement + ' ' + @SQL_OrderByStatement

        EXEC(_at_strFillTempTable)
      END

  END /* Get total number of records returned */ CREATE TABLE #TestTemp(AntRecs int)

SELECT _at_strGetReturn =  'declare @intAnt int ' + char(13)
SELECT _at_strGetReturn =  @strGetReturn + 'SET @intAnt = (SELECT count(*) from
' + _at_TempTableName + ')' + char(13)
SELECT _at_strGetReturn =  @strGetReturn + 'INSERT INTO #TestTemp VALUES

(_at_intAnt)'
EXEC(_at_strGetReturn)
SELECT _at_TotalRecs = (SELECT * FROM #TestTemp) DROP TABLE #TestTemp

/* Find last record that is going to be returned */ SELECT _at_LastRec = (@FromRecLoc + @PageSize) - 1

/* Get info about records left */
CREATE TABLE #TestTemp1(AntRecs int)

SELECT _at_strGetNumPages = 'declare @intAnt int ' + char(13) SELECT _at_strGetNumPages = @strGetNumPages + 'SET @intAnt = (SELECT COUNT(ID_COL) FROM ' + _at_TempTableName + ' TT WHERE TT.ID_COL > ' SELECT _at_strGetNumPages = @strGetNumPages + convert(varchar(4),_at_LastRec) + ')' + char(13)
SELECT _at_strGetNumPages = @strGetNumPages + 'INSERT INTO #TestTemp1 VALUES (_at_intAnt)'
EXEC(_at_strGetNumPages)
SELECT _at_AntRecsLeft= (SELECT * FROM #TestTemp1) DROP TABLE #TestTemp1

/*Join tables to retrieve records to return to client */ SELECT _at_strGetRecords = 'SELECT ' + @SQL_SelectCols + ' FROM ' + _at_TempTableName + ' TT Inner Join ' + rtrim(@TableName) SELECT _at_strGetRecords = @strGetRecords + ' N ON TT.INPUT_ID = N.' + _at_ID_ColName + ' WHERE TT.ID_COL BETWEEN ' SELECT _at_strGetRecords = @strGetRecords + convert(varchar(4),_at_FromRec) + ' AND ' + convert(varchar(4),_at_LastRec) EXEC(_at_strGetRecords)

SET NOCOUNT OFF
GO

Is there anyone out there that can give me some good tips on how to convert this proc ?

--
Regards
Robert Vabo
Application developer
Gecko Informasjonssystemer AS
www.gecko.no
robert.vabo_at_gecko.no

Received on Mon Aug 28 2000 - 15:51:30 CEST