| Problem Using table name in PLSQL fetched from cursor (merged) [message #394456] |
Fri, 27 March 2009 04:26  |
sachingoel411
Messages: 3
Registered: March 2009
Location: Pune
|
Junior Member |
|
|
DECLARE
v_temp_table_name VARCHAR2(100);
v_real_table_name VARCHAR2(100);
v_table_name VARCHAR2(100);
v_total_source NUMBER;
v_total_target NUMBER;
v_igen_cnt NUMBER;
cursor TBNAME is
select distinct real_table_name from DVT_TABLE_TESTS where
(substr(table_name,(length(table_name)-length(real_table_name)+1),length(real_table_name))) = real_table_name
and directory = 'CSV-BIL'
and real_table_name = 'IV_OBLIGATION';
cursor RSLT is
select b.real_table_name real_table_name,a.table_name table_name ,a.total_source total_source, a.total_target total_target
from DVT_TABLE_INFO a, DVT_TABLE_TESTS b where
a.table_name = b.table_name and b.real_table_name = v_temp_table_name;
BEGIN
For C_TBNAME in TBNAME LOOP
v_temp_table_name := C_TBNAME.real_table_name;
-- dbms_output.put_line(' REAL TABLE NAME :'||v_temp_table_name);
dbms_output.put_line('TABLE_NAME SPLIT_TABLE_NAME GENESIS_CNT IGEN_CNT');
FOR C_RSLT in RSLT LOOP
v_real_table_name := C_RSLT.real_table_name;
v_table_name := C_RSLT.table_name;
v_total_source := C_RSLT.total_source;
v_total_target := C_RSLT.total_target;
dbms_output.put_line(v_real_table_name||' '|| v_table_name||' '||v_total_source||' '||v_total_target);
END LOOP;
select count(*) into v_igen_cnt from igen_user.v_temp_table_name;
dbms_output.put_line('IGEN ACUTAL COUNT' || v_igen_cnt );
END LOOP;
END;
/
In the above code i am not able to use the table name feteched from the cursor i am initializing.
While running it is giving me error on " select count(*) into v_igen_cnt from igen_user.v_temp_table_name" as
ERROR at line 34:
ORA-06550: line 34, column 49:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 34, column 2:
PL/SQL: SQL Statement ignored
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Problem Using table name in PLSQL fetched from cursor [message #394485 is a reply to message #394483] |
Fri, 27 March 2009 06:28  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I had a look in the Oracle Error messages manual, and I can't seem to find 'havn't got sucess' as an error.
Like we said before - you can't use variables, including bind variables, for table names in queries.
The part of the query execution that binds variables in is long after the part that parses the query to make sure that the table-name and columns names are all valid.
Try
sql_stmt :='select count(*) from '||v_table_name;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
