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/ado2.html. You will be redirected in 5 seconds

PL/SQL Package Header

First, the package header…

CREATE OR REPLACE PACKAGE AL_PACKAGE AS
	TYPE mycursor IS REF CURSOR;
	   
	PROCEDURE AL_PROCEDURE1 (
	  			 Pmyid IN NUMBER,
				 Pmycursor OUT mycursor, -- Use cursor
				 Perrorcode OUT NUMBER);		
				 
	PROCEDURE AL_PROCEDURE2 (
				  Pmyquery IN VARCHAR2,
				  Pmycursor OUT mycursor,
				  Perrorcode OUT NUMBER);
END AL_PACKAGE;
/

Type the above script into your editor and execute it to create the package header. In the package header, I have declared my REF CURSOR as mycursor (see above). There are two procedures declared within the package header, AL_PROCEDURE1 and AL_PROCEDURE2.

AL_PROCEDURE1 accepts a number parameter as input and returns a variable of type mycursor (which is our REF CURSOR) and a number parameter (which will contain any SQL error codes).

The AL_PROCEDURE2 declaration is similar to AL_PROCEDURE1, but accepts a string parameter as input in place of a number.

Previous