Introduction and Set-up

PL/SQL Package Header

PL/SQL Package Body

Visual Basic Code I

Visual Basic Code II

Conclusion and Additional Resources

Please note this page has moved to http://oracledoug.com/ado3.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-)

PL/SQL Package Body

Next, the Package Body…

CREATE OR REPLACE PACKAGE BODY AL_PACKAGE AS
   PROCEDURE AL_PROCEDURE1 (
   			 Pmyid IN NUMBER,
			 Pmycursor OUT mycursor, -- Use cursor
			 Perrorcode OUT NUMBER) IS
   BEGIN
   		Perrorcode := 0;
          -- Open the REF CURSOR
          -- Use Input Variable "PmyID" as part of the query.
		OPEN Pmycursor FOR
				 SELECT location
					FROM Site
  				      WHERE site_id = Pmyid;
						
   EXCEPTION
   		WHEN OTHERS THEN
			 Perrorcode := SQLCODE;	
   END AL_PROCEDURE1; 
		
   PROCEDURE AL_PROCEDURE2 (
			  Pmyquery IN VARCHAR2,
			  Pmycursor OUT mycursor,
			  Perrorcode OUT NUMBER) IS
	BEGIN
	  	 	Perrorcode := 0;
         -- Open the REF CURSOR 
         -- This procedure uses a query
         -- which is passed in as a parameter.
	
	  		OPEN Pmycursor FOR Pmyquery;
		
	EXCEPTION
			 WHEN OTHERS THEN
			 	  Perrorcode := SQLCODE;
   END AL_PROCEDURE2;
END AL_PACKAGE;
/

As you can see from above, AL_PROCEDURE1 simply runs a pre-defined query, using a REF CURSOR. It uses the input variable to filter the query. In this example, the procedure expects an input parameter containing the site_id. The REF CURSOR (when opened) will contain the record with the corresponding site location.

AL_PROCEDURE2 is a more powerful procedure. It allows us to pass any valid SQL statement to be executed. The REF CURSOR will contain the results.

In both procedures, if there are any exceptions raised by Oracle, the SQL Error Code is passed back to the caller (Visual Basic) using the declared OUT parameter, Perrorcode.

Previous