PLSQL: How to create a cursor using a variable as table_name

Question

I'm trying the following in PLSQL:

I have a table who contain a list of table names, something like: Table_1, Table_2, Table_3, etc...
I rewrite that table on a daily basis, so the list of tables is dynamic, one day you might get 3 tables, tomorrow 7 tables and so on.
Based on that list of tables, i want to export the content of those tables using the UTL_FILE.

So far, i tried to fetch the table names into a variable and then loop over the variable, but it doesn't work.

I have the following codes:

Declare
    var1 SYS_REFCURSOR;
    var2 varchar2(20);
    var3 varchar2(20);    
    ARCHIVE UTL_FILE.FILE_TYPE;
    Cursor cur2 IS SELECT TABLE_NAME FROM LIST_OF_TABLES; 

BEGIN   

ARCHIVO:=UTL_FILE.FOPEN('test_path','test.txt','W');

        for i in cur2 loop

        var2:= i.table_name;

        OPEN var1 for 'SELECT SKUID, CMRPRICE FROM '||VAR2;
        loop

       FOR C IN MICURSOR LOOP
  UTL_FILE.PUT_LINE(ARCHIVE,(''||C.SKUID||''||','||''||  C.CMRPRICE||''));
      END LOOP;
      UTL_FILE.FCLOSE(ARCHIVE);
        close var1;
        end loop;

END;

i expect to obtain the same amount of files as tables on the table list

Thanks in advance

Can you expand on what "doesn't work" means to you? Are you getting an error? Is the code not doing something you expect? At a minimum, you're only opening a single file and that's outside the loop. If you want each table to be written to a different file, you'd need to open and close a file inside the loop. And, presumably, use a file name that relates to the table name. — Justin Cave
– Justin Cave, Commented Aug 2, 2019 at 23:03
You have used two columns -- SKUID, CMRPRICE. so Is that columns exist in each table? — Popeye
– Popeye, Commented Aug 3, 2019 at 4:50
Btw, you can try to put when others then dbms_output.put_line( dbms_utility.format_error_stack ); dbms_output.put_line( dbms_utility.format_error_backtrace ); just before the END; stated at the bottm. — Barbaros Özhan
– Barbaros Özhan, Commented Aug 3, 2019 at 7:17
What is MICURSOR? You open and close dynamic cursor var1 but you don't fetch from it. — William Robertson
– William Robertson, Commented Aug 3, 2019 at 7:22

APC · Accepted Answer · 2019-08-06 09:22:49Z

1

i expect to obtain the same amount of files as tables on the table list

In which case you need to open a different file for each table.

Also, you need to be consistent with variable naming (archivo|archive, cur2|micursor). Generally it is better to give variables meaningful names which reflect their usage. It is too easy to confuse var1 and var2.

I think you want to have the files' contents wrapped in quotes. I have used double-quotes, which is the standard for CSV format.

This code defines a PL/SQL record type, tgt_rec, with a projection which matches the projection of the query string. We open a dynamic ref cursor then in a loop fetch records into that record variable until the cursor is exhausted.

declare
    file_handle utl_file.file_type;

    rc sys_refcursor;          
    cursor cur_tables is 
        select table_name from list_of_tables; 
    type tgt_rec is record ( skuid number, cmrprice number); 
    l_rec tgt_rec;

begin   

    << tables >>
    for i in cur_tables loop

        file_handle := utl_file.fopen('test_path', i.table_name||'.csv','w');

        open rc for 'select skuid, cmrprice from '||i.table_name;

        << table_rows >>
        loop

           fetch rc into l_rec;
           exit when rc%not found;

            utl_file.put_line(file_handle, '"'||l_rec.skuid||'","'||  l_rec.cmrprice||'"');

        end loop table_rows;        

        utl_file.fclose(file_handle);

        close rc;

    end loop tables;

end;
/

You may not like the cosmetic changes I have made to your program (such as .csv for file extensions) and obviously you are free to revert them. After all, you are the Josiah Spode of your executable code.

"Do you know if I can print the result of two scripts using the same code structure?"

It depends. You can convert this anonymous block into a procedure and pass the query string - 'select skuid, cmrprice from ' - as a parameter. That would allow you to vary the executed query. However, you still need to fetch the result set into something, and that something must match the structure of the projection: same number of columns and same datatype. So that limits how flexible you can be.

Fortunately PL/SQL is a proper programming language, with lots of capabilities (although not Java-style Reflection). So you have the option of coding some seriously modular suite of programs, with sub-routines for generic things such as file handling and subroutines for query-specific data wrangling.

edited Aug 6, 2019 at 9:22

answered Aug 3, 2019 at 8:12

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Justin Cave Over a year ago

Is "the Josiah Spode of x" a British expression? That one definitely hasn't made it across the pond :-)

APC Over a year ago

@JustinCave - actually the "you are the New York Times of something that rhymes" is a riff from Chef John.

Juan Baquero Over a year ago

@APC thanks for the answer, however I have a problem executing the script:ORA-06550: LINE 19, column 21: PLS-00221: 'RC'is not a procedure or is undefined. Line 19:for recs in rc loop

Juan Baquero Over a year ago

@APC Thank you so much! Regards

Juan Baquero Over a year ago

@APC Do you know if I can print the result of two scripts using the same code structure? (I don´t want to repeat it copy(paste)

Collectives™ on Stack Overflow

PLSQL: How to create a cursor using a variable as table_name

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related