View Single Post
 
Old March 21st, 2007, 12:43 PM
mindScape mindScape is offline
Registered User
 
Join Date: Mar 2007
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Package Body Problem

Hi,

I hope someone can give some help to resolve this problem. I am trying to create a package that encapsulates a stored procedure to return the results to the user.

It is very simple and my understanding is if you specify a parameter as being OUT then this means the program requires no input from the user to work but using this code:

CREATE OR REPLACE PACKAGE tables
IS
  TYPE CURSOR_TYPE IS REF CURSOR;
  PROCEDURE viewColumnsFor (out_columns OUT CURSOR_TYPE);
END;
/
CREATE OR REPLACE PACKAGE BODY tables
IS
  PROCEDURE viewColumnsFor (out_columns OUT CURSOR_TYPE)
    IS
    BEGIN

    OPEN out_columns FOR
         SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, DATA_DEFAULT
         FROM USER_TAB_COLUMNS
         WHERE TABLE_NAME = 'STRINGS';
    END;
END;
/

I based this off of an example and all it is supposed to do in this case is return certain columns from the USER_TAB_COLUMNS table for the TABLE_NAME STRINGS.

But when I execute the package

EXEC TABLES.viewColumnsFor

I get this error:

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

What am I doing wrong? I am used to writing procedures in MSSQL and it is much simpler to me but I want to learn the correct way to do this in ORACLE.

It looks like its still expecting an input variable though I didn't specify it in the procedure.

help plz