hi,
I am looking for a soln,where in I can substiute the name of the table
dynamically with a paramter passed through the procedure in the Cursor
Defn
as shown below.

PROCEDURE FLAT_TCN
(p_table_name IN varchar2(50),
p_error_msg IN OUT varchar2)

IS

error_msg VARCHAR2(300) := SQLERRM;

TCN_record VARCHAR2(1000);

CURSOR TCN_cur IS

SELECT
py.SRV_CAT,
py.NET_FT,
py.JUR_CODE,
py.LATA_CODE,
py.CALL_TYPE,
py.CUST_SEG,
py.HIST_CTR,
py.EFF_DATE,
py.EXP_DATE,
py.TRAN_IND,
FROM
p_table_name py;

here note the p_table_name used for cursor declaration(is passed as a
parameter to the proc)....
Any soln or any other way of implemention this is appreciated.

Rgds
Vivian

You need to use dynamic SQL:

PROCEDURE FLAT_TCN
(p_table_name IN varchar2(50),
p_error_msg IN OUT varchar2)

IS

error_msg VARCHAR2(300) := SQLERRM;

TCN_record VARCHAR2(1000);

TYPE refcur IS REF CURSOR;
tcn_cur refcur;

BEGIN

OPEN tcn_cur FOR
'SELECT
py.SRV_CAT,
py.NET_FT,
py.JUR_CODE,
py.LATA_CODE,
py.CALL_TYPE,
py.CUST_SEG,
py.HIST_CTR,
py.EFF_DATE,
py.EXP_DATE,
py.TRAN_IND,
FROM '||p_table_name||' py';
...

By the way, I'm not sure this does what you intend:

error_msg VARCHAR2(300) := SQLERRM;

The value of SQLERRM will be assigned at the time the variable is declared - but there hasn't been any error yet, so the value will always be "ORA-0000: normal, successful completion". Or maybe that's the default value you want if no error occurs?

Hi,
Thks for ur reply.

I want this cursor fo looping record by record.How do I do that. eg


CREATE OR REPLACE procedure test (tab_id in integer)

TYPE refcur IS REF CURSOR;
c_Data refcur;


begin

select table_name into stg_table from table_source where table_type='ST' and table_id=tab_id and src_id=2;

open c_Data for 'Select * from '||stg_table||'order by record_id;';
loop
fetch c__data into rec_id ;
EXIT WHEN c_gib_data%NOTFOUND;
-----------
----------- /* Some actions
end Loop;



When I complie,I get a error at the cursor where the sql statment is created

BEGIN test_dym(1); END;

*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "USR.TEST", line 46
ORA-06512: at line 1

1) You need a space before 'order'
2) You should not put a semi-colon in the SQL string

open c_Data for 'Select * from '||stg_table||' order by record_id';

Thnks Andrew.I t worked.

Hi,
Suppose if I have to do the follwoing,how do I implment using REF cursor.Actually I need to pass a IN paramter to the cursor. Can this be implemeted in REF cursor


CURSOR c_Data( P_Serial_Number IN VARCHAR2 ) IS
SELECT
record_id,record_history_status ,updation_ico_count
FROM
prod_data
WHERE
record_id = ( SELECT max(record_id)
FROM
prod_data
WHERE
Serial_number LIKE P_Serial_Number);

You don't pass a parameter to the ref cursor, instead you use a BIND VARIABLE and the USING clause. Here is a simple example:

DECLARE
TYPE refcur IS REF CURSOR;
c refcur;
r dept%ROWTYPE;
BEGIN
OPEN c FOR 'SELECT * FROM dept WHERE deptno > :mindeptno' USING 10;
LOOP
FETCH c INTO r;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(r.deptno);
END LOOP;
CLOSE c;
END;
/

In this example, the value 10 is used for the bind variable :mindeptno in the query. In your example this would be used like this:

OPEN c_data FOR 'SELECT ... WHERE Serial_number LIKE :x' USING P_Serial_Number;

Hi,
I have defined the cursor as ref cursor as follows

TYPE c_Data_refcur IS REF CURSOR; --Cursor with dynamic table defn
c_Data c_data_refcur;

TYPE c_View_Data_refcur IS REF CURSOR; --Cursor with Dynamic
table defn and IN Para
c_View_Data c_View_Data_refcur;


Begin

--The value for stg_table is there with me ----
open c_Data for'Select * from '||stg_table||' order by record_id';
loop
fetch c_Data into rec_id ; (Q1 : rec id should be deifned as what %rowtype as the table name is generated dynamically)

EXIT WHEN c_data%NOTFOUND;

IF v_transformation_boolean = FALSE
THEN
SELECT Transfer_ID_Seq.NEXTVAL INTO v_TransferSeq FROM dual;
v_start_record_id := stg_table.record_id; (Q2: How do I specify the fetch cursor value here?)
v_transformation_boolean := TRUE;
END IF;

v_end_record_id := stg_table.record_id; (Q3 How do I specify the fetch cursor value here?)


Q4 : Here I have to inialize the field values as null for the second cursor which has a In paramter.Here again the table name is dynamically populated for the cursor defn.)
c_GibView_Data.updation_ico_count := NULL;
c_GibView_Data.record_history_status := NULL;
c_GibView_Data.ico_status:= NULL;


--2 cursor having IN Parameter---
OPEN c_View_Data for 'SELECT record_id, record_history_status ,updation_count, status FROM'||prd_table||'WHERE record_id = ( SELECT max(record_id) FROM '||prd_table||'WHERE Serial_number = :P_Serial_Number)' using c_Data.serial_number;(This defn is not allowed)
FETCH c_View_Data INTO {c_GibView_Data_Rec};(Q5: how do i defin this as %row type as the table is dynamically populated)
CLOSE c_View_Data;

Q1) You cannot declare a variable dynamically using NDS, you would need to know the structure of the record in advance. You need to use DBMS_SQL to handle this situation.
Q2) If you had a record declared like v_rec mytable%ROWTYPE into which you had fetched a row, then you would say v_rec.record_id
Q3) Same as Q2
Q4) Don't understand the question.