| MV creation error PLS-512: Cannot directly access remote package variable or cursor [message #613450] |
Wed, 07 May 2014 02:23  |
Buchas
Messages: 101
Registered: March 2006
|
Senior Member |
|
|
Hello,
I am trying to create a materialized view that uses DBlink:
CREATE MATERIALIZED VIEW DWPIPE.RETURNS_FACT_MV
NOLOGGING USING INDEX TABLESPACE INDX
REFRESH COMPLETE
AS
SELECT
SUM(POLISAS.GET_RODIKLIS.SKOLA_PO_NUTRAUKIMO_PROD@DISBIG_DISNEW( AP.ID, NULL)) AS SKOLA_PO_NUTRAUKIMO
FROM
POLISAS.APDRAUSTAS_PRODUKTAS_T@DISBIG_DISNEW AP
I get the following error:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-512: Implementation Restriction: 'VALIUTOS_TIPAS_BAZINE': Cannot
directly access remote package variable or cursor
Procedure SKOLA_PO_NUTRAUKIMO_PROD is on another DB and is declared in the following way:
CREATE OR REPLACE PACKAGE POLISAS.GET_RODIKLIS AS
VALIUTOS_TIPAS_BAZINE CONSTANT NUMBER := 0;
FUNCTION SKOLA_PO_NUTRAUKIMO_PROD(N_AP_ID_IPARM IN NUMBER,
DT_APSKAICIUOTI_DATAI_IPARM IN DATE,
VALIUTOS_TIPAS_PARM IN NUMBER DEFAULT VALIUTOS_TIPAS_BAZINE )
RETURN NUMBER;
END;
If I replace a named constant VALIUTOS_TIPAS_BAZINE with its value 0, I get no error creating MV in another DB.
I also get no error, if I only execute the SELECT statement of MV in another DB.
I also get no error, if I create MV in the same DB (without DB link).
Now please tell me, why can't I use a named constant in a function declaration while creating a materialized view in another DB?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: MV creation error PLS-512: Cannot directly access remote package variable or cursor [message #613468 is a reply to message #613450] |
Wed, 07 May 2014 07:08  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Buchas wrote on Wed, 07 May 2014 03:23Now please tell me, why can't I use a named constant in a function declaration while creating a materialized view in another DB?
Well, I agree Oracle could do a better job realizing it is package public global constant not package public global variable. Anyway, there is a bit of a "catch 22". Moving package public global variable/constant/cursor from package specification to package body would make it private and would eliminate the issue. However constant is used as parameter default value in function specification which creates that "catch 22". So what could you do? You could make constant private and use overloading:
CREATE OR REPLACE
PACKAGE GET_RODIKLIS
AS
FUNCTION SKOLA_PO_NUTRAUKIMO_PROD(
N_AP_ID_IPARM IN NUMBER,
DT_APSKAICIUOTI_DATAI_IPARM IN DATE,
VALIUTOS_TIPAS_PARM IN NUMBER
)
RETURN NUMBER;
FUNCTION SKOLA_PO_NUTRAUKIMO_PROD(
N_AP_ID_IPARM IN NUMBER,
DT_APSKAICIUOTI_DATAI_IPARM IN DATE
)
RETURN NUMBER;
END;
/
CREATE OR REPLACE
PACKAGE BODY GET_RODIKLIS
AS
VALIUTOS_TIPAS_BAZINE CONSTANT NUMBER := 0;
FUNCTION SKOLA_PO_NUTRAUKIMO_PROD(
N_AP_ID_IPARM IN NUMBER,
DT_APSKAICIUOTI_DATAI_IPARM IN DATE,
VALIUTOS_TIPAS_PARM IN NUMBER
)
RETURN NUMBER
IS
BEGIN
RETURN N_AP_ID_IPARM;
END;
FUNCTION SKOLA_PO_NUTRAUKIMO_PROD(
N_AP_ID_IPARM IN NUMBER,
DT_APSKAICIUOTI_DATAI_IPARM IN DATE
)
RETURN NUMBER
IS
BEGIN
RETURN SKOLA_PO_NUTRAUKIMO_PROD(
N_AP_ID_IPARM,
DT_APSKAICIUOTI_DATAI_IPARM,
VALIUTOS_TIPAS_BAZINE
);
END;
END;
/
Now:
SQL> CREATE MATERIALIZED VIEW RETURNS_FACT_MV
2 NOLOGGING
3 USING INDEX
4 REFRESH COMPLETE
5 AS
6 SELECT SUM(GET_RODIKLIS.SKOLA_PO_NUTRAUKIMO_PROD@D1MAXTRAN(1,NULL)) AS SKOLA_PO_NUTRAUKIMO
7 FROM DUAL@D1MAXTRAN AP
8 /
Materialized view created.
SQL> SELECT *
2 FROM RETURNS_FACT_MV
3 /
SKOLA_PO_NUTRAUKIMO
-------------------
1
SQL>
Sėkmės!
SY.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
though there are 38 packages with "default"