Back to Papers
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.