Package Execution and Error PLS-00306

[mivey4]

Hi,

I am not new to Oracle but I am new to creating packages.

Trying to convert to Oracle from MSSQL and getting my feet wet 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;
/
package created.

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;
/
package created.

This is a very simple package that I am trying to get to work so I can build on the concept from here.

Now I know this is going to sound like really dumb for someone more familiar with this, but a couple of questions:

1. Can the procedure in the package only be called within a block statement?
e.g.
BEGIN
dataObj.column_view;
END;
/

Cuz I don't seem to have any luck attempting something like:
EXEC <packageName>.<packageFunctionOrProcedure>

2. Am I taking the right approach for creating a procedure that will encapsulate and output the results of a select statement from a Cursor? I don't seem to have a problem creating procedures that are performing an INSERT, DELETE, or UPDATE but when I want to return a result set from a SELECT statement things tend to get a bit sticky. Oracle and MSSQL seem to differ a lot in this respect.

In either case I always get this error when I try to execute though I have clearly specified that no arguments are to be taken:

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

I understand the error but don't understand where its coming from in this case.
Any help is appreciated.
Thanks

[Dave44]

Hi,

I am not new to Oracle but I am new to creating packages.

Trying to convert to Oracle from MSSQL and getting my feet wet 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;
/
package created.

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;
/
package created.

This is a very simple package that I am trying to get to work so I can build on the concept from here.

Now I know this is going to sound like really dumb for someone more familiar with this, but a couple of questions:

1. Can the procedure in the package only be called within a block statement?
e.g.
BEGIN
dataObj.column_view;
END;
/

Cuz I don't seem to have any luck attempting something like:
EXEC <packageName>.<packageFunctionOrProcedure>

2. Am I taking the right approach for creating a procedure that will encapsulate and output the results of a select statement from a Cursor? I don't seem to have a problem creating procedures that are performing an INSERT, DELETE, or UPDATE but when I want to return a result set from a SELECT statement things tend to get a bit sticky. Oracle and MSSQL seem to differ a lot in this respect.

In either case I always get this error when I try to execute though I have clearly specified that no arguments are to be taken:

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

I understand the error but don't understand where its coming from in this case.
Any help is appreciated.
Thanks

Hi Mivey4,

1. Well the reason for the error is that your compiled procedure column_view has an out parameter of type ref cursor. that means whatever is calling this procedure will have to accept that datatype and do whatever it needs to do with it.

Its no different than any programming language, the API for the call says the calling method or procedure must accept a parameter or this type and direction.

2. as for is it the right approach... only you can answer that. you are trying to display the name column from the table, is it for a report or is it part of a larger process being performed.

for most procedures, yes, you will call them from a block named or anonymous.

so you would need to have a block with the same datatype then call
objdata.column_view(my_param_of_same_typ);

then open the results cursor and do what you need to do with it.

Is this helping?

[mivey4]

Hi Mivey4,

1. Well the reason for the error is that your compiled procedure column_view has an out parameter of type ref cursor. that means whatever is calling this procedure will have to accept that datatype and do whatever it needs to do with it.

Its no different than any programming language, the API for the call says the calling method or procedure must accept a parameter or this type and direction.

2. as for is it the right approach... only you can answer that. you are trying to display the name column from the table, is it for a report or is it part of a larger process being performed.

for most procedures, yes, you will call them from a block named or anonymous.

so you would need to have a block with the same datatype then call
objdata.column_view(my_param_of_same_typ);

then open the results cursor and do what you need to do with it.

Is this helping?

Dave44,

Yes, actually this helped a lot. For some reason all the documentation I've read led me to think that the requirement of an argument at the time of execution was directly related to the specifications of the argument list at the time the procedure was created for Oracle Stored Procedures.

So with this mindset, parameters specifying the keyword IN required an argument and OUT parameters didn't. That's what had me confused wondering why the error was getting thrown.

If I were calling a function, I'd immediately recognize the need for an argument if it required one but unlike the procedures, if a function doesn't require a passing argument it wouldn't complain if one weren't provided one at execution time.

Like -

CREATE OR REPLACE FUNCTION myFunction
RETURN varchar2
AS
myReturn varchar2(25);
BEGIN
myReturn:='Hello World';
RETURN myReturn;
END;
/

Could be executed as

select myFunction Value from dual;

VALUE
--------------
Hello World


Your explanation and more specifically your use of the keyword API started me viewing the problem from a different angle. In other words, if I were to write a program using the windows API; a call to most any of those structures would require a provided datatype to store the returned results back to the caller, even if the variable wasn't assigned any value initially.

Using this concept I declared a variable to pass to the procedure to return the results as you suggested:

var myReturn refcursor;
objdata.column_view(myReturn);

print myReturn;

NAME
------
HDV203
HFE230
JHN334
JHN500
JMV500
JMV500
MIV203
MIV334
MVE230

This is good. Now that I understand the concept a bit more clearly and believe I can put the remaining parts of my puzzle together.

Thanks for your assistance Dave44!