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
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
Comment