SQL & PL/SQL :: Active Autonomous Transaction Detected And Rolled Back
Jan 18, 2012
I have created a Package named pkg_pur_order which consists of a function and a Procedure.I have declared the procedure as Autonmous_ Transaction. But whenever I try to execute this procedure it fails and I get error msg as :
exec pkg_pur_order.prc_orders
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "DBO.PRC_WRITE_LOG", line 13
ORA-06512: at "DBO.PKG_PUR_ORDER", line 36
ORA-00001: unique constraint (DBO.SYS_C00138632) violated
ORA-06512: at line 1
[code]....
View 6 Replies
ADVERTISEMENT
Jul 28, 2010
what is autonomous transaction
View 8 Replies
View Related
Feb 9, 2011
procedure1
inser...
update....
delete...
insert...
update..
delete..
end procedure1;
i want save the transaction for specific stmt how do i do it
View 1 Replies
View Related
Jul 15, 2013
The actual flow, works on this way:
The Procedure A extracts and filter some data from the DW, this data is stored on the Global Temporary Table. Another Procedure, named B, use the data from the Global Temporary Table and store it on a normal table using another procedure Named X that Merge the data from Global Temporary against the Normal Table (inserting if not exist and updating some fields if exist).
(X isn´t important on the new flow)
Now, i need to add some steps on the normal flow:
The Procedure A extracts and filter some data from the DW, this data is stored on the Global Temporary Table. Another Procedure, named B, use the data from the Global Temporary Table and store it on a normal table. Using the Data from Global Temporary Teble i must to Store some fields on another normal table, for this i use another Procedure named C that merge the data from Global Temporary Table against the data from normal table, and i must to commit at this point. X Merge the data from Global Temporary Table and the data from the Normal table con the procedure "C" against another Normal Table (inserting if not exist and updating if exist).
The issue that i´m expecting is that i can´t use "C" for merge and commit, because this truncate the data on the global temporary table. I can´t change the on commit delete rows option, because another procedures are using this Global Temporary Table on production.
Before you ask, i try using AUTONOMOUS_TRANSACTION on "C" and didn´t works because "C" can´t found data on the Global Temporary table ( i use a select count on "C" from Global Temporary), this is because The Autonomous_Transaction (i think). So, what i can do? I tried to trace the session Number on C and A and with the AT is the same ( so isn´t session change problem).
I need:Commit on the Procedure "C" without Truncating Global Temporary Table because i need this data for X.
View 7 Replies
View Related
Aug 24, 2011
Is it possible to create autonomous transaction in anonymous block.
find the below code, when I am trying to execute I am getting error
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
[code].....
View 5 Replies
View Related
Oct 15, 2013
I am getting starting with rac environments. I need to configure a two nodes active/active with rac and I need to know if always using rac both nodes will be active or if I have to do that at some point when installing. I have reading some docs from oracle and others authors about rac and its configuration process but that is not understood for my yet.
View 5 Replies
View Related
Jul 25, 2012
We are using oracle as database in our application. For high availability we have a cluster of multiple nodes and the data is replicated using oracle streams. All the nodes in the cluster are active. We do not have any concept of stand-by.
Now we are planning to use oracle RMAN for backup and recovery. RMAN user's guide doesn't recommend any strategies for such deployments. It mainly focuses on primary/stand-by deployments.
View 1 Replies
View Related
Feb 26, 2013
It's somewhat related to my previous post DBMS_SQL Usage But here I tried to capture whole scenario.
Scenario is like below:
create user a identified by a;
grant connect,resource to a;
create user b identified by b;
grant connect,resource to b;
create user c identified by c;
grant connect,resource to c;
[code]....
Not getting why the schema is not changing... and how to resolve the error.
View 8 Replies
View Related
Jul 7, 2011
I have a trigger which is calling a stored procedure that has PRAGMA AUTONOMOUS_TRANSACTION defined. The values that are passed from the trigger have been committed already but it appears that the values are not available in the stored procedure. I'm not positive of this since the ability to log/commit is difficult and the timing of the output is confusing me a bit. I'd like to know if it's expected that any passed values are simply available in the stored procedure regardless of the AUTONOMOUS_TRANSACTION?
View 18 Replies
View Related
Jul 19, 2007
I keep getting an ora-04020: deadlock detected while trying to lock object XDB. SDNRB..The statement I'm trying to issue is:
REVOKE execute on abc."descript_T" FROM PUBLIC;
I am not able to find a solution for this besides the fact of trying the operation again at a later time.I did, but get the same error every single time.
View 6 Replies
View Related
Jun 8, 2012
I am trying to fetch data from a table but it takes time and give such an error:
SQL> Select * from adm_users;
Select * from adm_users
*
ERROR at line 1:
[Code]....
View 8 Replies
View Related
Mar 1, 2012
I am getting the below error with my bulk delete code.
ORA-14403: cursor invalidation detected after getting DML partition lock
declare
Ty_LMRowId is table of rowid;
Tbl_LMRowId Ty_LMRowId := Ty_LMRowId();
v_err_cnt number;
Ex_Dml_Errors Exception;
v_dte date := sysdate;
pragma exception_init(Ex_Dml_Errors, -24381);
[code]....
Record get deleted successfully for the first rowid in the collection, But for the remaining rowids it throws above error.
View 5 Replies
View Related
Nov 18, 2010
I am getting "ORA-00060: deadlock detected while waiting for resource" while deleting a datafile.
when i checked in dba_data_files and V$datafile the ONLINE_STATUS/STATUS is in RECOVER mode.
I do not need this datafile so i tried to drop using the following command ..
ALTER TABLESPACE DATAP_LARGE DROP DATAFILE 'D:\ORACLE\ORADATA\DATAP\DATAP_LARGE_12.DBF';
I checked the Trace file geenrated for the deadlock, the Session which is in question is the session where i ran the command (SQLPLUS).
View 8 Replies
View Related
Sep 17, 2013
solving the error below?
[oracle@localhost Desktop]$ netca
Oracle Net Services Configuration:
# An unexpected error has been detected by HotSpot Virtual Machine:
# SIGSEGV (0xb) at pc=0xa23ae762, pid=7422, tid=3075933888
# Java VM: Java HotSpot™ Server VM (1.5.0_17-b02 mixed mode)
# Problematic frame:
# C [libclntsh.so.11.1+0x429762] snlinGetAddrInfo+0x1b2
An error report file with more information is saved as hs_err_pid7422.log
#If you would like to submit a bug report, {URL}...
/u01/app/oracle/product/11.2.0/db_1/bin/netca: line 178: 7422 Aborted (core dumped) $JRE $JRE_OPTIONS -classpath $CLASSPATH oracle.net.ca.NetCA $*
View 1 Replies
View Related
Mar 16, 2013
I am configuring Grid Infrastructure 11g2 on node A, one of two cluster nodes, and getting the following message:
[INS-40916] Single-instance versions of Cluster Synchronization Services (CSS) are detected.
I executed runInstaller and installed software-only on both A and B nodes. It apparently the other node has already CSS instance running, and maybe caused by the installation.I remove the software from A first?
How do I stop CSS on the node A, and let the configuratoin to continue on this node B
View 2 Replies
View Related
Nov 4, 2013
i am getting a below error whenever executing the below select query. some times it will show dead lock detected while waiting for resource and terminated...some times it executes and gives result..but all the time it writes an alert to alert log
Env: Linux / Oracle 11.2.0.3.3..Error from alert log:Errors in file /u01/oracle/oracle/diag/rdbms/bdrdb/bdrdb/trace/bdrdb_p017_6076.trc:ORA-00060: deadlock detected while waiting for resourceORA-10387: parallel query server interrupt (normal) Trace file info... bdrdb_p017_6076.trc:Trace file /u01/oracle/oracle/diag/rdbms/bdrdb/bdrdb/trace/bdrdb_p017_6076.trcOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORACLE_HOME = /u01/oracle/oracle/product/11.2.0/dbhome_1System.
[code]....
View 11 Replies
View Related
Apr 9, 2012
I have just migrated database to 11.2 ..Migration is successfull and now database is in open mode working fine.BUT i m getting following mesage in alert log file
"Time drift detected. Please check VKTM trace file for more details."I m using windows platform.
View 4 Replies
View Related
Jun 1, 2013
How the loop back entry in /etc/hosts relates to listener?
View 1 Replies
View Related
Aug 16, 2011
The requirement is, the combination of col1,col2,col3 and col4 should always be unique, and wherever the col1, col2,col3 are same then col4 should be the sequence, starting from 1. Likewise the data should be updated back to the table.I'm able to do this using PL/SQL. Can I do the same using a single update statement?
create table tab1 (col1 number(5), col2 number(5), col3 number(5), col4 number(5));
Existing Data:
insert into tab1 values (101,521,3,1);
insert into tab1 values (101,521,3,1);
insert into tab1 values (101,522,3,2);
insert into tab1 values (101,522,3,2);
insert into tab1 values (101,523,3,1);
insert into tab1 values (101,523,3,2);
[code]....
View 3 Replies
View Related
Nov 8, 2010
I have deleted all the records from the table.And I have committed.Now I want to get all the records back.
View 16 Replies
View Related
Jul 22, 2010
I want a function that'll execute a query and return the whole table record then i need to somehow pick a column in that record and return the data in that column
So like
function something(p_param varchar2)
return table_record
something('blah).employee_number
where employee_number is a column in that table
View 3 Replies
View Related
Feb 21, 2012
I'm making a menu in my form, wherein it has FILE, TRANSACTION and REPORT. Under FILE it has BACK, and LOGOUT. In my back menu item, i want to go back to the previous block or previous module. I used previous_block but it's not working in some of my blocks.
View 8 Replies
View Related
Mar 13, 2011
cache sequence all my sequence scripts has a cache of 20...here is the script
CREATE SEQUENCE APP_TEMP_SEQ
START WITH 1000400
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;
is there any draw back caching sequence ? I noticed in my tables several sequences are skipped.
View 2 Replies
View Related
Apr 9, 2012
I come from a world of MSSQL and have been thrown into doing some Oracle work. Great! Ok, moving on.. I work in an environment where I do not have direct access to the database tables that I need data from. As a workaround, I have been asked to create a stored procedure that will be loaded into our CRM system's production db once it goes through the internal "approval" process.Basically, I need to return a result set back to the client by calling a stored procedure.
Version 1 of this that was already in place was done with the following code.
procedure events_by_day (p_start_date IN OPERATION_LOG.DT%type,
p_end_date IN OPERATION_LOG.DT%type,
p_results OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_results FOR
[code]....
Then the code is executed from the client side like so:
events_by_day(p_start_date => to_date('2012-3-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),
p_end_date => to_date('2012-3-29 23:59:59', 'YYYY-MM-DD HH24:MI:SS'),
p_results => r_cursor);
LOOP
FETCH r_cursor
INTO event_date, event_acct_no, event_type, event_count;
[code]....
As you can see, a SYS_REFCURSOR was used in this case to pass the data back.
View 3 Replies
View Related
Jul 22, 2013
When we are running a query it is giving us the result based on the conditions .But to know what exactly is happening in the database when we are running a query against it and how finally it returns the result.
View 1 Replies
View Related
Apr 10, 2012
I have a 3 node RAC server on Windows Server 2008. Last week the hard drive went out on one of the nodes and I have had to rebuild as I could not recover anything.
I went through and deleted the old node and now I have just finished adding the new node back to my cluster via documentation. Once I created the new instance on that server DBCA attempted to start and it failed gaving me a crs error. I found out later that the other 2 nodes went down and the new one that did not start correctly was the only one up!! I went and stopped the new instance and restarted the first 2. The associated services did not start with the instance so I had to start each manually. The trace files show an ORA-29702 error with cluster group service and the instance being stopped on both of the existing nodes. No other error messages stood out.
Now I cannot get any crs services to start on that 3rd node even if I attempt to start manually. I have also tried stopping all and restarting and that does not work. I found another post on this forum from you and followed it. The ASM service was fine the entire time through all the logs and I don't know how to verify LMON in Windows but I didn't see any LMON errors in the alert log. Also, the voting disks are online. Each node has their own and they are mirrored. Where else to look?
View 7 Replies
View Related
May 11, 2011
here i have an question with oracle database backup strategy.my question is
how to backup my oracle database call DB11G without archived logs while the database is open for user activity and also this should be the base for an incremental backup strategy?
View 5 Replies
View Related
Mar 31, 2007
I am trying to generate a software that used VB as the front end and Oracle 10G as the back end. I have noticed that the date format is VB is : m/dd/yyyy whereas the date format in oracle is dd-mm-yyyy. Whenever i try to insert a tuple with either of the date formats, the program halts running and after a long time , i get a message saying a deadlock was encountered. I have never had any formal introduction to either of the two tools
View 1 Replies
View Related
Mar 29, 2012
I overwritten the package and want to get the previous version.
Is there a way I can get it using FLASHBACK or any other feature?
My user_recyclebin is showing only tables.
SELECT object_name, original_name, TYPE
FROM user_recyclebin;
View 10 Replies
View Related
Aug 15, 2012
We have a forms application in Forms 10g. We scan a document and gets it to a image item in the Form. Now, we need to pass this image to a 3rd party application (document mgt. system, ). They have given us a web-service for this. We have to convert the image to a BASE64 string and then invoke the web-service method and input it.
The, 3rd party application document management system will also send images as BASE64 strings through the web-service. We need to convert them back to a blob and put to an image item in the form so that it can be viewed by the end-user.
So, how can we convert a image item in form (as BLOB) to a base64 string and also do the reverse???
View 2 Replies
View Related
