Home » SQL & PL/SQL » Client Tools » How to query a tablename that don't exist and not get an error (TOAD SQL)
How to query a tablename that don't exist and not get an error [message #569309] Wed, 24 October 2012 11:04 Go to next message
bws93222
Messages: 27
Registered: April 2009
Junior Member
...in other words, here is the query I want to be able to run...

select * from NonExistingTableName

I want the query above to be ignored and return no results (rather than generating an error) when the tablename does not exist.
But how?

Thx

Re: How to query a tablename that don't exist and not get an error [message #569345 is a reply to message #569309] Thu, 25 October 2012 01:52 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

it's not possible..
Re: How to query a tablename that don't exist and not get an error [message #569346 is a reply to message #569345] Thu, 25 October 2012 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you claim you know all what is possible? Twisted Evil

Regards
Michel
Re: How to query a tablename that don't exist and not get an error [message #569353 is a reply to message #569346] Thu, 25 October 2012 03:55 Go to previous messageGo to next message
Luke.M
Messages: 9
Registered: October 2012
Location: UK
Junior Member
Would I be right in thinking an IF can be used for this sort of thing?
Re: How to query a tablename that don't exist and not get an error [message #569355 is a reply to message #569353] Thu, 25 October 2012 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You'd be right to post the code you are thinking about.

Regards
Michel
Re: How to query a tablename that don't exist and not get an error [message #569356 is a reply to message #569353] Thu, 25 October 2012 04:12 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
An IF would probably be right. Of course the real question then becomes an IF in what language in what client or server process.

I would have to somewhat agree with muralikri that the answer to the question "Can I run 'select * from NonExistingTableName' in Toad and not get an error" is indeed "It's not possible".

There might of course be ways to either prevent the select on the non existent table from ever happening, of to catch the error after it happens. Which way that could happen would depend on the actual "Business" requirement which we don't know (yet).



Re: How to query a tablename that don't exist and not get an error [message #569360 is a reply to message #569355] Thu, 25 October 2012 04:35 Go to previous messageGo to next message
Luke.M
Messages: 9
Registered: October 2012
Location: UK
Junior Member
Michel Cadot wrote on Thu, 25 October 2012 04:08
You'd be right to post the code you are thinking about.


I'm thinking along the lines of:

DECLARE
   NonExistingTableName;
   element INTEGER := 1;
BEGIN
   IF NonExistingTableName.EXISTS(element)
   THEN
     SELECT * from NonExistingTableName;
   ELSE
     (Generate missing table response)
END IF;
END;


That's as far as I got... break's over, gotta get back to my own work...
Re: How to query a tablename that don't exist and not get an error [message #569362 is a reply to message #569360] Thu, 25 October 2012 04:57 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NonExistingTableName is (should be) a real table stored in the database not a PL/SQL table you can't do "SELECT * from NonExistingTableName;" on a PL/SQL table.

Regards
Michel
Re: How to query a tablename that don't exist and not get an error [message #569365 is a reply to message #569362] Thu, 25 October 2012 05:03 Go to previous messageGo to next message
Luke.M
Messages: 9
Registered: October 2012
Location: UK
Junior Member
I thought this code checks that the table exists first, then selects from it if EXISTS returns true.
If it returns FALSE then... I wasn't entirely sure what it should do. He says he wants to return no results but he needs to be more clear on that, return null fields or '0' values?

--EDIT Oh wait, silly me... I see what you mean... okay discard my idea Razz

[Updated on: Thu, 25 October 2012 05:05]

Report message to a moderator

Re: How to query a tablename that don't exist and not get an error [message #569366 is a reply to message #569362] Thu, 25 October 2012 05:04 Go to previous messageGo to next message
rita_c
Messages: 8
Registered: August 2010
Junior Member
Hi Luke,
Try this.

PROCEDURE sp_check( p_table in varchar2)
V_CNT number:=0;
BEGIN
SELECT COUNT(1)
INTO V_CNT
FROM USER_TABLES WHERE TABLE_NAME = p_table;

IF V_CNT>0
THEN
execute immeditae 'SELECT * from '||p_table;
ELSE
(Generate missing table response)
END IF;
END;

Re: How to query a tablename that don't exist and not get an error [message #569368 is a reply to message #569366] Thu, 25 October 2012 05:08 Go to previous messageGo to next message
Luke.M
Messages: 9
Registered: October 2012
Location: UK
Junior Member
rita_c wrote on Thu, 25 October 2012 05:04
Hi Luke,
Try this.


The original problem was bws93222's, not mine. I just pushed my nose in to try and solve it. I'm still a student, this is how I learn Razz
Re: How to query a tablename that don't exist and not get an error [message #569372 is a reply to message #569366] Thu, 25 October 2012 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@rita_c

Your code is bad (and badly posted as already asked to you many times), because:
1/ It does not compile (even if you put in comment what is between parentheses), you should test your code before posting
2/ Between the count and the select the table might disappear and so you didn't solve anything with it.
3/ It will anyway not work as SELECT implies something to return the values to.
...

Regards
Michel

[Updated on: Thu, 25 October 2012 06:02]

Report message to a moderator

Re: How to query a tablename that don't exist and not get an error [message #569393 is a reply to message #569372] Thu, 25 October 2012 10:03 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Here is an example that does work in SQL*Plus (and should work in all client applications that can handle ref cursors) :

var sqlplus_refcursor refcursor

declare 
  plsql_refcursor SYS_REFCURSOR;
  table_not_found EXCEPTION;
  PRAGMA EXCEPTION_INIT(table_not_found, -942);
  
begin 
	
	begin 
	  open plsql_refcursor for 
	   'select 1 from foo';
	 
    exception  
      when table_not_found then 
        open plsql_refcursor for 
	        select null from dual where 1=2;
  end;
  :sqlplus_refcursor := plsql_refcursor;	        
	 
end;
/	

print :sqlplus_refcursor


Execution with existing and non-existing tables:

SQL>
SQL> var sqlplus_refcursor refcursor
SQL>
SQL> declare
  2    plsql_refcursor SYS_REFCURSOR;
  3    table_not_found EXCEPTION;
  4    PRAGMA EXCEPTION_INIT(table_not_found, -942);
  5
  6  begin
  7
  8     begin
  9       open plsql_refcursor for
 10        'select 1 from dual';
 11
 12      exception
 13        when table_not_found then
 14          open plsql_refcursor for
 15             select null from dual where 1=2;
 16    end;
 17    :sqlplus_refcursor := plsql_refcursor;
 18
 19  end;
 20  /

PL/SQL procedure successfully completed.

SQL>
SQL> print :sqlplus_refcursor

         1
----------
         1

1 row selected.

SQL>
SQL>
SQL> var sqlplus_refcursor refcursor
SQL>
SQL> declare
  2    plsql_refcursor SYS_REFCURSOR;
  3    table_not_found EXCEPTION;
  4    PRAGMA EXCEPTION_INIT(table_not_found, -942);
  5
  6  begin
  7
  8     begin
  9       open plsql_refcursor for
 10        'select 1 from foofoo';
 11
 12      exception
 13        when table_not_found then
 14          open plsql_refcursor for
 15             select null from dual where 1=2;
 16    end;
 17    :sqlplus_refcursor := plsql_refcursor;
 18
 19  end;
 20  /

PL/SQL procedure successfully completed.

SQL>
SQL> print :sqlplus_refcursor

no rows selected

SQL>


Re: How to query a tablename that don't exist and not get an error [message #569395 is a reply to message #569393] Thu, 25 October 2012 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
 14          open plsql_refcursor for
 15             select null from dual where 1=2;


./fa/2115/0/ This is the neat trick!

Regards
Michel
Re: How to query a tablename that don't exist and not get an error [message #569396 is a reply to message #569395] Thu, 25 October 2012 10:41 Go to previous message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Michel Cadot wrote on Thu, 25 October 2012 17:29

./fa/2115/0/ This is the neat trick!

./fa/2877/0/ Thank You. ThankYouVeryMuch.
Previous Topic: Describe command
Next Topic: Toad Converts Chinese to ?
Goto Forum:
  


Current Time: Tue Sep 02 15:29:22 CDT 2014

Total time taken to generate the page: 0.88621 seconds