Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    148

    Unanswered: Stored Procedure + Sequence Question

    Hi all. I need to convert a MS SQL Stored Proc (below for reference) to Oracle. The Stored Proc basically pages through records in the DB. The problem is it uses the MS "Identity" datatype to increment a column in a temporary table. I am famliar with making triggers/sequences to accomplish such things, but how do I pull that off with a temporary table? Any suggestions would be appreciated.

    Code:
    CREATE Procedure sp_GetRequests
    	(
    		@Page int,
    		@RecsPerPage int,		
    		@SortSQL varchar(100),
    		@WhereSQL varchar(200)
    	)
    As
    -- We don't want to return the # of rows inserted
    -- into our temporary table, so turn NOCOUNT ON
    SET NOCOUNT ON
    
    --Create a temporary table
    CREATE TABLE #TempItems
    (
    	ReqAutoID int IDENTITY,
    	ReqID int
    )
    
    -- Insert the rows from tblItems into the temp. table
    DECLARE @SearchSQL varchar(5000)
    SELECT @SearchSQL = 'INSERT INTO #TempItems (ReqID) SELECT id ' +
        'FROM tbl_Reqs WHERE ' + @WhereSQL + ' ORDER BY ' + @SortSQL
    EXECUTE(@SearchSQL)
    
    -- Find out the first and last record we want
    DECLARE @FirstRec int, @LastRec int
    SELECT @FirstRec = (@Page - 1) * @RecsPerPage
    SELECT @LastRec = (@Page * @RecsPerPage + 1)
    
    -- Now, return the set of paged records, plus, an indiciation of we
    -- have more records or not!
    SELECT *, 
           MoreRecords = 
    	(
    	 SELECT COUNT(*) 
    	 FROM #TempItems TI
    	 WHERE TI.ReqAutoID >= @LastRec
    	) 
    FROM #TempItems T (nolock)
    	INNER JOIN tbl_Reqs OWR (nolock) ON
    		id = T.ReqID
    WHERE ReqAutoID > @FirstRec AND ReqAutoID < @LastRec ORDER BY T.ReqAutoID
    
    -- Turn NOCOUNT back OFF
    SET NOCOUNT OFF
    
    
    GO

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You can use ROWNUM:
    Code:
    SearchSQL := 'INSERT INTO TempItems (ReqAutoID, ReqID) '
                    ||'SELECT ROWNUM, id FROM tbl_Reqs '
                    ||'WHERE '||WhereSQL||' ORDER BY '||SortSQL
    EXECUTE IMMEDIATE SearhSQL;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Dec 2003
    Posts
    148
    awesome i will give that a try

  4. #4
    Join Date
    Dec 2003
    Posts
    148
    Hmm. I can't really try that out because I get an error on "create"


    Line # = 1 Column # = 35 Error Text = PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: ( ; is with authid as cluster compress order using compiled wrapped external deterministic parallel_enable pipelined


    Is that syntax incorrect?

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Try this:
    Code:
    EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE TempItems '
                             ||'(ReqAutoID NUMBER, ReqID NUMBER) '
                             ||' ON COMMIT PRSERVER ROWS';


    NOTE: Better remove this code from the procedure and execute once, temporary tables need not be created every time.
    Last edited by LKBrwn_DBA; 07-14-04 at 18:42.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •