Home » SQL & PL/SQL » SQL & PL/SQL » passing array to function (plsql / oracle 10g [10.1] / xp)
passing array to function [message #281738] Mon, 19 November 2007 03:09 Go to next message
sammeras
Messages: 28
Registered: September 2007
Location: Israel
Junior Member

Hello,
I am new here and in ORACLE pl/sql.
The array dosn't passing to the body of the function.
If Ireplace the:
x InStrTab := InStrTab(v_object_name); -- Get the objects name 
from the select.
TO
x InStrTab := InStrTab('TBL1','TB1'); -- TBL1, TB1 are tables in my DB

The function work well.

The Function:

CREATE OR REPLACE TYPE InStrTab IS TABLE OF VARCHAR2(4000 BYTE);
/
CREATE OR REPLACE TYPE gett_arr1 AS OBJECT (
      v_OBJECT_ID     NUMBER
);
/

CREATE OR REPLACE TYPE gett_arr_TBL AS TABLE OF gett_arr1;
/


CREATE OR REPLACE FUNCTION fn_gett_arr(
      v_type IN VARCHAR2,
      v_schema_name IN VARCHAR2,
      v_object_name IN VARCHAR2
)
RETURN gett_arr_TBL AS v_ret  gett_arr_TBL;
      v_stam  VARCHAR2(250);
      v_int PLS_INTEGER;
      x InStrTab := InStrTab(v_object_name);
BEGIN
      dbms_output.put_line('The Objects var :'||v_object_name);
      SELECT
            CAST(
            multiset(
                  SELECT OBJECT_ID FROM ALL_OBJECTS WHERE OBJECT_NAME IN(SELECT TO_CHAR(column_value) FROM TABLE(CAST(x AS InStrTab)))
                  AND OWNER LIKE ''||v_schema_name||''      
            ) AS gett_arr_TBL) INTO v_ret FROM dual;
      RETURN v_ret;
      
END;
/
sho err


SELECT * FROM TABLE(fn_gett_arr('UP','SYS','''TBL1'',''TB1'''));




can anybody take a look to my function ?

Thanks guys.
SAM

[Mod: added code tags]

[Updated on: Mon, 19 November 2007 07:51] by Moderator

Report message to a moderator

Re: passing array to function [message #281797 is a reply to message #281738] Mon, 19 November 2007 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: passing array to function [message #281831 is a reply to message #281797] Mon, 19 November 2007 08:01 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The line:
x InStrTab := InStrTab(v_object_name);
creates an array with ONE element in it, and that element is ('TBL1','TB1').

In your alternate:
x InStrTab := InStrTab('TBL1','TB1');

this creates an array with TWO elements.

You can make it work by changing the function declaration so that v_object_name is of type InStrTab rather than VARCHAR2.

Then when you call the function, you use the syntax:
SELECT * 
FROM TABLE(fn_gett_arr('UP','SYS',InstrTab('TBL1','TB1')));


Ross Leishman
Re: passing array to function [message #281841 is a reply to message #281831] Mon, 19 November 2007 08:36 Go to previous message
sammeras
Messages: 28
Registered: September 2007
Location: Israel
Junior Member

Thanks
Previous Topic: round function in dates
Next Topic: Unique Cosntraint Without Unique Index
Goto Forum:
  


Current Time: Fri Dec 09 21:45:36 CST 2016

Total time taken to generate the page: 0.10570 seconds