Home » SQL & PL/SQL » SQL & PL/SQL » This returns "table does not exist" but reads correctly in my DBMS_OUTPUT. HELP! thx... (Oracle 10g v10.2.0.4.0 UNIX)
This returns "table does not exist" but reads correctly in my DBMS_OUTPUT. HELP! thx... [message #425583] Fri, 09 October 2009 15:55 Go to next message
dmiller2112
Messages: 6
Registered: October 2009
Junior Member
DECLARE
CURSOR cv_table_name IS
SELECT table_name FROM dba_tables
WHERE owner='COMMON' AND (table_name = 'PRDMAS' or table_name = 'QUESMAS');

v_table_name VARCHAR2(30);
v_stage_cnt NUMBER;

BEGIN
OPEN cv_table_name;
LOOP
FETCH cv_table_name INTO v_table_name;
EXIT WHEN cv_table_name%NOTFOUND;
SELECT COUNT(*) INTO v_stage_cnt FROM v_table_name;
DBMS_OUTPUT.PUT_LINE(v_table_name||' record count is '||v_stage_cnt);
END LOOP;
CLOSE cv_table_name;
END;
Re: This returns "table does not exist" but reads correctly in my DBMS_OUTPUT. HELP! thx.. [message #425585 is a reply to message #425583] Fri, 09 October 2009 16:26 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
A table named v_table_name does not exist. You can't use variables for the table name.
Re: This returns "table does not exist" but reads correctly in my DBMS_OUTPUT. HELP! thx.. [message #425588 is a reply to message #425585] Fri, 09 October 2009 16:35 Go to previous messageGo to next message
dmiller2112
Messages: 6
Registered: October 2009
Junior Member
No wonder I can't get it to work, how can I process a list of tables Dynamically? I've also tried EXECUTE IMMEDIATE and that doesn't work in their either. thanks...
Re: This returns "table does not exist" but reads correctly in my DBMS_OUTPUT. HELP! thx.. [message #425594 is a reply to message #425583] Fri, 09 October 2009 17:23 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
EXECUTE IMMEDIATE will work for that if you use it properly. So the question is what did you do?
Re: This returns "table does not exist" but reads correctly in my DBMS_OUTPUT. HELP! thx.. [message #425599 is a reply to message #425588] Sat, 10 October 2009 00:17 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
I've also tried EXECUTE IMMEDIATE and that doesn't work in their either.


Can you show how you used it?
What is the problem you were facing?

regards,
Delna
Re: This returns "table does not exist" but reads correctly in my DBMS_OUTPUT. HELP! thx.. [message #425859 is a reply to message #425583] Mon, 12 October 2009 11:57 Go to previous messageGo to next message
dmiller2112
Messages: 6
Registered: October 2009
Junior Member
Now I can't even get the execute immediate(EI) to work. Below the first EI gets error="missing expression", the second EI gets error="missing keyword". THANKS guys!

DECLARE
CURSOR cv_table_name IS
SELECT table_name FROM dba_tables
WHERE owner='COMMON' and table_name = 'PRDMAS';

v_table_name VARCHAR2(30);
v_stage_cnt NUMBER;
exestring VARCHAR2(4000);

BEGIN
OPEN cv_table_name;

LOOP
FETCH cv_table_name INTO v_table_name;
EXIT WHEN cv_table_name%NOTFOUND;

exestring := 'SELECT COUNT(*) INTO '||v_stage_cnt||' FROM '||v_table_name;
--exestring := 'SELECT COUNT(*) INTO v_stage_cnt FROM '||v_table_name;
execute immediate exestring;

END LOOP;

CLOSE cv_table_name;

END;
Re: This returns "table does not exist" but reads correctly in my DBMS_OUTPUT. HELP! thx.. [message #425861 is a reply to message #425583] Mon, 12 October 2009 12:11 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
A quick read of the documentation on execute immediate shows that the into belongs outside the dynamic string.
So:
exestring := 'SELECT COUNT(*) FROM '||v_table_name;
execute immediate exestring INTO v_stage_cnt;


In this scenario execute immediate works like an explicit cursor. The dynamic string is the cursor declaration. The execute immediate is the open/fetch/close.

I'd also use a FOR LOOP rather than a simple LOOP in this case.

And next time you post code could you please use code tags, see the orafaq forum guide if you're not sure how.
Re: This returns "table does not exist" but reads correctly in my DBMS_OUTPUT. HELP! thx.. [message #425862 is a reply to message #425859] Mon, 12 October 2009 12:11 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
When all else fails, Read The Fine Manual

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/dynamic.htm#sthref1557
Re: This returns "table does not exist" but reads correctly in my DBMS_OUTPUT. HELP! thx.. [message #425864 is a reply to message #425859] Mon, 12 October 2009 12:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And also read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: This returns "table does not exist" but reads correctly in my DBMS_OUTPUT. HELP! thx.. [message #425871 is a reply to message #425583] Mon, 12 October 2009 13:22 Go to previous message
dmiller2112
Messages: 6
Registered: October 2009
Junior Member
Thanks guys, the corrections worked, I'll definitely use all suggestions in future posts.
Previous Topic: Improve running time
Next Topic: truncate via different schema owner
Goto Forum:
  


Current Time: Sat Dec 10 20:35:34 CST 2016

Total time taken to generate the page: 0.08650 seconds