Array parameter in stored procedure

[trustno1]

Hallo Zusammen

Ich habe ein Problem mit einer stored procedure. Ich möchte gerne ein int array als Parameter mitgeben:

CREATE OR REPLACE PACKAGE BALANCES
AS
TYPE RESULT_CURSOR IS REF CURSOR;
TYPE accountFkArray is table of number;

PROCEDURE GET_BA_TURNOVERS_FOR_PL_M_ACC(
fromDate IN DATE,
toDate IN DATE,
result OUT RESULT_CURSOR);

END BALANCES;
/

CREATE OR REPLACE PACKAGE BODY BALANCES
AS
PROCEDURE GET_BA_TURNOVERS_FOR_PL_M_ACC(
fromDate IN DATE,
toDate IN DATE,
result OUT RESULT_CURSOR)
IS
BEGIN
OPEN result FOR
SELECT
dt.BAMJ_PLACCOUNT1FK,
dt.BAMJ_PLACCOUNT2FK,
dt.BAMJ_VALUEDATE,
dt.BAMJ_MANDANTFK,
dt.BAMJ_LEPLAMTCCYFK,
NVL(placc1.BAMJ_LEPLAMT1, 0) AS BAMJ_LEPLAMT1,
NVL(placc2.BAMJ_LEPLAMT2, 0) AS BAMJ_LEPLAMT2
FROM
(SELECT
BAMJ_PLACCOUNT1FK,
BAMJ_PLACCOUNT2FK,
BAMJ_VALUEDATE,
BAMJ_MANDANTFK,
BAMJ_LEPLAMTCCYFK
FROM BA_MAINJOURNAL
WHERE (BAMJ_PLACCOUNT1FK in (accountFkArray)
OR BAMJ_PLACCOUNT2FK in (accountFkArray))
AND BAMJ_VALUEDATE >= fromDate
AND BAMJ_VALUEDATE <= toDate
AND BAMJ_BOOKSTATUS1044 = 0
AND BAMJ_VIRTUALFL = 0
AND BAMJ_PLBOOKFL = 1
GROUP BY
BAMJ_PLACCOUNT1FK,
BAMJ_PLACCOUNT2FK,
BAMJ_VALUEDATE,
BAMJ_MANDANTFK,
BAMJ_LEPLAMTCCYFK
) dt,
(SELECT
BAMJ_PLACCOUNT1FK,
BAMJ_PLACCOUNT2FK,
BAMJ_VALUEDATE,
BAMJ_MANDANTFK,
BAMJ_LEPLAMTCCYFK ,
SUM(BAMJ_LEPLAMT1) AS BAMJ_LEPLAMT1
FROM BA_MAINJOURNAL
WHERE BAMJ_PLACCOUNT1FK in (accountFkArray)
AND BAMJ_VALUEDATE >= fromDate
AND BAMJ_VALUEDATE <= toDate
AND BAMJ_BOOKSTATUS1044 = 0
AND BAMJ_VIRTUALFL = 0
AND BAMJ_PLBOOKFL = 1
GROUP BY
BAMJ_PLACCOUNT1FK,
BAMJ_PLACCOUNT2FK,
BAMJ_VALUEDATE,
BAMJ_MANDANTFK,
BAMJ_LEPLAMTCCYFK
) placc1,
(SELECT
BAMJ_PLACCOUNT1FK,
BAMJ_PLACCOUNT2FK,
BAMJ_VALUEDATE,
BAMJ_MANDANTFK,
BAMJ_LEPLAMTCCYFK ,
SUM(BAMJ_LEPLAMT2) AS BAMJ_LEPLAMT2
FROM BA_MAINJOURNAL
WHERE BAMJ_PLACCOUNT2FK in (accountFkArray)
AND BAMJ_VALUEDATE >= fromDate
AND BAMJ_VALUEDATE <= toDate
AND BAMJ_BOOKSTATUS1044 = 0
AND BAMJ_VIRTUALFL = 0
AND BAMJ_PLBOOKFL = 1
GROUP BY
BAMJ_PLACCOUNT1FK,
BAMJ_PLACCOUNT2FK,
BAMJ_VALUEDATE,
BAMJ_MANDANTFK,
BAMJ_LEPLAMTCCYFK
) placc2
WHERE dt.BAMJ_VALUEDATE = placc1.BAMJ_VALUEDATE (+)
AND dt.BAMJ_MANDANTFK = placc1.BAMJ_MANDANTFK (+)
AND dt.BAMJ_LEPLAMTCCYFK = placc1.BAMJ_LEPLAMTCCYFK (+)

AND dt.BAMJ_PLACCOUNT1FK = placc1.BAMJ_PLACCOUNT1FK (+)
AND dt.BAMJ_PLACCOUNT2FK = placc1.BAMJ_PLACCOUNT2FK (+)

AND dt.BAMJ_VALUEDATE = placc2.BAMJ_VALUEDATE (+)
AND dt.BAMJ_MANDANTFK = placc2.BAMJ_MANDANTFK (+)
AND dt.BAMJ_LEPLAMTCCYFK = placc2.BAMJ_LEPLAMTCCYFK (+)

AND dt.BAMJ_PLACCOUNT1FK = placc2.BAMJ_PLACCOUNT1FK (+)
AND dt.BAMJ_PLACCOUNT2FK = placc2.BAMJ_PLACCOUNT2FK (+);

END GET_BA_TURNOVERS_FOR_PL_M_ACC;
END BALANCES_TURNOVERS;
/

Ich habe noch mehr procedures in dem package, aber der Übersichtshalber nicht gepostet.

Beim Kompilieren bekomme ich den Fehler: ORA-00904: "ACCOUNTFKARRAY": ungültiger Bezeichner.

Kann mir jemand weiterhelfen? Was habe ich da vergessen resp. übersehen?

Vielen Dank
mfg
Andrea

[jum]

Sieht trotzdem noch hinreichend kompliziert aus , hast Du schon mal versucht den TYP accountFkArray außerhalb des Packages zu deklarieren ?
Außerdem bin ich mir nicht sicher bei der Syntax:

...WHERE BAMJ_PLACCOUNT2FK in (accountFkArray)

[trustno1]

Danke für den Hinweis... Ich habe den type mal ausserhalb deklariert:

CREATE OR REPLACE
type NUMARRAY as table of number;

Ich habe dann die Variable wie folgt deklariert:

PROCEDURE GET_BA_TURNOVERS_FOR_PL_M_ACC(
fromDate IN DATE,
toDate IN DATE,
accountFK in out numarray,
result OUT RESULT_CURSOR)

Dann bekomme ich aber die Meldung:

ORA-00932: Inkonsistente Datentypen: NUMBER erwartet, NUMARRAY erhalten...

aber mein NUMARRAY hat ja als Datentyp NUMBER...

hm.....

Tja, das mit der Syntax kann auch sein, darum hätte ich mich anschliessend gekümmert

[dimitri]

Hi,

hier sind mehrere (Denk)fehler vorhanden. Zum einen hast Du nur eine Typendefinition von accountFkArray angegeben jedoch nie eine echte Variable davon deklariert.
Des weiteren mischt Du SQL mit PL/SQL Bestandteilen. Auch wenn diese beiden Sprachen eng zusammenarbeiten und viele Schnittstellen aufweisen, sind es doch zwei unterschiedliche Sprachen, die in unterschiedlichen Kontexten von unterschiedlichen Laufzeitumgebungen ausgeführt werden.

Es ist daher nicht einfach möglich eine PL/SQL Collection als Wert in ein SQL einzubinden. Es ist möglich eine Nested Table über den CAST Operator in ein SQL einzubinden als ob es eine Tabelle wäre. Dazu muss zusätzlich aber im SQL Kontext auch noch ein passender TYPE definiert werden.

Damit sollte das Thema auch beendet sein, denn wenn mehrere Werte erst zur Laufzeit bekannt sind, dann verwendet man Tabellen um diese zu übergeben. Das ist die Art und Weise wie es (in Oracle) funktioniert. Leg dir also einmalig eine Global Temporary Table (GTT) an und schreibe deine Werte dort hinein. Das ist einfach, verständlich und der Optimizer kann, wenn er den Ausführungsplan errechnet, auch die Werte verwenden die in der Tabelle vorhanden sind. Diese Möglichkeit wäre bei deinem Ansatz nicht gegeben.

Fazit: Weg mit den Array - die haben in dem SQL nichts zu suchen.

Dim