Results 1 to 2 of 2

Thread: Database - Returning a recordset from an Oracle SP, and Testing a SP from SQL*Plus

  1. #1

    Thread Starter
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Database - Returning a recordset from an Oracle SP, and Testing a SP from SQL*Plus

    Getting a resultset back from an Oracle SP is not as simple as it is in SQL Server. The results must come back to the calling program in something called a reference cursor (ref cursor). This will discuss what a ref cursor is and how to implement them and get data back.

    Oracle creates an implicit cursor for every select query (I think that is the same for any database system). The cursor is simple the recordset results. If you are not going to use that result set for anything else (ie: to generate another query to execute) then you do not need to declare a cursor. But to get the result set out of Oracle you need something that is called a ref cursor. This ref cursor is more or less the same as and ADO recordset. You declare the ref cursor in code some where on the Oracle database, that ref cursor (sort of a structure in .Net) is then listed as an In and Out parameter of the SP.

    You generate the select statement you want to run then open the ref cursor you created as follows:
    Code:
        Open cRefCur For 
           Select ....... (columns form whatever tables)
             From (table names)
             Where (conditions and Joins).
    Standard SQL here with one big exception since if using Oracle 8i or earlier, Oracle at that release level did not support the Inner and Outer Join statements. You must use the Oracle version of them. Inners are just and equal sign as in Sales.ProductID = Prodcuts.ProductID. The Outer join is a lot messier, outer joins use the same equals sign and also a plus (+) sign on the deficient side of the equal sign.

    This is the way to create the cursor:

    First we create a package that will hold all the different return types:
    Code:
    CREATE OR REPLACE PACKAGE cv_types AS
      
    	TYPE WellData IS RECORD(  
    		WellName		Varchar2(50),
    		ResultsCount	Number
    	);
    	TYPE CV_WEllData IS REF CURSOR RETURN WellData;
        
    End;
    /
    Next we create a stored procedure that will use that ref cursor declared above:
    (This procedure does not have any inputs, only output paramters).
    Code:
    Create Or Replace Procedure WellCounting (    
    	pWellName   OUT	VARCHAR2,
        pCount		OUT	NUMBER,
        rsWellData	IN OUT cv_types.CV_WEllData)
    
    AS
    	
    BEGIN
    	Open rsWellData For
    		Select 
    			Wells.WELLNAME,Count(RESULTS.WELLID) 
    		Into 
    			pWellName,
    			pCount
    		From 
    			Wells, Results 
    		Where 
    			Wells.WellID = Results.WellID
    		 group by 
    		 	WEllName;
                
    EXCEPTION 
      WHEN OTHERS THEN         
          ROLLBACK WORK;
          RAISE;
    
    End WellCounting;
    /
    We can then call the stored procedure from VB as shown in the included VB Projects.

    An example of a stored procedure with input parameters is here:
    Code:
    Create Or Replace Procedure OneWellCount (    
    	pWellID		IN  Number,
    	pWellName   OUT	VARCHAR2,
        pCount		OUT	NUMBER,
        rsWellData	IN OUT cv_types.CV_WEllData
        )
    
    AS
    BEGIN
    	Open rsWellData For
    		Select 
    			Wells.WELLNAME,Count(RESULTS.WELLID) 
    		Into 
    			pWellName,
    			pCount
    		From 
    			Wells, Results 
    		Where 
    			Wells.WellID = pWellID And 
    			Wells.WellID = Results.WellID
    		 group by 
    		 	WEllName;
    EXCEPTION 
      WHEN OTHERS THEN         
          ROLLBACK WORK;
          RAISE;
    
    End OneWellCount;
    /
    We can also test these procedures (and ref cursors) from the SQL*Plus prompt by doing the following:
    1. Enter the command SET SERVEROUTPUT ON;
    2. Now we set up variables to hold data going into and out of the SP:
      Assuming we are using the first SP displayed, the we will need 3 variables:
        VARIABLE P1   VARCHAR2(50) This is because the field we are returning is 50 chars
        VARIABLE P2   Number           This is a number coming back from the SP;
        VARIABLE P3   REFCURSOR     This will hold the result set that is coming back
    3. From the SQL prompt enter:
        EXECUTE WellCounting( :P1, :P2, :P3);
    4. If the procedures completes successfully we can now display the output.
      The variable P1 and P2 will hold the last Well Name and number of results
      for that well name. The variable P3 will hold the complete recordset that
      is being returned. To display that result in SQL*Plus enter:
        Print P3


    The attachment below is a VB6 example of the above.
    Attached Files Attached Files
    Last edited by si_the_geek; Mar 18th, 2006 at 12:51 PM.

  2. #2

    Thread Starter
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Database - Returning a recordset from an Oracle SP, and Testing a SP from SQL*Plus

    This is a Dot Net example of Oracle use. I am using the Oracle provider in this example.
    Attached Files Attached Files
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

Posting Permissions

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



Click Here to Expand Forum to Full Width