output parameter/pl-sql table type

From: Basavaraja, Ravindra <Ravindra.Basavaraja_at_t-mobile.com>
Date: Wed, 01 Oct 2003 18:09:42 -0800
Message-ID: <F001.005D1B71.20031001180942@fatcity.com>

I have a package Spec that has a types defined as

TYPE FIRSTNAMETABLE IS TABLE OF V_CUS.FIRSTNAME%TYPE INDEX BY BINARY_INTEGER;
TYPE LASTNAMETABLE IS TABLE OF V_CUS.LASTNAME%TYPE INDEX BY BINARY_INTEGER;
TYPE EMAILADDRESSTABLE IS TABLE OF V_CUS.EMAILADDRESS%TYPE INDEX BY BINARY_INTEGER;
TYPE ISGRANTEDMASTERPRIVILEGESTABLE IS TABLE OF V_CUS.ISGRANTEDMASTERPRIVILEGES%TYPE INDEX BY BINARY_INTEGER; TYPE USERNAMETABLE IS TABLE OF CUS_EN.USERNAME%TYPE INDEX BY BINARY_INTEGER;
TYPE PASSWORDTABLE IS TABLE OF CUS_EN.PASSWORD%TYPE INDEX BY BINARY_INTEGER;
TYPE MSISDNTABLE IS TABLE OF V_CUS.MSISDN%TYPE INDEX BY BINARY_INTEGER;

TYPE OSUCCESSTABLE      IS TABLE OF CHAR(1) INDEX BY BINARY_INTEGER;
TYPE OERRORCODETABLE        IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE OERRORMSGTABLE         IS TABLE OF VARCHAR2(60) INDEX BY
BINARY_INTEGER;
TYPE ADDRESS1TABLE          IS TABLE OF ADD.ADDRESS1%TYPE INDEX BY
BINARY_INTEGER;
TYPE ADDRESS2TABLE          IS TABLE OF ADD.ADDRESS2%TYPE INDEX BY
BINARY_INTEGER;
TYPE BILLCITYTABLE          IS TABLE OF ADD.CITY%TYPE INDEX BY
BINARY_INTEGER;
TYPE BILLSTATETABLE          IS TABLE OF ADD.STATE%TYPE INDEX BY
BINARY_INTEGER;
TYPE BILLZIPTABLE          IS TABLE OF ADD.ZIPCODE%TYPE INDEX BY

BINARY_INTEGER;
TYPE BILLFIRSTNAMETABLE IS TABLE OF CUS.FIRSTNAME%TYPE INDEX BY BINARY_INTEGER;
TYPE BILLLASTNAMETABLE IS TABLE OF CUS.LASTNAME%TYPE INDEX BY BINARY_INTEGER;
TYPE SERVICELEVELTABLE IS TABLE OF CUSCODE.SERVICECODEID%TYPE INDEX BY BINARY_INTEGER;

Procedure             GETUSER
     (  	pMSISDN   	    IN	VSTR_Customer.MSISDN%TYPE,
    	    oFirstName		    OUT	FIRSTNAMETABLE,
    	    oLastName		    OUT	LASTNAMETABLE,
            oBillingFirstName   OUT BILLFIRSTNAMETABLE,
            oBillingLastName    OUT BILLLASTNAMETABLE,
            oEmailAddress       OUT EMAILADDRESSTABLE,
			oMSISDN			    OUT	MSISDNTABLE,
			oIsGrantedMasterPrivileges	OUT
ISGRANTEDMASTERPRIVILEGESTABLE,
			oServiceLevel		OUT SERVICELEVELTABLE,
         	oUserName   	    OUT USERNAMETABLE,
 	    	oPassword		    OUT	PASSWORDTABLE,
            oAddress1           OUT	ADDRESS1TABLE,
            oAddress2           OUT	ADDRESS2TABLE,
            oCity               OUT	BILLCITYTABLE,
            oState              OUT	BILLSTATETABLE,
            oZip                OUT	BILLZIPTABLE,
            oErrorCode          OUT OERRORCODETABLE,
            oErrorMsg           OUT oERRORMSGTABLE

   );

I want to execute this above procedure by passing the IN paramter and to display the OUT paraeter in SQLPLUS.

I tried using..trying to print one of the paramter.I believe i am getting this error paramter of TABLE type.Looked up on metalink,didn't find aything useful

declare

 	myFirstname pkg_streamline.Firstnametable;
	myLASTNAME pkg_streamline.LASTNAMETABLE;
 	myISGRANTEDMASTERPRIVILEGES
pkg_streamline.ISGRANTEDMASTERPRIVILEGESTABLE;
 	myUSERNAME pkg_streamline.USERNAMETABLE;
 	myPASSWORD  pkg_streamline.PASSWORDTABLE;
 	myMSISDN pkg_streamline.MSISDNTABLE;
 	myOERRORCODE pkg_streamline.OERRORCODETABLE;
 	myOERRORMSG pkg_streamline.OERRORMSGTABLE;
 	myADDRESS1  pkg_streamline.ADDRESS1TABLE;
 	myADDRESS2 pkg_streamline.ADDRESS2TABLE;
 	myBILLCITY pkg_streamline.BILLCITYTABLE;
 	myBILLSTATE pkg_streamline.BILLSTATETABLE;
 	myBILLZIP pkg_streamline.BILLZIPTABLE;
 	myBILLFIRSTNAME pkg_streamline.BILLFIRSTNAMETABLE;
 	myBILLLASTNAME pkg_streamline.BILLLASTNAMETABLE;
	-- myEMAILADDRESS pkg_streamline.EMAILADDRESSTABLE;
	myEM pkg_streamline.EMAILADDRESSTABLE;
 	mySERVICELEVEL pkg_streamline.SERVICELEVELTABLE;

begin  

pkg_streamline.GETUSERFOR(7777777777,myFirstName,myLastName,myBillFirstName,         

myBillLastName,myEM,myMSISDN,myIsGrantedMasterPrivileges,myServiceLevel,         

myUserName,myPassword,myAddress1,myAddress2,mybillCity,mybillState,mybillZip ,myoErrorCode,myoErrorMsg);

        dbms_output.put_line(myFirstName);
end;
/

I get the following error
SQL> /
dbms_output.put_line(myFirstName);
*
ERROR at line 24:

ORA-06550: line 24, column 1:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 24, column 1:

PL/SQL: Statement ignored

HOW DO I GET THIS WORKING.I TRIED DECLARING VARIABLE OF TABLETYPE AND TO PRINT IN SQLPLUS BUT I STILL
THE SAME ERROR PLS-00306.
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Basavaraja, Ravindra
  INET: Ravindra.Basavaraja_at_T-Mobile.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Oct 01 2003 - 21:09:42 CDT