OraFAQ Forum: SQL & PL/SQL » Problem executing procedure in a Package

Home » SQL & PL/SQL » SQL & PL/SQL » Problem executing procedure in a Package

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem executing procedure in a Package [message #225845]

Wed, 21 March 2007 13:30

mivey4
Messages: 19
Registered: March 2007

Junior Member

Hi,

I am not new to Oracle but I am new to creating packages. I have created the following package that has 1 procedure in it. No problems compiling.

CREATE OR REPLACE PACKAGE dataObj
AS
TYPE CURSOR_TYPE IS REF CURSOR;
PROCEDURE column_view (results_cursor OUT CURSOR_TYPE);
END;
/
CREATE OR REPLACE PACKAGE BODY dataObj
AS
PROCEDURE column_view (results_cursor OUT CURSOR_TYPE)
AS
BEGIN
OPEN results_cursor FOR
SELECT NAME FROM STRINGS
ORDER BY NAME;
END;
END;
/

How should I now call the procedure in the package? From my understanding it would be:

EXEC dataObj.column_view()
or
EXEC dataObj.column_view()

But I get this error when I try that:

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'COLUMN_VIEW'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Anybody know why this is happening? The procedure takes no arguments and should only be returning the results.

Any help is appreciated.
Thanks

Report message to a moderator

Re: Problem executing procedure in a Package [message #225861 is a reply to message #225845]

Wed, 21 March 2007 14:58

S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom

Senior Member

Procedure by definition cannot return a value. But if you define any arguments to the procedure then you have to match the definition of your procedure (unless and until you have specified any default values for those parameters if not supplied). In your example you are planning to return a cursor but while calling the procedure you have not supplied any value to receive it. Hence you are receiving this error.

Report message to a moderator

Re: Problem executing procedure in a Package [message #225869 is a reply to message #225861]

Wed, 21 March 2007 15:42

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

In other words, you're looking for something like this:

SQL> var l_name refcursor;
SQL> exec dataobj.column_view(:l_name);

PL/SQL procedure successfully completed.

SQL> print l_name;

ENAME
----------
ADAMS
ALLEN
BLAKE
CLARK
FORD
...

Report message to a moderator

Re: Problem executing procedure in a Package [message #225878 is a reply to message #225869]

Wed, 21 March 2007 16:02

mivey4
Messages: 19
Registered: March 2007

Junior Member

Thanks for the replies.

Yes, that's exactly what I expected and want but I don't understand this one thing.

Why do you still have to supply a variable and an argument in execution of the procedure if the only argument specified is an OUT parameter during the creation? I thought that Oracle wouldn't expect an argument unless the IN parameter was specified.

Could you please explain this. Sorry, I am a SQL developer and trying to understand the logic of how Oracle interprets things.

Thanks

Report message to a moderator

Re: Problem executing procedure in a Package [message #225948 is a reply to message #225878]

Thu, 22 March 2007 01:24

Frank
Messages: 7901
Registered: March 2000

Senior Member

In Oracle there is a distinction between the value returned by a function and out parameters of procedures and functions.
A returnvalue of a function can be used to assign to a variable:
a := fn(x);

Parameters, be it out or in out, must be given values or variables in which the parametervalue can be stored.

Report message to a moderator

Re: Problem executing procedure in a Package [message #225995 is a reply to message #225948]

Thu, 22 March 2007 04:08

mivey4
Messages: 19
Registered: March 2007

Junior Member

Okay I get it now. It's like making a call to a windows API, there must be a datatype provided so the return value can be stored in it.

Thanks all for your assistance and clarifications.

Report message to a moderator

Previous Topic:	Dealing with Foreign Language Characters
Next Topic:	String Buffer Error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 07 02:40:53 CST 2024