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..
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
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.
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?
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')
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]......
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 RelatedI 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.
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]....
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.
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.)
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
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?
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 RelatedI 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 RelatedWhile 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)
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
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]...
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.
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.
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 RelatedI 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 RelatedI 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.
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?
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].........
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 RelatedI 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]....
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 RelatedIn 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 .
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?