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
|
|
|
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.
|
|
|
|
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
|
|
|
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.
|
|
|
|