SQL & PL/SQL :: Can Use Autonomous Transaction In Procedure

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


ADVERTISEMENT

SQL & PL/SQL :: What Is Autonomous Transaction

Jul 28, 2010

what is autonomous transaction

View 8 Replies View Related

SQL & PL/SQL :: Global Temporary Table And Autonomous Transaction?

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

SQL & PL/SQL :: Create Autonomous Transaction In Anonymous Block?

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

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 View Related

SQL & PL/SQL :: Calling Other Procedures From Autonomous Procedure?

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

SQL & PL/SQL :: Trigger Calling An Autonomous Stored Procedure

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

SQL & PL/SQL :: Set Transaction Use Rollback?

Aug 31, 2013

Having PL/SQL procedure and calling sys dba procedure to alter rollback segment to online and after that to set the transaction to this rollback. The rollback altered to online but transaction still running on available rollback and not the one I was meant to. The user have executing grant on the sys dba procedure.

View 21 Replies View Related

Client IP Of Previous Transaction

Mar 11, 2013

Is it possible for me as a DBA to find IP address of the client who ran a specific transaction or query in past?

Oracle server version I'm using is 11g.

View 4 Replies View Related

SQL & PL/SQL :: DBA Pending Transaction In Database

Jun 16, 2011

application was not starting due to some pending transaction in database.Pending transactions were rolled back by DBA team.To avoid such situations what I thought was, having a job that will call a procedure that will monitor table status everyday and will send the mail. Now that job is working fine for no pending transactions in DBA_PENDING_TRANSACTIONS.

But now I am in doubt if someday there are PENDING TRANSACTIONs in the table DBA_PENDING_TRANSACTIONS, will SELECT * FROM DBA_ PENDING_ TRANSACTIONs query will work as normal or this whole process of monitoring table and sending mail will work fine?

View 3 Replies View Related

SQL & PL/SQL :: Further Changes To Block By Transaction Not Allowed

Jun 6, 2011

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE10.2.0.4.0Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

We are running a code through informatica and are frequently getting the ORA-08007. The error and the code causing it are mentioned below. (The '?' implies a variable which is supplied by informatica). The source is a flat file.

ORA-08007: Further changes to this block by this transaction not allowed

UPDATE table_20
SET capxuser = ?,
capxtimestamp = ?,
capxaction = ?,

[code]...

1. Tried initially by reducing the commit interval to commit at 100 rows. But still getting the same error.
2. DBA has installed the patch # 6790768. But it still fails.

View 10 Replies View Related

SQL & PL/SQL :: How To Get First Transaction For Every Unique Client

Nov 2, 2010

I have a table called pf_stock_txns which just stores all of a clients transactions

If I do select distinct(client_id) from pf_stock_txns then I get back a unqiue list of all clients.

However, I need a query that will give me the first transaction for every unqiue client.

View 17 Replies View Related

SQL & PL/SQL :: Commit By Changes In Database After Second Transaction?

Aug 26, 2012

I hv a situation where a webservice interacts with the database.

Here the webservice will first make a request to database for some operation but i dont want the database to commit changes in first request itself. A response will be sent to webservice further a second request will be sent to database for committing the changes. So can that be done?

View 11 Replies View Related

SQL & PL/SQL :: Retrieve Records Of 2 Way Transaction

Apr 13, 2011

I want Query on this table. EXAMPLE TABLE:

SENDER RECEIVER AMOUNT

A B 1000
B C 2000
A D 3000
C A 3000
B A 4000
A B 5000
A B 1000
B C 2000
C A 3000
B A 4000
A B 5000
A C 10000

Dynamically I am giving user name A. I want to retrieve the data how much money user B taken from user A and how much money user B given to A lly for C and D.

CONDITION: I want to retrieve the records of 2 way transaction that means user A sent money to user B and user B must sent money to user A. I don't want one way transaction records i.e user A sent money to D but user A didn't get money from user D like this records.

OUTPUT LIKE:

USER MONEY_TAKEN_FROM_A MONEY_GIVEN_TO_A
B 12000 8000
C 10000 6000

View 14 Replies View Related

Distributed Transaction - Ensure Commit

Jun 26, 2013

Query in format Query@DB_Link is sometimes dropping transactions. Move it to a package on target dababase "B" called from DATABASE "A" via package.procedure(varaibles);

Is that all I need to do to get the transaction to always go? I tried a commit in the package body, with repeated attempts if it fails. Btu when I use commit, the package doesn't work. (Compiles fine) Why would the commit fail?

My goal is to make that the transaction succeeds.Code for what I tried below. the COMMIT is commented out - if I uncomment it, the transaction fails. I have done this before calling an oracle SP from SQLServer and it worked

Comments:

The loop is to avoid an endless cycle..The concept is to perform the update, commit it, if the commit fails, rollback and try again.I'm not sure if I even need a loop, but I don't know if the unresolved transaction would get fixed by the remote DB, or dropped.

PROCEDURE BTC_UpdatePart
(
vPart_ID varchar2,
vEngineering_Mstr varchar2,
vCommodity_code varchar2,
vDef_orig_country varchar2,
vDescription varchar2,
[code]...

View 1 Replies View Related

Unexplainable Exclusive Transaction Locks?

Aug 23, 2010

I have a job which runs the procedure below concurrently in 4 different sessions; It updates two tables.

PROCEDURE UPDATE_TAB_1_AND_TAB_2( traceIdTab IN pl_sql table,
individualIdTab IN pl_sql table,
fileSeqIdTab IN pl_sql table,

[Code].....

Each session is passed a sequence generated file ID; so the records processed in each session are completely different.

However, everytime this job runs, without failure, ONLY TWO sessions process concurrently, while the other two sessions are blocked. Once the first two have finished, then the remaining two sessions start work.

I have being monitoring the sessions on each run daily, and realized that the first two sessions processing are each holding a transaction exclusive lock on objects; hence blocking the other two sessions.

When I tried to find the objects that the two running sessions have a lock on, I cant seem to find anything by searching DBA_OBJECTS.object_id. However, when I look for the objects that are being waited on, its either a partitioned table or a partitioned index on either table_1 or table_2.

At first, I thought it was an ITL lock and after much analysis, I did not find anything which definitely pointed to an ITL lock. However, just to make sure, I increased the INITTRANS value from 2 to 5, but the problem did not go away.

At this point, I have applied all I know to understand the origin of the TX lock and how I can work around this situation, and decided to ask higher powers in Oracle for advise.

View 4 Replies View Related

Oracle Trigger On Transaction Commit

Nov 29, 2007

I am working with an oracle table that is populated by a trigger on another table.. So Table A is an audit of table B. The trigger also uses sysdate to populate a modification Date column on the Audit table.

I was using this modification Date column in a query interface to get changes that happened on the main table after a certain date/time.

The problem is that there is an application that uses transactions to write to table B and sometime this transaction may not be committed for over a minute so the modification Date is not a reliable way to query the table for changes after a certain time.

Is there a way to update the trigger/create a new one where the sysdate that gets written to the audit table is from when the transaction is committed, not when the transaction starts?

View 2 Replies View Related

SQL & PL/SQL :: Retrieve IP Address For Client Who Do The Transaction

Apr 19, 2011

am using oracle 10g

and i want to retrieve IP address for client who do the transaction

and i try to using some selects like select terminal from v4session but it return the IP for application server

so i think to use commands ipconfig

how can i call windows commands by oracle

View 8 Replies View Related

SQL & PL/SQL :: Manage Transaction When Called From Java?

Jun 21, 2010

A Java program would call a sequence of 5 PLSQL procedures, and they all need to participates the Tranasction defined in Java.

What do I do for the transactions inside PLSQL to ensure all 5 pariticpate in the Java transaction? I suppose I just do not specify any COMMIT in the PLSQL, but that probably would not work as PLSQL would COMMIT by default after exiting the proc?

View 2 Replies View Related

Transaction To Be Updated To Master Table

Feb 16, 2012

I have approximately 1200 transaction to be updated to a master table. There are other columns in the master table but only one column is being updated. I would like to use sqlloader if possible or any other efficient means. Those 1200 record is stored in an excel spreadsheet. The col1 of the excel spreadsheet have to match col1 of the master table inorder for update col2 from the excel spreadsheet. Here is an example of the data. My operation system is HPUX and database is Oracle 10g.

Master table
col1 col2 col3 col 4
4238 susan 56e
5879 h698c rich 12g
7091 joyce 34b
0876 mike 25n
7501 k956b robert 87c
9498 angela 67r
3645 doris 92y

excel spreadsheet

col1 col2
9498 a784r
3645 a784r
4238 a784r
7091 a784r
0876 a784r

View 2 Replies View Related

SQL & PL/SQL :: Transaction Table - Index Recreation?

Aug 3, 2010

There is a transaction table which becomes very slow after 3-4 months in spite on regular weekly analyze and index recreation.

But once we drop the constraint (PK) and recreate it again it becomes ok. The same doesn't happen even when we rebuild the index.

View 2 Replies View Related

PL/SQL :: How To Create Transaction Table In Oracle

Mar 15, 2013

in oracle transaction table /temporary table /at the time of movement data insert

how to handle these table in merge no primary key

View 3 Replies View Related

SQL & PL/SQL :: Insert Statement Which Will Commit Transaction By Itself

May 22, 2010

What is the syntax to write an insert statement which will commit the transaction by it self,not using an exclusive commit statement after wards.

View 2 Replies View Related

Oracle Crash Before Uncommitted Transaction

Jul 10, 2012

Consider a situation:
There are two users 'A' and 'B'
account number of A: 001234
account number of B: 002678

Suppose, if A wants to transfer $1500 to B's account

update cust_acc
  set balance = balance - 1500
where
  account_no = '001234';
[code]....

Assume that, oracle crashes somewhere between first commit and second commit.In this case, A's total money is deducted by $1500..But $1500 is not yet transferred to B's account.How will oracle keep track of these information?

View 19 Replies View Related

Locked Resources By Distributed Transaction?

Jun 15, 2012

db 10.2.0.4

select * from DBA_2PC_PENDING shows pending transactions in prepared state.

How can I know the resources (e.g. tables) locked by these transactions?

View 2 Replies View Related

SQL & PL/SQL :: Executable Blocks Using DML And Transaction Control

Jul 6, 2011

what executable statements in PL/SQL mean? My objectives are:

1)Writing Executable Statements
2)Create PL/SQL executable blocks using DML and transaction control

I have to explain what exactly each is and what should come under it. what kind of statements and what should be covered under executable statements?

View 3 Replies View Related

Group By Month - Query A Transaction Table?

Oct 19, 2010

sql statement to query a transaction table that stores transactions of items bought from my organisation.The report i would like to generate is one that lists the items bought and this should be grouped month by month.

View 2 Replies View Related

SQL & PL/SQL :: Oracle Table - Finding Transaction Logs?

Dec 9, 2010

How can we see the history of transactions on a table.

For eg, If I have a table called TEST123, I would like to know whatever SQLs fired on that table on today.

View 4 Replies View Related

SQL & PL/SQL :: Identify Due Date For A Transaction - Working Days

Jul 5, 2011

I need to identify a due date for a transaction (date) that should be completed within 15 working days from the date of the transaction date. For example, a request is submitted on Wednesday, December 29th 2010. This request should be then 'answered' on Thursday, January 20th 2011 at the latest.

I have a table that identify the status of every calendar day between 1960 to 2060; fields are: calendar day, calendar year, ..., day of week, week day flag, holiday flag.

In my example above, January 1st and 2nd are weekend days and Monday, January 3rd is an holiday.

How do I go getting all (calendar day + 15 working days) for all days of the year?

View 2 Replies View Related

Submitting Transaction / Master Data Through API Without Database Credentials

Mar 15, 2011

Submitting transaction / master data through API without exposing DB credentials...I am developing a scenario where I will submit transaction / master data like sales order / master item to Oracle apps (EBS) through MS Excel via API / interface table but like to provide only apps (EBS) user credentials rather then DB credentials so DB password could not expose to normal end user.

View 1 Replies View Related







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