|
|
|
|
|
|
|
|
|
|
| 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   |
ThomasG
Messages: 3212
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 #569372 is a reply to message #569366] |
Thu, 25 October 2012 06:02 |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
ThomasG
Messages: 3212
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>
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
