Iterer over en collection i PL/SQL
Hejsa jeg har følgende struktur// adviser type as OBJECT
create or replace TYPE adviser AS OBJECT
EXTERNAL name 'oracle.bean.JAdviser' LANGUAGE JAVA USING SQLData
(
agentKey varchar2(100)
external name 'java.lang.String',
constructor function adviser
return self as result,
member function getKey
return varchar2 as language java
name 'oracle.bean.JAdviser.getKey() return java.lang.String',
member function getSQLTypeName
return varchar2 as language java
name 'oracle.bean.JAdviser.getSQLTypeName() return java.lang.String'
);
// collection at typen adviser
create or replace TYPE ADVISERLIST is TABLE OF adviser;
// funktion der returnerer en ADVISERLIST
create or replace FUNCTION
testFunction (tableName varchar2) RETURN ADVISERLIST
AS LANGUAGE JAVA
NAME 'oracle.bean.TestFunction.getAdvisers (java.lang.String) return oracle.sql.ARRAY';
Jeg prøver via PL/SQL at kalde min funktion testFunction
Hvis jeg skriver
select testFunction('dummy') from dual; virker det
jeg får output
TESTFUNCTION('DUMMY')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WEATHR.ADVISER(WEATHR.ADVISER,WEATHR.ADVISER,WEATHR.ADVISER)
1 rows selected
Dette output er rigtigt idet min funktion returnerer 3 x ADVISER typer i min ADVISERLIST.
Men jeg vil gerne iterere over min collection og f.eks. kalde metoden ADVISER.getKey(). JEg har skrevet følgende PL/SQL
DECLARE
TYPE collection_advisor is VARRAY(3) of ADVISER;
kajkvaj collection_advisor;
BEGIN
kajkvaj := testFunction('dummy');
END;
Men får følgende fejl:
Error starting at line 1 in command:
DECLARE
TYPE collection_advisor is VARRAY(3) of ADVISER;
kajkvaj collection_advisor;
BEGIN
kajkvaj := testFunction('dummy');
END;
Error report:
ORA-06550: linje 5, kolonne 14:
PLS-00382: expression is of wrong type
ORA-06550: linje 5, kolonne 3:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
Jeg har søgt nettet tynd uden held, så jeg håber virkelig der er nogle af jer hardcore database folk der kan hjælpe mig :-)
