CODEWITHSUNDEEP
sqloracle-databaseplsql
y2j
y2j

Reputation: 207

error using two cursors in plsql code

I have two tables ERROR_DESCRIPTION and ERROR_COLUMN.

ERROR_DESCRIPTION has below data :
"error processing column a_type"
"error processing column a_type"
"error processing column a_type"

ERROR_COLUMN has below data:
"abc",123334,"jdjjd"
"jdjd",2344,"djjd"
"djjd",234,"kkfkf"

at last my data should look like this :

error processing column a_type -"abc",123334,"jdjjd"
error processing column a_type - "jdjd",2344,"djjd"

so on ...

"a_type" is column name from ERROR_COLUMN table

i am trying to achieve this using cursors .

declare    
cursor c_log is  select * from ERROR_DESCRIPTION where error_data_log like'error%' ORDER BY error_data_log;   
r_log ERROR_DESCRIPTION %ROWTYPE;   
v_error varchar2(1000);    
cursor c_dsc is select * from ERROR_COLUMN;
r_dsc ERROR_COLUMN%ROWTYPE;
begin 
 open c_log;    
loop    
     fetch c_log into v_error;      
     open c_dsc ;    
          fetch c_dsc into r_dsc     
          dbms_output.put_line( 'error is'||v_error||'-'||r_dsc.xyz);

 close c_dsc ;


end loop;   
close c_log;   
end ;

i am not able to get desired result .

r_dsc.xyz is column defined for that record type

can any one tell how i can i get above result.

Upvotes: 0

Views: 62

Answers (1)

void
void

Reputation: 7890

I prefer you not use cursor when you can achieve the result with simple queries, you can get the result you mentioned with below join using substr function:

select d.val || substr(d.val,24) || c.val2 || c.val3
from ERROR_DESCRIPTION d
join ERROR_COLUMN c on substr(d.val,24)=c.val1

assuming your structure is: ERROR_DESCRIPTION(val), ERROR_COLUMN(val1,val2,val2) according to sample data you provided.

EDIT:(after comments and edit of question) if you don't have a specific formula or pattern for join and you want to join them only on base of number of recor then use rownum within subquery:

select d.val || '-' || c.val1,c.val2,c.val3
from (select rownum rn,val from ERROR_DESCRIPTION) d
join (select rownum rn,val1,val2,val3 from ERROR_COLUMN) c
on d.rn=c.rn

Upvotes: 1

Related Questions