Home » SQL & PL/SQL » SQL & PL/SQL » Problem executing procedure in a Package
icon9.gif  Problem executing procedure in a Package [message #225845] Wed, 21 March 2007 13:30 Go to next message
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 Go to previous messageGo to next message
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 #225869 is a reply to message #225861] Wed, 21 March 2007 15:42 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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
...
Re: Problem executing procedure in a Package [message #225878 is a reply to message #225869] Wed, 21 March 2007 16:02 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: Problem executing procedure in a Package [message #225995 is a reply to message #225948] Thu, 22 March 2007 04:08 Go to previous message
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.
Previous Topic: Dealing with Foreign Language Characters
Next Topic: String Buffer Error
Goto Forum:
  


Current Time: Thu Dec 08 19:53:26 CST 2016

Total time taken to generate the page: 0.09997 seconds