0

I have a query, this query is joining between two tables and those tables have a lot of columns and some columns have the same name, when I run the query alone in SQL windows work fine without any error, but when I put it in the cursor I get the error: PLS-00404: list of the cursor in order to avoid duplicate column requested Alias names

I know I should give alise to those columns but why it works in the SQL window and didn't work in the cursor, and is there any way to avoid this error except give alise.

My Script is to export data to excel using utl_file:

declare

file_handle UTL_FILE.file_type;
vdir varchar2(255);
vfile varchar2(255);
V_CLOB CLOB;

begin
 
vdir  := 'TEST1';
vfile := '1.xls';

file_handle := utl_file.fopen(vdir, vfile,'W');

utl_file.put_line(file_handle, 'xxxx xxxx' || chr(9) || '');
for rr in (
select t.*, t.Rowid  , VAC.* 

 FROM TH1 T, 
 (SELECT V.* FROM vac_history v WHERE V.ID IN ( SELECT MAX(VH.ID) FROM VAC_HISTORY VH GROUP BY VH.TH_ID1))  VAC 
 WHERE t.id1 = VACH.th_id1(+) 

ORDER BY t.Name 
)loop
begin 

utl_file.put_line(file_handle,  rr.xxxx|| chr(9) || '');
exception 
  when others then 
   dbms_output.put_line('Error: ' ||SQLERRM(sqlcode)||
                        'at line: ' ||  $$plsql_line);

end;

end loop;

utl_file.fclose(file_handle);

end;

The query:

select t.*, t.Rowid, VAC.* 
    
     FROM TH1 T, 
     (SELECT V.* FROM vac_history v WHERE V.ID IN ( SELECT MAX(VH.ID) FROM VAC_HISTORY VH GROUP BY VH.TH_ID1))  VAC 
     WHERE t.id1 = VACH.th_id1(+) 
    
    ORDER BY t.Name
M.Youssef
  • 146
  • 9

1 Answers1

0

This is because when you have a cursor used like this, the columns are referred to using syntax like this rr.x for column x in cursor r. So if there where no aliases, pl/sql cannot judge which column you are referring to. When you run the SQL in any tool like sqlplus or sqldev, the tool will retrieve the rows in an array-like structure and print them in the order in that structure. Some tools will add a suffix to the column name.

gsalem
  • 1,957
  • 1
  • 8
  • 7
  • Thanks gsalem, but is there any way to avoid the conflict except to write all columns and get alias to conflict column because the query is always changeable but the structure of the utl_file is constant. – M.Youssef Jun 16 '21 at 12:37
  • 1
    Maybe if you create a view with the same query, and use it instead. So if the requirements change, you just change the view. Depends on your actual needs. – gsalem Jun 17 '21 at 10:24
  • Thanks, I think this is the only way. – M.Youssef Jun 17 '21 at 12:50