Home » SQL & PL/SQL » SQL & PL/SQL » Cursor rows to be inserted into table columns
Cursor rows to be inserted into table columns [message #190839] Fri, 01 September 2006 09:11 Go to next message
trupti.nanajkar
Messages: 4
Registered: August 2006
Junior Member
Hi,

I have a cursor

CNT NAME PERC
---------------
3 trupti 30.23
5 Soma 42
6 Ash 55

I need to insert these rows into a table with cols

.. CNT1,NAME1,PERC1,CNT2,NAME2....

Please help!!
Thanks in advance!

Re: Cursor rows to be inserted into table columns [message #191070 is a reply to message #190839] Mon, 04 September 2006 06:12 Go to previous messageGo to next message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

Can please give the details here??

Where r u getting the data into the cursor?

better you use cursor for loop for this situation.

begin
......
......

For cursor_name IN ( select cnt, name, perc from ur_table1
Where clause (if any)
)
Loop
Insert into ur_table2(cnt, name, perc)
values ( cursor_name.cnt, cursor_name.name, cursor_name.perc);
end loop;

.....
......
end;

If this is not the solution you are looking for, then plz give me some details about your query.

Thks & Rgds
venkat

[Updated on: Mon, 04 September 2006 06:13]

Report message to a moderator

Re: Cursor rows to be inserted into table columns [message #191133 is a reply to message #191070] Tue, 05 September 2006 00:13 Go to previous messageGo to next message
trupti.nanajkar
Messages: 4
Registered: August 2006
Junior Member
Thnks Venkat.. some more description..abt my problem..

my cursor query is something like this..
OPEN o_rset FOR
'SELECT a.er_error_code,a.cnt,(a.cnt/(SELECT COUNT(*) into Count_Failed_PNR FROM error_log)*100) into percentage
FROM(select trim(er_error_code) er_error_code,count(trim(er_error_code)) cnt from error_log, audit_master
where error_log.er_audit_logid = audit_master.audit_logid
group by trim(er_error_code) order by cnt DESC) a WHERE ROWNUM <6)';

This returns a cursor..like
CNT ER_ERROR_CODE PERCENTAGE
12 ER_001 30
11 ER_002 30
10 ER_003 30
9 ER_001 5
8 ER_001 5

Now.. I have a table report1 with cols
Error1,count_error1, perc_error1, error2, count_erro2, perc_error2.........till error5

I need to insert the cursor result into this table.
Also, there might be a case when I just get 3 rows in the cursor.

Please help!!





Re: Cursor rows to be inserted into table columns [message #191307 is a reply to message #191133] Tue, 05 September 2006 11:12 Go to previous message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
do you really need the cursor here. after i took a look into your query, i believe that you no need to use the cursor to insert the records as you required
you just try this query...
PS: i do not test here. just revert me incase of any issue

insert into report1
select 
max(decode(rn,1,er_error_code,null)),max(decode(rn,1,cnt,null)),max(decode(rn,1,percentage,null)),
max(decode(rn,2,er_error_code,null)),max(decode(rn,2,cnt,null)),max(decode(rn,2,percentage,null)),
max(decode(rn,3,er_error_code,null)),max(decode(rn,3,cnt,null)),max(decode(rn,3,percentage,null)),
max(decode(rn,4,er_error_code,null)),max(decode(rn,4,cnt,null)),max(decode(rn,4,percentage,null)),
max(decode(rn,5,er_error_code,null)),max(decode(rn,5,cnt,null)),max(decode(rn,5,percentage,null)) 
from
(SELECT a.er_error_code,a.cnt,(a.cnt/(SELECT COUNT(*) into Count_Failed_PNR FROM error_log)*100) percentage, rownum rn 
FROM (
	select  trim(er_error_code) er_error_code, count(trim(er_error_code)) cnt 
	from 	error_log, audit_master
	where 
		error_log.er_audit_logid = audit_master.audit_logid
	group by trim(er_error_code) 
	order by 2 DESC
     ) a 
WHERE ROWNUM <6)

Thanks,
Thangam
Previous Topic: PL/SQL and System Tablespace
Next Topic: Reg: Table Space error...
Goto Forum:
  


Current Time: Sat Dec 10 08:52:16 CST 2016

Total time taken to generate the page: 0.07809 seconds