SQL & PL/SQL :: ORA-24338 / Statement Handle Not Executed

Jun 14, 2011

create table myex(qid number, lid number, myname varchar2(20), status varchar2(30));
insert into myex values(1,1,'uu',null);
commit;
CREATE OR REPLACE PACKAGE mypack
IS
PROCEDURE p_get (in_qid myex.qid%TYPE, out_mycur OUT SYS_REFCURSOR);
end mypack;
/

[code].....

Note the one record in the table does not satisfy the cursor query criteria, So I try to pass in 1 to see what happens.

In sqlplus:
var out_mycur refcursor;
begin
mypack.p_get(1,:out_mycur);
end;
/
print out_mycur;

ORA-24338: statement handle not executed

View 5 Replies


ADVERTISEMENT

PL/SQL :: ORA-24338 - Statement Handle Not Executed?

Oct 1, 2012

For the attached stored Proc - I am getting the following error:

ORA-24338:statement handle not executed (Database Vendor Code: 24338)

I cannot attach the stored proc as it is too big and there is no option for attachment..

View 4 Replies View Related

Find DDL Statement Executed On A Table?

May 19, 2011

I want to know the DDL statements executed on a table. Example:

i have a table test1 with structure as below: test1 (aa number, bb varchar2(10))

After some period of time, one DDL statement to alter the column is issued.

alter table test1 modify (bb varchar2(30));

so new table structure is: test1(aa number, bb varchar2(30))

Basically i would like to find these 2 DDL statements executed on this table - test1, so that i compare which column get modified and what modification done.

Is this information stored in any data dictionary table?

note - audit, flashback option not enabled in this database.

View 3 Replies View Related

Database SQL Statement Executed Time

Oct 23, 2008

If I like to identify the executed time of a particular SQL Statement, beside v$sql, is there any other dictionary or lookup table that have this piece of information?

Why v$sql is not sufficient, because this is a recurrsive update statement which is regularly called, and thus the last_load_time is overwritten.

My archivelog had been purged due to our scheduled backup.

Is there any other way to identify when the particular SQL statement is executed?

View 4 Replies View Related

SQL & PL/SQL :: Convert Update Query To Dynamic To Be Executed From Execute Immediate Statement

Oct 25, 2010

I want to convert the below SQL to a dynamic sql to be executed from execute immediate statement.

UPDATE transaction SET loannum = lpad(loannum,12,'0')

View 15 Replies View Related

Forms :: How To Handle Multi Row Select Statement

Feb 3, 2011

I am doing simple project, where i need to check date(form which we give in run time) with database column date.problem i have written the code for program now i found that select statement is return multi rows.

code is

when_button_pressed trigger.

declare
fdcode dmas.dcode%type;
fcodno dmas.codno%type;
famtrs dmas.amtrs%type;
fdate donres.rdate%type;
begin
[code]......

View 4 Replies View Related

SQL & PL/SQL :: Database Connector Error ORA-24338

Apr 18, 2012

We have a Crystal report which calles a stored procedure using cursors. One of the tables in the stored procedure has been changed to a view which has GRANT access for the connecting user. When the report is run I get a failed to connect to database error message.

View 1 Replies View Related

Trigger Not Executed

Jun 11, 2009

I have written small procedure here...

I used two tables in my procedure..TBLEMP and TBLORG..

STEP I :->
I try to insert record in both table TBLEMP and TBLORG...I written seperate BEGIN and END block for TBLEMP and TBLORG Insert operation...When some exception is occured then I rollback all the data..if not then insert statements are commited..

STEP II :->
On table TBLORG one trigger is written..that will execute on each new insert...In this trigger I get the current record(Current employee) data from TBLEMP

Now my question is for executing trigger is it necessary to commit the TBLEMP operation, before getting data from TBLEMP.

View 1 Replies View Related

Executed Two Jobs Using Dbms_scheduler

Aug 20, 2011

I have executed two jobs using dbms_scheduler. The value of the job_queue_process is 10 in my database. It is RAC database with Oracle 10g.

My job gets executed. But the next run is not getting executed at the next run date. In the below example, the systimestamp is 12:27. The first job's next run date is 12:30 so that is fine. But the second job's run date is 12:21 which is 6 mintues less than current time. As a result, the second job does not get executed after this at all..

SQL> select job_name, next_run_date, last_start_date, last_run_duration from user_scheduler_jobs;
HISASAV_JOB
20-AUG-11 12.30.41.000000 PM ASIA/CALCUTTA
20-AUG-11 12.20.41.122959 PM ASIA/CALCUTTA
+000000000 00:00:01.307071
[code]....

View 4 Replies View Related

SQL & PL/SQL :: Record Of Last Executed Queries

Sep 15, 2010

I have some troubles when I try to retrieve last executed queries in a database.

For example;

I run the script below:

select distinct t.first_load_time, t.sql_text, t.last_load_time, s.username
from v_$sql t, v$session s
where s.username='SYS'

And as a result, I retrieve the queries executed by SYS user. But the problem is that, if SYS user executed the same query more than once,
only the very first record is shown.

It is like this,

SYS user executes "select * from table_abc" at 10:54:35, and after that SYS executes the same query at 13:45:55. and after running
the query above, I can only see the record which was executed at 10:54:35. I need to see the both results.

View 39 Replies View Related

SQL & PL/SQL :: Activities When Procedure Executed

Sep 6, 2010

when a procedure, package or function is executed which part of oracle code gets loaded, where the values of the variables are stored etc. When a package is loaded in the memory does specification and body both gets loaded?

How can I found how much memory is required to execute a procedure? consider oracle architecture (SGA, PGA, UGA etc.)

View 3 Replies View Related

Timestamp Of A Command Executed

Sep 17, 2012

OS:Linux 5

how to find out the timestamps of a OS command executed previously. "history" command doesn't show the timestamps. I checked "man history" but i couldn't able to find the proper option.

1222 db
1223 oh
1224 cd dbs
1224 cd $TNS_ADMIN

View 5 Replies View Related

PL/SQL :: Queries Executed By A Session

Jul 11, 2012

I am trying to trace the SQL statements executed against a particular database user. I don't have AUDITING enabled and I am using Oracle 11g.I have the following query :

SELECT
S.MODULE,
SQL_TEXT ,
S.EXECUTIONS
FROM
[code].....

But if multiple users running the 'APP.EXE' are connected to the same db user, I am not able to understand which OS user executed which query. So I tried to join with V$SESSION view to get the user details.

SELECT
S.MODULE,SQL_TEXT ,SN.OSUSER, SN.MACHINE, S.EXECUTIONS
FROM
SYS.V_$SQL S,
SYS.ALL_USERS U,
V$SESSION SN
WHERE
S.PARSING_USER_ID=U.USER_ID
AND UPPER(U.USERNAME) IN ('USERNAME')
AND (UPPER(S.MODULE)='APP.EXE')
AND S.SQL_ID=SN.SQL_ID
ORDER BY S.LAST_LOAD_TIME

But this doesn't seems to be working(In my case it didn't return any rows) I have also tried the following

select S.SQL_TEXT,S.MODULE,S.EXECUTIONS,SN.OSUSER,SN.MACHINE,SN.SID, U.username,U.user_id from
SYS.V_$SQL S, v$open_cursor oc,SYS.ALL_USERS U,V$SESSION SN
where S.PARSING_USER_ID=U.USER_ID
AND UPPER(U.USERNAME) IN ('USERNAME')
AND (UPPER(S.MODULE)='APP.EXE')
and oc.SQL_ID=s.SQL_ID AND SN.SID=OC.SID AND SN.SADDR=OC.SADDR;

but I am not sure whether this is giving the right results. So, I have the following questions

1) How do I get the queries executed by each session?

2) The EXECUTIONS column of V_$SQL seems to the executions from all the sessions. How do I know the number of times a particular query is executed by a session?

3) How long a record about a query will be stored in V_$SQL? When do Oracle delete it from the view?

View 5 Replies View Related

Selecting All Queries Executed Between Given Date And Now

Oct 26, 2010

Is information in v$sql enough to select all queries executed between given date and now? When the queries are removed from v$sql?

View 1 Replies View Related

SQL & PL/SQL :: Same Query Taking Different Time When Executed Through Different DBs?

Feb 19, 2013

I have a query which is executing fast in dev env,but very long time in qa env.What is the criteria when this behaviour occurs.Though qa is having more data than dev.But still it is taking long time for 1 rows also.When I am using the query rownum<=1.So What to check for this.

View 6 Replies View Related

SQL & PL/SQL :: Where Conditions That Have Dependency Don't Get Executed In Order

May 26, 2010

While there's numerous QAs about inserting an image into the DB using the Data Block, how does one remove an image? Obviously there's the "UPDATE ... SET X = EMPTY_BLOB();", however, that kills the Data Block/Form's flow, and it doesn't update.

I've tried a few things:

* :CONTROL.IMG1 := NULL; (bad bind variable)
* :CONTROL.IMG1 := EMPTY_BLOB(); (some other error, probably as above)

View 10 Replies View Related

Crash RAC Instance When Specific SQL Executed?

Oct 11, 2013

however the problem we have is when testing a third party supplied application we have evidence of data loss when we crash an instance in the RAC cluster, however this only occurs during approximately 30% of tests, our suspicion is that the application is managing the failover and reapplying only certain types of transaction, the problem is as we have to perform multiple tests to get a reproduction it is immensely time consuming and expensive to test this and the vendor has a tendency to report that the issue is fixed in a patch and then we reproduce the bug during testing. 

For a session I can cause it to crash using the oradebug event e.g. alter session set events

'sql_trace[sql: 7h35uxf5uhmm1] RDBMS.crash(0)'  

Support have advised that there is no way to cause an instance crash this way, so I am in the process of raising this as a feature request however I would be interested in how other people currently test how third party applications respond to RAC node failures, we currently use a script that executes a kill -STOP to pasue logwriter then sleeps for a short period of time before aborting the database instance, this at least allows us to force a failure during the commit so we are more likely but we can't guarantee that we have tested the exact type of transaction that is causing the problem. Oracle 11.2.0.3 on SUSE 11

View 3 Replies View Related

PL/SQL :: O/P Not Displaying While Procedure Is Successfully Executed?

Apr 4, 2013

I m executing below PLSQL procedure . Its getting executed successfully but not displaying the O/P. I have set serveroutput on also.

set serveroutput on

DECLARE
   lc_file_handle        UTL_FILE.file_type;
   lc_file_dir           VARCHAR2 (100);
   lc_file_name          VARCHAR2 (50);
  
[code]...

View 14 Replies View Related

SQL & PL/SQL :: ORA-04067 Not Executed / Procedure Does Not Exist?

Jul 25, 2011

I am facing error in application.

ORA-04067: not executed ,procedure "BALA.SEQ_RR_ID" does not exist.

This procedure exist in database.There is one synonym with the name also exist in database.

View 7 Replies View Related

Executed Procedure - Buffer Overflow Error

Nov 16, 2011

when i am executing the procedure, i am getting the below error.

I have added below lines in the procedure.

DBMS_OUTPUT.ENABLE(100000);

Even though i am getting the error.

ORA-20000: ORU-10027: buffer overflow, limit of 100000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at "Procedure name", line 199
ORA-06512: at line 1
20000. 00000 - "%s"

*Cause: The stored procedure 'raise_application_error' was called which causes this error to be generated.

*Action: Correct the problem as described in the error message or contact the application administrator or DBA for more information.

View 6 Replies View Related

Performance Tuning :: Number Of Times Sql Had Been Executed / Run?

Oct 25, 2011

how many number of times the sql's had been executed/run in an oracle session. Executions in V$sql, is it the right place to look at? Will it give the number of times the sql has been executed in a particular session?

View 3 Replies View Related

Procedure In A Job Takes Less Time To Run When Executed Manually

Apr 25, 2013

I am using oracle 9.2.0.8 on RHEL 4.8 (64-bit). I am facing a strange problem. I have this one job in database that takes almost 12-15 minutes to execute but when I execute procedure in that job manually, it executes in one minute. Even when no other job is running in database, it takes more than 10 minutes to execute.

View 1 Replies View Related

PL/SQL :: ORA-01840 - Executed Function - Throw Error?

Mar 7, 2013

I cerated a function with two date parameters. I am using these parameters in query. The function created successfully.But when i executed this function, it throw following error:

ORA-01840: input value not long enough for date format in function

I searched on internet, but not found the proper solution. n with example.Table column is varchar2 type and values store in 'RRRR/MM/DD' format.

View 3 Replies View Related

Handle When No Value In (Select Into)?

Mar 24, 2008

I have a stored procedure that does a "select name into v_name" SQL statement, which works fine. The only problem is when the query finds no data (the procedure will error because there is no value to put into the variable). Now i have a work around to this by running the query first with a count statement (which will always have results) and then if it is not equal to 0, then i will run the select into.

My question is, is there a better way to handle this kind of issue?

View 1 Replies View Related

SQL & PL/SQL :: How To Handle The Exception

May 19, 2011

The following code is working fine,But the thing is if column already exists in the table,then also the other statements should be executed instead of coming out of procedure.SO how can I handle that exception??

SQL> CREATE OR REPLACE PROCEDURE sp_execparameters(tname IN VARCHAR2,
colname IN VARCHAR2,datatype IN VARCHAR2)
2 AS
3 v_sqlstr1 VARCHAR2(1000);
4 BEGIN
5 v_sqlstr1 := 'alter table '||tname||' add '||colname ||' '|| datatype ;
[code].........

View 3 Replies View Related

How To Handle The Old Data

Oct 6, 2013

we are using oracle database. We hold the company records in the database. The records of the company should be available at anytime but over years the database keeps growing. Now how to handle the old data. All the data is important but if this goes for few more years then we need more and more disk space to handle. Is there any efficient methodologies to handle the old data? For us old mean the data that is 10 year old. 

View 9 Replies View Related

When Executed Procedure SEND_MAIL / Recipient Is Not Receiving Mail

Jul 7, 2011

I created the following procedure in my local database.When I executed the procedure SEND_MAIL, recipient is not receiving mail.Do I need any setup in my database?

here is my procedure.
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS
c utl_smtp.connection;
rc integer;
msg_from varchar2(50) := 'Oracle11.2';
mailhost VARCHAR2(30) := '10.137.133.18'; -- local database host
[code]....

View 3 Replies View Related

SQL & PL/SQL :: Record Count Mismatch In Dataset And Query Executed

Oct 14, 2011

I am using an query to fetch the data from oracle DB and fill dataset using oledb dataadapter in ASP.net.When i run the same query in PL/SQL i am getting 14952 records,but when i am filling it to dataset i am getting only 13700 records.

View 2 Replies View Related

Performance Tuning :: ASH Report - SQL Has Been Executed For 1567 Times?

Sep 12, 2012

In ASH report

there is a section that goes like this

SQL ID Planhashed Sampled # of Executions % ActivityEvent% Event Top Row Source
fdy93qpr1227 1567 7.58direct path read 3.65TABLE ACCESS - FULL

does it suggest that this SQL has been executed for 1567 times is this correct .

View 7 Replies View Related

How To Schedule Script In Crontab Executed On 2nd Sunday Of Every Month

Jan 7, 2011

I need to schedule a script in crontab which needs to be execcuted on every 2nd Sunday of every month.

I used this logic

30 9 8-14 * 0 /$PATH/$FILE_NAME.sh but this is not working only on sunday but on all days of second week.

Any logic to schedule this?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved