Home » SQL & PL/SQL » SQL & PL/SQL » Problem Using table name in PLSQL fetched from cursor (merged)
Problem Using table name in PLSQL fetched from cursor (merged) [message #394456] Fri, 27 March 2009 04:26 Go to next message
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 PL/SQL fetched from cursor [message #394458 is a reply to message #394456] Fri, 27 March 2009 04:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quite correct - SELECT statements and Cursors need to have an explicitly declared table name.

You need to look at Dynamic SQL
Re: Problem Using table name in PLSQL fetched from cursor [message #394461 is a reply to message #394456] Fri, 27 March 2009 04:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Please do not multi-post, particularly AFTER I've replied to your question.
icon11.gif  Re: Problem Using table name in PLSQL fetched from cursor [message #394482 is a reply to message #394456] Fri, 27 March 2009 06:14 Go to previous messageGo to next message
mohan_krishnan83
Messages: 10
Registered: July 2006
Junior Member
Variable name cannot be considered as table name.You need to use dynamic query to resolve the issue.
Re: Problem Using table name in PLSQL fetched from cursor [message #394483 is a reply to message #394482] Fri, 27 March 2009 06:17 Go to previous messageGo to next message
sachingoel411
Messages: 3
Registered: March 2009
Location: Pune
Junior Member

in the above program i tried using it like this also but haven't got success

sql_stmt :='select count(*) from :1';
EXECUTE IMMEDIATE sql_stmt INTO v_igen_cnt USING v_temp_table_name;

Re: Problem Using table name in PLSQL fetched from cursor [message #394485 is a reply to message #394483] Fri, 27 March 2009 06:28 Go to previous messageGo to next message
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;
Re: Problem Using table name in PLSQL fetched from cursor [message #394486 is a reply to message #394485] Fri, 27 March 2009 06:33 Go to previous message
sachingoel411
Messages: 3
Registered: March 2009
Location: Pune
Junior Member

Thanks a lot it is working ...........yeppieeIt is working now....
Previous Topic: VARCHAR2 cannot take a data more than 32KB.
Next Topic: selecting 12 char
Goto Forum:
  


Current Time: Sat Dec 10 22:40:19 CST 2016

Total time taken to generate the page: 0.07983 seconds