Home » SQL & PL/SQL » SQL & PL/SQL » Paging Row selection retention & ORA-14450 (merged)
Paging Row selection retention & ORA-14450 (merged) [message #251035] Thu, 12 July 2007 04:22 Go to next message
gvenkatabbu
Messages: 11
Registered: July 2007
Junior Member
I have an unique problem. In my application supported with paging when a row is selected and sorted on different column/different sort order I need to fetch the page under which the selected row appears. For this I have implemented the following.

Create a temporary table and using it in procedure

CREATE GLOBAL TEMPORARY TABLE test_nodes (nodenumber INTEGER) ON COMMIT DELETE ROWS

CREATE OR REPLACE PROCEDURE testPageData(nodeNumbers IN NUM_ARRAY,p_results OUT IDLIST)
AS
BEGIN

FOR nodenum IN 1 .. nodeNumbers .count
LOOP
EXECUTE IMMEDIATE 'INSERT INTO test_nodes values (' || to_char(nodenum) || ')';
END LOOP;

EXECUTE IMMEDIATE 'WITH rententiontab AS (select rownum num ,id,entity,severity from (select id FROM EVENT,test_nodes where event.nodenumber=test_nodes.nodenumber ' ||
'AND (severity = 1 OR severity=2 OR severity=3 OR severity=4 OR severity=5) order by ttime asc)),'||
'startNum as (select num from rententiontab where id = 1248508),' ||
'endNum as (select (num+3000) num from startNum)' ||
'select * from (select * from rententiontab where num >= (select num from endNum)) where rownum <=(select num from startNum)'
IBULK COLLECT INTO p_results;
commit;
END;

but when I execute this procedure I am always getting the following error

ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use
ORA-06512: at "LARGENW.TESTPAGEDATA", line 14
ORA-06512: at line 1

I searched the web but couldn't find any solution.

Is there any way we can achieve the same using SELECT statement with using WITH clause?I mean can we have a condition in where for the inner query,something like this

select * from (select rownum RN,A.* from (select * from event where nodenumber =1 and (severity =1 or severity =2) order by ttime asc) A) B where b.RN >=( select RN from A where id=1248508)

Thanks
Venkat

[Updated on: Thu, 12 July 2007 04:28]

Report message to a moderator

Re: Paging Row selection retention [message #251041 is a reply to message #251035] Thu, 12 July 2007 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
FOR nodenum IN 1 .. nodeNumbers .count
LOOP
EXECUTE IMMEDIATE 'INSERT INTO test_nodes values (' || to_char(nodenum) || ')';
END LOOP; 

Good! you find the very very most efficient way to kill your server.

I didn't go further.

Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
ORA-14450: attempt to access a transactional temp table already in use [message #251103 is a reply to message #251035] Thu, 12 July 2007 07:08 Go to previous messageGo to next message
gvenkatabbu
Messages: 11
Registered: July 2007
Junior Member
In my application, paging is supported with row retention feature. So when a row is selected and sorted on different column/different sort order I need to fetch the page under which the selected row appears. For this I have implemented the following.

Create a temporary table and using it in procedure

CREATE GLOBAL TEMPORARY TABLE test_nodes (nodenumber INTEGER) ON COMMIT DELETE ROWS

CREATE OR REPLACE PROCEDURE testPageData
			(nodeNumbers IN NUM_ARRAY,p_results OUT IDLIST)
AS               
BEGIN

   FOR nodenum IN 1 .. nodeNumbers.count
   LOOP
      EXECUTE IMMEDIATE 'INSERT INTO test_nodes values 
      					(' || to_char(nodenum) || ')';
   END LOOP; 

   EXECUTE IMMEDIATE 'WITH rententiontab AS (select rownum num ,id,entity,' ||
   'severity from (select id FROM EVENT,test_nodes where event.nodenumber=' ||
   'test_nodes.nodenumber AND (severity = 1 OR severity=2 OR severity=3 OR' ||
   ' severity=4 OR severity=5) order by ttime asc)),startNum as (select num'||
   ' from rententiontab where id = 1248508), endNum as (select (num+3000) ' ||
   ' num' from startNum), select * from (select * from rententiontab where' ||
   ' num >= (select num from endNum)) where rownum <=(select num from ' ||
   ' startNum)' BULK COLLECT INTO p_results; 
                      
  commit;
END;


When I execute the procedure I am getting the following error.
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use
ORA-06512: at "LARGENW.TESTPAGEDATA", line 24
ORA-06512: at line 1

Could not figure what is the rootcause.

Also is there any way we can achieve the same using SELECT statement without using WITH clause?I mean can we have a condition in where cluase for the inner query,something like this

select * 
from (select rownum RN,A.* 
      from (select * 
            from event 
            where nodenumber =1 and (severity =1 or severity =2) 
            order by ttime asc) A) B 
where b.RN >=( select RN from A where id=1248508)


Also let me know if you have suggestion or correction with the procedure

[Updated on: Thu, 12 July 2007 07:13] by Moderator

Report message to a moderator

Re: ORA-14450: attempt to access a transactional temp table already in use [message #251107 is a reply to message #251103] Thu, 12 July 2007 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First don't use "execute immediate" and format your query.

Regards
Michel
Re: ORA-14450: attempt to access a transactional temp table already in use [message #251139 is a reply to message #251107] Thu, 12 July 2007 08:31 Go to previous messageGo to next message
gvenkatabbu
Messages: 11
Registered: July 2007
Junior Member
I removed "execute immediate" from the insert query but I can not remove the select query as the query is dynamic created based. The where condition is not fixed. Please let me know if there is any other way to execute the dynamic SQL in procedure.


CREATE OR REPLACE PROCEDURE testPageData
			(nodeNumbers IN NUM_ARRAY,p_results OUT IDLIST)
AS               
BEGIN

   FOR nodenum IN 1 .. nodeNumbers.count
   LOOP
      INSERT INTO test_nodes values (nodenum); 				
   END LOOP; 

   EXECUTE IMMEDIATE 'WITH rententiontab AS 
	(select rownum num,id,entity,severity from (select id,entity,severity'||
	'FROM EVENT,test_nodes where event.nodenumber=test_nodes.nodenumber ' ||
	'AND (severity = 1 OR severity=2 OR severity=3 OR severity=4 '||
	'OR severity=5) order by ttime asc)), ||
	
	'startNum as (select num from rententiontab where id = 1248508),' ||
	
	'endNum as (select (num+3000) num from startNum)
	
	'select * from (select * from rententiontab where num >= '||
	'(select num from endNum)) where rownum <= (select num from ' ||
   	' startNum)'
   	BULK COLLECT INTO p_results; 
                      
  commit;
END;


Re: ORA-14450: attempt to access a transactional temp table already in use [message #251144 is a reply to message #251139] Thu, 12 July 2007 08:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you can't remove the execute immediate but you can write the query in a way that is easy to read.
Do easily read the query in your strings?
Don't you see the way I modified your last query, isn't it easier to read than a raw line of 200 characters or lines broken in a random way?

Regards
Michel
Re: ORA-14450: attempt to access a transactional temp table already in use [message #251146 is a reply to message #251144] Thu, 12 July 2007 09:01 Go to previous messageGo to next message
gvenkatabbu
Messages: 11
Registered: July 2007
Junior Member
Earlier I assumed max 80 charters per line. I have formatted the SQL. In my application I am sending the query as parameter to the procedure.



WITH 
  rententiontab AS (
    select rownum num,id,entity,severity 
    from (select id,entity,severity 
          FROM EVENT,TEST_NODES
          where EVENT. nodenumber=TEST_NODES.nodenumber
            AND (severity = 1
                OR severity=2 
                OR severity=3 
                OR severity=4 
                OR severity=5) 
          order by ttime asc)
  ),
  startNum as (select num from rententiontab where id = 1248508)
select * 
from ( select * 
       from rententiontab 
       where num >= (select num from startNum)
     ) 
where rownum <= (select (num+3000) from startNum)

[Updated on: Thu, 12 July 2007 09:07] by Moderator

Report message to a moderator

Re: ORA-14450: attempt to access a transactional temp table already in use [message #251148 is a reply to message #251146] Thu, 12 July 2007 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Isn't it easier to read like I formatted it.
Now I spent the time I wanted to use to help formatting your query and have no more time to help you.

Regards
Michel

[Updated on: Thu, 12 July 2007 09:06]

Report message to a moderator

Re: ORA-14450: attempt to access a transactional temp table already in use [message #251150 is a reply to message #251148] Thu, 12 July 2007 09:20 Go to previous messageGo to next message
gvenkatabbu
Messages: 11
Registered: July 2007
Junior Member
Hi Michel,

Sorry I didn't quite follow about formatting. When I saw your formatting then I realized the importance and how easy to read.

Thanks
Venkat
Re: ORA-14450: attempt to access a transactional temp table already in use [message #251168 is a reply to message #251150] Thu, 12 July 2007 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, now we see where is the windowing.
I still don't see where is the variable part and why you need "execute immediate".

Add "where rownum>=1" in each subquery of with clause.

Regards
Michel

[Edit: Fix ">=" and not "="]

[Updated on: Thu, 12 July 2007 10:48]

Report message to a moderator

Re: ORA-14450: attempt to access a transactional temp table already in use [message #251183 is a reply to message #251150] Thu, 12 July 2007 10:35 Go to previous messageGo to next message
gvenkatabbu
Messages: 11
Registered: July 2007
Junior Member
Hi Michel,

Are you suggesting to append (rownum =1) in all subquery WHERE condition with logical AND condition? I am new to Oracle please bare me. Can you give an example? I have tried adding (rownum =1) condition with AND & OR in all the WHERE clause but got the same error ORA-14450.

Regarding the variable portion, sometimes severity column condition can have only 1&2 or 1,2&3 ..etc and also additional condition will be added for columns like entity in some scenarios.

Thanks
Venkat

Re: ORA-14450: attempt to access a transactional temp table already in use [message #251185 is a reply to message #251183] Thu, 12 July 2007 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry I meant "rownum >= 1". Previous post fixed.
Re: ORA-14450: attempt to access a transactional temp table already in use [message #251198 is a reply to message #251185] Thu, 12 July 2007 12:20 Go to previous messageGo to next message
gvenkatabbu
Messages: 11
Registered: July 2007
Junior Member
After adding the rownum>=1 condition I got the same error. Please see modified code below

ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use
ORA-06512: at "LARGENW.TESTPAGEDATA", line 7
ORA-06512: at "LARGENW.TESTPAGEDATA", line 37
ORA-06512: at line 1

WITH 
  rententiontab AS (
    select rownum num,id,entity,severity 
    from (select id,entity,severity 
          FROM EVENT,TEST_NODES
          where EVENT. nodenumber=TEST_NODES.nodenumber
            AND (severity = 1
                OR severity=2 
                OR severity=3 
                OR severity=4 
                OR severity=5)
            AND ( ROWNUM >=1)
          order by ttime asc)
  ),
  startNum as (select num 
        from rententiontab where id = 1248508 AND (ROWNUM >=1))
select * 
from ( select * 
       from rententiontab 
       where num >= (select num from startNum AND ( ROWNUM >=1))
     ) 
where rownum <= (select (num+3000) from startNum AND ( ROWNUM >=1))
Re: ORA-14450: attempt to access a transactional temp table already in use [message #251205 is a reply to message #251198] Thu, 12 July 2007 12:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not the correct place:
WITH 
  rententiontab AS (
    select rownum num,id,entity,severity 
    from (select id,entity,severity 
          FROM EVENT,TEST_NODES
          where EVENT. nodenumber=TEST_NODES.nodenumber
            AND (severity = 1
                OR severity=2 
                OR severity=3 
                OR severity=4 
                OR severity=5)
          order by ttime asc)
    where ROWNUM >=1
  ),
  startNum as (select num 
        from rententiontab where id = 1248508 AND ROWNUM >=1)
select * 
from ( select * 
       from rententiontab 
       where num >= (select num from startNum)
     ) 
where rownum <= (select (num+3000) from startNum)

Do you use autonomous transaction?

Regards
Michel
Re: ORA-14450: attempt to access a transactional temp table already in use [message #251333 is a reply to message #251205] Fri, 13 July 2007 00:37 Go to previous messageGo to next message
gvenkatabbu
Messages: 11
Registered: July 2007
Junior Member
Hi Michel,

Tried the query got the same error.I am not using autonomous transaction.

Another observation, if I don't use the temporary table "TEST_NODES"
in the WITH clause the statement is getting executed.
But I need to use the temporary table as the NodeNumber shall
have more than 1000 values.

Is it possible to achieve the same using SQL SELECT. Something like this

select * 
from (select * 
	from (select rownum RN,A.* 
	      from (select * 
		    from event 
		    where nodenumber =1 and (severity =1 or severity =2) 
		    order by ttime asc) A) B 
	where b.RN >=( select RN from A where id=1248508) )
where ROWNUM <= 3000
Re: ORA-14450: attempt to access a transactional temp table already in use [message #251335 is a reply to message #251333] Fri, 13 July 2007 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can write the query in any equivalent way. With clause is not mandatory.
I don't have a test case to check what's going wrong.
If you post one (attach a text file no other format), maybe I'll can find some time to investigate.

Regards
Michel
Re: ORA-14450: attempt to access a transactional temp table already in use [message #251371 is a reply to message #251335] Fri, 13 July 2007 06:33 Go to previous messageGo to next message
gvenkatabbu
Messages: 11
Registered: July 2007
Junior Member
Hi Michel,

I have attached a file containing all create and insert SQLs
necessary for a test case. In production we would have millions of
rows in the EVENT table and number of distinct values for
"NODENUMBER" column shall be ~2000. Please help in getting the
result. Let me know if you need any further information.

Thanks for helping us.

-Venkat
Re: ORA-14450: attempt to access a transactional temp table already in use [message #262127 is a reply to message #251371] Fri, 24 August 2007 11:55 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Venkat,

I'm not sure if you are still experiencing a problem, but I ran through your test script and did not experience the exception in question. I was looking for answers to the same ORA-14450 exception in my own environment.

I did notice two questionable pieces to your test. First, the values inserted in the TEST_NODE temporary table do not reflect the contents of the nested table parameter. Second, the second factored subquery (STARTNUM) references an ID value that does not exist in the EVENT table. I'm sure I have completely changed the logic so it does not reflect your original goal, but the bottom line is that I could not reproduce your error. I don't believe you mentioned what version of Oracle you are running. That may be the difference; I tested in a 10.2.0.1 environment.

Here is the code with the changes made (I also did not see the need for the previously mentioned ROWNUM >= 1 clause)

CREATE OR REPLACE PROCEDURE testPageData 
 ( nodeNumbers IN  NUM_ARRAY
 , p_results   OUT SELIDLIST)
IS           
BEGIN
   FORALL i IN 1 .. nodeNumbers.count
   INSERT INTO test_nodes values (nodeNumbers(i)); 		

   SELECT selrowobject( rownum, id, entity, severity, faultaid, motype, etype
                      , esubtype, ttime, text, inputparamlist, nodeid, circuitid
                      , username, proxyusername)
   BULK COLLECT INTO p_results
   FROM   event e, test_nodes t
   WHERE  e.nodenumber = t.nodenumber
     AND  e.severity BETWEEN 1 AND 5
   ORDER  BY ttime;

  COMMIT;
END;
/


I used the following code to test it

DECLARE
  -- I used these values because only event 103 contained applicable SEVERITY values (1-5)
  in_arr  num_array := num_array(100, 101, 102, 103, 104);
  out_arr selidlist;
BEGIN
  testPageData(in_arr, out_arr);
  FOR i IN 1..out_arr.COUNT LOOP
    dbms_output.put_line('ID: ' || out_arr(i).id);
    dbms_output.put_line('EVENT: ' || out_arr(i).entity);
    dbms_output.put_line('SEVERITY: ' || out_arr(i).severity);
  END LOOP;
END;
/


And received the following output

ID: 561
EVENT: /000641780001/ALARM=SONETCLIENTCTP%1-A-4-T2-2%AIS-CL
SEVERITY: 1
ID: 562
EVENT: /000641780001/ALARM=SONETCLIENTCTP%1-A-4-T2-3%AIS-CL
SEVERITY: 1
ID: 563
EVENT: /000641780001/ALARM=SONETCLIENTCTP%1-A-4-T2-4%AIS-CL
SEVERITY: 1
ID: 564
EVENT: /000641780001/ALARM=SONETCLIENTCTP%1-A-4-T3-1%AIS-CL
SEVERITY: 1
ID: 565
EVENT: /000641780001/ALARM=SONETCLIENTCTP%1-A-4-T3-2%AIS-CL
SEVERITY: 1


In any event, if you did identify the cause of the ORA-14450 exception, please post your findings.

Now, back to your original problem. I wanted to clarify your needs, so I'm just using a basic test table.

CREATE TABLE t1
 ( id        NUMBER(2)
 , sort_col1 NUMBER(3)
 , sort_col2 VARCHAR2(1) );
 
INSERT INTO t1 VALUES (1, 100, 'A');
INSERT INTO t1 VALUES (2, 310, 'D');
INSERT INTO t1 VALUES (3, 165, 'E');
INSERT INTO t1 VALUES (4, 467, 'H');
INSERT INTO t1 VALUES (5, 923, 'E');
INSERT INTO t1 VALUES (6, 412, 'Q');
INSERT INTO t1 VALUES (7, 912, 'B');
INSERT INTO t1 VALUES (8, 345, 'L');
INSERT INTO t1 VALUES (9, 912, 'C');
INSERT INTO t1 VALUES (10, 512, 'W');
INSERT INTO t1 VALUES (11, 217, 'K');

COMMIT;

ID    SORT_COL1  SORT_COL2
----  ---------  ---------
1     100        'A'
2     310        'D'
3     165        'E'
4     467        'H'
5     923        'E'
6     412        'Q'
7     912        'B'
8     345        'L'
9     912        'C'
10    512        'W'
11    217        'K'


The way I understand your problem, you want to identify the current row and any other rows that follow based upon dynamic sort criteria. In other words, given the data above, you are looking for the following results.

When sorted by SORT_COL1 and the current row is ID = 4, the results should be:

ID SORT_COL1 SORT_COL2
---- --------- ---------
1 100 'A'
3 165 'E'
11 217 'K'
2 310 'D'
8 345 'L'
6 412 'Q'
/***** FROM HERE ON ******/
4 467 'H'
10 512 'W'
7 912 'B'
9 912 'C'
5 923 'E'


However, when sorted by SORT_COL2 and the current row is ID = 4, the results should be:

ID SORT_COL1 SORT_COL2
---- --------- ---------
1 100 'A'
7 912 'B'
9 912 'C'
2 310 'D'
3 165 'E'
5 923 'E'
/****** FROM HERE ON *****/
4 467 'H'
11 217 'K'
8 345 'L'
6 412 'Q'
10 512 'W'

Is that correct? If so, why not use a query such as this, which could be built at runtime and executed with EXECUTE IMMEDIATE like you did previously.

SELECT * 
FROM   t1
WHERE  sort_col1 >= ( SELECT sort_col1 FROM t1 WHERE id = 4 )
ORDER  BY sort_col1;


Also, is there a reason you chose to use a collection rather than a REF CURSOR? Just curious...

Re: ORA-14450: attempt to access a transactional temp table already in use [message #262132 is a reply to message #262127] Fri, 24 August 2007 12:13 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Interesting...where have you been for the last 2 years?
Re: ORA-14450: attempt to access a transactional temp table already in use [message #262135 is a reply to message #262132] Fri, 24 August 2007 12:32 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Has it really been two years?!? Wow, time flies! It's so sweet that you noticed my return Embarassed
Re: ORA-14450: attempt to access a transactional temp table already in use [message #262168 is a reply to message #262135] Fri, 24 August 2007 14:53 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Not sweet, just very analytical. You answered a post from over 1 month ago, so I went to check to see if you are one of those people who tries to answer questions that have no responses (a noble undertaking, but not necessarily paying attention to how old the post is). I then noticed your last post from from 2005.
So, unfortunately I am not some mental person who remembers when you last posted, just someone who over analyzes.
Previous Topic: ORA-00979: not a GROUP BY expression
Next Topic: write pl/sql result in xl sheet
Goto Forum:
  


Current Time: Sat Dec 03 01:18:19 CST 2016

Total time taken to generate the page: 0.06051 seconds