Dynamic Table Name in a Cursor

[TimboA]

I have 12 tables (T_JAN, T_FEB, T_MAR etc..). I want to write a single procedure that can be used to extract data from any one table and output it to a csv file.

How do I get a dynamic table in a cursor?? I've tried passing a variable (of JAN or FEB or MAR) into the procedure as follows :-

create or replace procedure pr_mth_csv
(p_mth VARCHAR2)
as
v_mth VARCHAR2(5) := 'T_'||p_mth||

CURSOR TAB_DATA IS
SELECT *
FROM v_mth;

etc....


But this gives me an error of "PL/SQL: ORA-00942: table or view does not exist"

Any ideas???

Many thanks in advance.

 

[lewisp]

Use a REF CURSOR or the dynamic SQL package DBMS_SQL.

 

[sem]

I wonder why you need those 12 independent tables?

Regards, Dima

http://seminihin.narod.ru

 

[TimboA]

The db is used to store monthly transaction data (approx 40 million trans per month) from another system and we'll be keeping a rolling 12 months of data. Each month end, we'll be clearing out the appropriate month (ie at this month end we'll clear out data from March 2003 and load in data for March 2004). My DBA decided that having 12 tables was best from a performance side of things...doesn't help with the querying though!

 

[dsanchez2]

Partions could be the solution to your requirements.

Partions let you breaklarge tables into smaller and more manageable pieces. Each partion can be manipulated individually (eg. dropping or adding a partition). However, SQL queries and DML statements do not need to be modified in order to access partitioned tables.

 

[stefanhei]

Hí.
40 Million records sound like batch-operation and I'd use SQL*Plus for that. Your script could look like:

set feedback off
set flush off
set heading off
set headsep off
set timing off
set pagesize 0
set recsep off
set termout off
set trimspool off
set verify off
set wrap off
set colsep ';'
set pause off
define spoolfile=extract_&1
define table_name=t_&1
spool spoolfile..csv
select * from &table_name;
spool off

This script takes the month as first parameter.
Stefan