Home » SQL & PL/SQL » SQL & PL/SQL » Calling a function in a package (merged by CM) (Oracle 10.2)
Calling a function in a package (merged by CM) [message #446657] Tue, 09 March 2010 17:33 Go to next message
akabir77
Messages: 6
Registered: May 2009
Junior Member
I have this following pakg

CREATE OR REPLACE PACKAGE pkg_test AS

   -- Create a table type of the table you want
   TYPE tbl_test IS varray(100) of VARCHAR2(30); 


   -- Function that will return the table type
   FUNCTION fnc_test RETURN tbl_test;

-- End package
END;


CREATE OR REPLACE PACKAGE BODY pkg_test AS


   FUNCTION fnc_test RETURN tbl_test IS

   -- type table_name_va is varray(100) of VARCHAR2(30);  
-- Variable of the type tbl_test
      var_tbl_test tbl_test; 
   BEGIN
     var_tbl_test := tbl_test(  
     'NIPR_TRANS_STATUS',  
     'NIPR_TRANS_NOTES',  
     'NIPR_TRANS_GROUP',  
     'NIPR_TERM_REASON',  
     'NIPR_RETRIEVE_HIS',  
     'NIPR_RESP_MESSAGE',  
     'NIPR_RESPONSE',  
     'NIPR_PICS',  
     'NIPR_PHONE',  
     'NIPR_PAYMENT',  
     'NIPR_OLD_ADDRESS',  
     'NIPR_NON_UNIFORM',  
     'NIPR_NAME',  
     'NIPR_LOA',  
     'NIPR_LICENSE_KEY',  
     'NIPR_LICENSE',  
     'NIPR_LEGACY',  
     'NIPR_INT_RESPONSE',  
     'NIPR_FILES',  
     'NIPR_EMPLOY_HIST',  
     'NIPR_EMAIL',  
     'NIPR_DOCUMENTATION',  
     'NIPR_COUNTY',  
     'NIPR_CONTACT',  
     'NIPR_BACKGROUND',  
     'NIPR_AUTHORIZATION',  
     'NIPR_AUDIT_LOG', 
     'NIPR_APPOINTER',  
     'NIPR_APPLICANT',  
     'NIPR_AMENDED',  
     'NIPR_AFFILIATION_OWNER',  
     'NIPR_ADDRESS',  
     'NIPR_MAIN'); -- NIPR_MAIN should always be last

      -- Returns the table type populated with roles of the user
     RETURN var_tbl_test;


   -- End function
   END;    
   -- End package body
END; 


But i am having problem calling this to test it.

declare
    TYPE tbl_test IS varray(100) of VARCHAR2(30); 
    var_tbl_test tbl_test;
    begin
    var_tbl_test:= pkg_test.fnc_test;

   FOR i in var_tbl_test.first..var_tbl_test.last  
        LOOP  
            dbms_output.put_line(var_tbl_test(i));
        END LOOP; 
end;
 /   


I tried this but getting this error
var_tbl_test:= pkg_test.fnc_test();
*
ERROR at line 5:
ORA-06550: line 5, column 20:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 5:
PL/SQL: Statement ignored

Please help
Re: Calling a function in a package (merged by CM) [message #446660 is a reply to message #446657] Tue, 09 March 2010 17:46 Go to previous messageGo to next message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
This:
declare
    TYPE tbl_test IS varray(100) of VARCHAR2(30); 
    var_tbl_test tbl_test;

should be:
declare
    var_tbl_test pkg_test.tbl_test;


For this you have to use the same type, rather than a different type with the same structure.

EDIT: added missing tag

[Updated on: Wed, 10 March 2010 07:21]

Report message to a moderator

Re: Calling a function in a package (merged by CM) [message #446780 is a reply to message #446660] Wed, 10 March 2010 07:15 Go to previous message
akabir77
Messages: 6
Registered: May 2009
Junior Member
Thanks a lot. it worked.
Previous Topic: Which query would execute quickest
Next Topic: Trigger
Goto Forum:
  


Current Time: Wed Feb 19 03:27:34 CST 2025