tdavis30
asked on
Execute a DBMS package from within another package????
I would like to create a package with one procedure that refreshes a materialized view. Here is the code......what am I doing wrong here?
SQL> CREATE OR REPLACE PACKAGE TNET.REFRESH AS
2 PROCEDURE TRANS_NETWORK_REFRESH;
3 END REFRESH;
4 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY TNET.REFRESH AS
2 PROCEDURE TRANS_NETWORK_REFRESH
3 IS
4 BEGIN
5 EXECUTE IMMEDIATE 'dbms_refresh.refresh('TRA NS_NETWORK _MV')';
6 END TRANS_NETWORK_REFRESH;
7 /
Warning: Package Body created with compilation errors.
SQL> CREATE OR REPLACE PACKAGE TNET.REFRESH AS
2 PROCEDURE TRANS_NETWORK_REFRESH;
3 END REFRESH;
4 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY TNET.REFRESH AS
2 PROCEDURE TRANS_NETWORK_REFRESH
3 IS
4 BEGIN
5 EXECUTE IMMEDIATE 'dbms_refresh.refresh('TRA
6 END TRANS_NETWORK_REFRESH;
7 /
Warning: Package Body created with compilation errors.
EXECUTE IMMEDIATE 'dbms_refresh.refresh(''TR ANS_NETWOR K_MV'')';
You have to use two single quotes (not double quote) inside the string.
You can use
sql> SHOW ERRORS
in SQL*Plus to get more information on compilation errors.
Regards,
JacekMycha
You have to use two single quotes (not double quote) inside the string.
You can use
sql> SHOW ERRORS
in SQL*Plus to get more information on compilation errors.
Regards,
JacekMycha
ASKER
These answers did not help. I think that I am not supposed to use EXECUTE IMMEDIATE when trying to execute a package from within pl/sql but I don't know enough. This is the error I am recieving:
SQL> CREATE OR REPLACE PACKAGE TNET.REFRESH AS
2 PROCEDURE TRANS_NETWORK_REFRESH;
3 END REFRESH;
4 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY TNET.REFRESH AS
2 PROCEDURE TRANS_NETWORK_REFRESH
3 IS
4 BEGIN
5 dbms_refresh.refresh('TRAN S_NETWORK_ MV');
6 END TRANS_NETWORK_REFRESH;
7 /
Warning: Package Body created with compilation errors.
SQL> SHOW ERRORS
Errors for PACKAGE BODY TNET.REFRESH:
LINE/COL ERROR
-------- -------------------------- ---------- ---------- ---------- ---------
6/26 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
begin end function package pragma procedure form
SQL>
SQL> CREATE OR REPLACE PACKAGE TNET.REFRESH AS
2 PROCEDURE TRANS_NETWORK_REFRESH;
3 END REFRESH;
4 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY TNET.REFRESH AS
2 PROCEDURE TRANS_NETWORK_REFRESH
3 IS
4 BEGIN
5 dbms_refresh.refresh('TRAN
6 END TRANS_NETWORK_REFRESH;
7 /
Warning: Package Body created with compilation errors.
SQL> SHOW ERRORS
Errors for PACKAGE BODY TNET.REFRESH:
LINE/COL ERROR
-------- --------------------------
6/26 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
begin end function package pragma procedure form
SQL>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SQL> CREATE OR REPLACE PACKAGE BODY TNET.REFRESH AS
2 PROCEDURE TRANS_NETWORK_REFRESH
3 IS
4 BEGIN
5 EXECUTE IMMEDIATE dbms_refresh.refresh('TRAN
6 END TRANS_NETWORK_REFRESH;
7 /
or use escape character. Because after 'dbms_refresh.refresh('
TRANS_NETWORK_MV is no longer considered as character string.
Best Regards