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

I want to retrieve data from a table which is not certain part of a database. If this table is not in the database I'll get a error "table or view does not exist". How can I work around this?

I've tried to look in the dba_tables if the table exists and if it exists I wanted to run the query on the table. But it doesn't work because it will parse every statement and when the table isn't in the database it will give me an error.

Any idea?

Cheers
burleson
>> I want to retrieve data from a table which is not certain part of a database.

Huh? If it's not there, how do you plan to retrieve it?

*********************************************************************
>> If this table is not in the database I'll get a error "table or view does not exist".

True.
*********************************************************************
>> How can I work around this?

I could be misunderstanding your question, but it sounds like you are using table names inappropriately.

For example, if I have a bad_credit table, it may have no rows, but it will always exist nonetheless.

Can you please post an example?
*********************************************************************
>> I've tried to look in the dba_tables if the table exists and if it exists I wanted to run the query on the table.

How did you do it? Can you post the code?
HAL9000
This is a BAD design, testing for table names, bad logic.

Can you explain what you are trying to do, and perhaps we can give you a better approach?

Something like this should work (just psuedocode, not correct):

CODE
declare cursor c1 as select table_name from dba_tables where table_name = 'MYTAB';

when others then exit else select stuff from mytab;


Or perhaps this: (I'm not a coder, so this syntax is likely wrong)

CODE
declare cursor c1 as select table_name from dba_tables where table_name = 'MYTAB';
open cusror c1;
for c1 in tabname
loop;
   select stuff from :tabname
end loop;
andy_9
this is what I've tried

QUOTE
SELECT version INTO vversion from v$instance;

IF vversion like '9.%' OR vversion like '10.%' THEN
SELECT xmlelement ("data_enc", NULL)
INTO vxml
FROM dual;
ELSE
SELECT xmlelement ("data_enc",
xmlagg(
(SELECT xmlelement("column_name",
xmlattributes (table_name AS "table_name", encryption_alg AS "encryption_alg", salt AS "salt"), column_name)
FROM user_encrypted_columns)))
INTO vxml
FROM dual;
END IF;
burleson
>> I want to retrieve data from a table which is not certain part of a database.

Unclear.

What, exactly, are you try to do?
SteveC
CODE
declare
  v_cnt number;
begin
  select count(*) into v_cnt
  from dba_tables (do you need to go to dba_tables, or will all or user level work?)
  where table_name = 'WHATEVER'
  and owner = 'WHOMEVER';
--this always returns a count, and you will get 0 or 1
  if v_cnt = 1 then
    do_whatever;
  end if;
end;
/
andy_9
QUOTE (SteveC @ Jul 25 2008, 03:53 PM) *
CODE
declare
  v_cnt number;
begin
  select count(*) into v_cnt
  from dba_tables (do you need to go to dba_tables, or will all or user level work?)
  where table_name = 'WHATEVER'
  and owner = 'WHOMEVER';
--this always returns a count, and you will get 0 or 1
  if v_cnt = 1 then
    do_whatever;
  end if;
end;
/


won't work because it will parse the select from whatever_table; statement in the if-clause. And if the table is not in the database it will cause the error "table or view does not exist"
SteveC
Wrong. Way wrong. Try it for yourself.
andy_9
i've tried it and it doesn't work because it will parse the select count(*) into iv_recordsdb from odm_record; and that will cause a error in a database that do not have this table.
CODE
CREATE or REPLACE FUNCTION 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';
        EXCEPTION
           WHEN NO_DATA_FOUND THEN
                        iv_exist := 0;

        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 recordsdb_used
/
SteveC
Your code doesn't make any sense, so it's hard to make sense of what you're asking. The select count(*) into construct is an existence check. If the count is one, based on the filters of table name and owner, go do whatever. If the count is zero, it is obvious the table does not exist. There is no error associated with that, unless you continue to try and do something after the count has told you zero, meaning the table does not exist, so why continue processing down that branch? Which other database are you talking about or referring to? Your code certainly doesn't reflect that, unless there is a synonym involved.

Given that the count function is always going to return a value, how do you expect to hit your when no_data_found exception? That is nonsensical.

Another thing - learn the terminology.

parse the select count(*) into iv_recordsdb from odm_record

There is nothing parsed from the select count(*). The statement is parsed, it is executed, and one or more records is fetched.
andy_9
Why does it not make any sense?

I check if the table is existing. If it's existing I'll get the 1 otherwise 0.

The if-else says: Retrieve Data from the table if it's existing and otherwise give this variable a 0.
So, I do "nothing" when the exist-check is 0.

That's the logic.

The problem is that the statement which should retrieve data from the table will be parsed. Never mind whether the exist-check give me a 1 or a 0.

Do you know what I mean?

Sorry, misunderstanding: there is no other database.
aussie_dba
"Why does it not make any sense?"

Because you are using improper terminology, and you don't appear to understand the most fundamental concepts.

"The problem is that the statement which should retrieve data from the table will be parsed. "

See, this statement makes absolutely no sense, NONE.

"I check if the table is existing."

That's HORRIBLE design, REALLY POOR design, and it violates all of the tenets of database normalization.

There are other methods that do this far more reliable and with less resources.

You seem to have no clue what you are doing, and it's probably in your own best interests to seek help elsewhere.

This is a professional DBA forum, it's not for you.
SteveC
1. Your select count(*) from dba_tables - you will get 0 or more as an answer. Not limited to 0 or 1 because you do not account for the fact that users can have similarly named objects among them. I have a table named emp, you have named emp. Which table are you really looking to see exists? I already pointed this out, you fail to read or understand this concept.

2. After the select, and applying better formatting - nothing will be done. Why? Because you immediately roll into the outer exception block, and the exception condition does not apply whatsoever. How will you ever encounter no_data_found in a count(*) scenario? This has been pointed as well, still no understanding of the logic error on your part.

3. Supposing you ever do get to having iv_exists being > 0 (which is really going to be =1), the select count(*) from odm_record is looking for 0 or greater than 0. What if odm_record has a billion rows. Do you need to count all billion rows, or is that fact that there is at least one row enough? I've mentioned this before - existence check. You don't get that either.

CODE
...
BEGIN
    SELECT count(*) INTO iv_exist FROM dba_tables WHERE table_name ='ODM_RECORD';
EXCEPTION
  WHEN NO_DATA_FOUND THEN
...
burleson
Hi Andy,

You need to come-up with a better design. While this may eventually work, once it's rolled-out into production, you may regret it.

What, exactly, are you try to do?
andy_9
QUOTE (SteveC @ Jul 26 2008, 02:11 PM) *
1. Your select count(*) from dba_tables - you will get 0 or more as an answer. Not limited to 0 or 1 because you do not account for the fact that users can have similarly named objects among them. I have a table named emp, you have named emp. Which table are you really looking to see exists? I already pointed this out, you fail to read or understand this concept.

yeah, that's right but it doesn't matter for my script.

QUOTE (SteveC @ Jul 26 2008, 02:11 PM) *
2. After the select, and applying better formatting - nothing will be done. Why? Because you immediately roll into the outer exception block, and the exception condition does not apply whatsoever. How will you ever encounter no_data_found in a count(*) scenario? This has been pointed as well, still no understanding of the logic error on your part.

right! i've deleted the exception part and it doesn't make any difference.

QUOTE (SteveC @ Jul 26 2008, 02:11 PM) *
3. Supposing you ever do get to having iv_exists being > 0 (which is really going to be =1), the select count(*) from odm_record is looking for 0 or greater than 0. What if odm_record has a billion rows. Do you need to count all billion rows, or is that fact that there is at least one row enough? I've mentioned this before - existence check. You don't get that either.

i understand what you mean but doesn't make a difference to my script

[code]


The logic is:
- Check if the table odm_record is part of the database
- if it is part of the database check if this table contains a couple of rows
- if it's not part of the database give the variable the number 0

That's all what I wanna do. It's just to check if records database is used in the database or not. Do you get me?

Thanks for your help!
SteveC
I understand exactly what you are trying to do. What you don't understand is how bad your approach is. Let's suppose false is returned. What does it really mean?

1. The table does not exist
2. It does, but has no records

What is the value of that? The only valid answer is true, at least with that you know the table exists and it has records.

CODE
create or replace function bad_code return varchar2 is
  v_cnt number;
  v_status varchar2(5) := 'FALSE';
begin
  --does the table exist?
  --this is really dumb without also qualifying by owner
  --this also assumes the user can select from dba_tables
  --which is an extremely poor assumption
  select count(*) into v_cnt
  from dba_tables
  where table_name = 'EMP';
  
  --yes?
  if v_cnt > 0 then
    --does it have any records?
    --this returns 0 or 1 with the rownum=1 stop key
    --this assumes the person running this is the owner
    --of the table, or that a synonym exists
    --very weak programming logic
    select count(*) into v_cnt
    from emp
    where rownum = 1;
    
    if v_cnt = 1 then
      v_status := 'TRUE';
    end if;
  return v_status;
end;
/
andy_9
It doesn't make any difference for me whether the table exists and has no records or the table doesn't exists. So it should return a false in both cases.

The only problem is that I don't know if the table exists or not and it should only retrieve data from the table when the table exists. But that doesn't make any difference to the parser because he parses everything.

What would be a good approach?
SteveC
I have provided a crystal clear example. What do you not understand? If your table exists and has data, you get true returned. Stop using the word parse until you know what it means in terms of parse, execute, fetch. There is no "parser." You're going to have to start paying me for anything else.
andy_9
One more question: Did you try that with a non-existing table?

I've tried your example for my case (the odm_record table) in a database which do not contain this table. It didn't work. Sure, with the emp table is no problem.
aussie_dba
"Did you try that with a non-existing table?"

Boy, do you have nerve.

You are incompetant in Oracle, a noted expert and author helps you get started, and you want him to debug it for you!!!!

Do you own job.
andy_9
I don't want him to debug it. I can't see how it should work if the table is not existing in the database. Sorry, I always get the same error "View or table does not exist".

I'm a starter in Oracle, that's right and I would like to do it by myself but I can't. Sorry!
SteveC
Maybe this helps - if the table does not exist in dba_tables, not finding the count of 1 (i.e., 0) is not going to produce an error. If you are pinging the table directly and it does not exist, you will get an ORA-00942 table or view does not exist error. Do you see the difference?
andy_9
I see the difference indeed. Sure, I could just ask if the table exists or not from the dba_tables and give the variable a "TRUE" or "FALSE". But if the table exists I have to check if there are some records in the table as well.

So, I have to include a select from the table in my script. And thats always causing an error for me if the table does not exist even if it's in the if-block which shouldn't be reached.

Sorry, I don't understand how to exclude the direct pinging from the table when I have to check if there are some records in the table.

Sorry for my questions...
SteveC
Maybe now you see the problem in selecting from dba_tables, and then turning around and selecting from the table. This only works if the user running your function can:

1. select from dba_tables
2. happens to be the owner of the table you selecting count(*) from, or it is a synonym.

If I am running your function as scott and I can select from dba_tables AND I do not own a table named whatever_it_was, I will get an error. I told you this like 84 posts ago.
andy_9
QUOTE (SteveC @ Jul 26 2008, 10:59 PM) *
Maybe now you see the problem in selecting from dba_tables, and then turning around and selecting from the table. This only works if the user running your function can:

1. select from dba_tables
2. happens to be the owner of the table you selecting count(*) from, or it is a synonym.

If I am running your function as scott and I can select from dba_tables AND I do not own a table named whatever_it_was, I will get an error. I told you this like 84 posts ago.



Ok, that's fair enough. So, what I have to do is to get the owner as well when I check if the table exists and retrieve data from owner.table_name.

But anyway I would still have the problem with the select on the table when it is not existing. Is there a logic to program it how I want or is it just possible to check if the table exists or not?

The script will run as sys.
SteveC
Okay, you are in way over your head. There is no way this can be a work requirement. Who is letting you run this as sys? Why on Earth would you run something like this as sys?

You totally do not get the concept of what selecting from the data dictionary is versus selecting from an unqualified table name. It has been pointed out to you several times what the difference or potential problem is. Stop, back away from the computer, buy some books, and start reading. If the frigging table exists in dba_tables, and you have selected that table name AND have that based on the owner name, AND get a count returned of 1, how much more clearer does it need to be explained to you that the table exists? If it doesn't exist, why do you keep talking about having a problem selecting on the table when it is not existing? That is a self-inflicted problem. Why would you select from a table that doesn't exist? I'm done with this. I'm looking for the Candid Camera crew. There is no way this is a real question.
burleson
Hi Steve,

>> If it doesn't exist, why do you keep talking about having a problem selecting on the table when it is not existing?

I asked him that days ago, but he never replied.

Is it possible that this is someone's idea of a gag?
andy_9
QUOTE (burleson @ Jul 27 2008, 09:41 PM) *
Hi Steve,

>> If it doesn't exist, why do you keep talking about having a problem selecting on the table when it is not existing?

I asked him that days ago, but he never replied.

Is it possible that this is someone's idea of a gag?


Ok, I get that done with dynamic sql. I don't know in advance if the table on a certain database will exist or not. That's why I had the if-case. If the table exists it should retrieve data from it otherwise not.
SteveC
There is exactly zero dynamic SQL involved here.
andy_9
I found a solution with dynamic sql and it works. So, it's ok for me.
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.