Help - Search - Members - Calendar
Full Version: query a non-existing table in a function
Oracle DBA Forums > Oracle > Oracle Forum
andy_9
Hi,

I'm trying the following:

function func_recordsdb_used return varchar2
is
iv_exist NUMBER;
iv_recordsdb NUMBER;
iv_value varchar2(5) ;

BEGIN

select count(*) INTO iv_exist from dba_tables where table_name ='ODM_RECORD';

IF iv_exist > 0 THEN
SELECT count(*)
INTO iv_recordsdb
FROM odm_record;
ELSE
iv_recordsdb := 0;
END IF;

IF iv_recordsdb > 0 THEN
iv_value := 'TRUE';
ELSE
iv_value := 'FALSE';
END IF;
RETURN(iv_value) ;
END func_recordsdb_used;

I get a error because odm_record isn't there but that shouldn't be a problem because it won't be reached when the iv_exist variable is 0.

How can I work around this problem?
SteveC
Should we guess which error you received?
andy_9
QUOTE (SteveC @ Jul 6 2008, 07:32 PM) *
Should we guess which error you received?


No, sorry, it says "Line (that with the first select) ignored" and "View or Table doesn't exists" (for the query with the odm_record table).
SteveC
The user running this - does he/she have select privs on dba_tables?
andy_9
Yes, he has.
HAL9000
Can you post the exact run and error message?

Try re-running it signed on as SYS.

PL/SQL may be pre-processing the tables names.

Also, what is the value of iv_exist?

What are you trying to accomplish?
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.