I want to execute a pl/sql procedure using dbms_pipe package.
But it's compiling,While executing the error is coming..Can u help me/.....
EXAMPLE IS LIKE THIS;
SQL> CREATE OR REPLACE PROCEDURE sendmessage(msg VARCHAR2) AS
2 status_id NUMBER;
3 BEGIN
4 DBMS_PIPE.PACK_MESSAGE(LENGTH(msg));
5 DBMS_PIPE.PACK_MESSAGE(msg);
6 status_id := DBMS_PIPE.SEND_MESSAGE('send_message');
7 IF status_id != 0
8 THEN
9 raise_application_error(-20099, 'send error');
10 END IF;
11 END sendmessage;
12 /
Procedure created.
SQL> exec sendmessage('hello');
BEGIN sendmessage('hello'); END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04067: not executed, package body "SCOTT.DBMS_PIPE" does not exist
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SCOTT.SENDMESSAGE", line 4
ORA-06512: at line 1 |
Did you create synonyms under the SCOTT schema ?
Or did you run the catalog scripts connected as the SCOTT user?
Check the all_synonyms and all_objects data dictionary views to see where DBMS_PIPE points to |
I am having error free packages but whenever i run my concern java file i am getting the error as ora-04068 but at the same time i am getting the valid state of that package at backend. why there is such kind of problem and in case I compile the package it works fine but as it is been used by different user it again gets invalid in frontend in some minutes. I had checked that with no-compilation but it doesn't work.
PLEASE if any one of you can HElp me....I would be grateful to you...alll |
Hi
I am getting this error when i am creating materialized view. 1 month before i upgraded my oracle from 9.2.0.1.0 to 9.2.0.8.0. But after that it was working fine. Two days before i dropped the materialized view and i tried to create again. At that time i got this error.
sQL> CREATE MATERIALIZED VIEW CBS_MERCHANT_MVIEW
BUILD IMMEDIATE
REFRESH FAST
START WITH SYSDATE
NEXT sysdate + 1/(60*24)
ON DEMAND
AS
SELECT * FROM TBA_FCET_FOREX_INTERFACE_TBL@trgFIN; 2 3 4 5 6 7 8
SELECT * FROM TBA_FCET_FOREX_INTERFACE_TBL@trgFIN
*
ERROR at line 8:
ORA-04068: existing state of packages has been discarded
ORA-04063: package body "SYS.DBMS_LOGSTDBY" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SYS.DBMS_JOB", line 122
ORA-06512: at "SYS.DBMS_IREFRESH", line 186
ORA-06512: at "SYS.DBMS_IREFRESH", line 369
ORA-06512: at "SYS.DBMS_REFRESH", line 88
ORA-06512: at "SYS.DBMS_REFRESH", line 62
ORA-06512: at "SYS.DBMS_ISNAPSHOT", line 76
ORA-01403: no data found
ORA-06512: at line 1
wat could be the reason for this error. Can someone help me out... |
| : -> 16-APR-2008 11:28:29 | Recompile SYS objects | DbMotive | Reply |
You have to recompile the SYS objects, as there seems to be a problem with it.
Logon to your database using "/ as sysdba" and execute
@?/rdbms/admin/utlrp
After that you might want to check for invalid objects in the SYS schema:
select object_name, object_type from dba_objects where owner='SYS' and status='INVALID'
Also make sure the patch was successful, check v$registry for that purpose and make sure all is ok and has the correct version.
|
Hi all,
I created materialized view in Database B. The master table is in Database A.
If i shutdown the database B and bring it up again, the next refresh time in dba_jobs table
in database A is coming as 01-jan-00. It is not getting refreshed automatically. The
job_queue_process is 10. wat could be the reason, for the refresh failure. Can someone help me out..
thanks in advance... |
| : -> 18-APR-2008 08:29:33 | Job is broken | DMotive | Reply |
Your job is broken, the date will be 01-jan-4000.
Check the status of the job, and set it to unbroken using:
exec dbms_job.broken(<jobid>, false);
|
| :: -> 18-APR-2008 10:18:56 | materialized view prob | prem | Reply |
It started to work after i run this command. exec dbms_job.broken...
I want to know why the job is getting broken after i shutdown the database....
thanks for ur reply... |
| ::: -> 18-APR-2008 10:26:05 | Check the job queue files in the background dump destination | DbMotive | Reply |
In order to know what is wrong, you have to check the logfiles.
The log files for the job queue processes are in the background dump destination of the database.
In order to find this, login as a user with dba privileges and execute command:
SQL> show parameter background_dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /opt/oracle/admin/MYDB/bdum p
Navigate to this directory and look for files of the format:
mydb_j000_<spid>.trc
Have a look at the files, they will tell you what is wrong.
|
Home