Recent content by engineer2100

so as mentioned return a REF CURSOR CREATE OR REPLACE PACKAGE PKG_REPORT_GEN IS pREPORT_DATA IS REF CURSOR; END; / CREATE OR REPLACE PROCEDURE RETURN_DATA_FOR_REPORT (pData OUT PKG_REPORT_GEN.pREPORT_DATA) IS BEGIN OPEN pDATA FOR SELECT A.WORKTYPE, COUNT(*) AS ITEMCOUNT FROM...

return a REF CURSOR as an OUT parameter from the SP or as Return parameter from a Function. HTH Engi

Bill, I have the link myself but what i was looking at was what you seasoned DBA's have to say. Thanks Engi

Santa, Bill and all the gurus!! can we start with a post of whats news in Oracle 11g start from 1. Architecture, 2. Basics SQL, 3. PL/SQL, 4. Administration 5. Tools Etc.. this could be either one thread or one for each of the above bulleted point. Thanks -Engi

Actually this issue is caused by an incorrect character set. I am confident on this because we recently faced this issue and changing the character set made the difference....

Are you by anychance running applications that make use of connection pools? may be that is the place where you can start your search.. often the applications connect to the DB and fail to disconnect...

Since you setting the columns conditionally it could be that one or two conditions might have got satisfied and you are still referring to those variables. You should Execute Immediate based on how many variables are being set/referred. HTH -Engi

As Cooper pointed out, the issue is with the line below ( :new.msgdet_message is not like '%B12%');

Alter System Kill Session 'Sid,Serial#'; SID and Serial# are from V$SESSION. HTH -Engi

Got some time before I leave.... Why not kill the sessions after vieweing V$Session?

Finally some light at the end of the tunnel!!! anyways, since now it is in the safe hands of Santa and Bill, I think i will take a walk out.. Mexico here i come!!!

Did you gather latest statistics? To find when the Statistics were gathered fire the below SQL Select table_name, last_analyzed from user_tables where table_name in ('IKS_BINARYDOCUMENT', 'IKS_ASCIIDOCUMENT') If not quickly gather statistics and then try to execute the queries! Let us know.

Did you happen to run the above queries? Please send me the output of HIGH I/O Statements - atleast top 10-15 ones

That definitely is not a healthy sign at all. Actually you need to run the below to understand on an average how disk read have happened over the executions. -- IDENTIFY BAD SQL's --HIGH CPU Statements select trunc(buffer_gets/executions),trunc(plsql_exec_time/executions),vsa.* from...

yes it does only that. Your requirement is not catered to as i see it.