Using MS Access to call an Oracle Stored Procedure and capture DBMS_OUTPUT

  • Thread starter Thread starter Adonus
  • Start date Start date
A

Adonus

I am using MS Access (2000) as a front-end to run various stored
procedures in an Oracle database (8.1.7.4). These stored procedures
update data and I would like to capture the DBMS_OUTPUT from them and
return them to Access so that I can use them to populate a message box
for the user to see. I could convert this to a function but I'm not
sure how I can use a function to return information and also have
update statements at the same time. I tried that and I got a message
in oracle saying:

** This is an example of some code in my plsql stored procedure...

BEGIN

-- set the variable v_message with the following message in case the
update fails
V_MESSAGE := 'Unable to update mytable';

-- update mytable
UPDATE mytable
SET field1 = 'Y'
WHERE field1 = 'N';

-- update v_message with the # of rows that were updated
V_MESSAGE := SQL%ROWCOUNT || ' rows updated';

COMMIT;

-- output the contents of v_message
DBMS_OUTPUT.PUT_LINE(V_MESSAGE);

EXCEPTION
WHEN OTHERS THEN
-- output the contents of v_message
DBMS_OUTPUT.PUT_LINE(V_MESSAGE);

END;
/
 
Whoops...forgot to insert the oracle error message when I tried to use
a function to return the messge:

ORA-14551: cannot perform a DML operation inside a query

I had created the function to accept 3 input parameters and return 1
varchar2 field which would have been v_message
 
Back
Top