Recent content by mahesh736

The problem was resolved by removing an UPDATE statement from the PRAGMA AUTONOMOUS_TRANSACTION and doing it in the main cursor loop. So the conclusion I came to was that PRAGMA AUTONOMOUS transaction acquires table-level lock. Am I correct ?? The original code with the UPDATE statement...

Hi, I am trying to update a table in an error logging routine that uses PRAGMA AUTONOMOUS_TRANSACTION. The procedure which calls this function is run in a multi-threaded mode.(2 programs run simultaneously) When I try to update this table, I get the error "ORA - 00060: deadlock detected...

What is the best option to resolve this problem? I was thinking of another option; selecting the ROWID's in the cursor itself. ie declare cursor my_cur is select t1.ROWID RowID1, t2.ROWID RowID2, t1.col_1, t1.col_2, t2.col_1, t2.col_22 from t1, t2 where t1.col_1 =...

Hi, I have defined a cursor by joining 2 tables. Can I use "CURRENT OF" to update values in BOTH the tables which are joined in the cursor definition? eg. declare cursor my_cur is select t1.col_1, t1.col_2, t2.col_1, t2.col_22 from t1, t2 where t1.col_1 =...

Do you think the foll. query will be more efficient: select a.colB, count(*) from TableA a where exists (select b.colB from TableA b where a.colB = b.ColB and a.rowid <> b.rowid ) group by a.colB; TableA is Indexed on colB.. Will the above query use this index? Thanks

Thank You. It works fine. But does the 'HAVING' clause use indexes. If not, I may run into performance issues as I might be processing about million records. Thanks..

I am trying to figure out a way of finding out if any duplicates exist in the data values of a particular column. In short, we should be able to define an Unique key constraint on that column. eg. Table A has colA and colB, then I want to find if duplicate values exist in colB. Looping thru'...

Is there any way I can programitically list the contents of all package bodies to a file? The query, &quot;select * from sys.user_objects where object_type = 'PACKAGE BODY'&quot; gives me just the names of all the package bodies. Which table stores the contents of the package bodies? Thanks

Hi How do we access Stored procedure output into a cursor variable? For eg. I want to access the FKCOLUMN_NAME output given by the sp_fkeys stored procedure. EXEC sp_fkeys @pktable_name = N'SCHOOL_DISTRICTS' will list the foreign keys for SCHOOL_DISTRICTS table. But I want to store the...