CODEWITHSUNDEEP
oracle-databaseplsqlreturn
Josh
Josh

Reputation: 189

How to retrieve values post-execution of PL/SQL stored procedure?

Im very new to oracle database stuff. There is a PL/SQL proceedure which someone else wrote and is stored on the database I am accessing. I want my program to execute it and retrieve the result. Execution is working. I cannot retrieve the result however. Obviously I am not doing it right, but I cannot find the right way in the documentation.

Here is the gist of the stored procedure (with extraneous lines removed)

procedure ISDRAWINGVALID(DWGNO_IN in VARCHAR2) is
valid BOOLEAN;
begin
-- do some stuff to see if the drawing is valid
IF <some stuff> THEN
valid := TRUE;
ELSE
valid := FALSE;
END IF;
END ISDRAWINGVALID;

My program issues the following commands to the database to execute and retrieve the return. 

BEGIN ISDRAWINGVALID( <drawingnumber> ); END;
SELECT ISDRAWINGVALID.valid FROM DUAL;

The first line works fine, the proceedure executes and has the desired effect. The second line returns an error, invalid identifier "ISDRAWINGVALID.valid"

Clearly i am not using the right way to retrieve the value. Can someone please clue me in?

thanks

Upvotes: 0

Views: 416

Answers (2)

doberkofler
doberkofler

Reputation: 10341

Use a function and return a NUMBER to be used in SQL:

CREATE OR REPLACE
FUNCTION ISDRAWINGVALID(DWGNO_IN in VARCHAR2) RETURN NUMBER
IS
   valid NUMBER;
BEGIN
   IF <some stuff> THEN
      valid := 1;
   ELSE
      valid := 0;
   END IF;
   RETURN valid;
END ISDRAWINGVALID;

Use from PL/SQL:

DECLARE
   valid NUMBER;
BEGIN
   valid := ISDRAWINGVALID( <drawingnumber> );
END;
/

Use from SQL:

SELECT ISDRAWINGVALID( <drawingnumber> ) FROM DUAL;

Upvotes: 0

citywall
citywall

Reputation: 235

As you present the problem, there is no way to get the result.

If you can get the procedure as a function instead, you can call it directly in the select statement.

Otherwise you would have to take a long detour to solve it, involving a result table or a pl/sql package with a result function and a package variable.

The procedure you have there has been made to be called from other pl/sql code - not in a select query.

EDIT

I think I might be wrong after all. In Java you can create a prepared statement with a call, and pick up the return value directly as a result-set. Check this out and come back with the result: http://archive.oreilly.com/pub/a/onjava/2003/08/13/stored_procedures.html?page=2

Sorry if you are not using Java, I was not able to see what you are using.

Upvotes: 1

Related Questions