If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Wrong schema name as input

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Mar 2008
Posts: 89
Question Wrong schema name as input

Hi all,

I am fairly new to this, so hopefully I'm not making a fool out of myself...
But I came across a problem when trying to write a PL/SQL block,
that is supposed to take the schema name in the FROM section from an input coming from the user. Much like this:

DECLARE
mySchema all_tables.owner%type := '&mySchema';
num number(15);
BEGIN
select count(*) into num from &mySchema.myTable;
[...]
EXCEPTION
When OTHERS Then dbms_output.put_line('Oops!');
END;

First of all... I cannot use the variable "mySchema" apparently, as it would then look for a table with the name of the variable, and not its substitution.
That's fine though, once i figured that out.

But then, if I accidently input a schema name that does not exist in the database, the program does not jump to the exception clause and does not let me pre-defined OTHERS statement handle the problem!
Instead I always get the error message 942, that the table or view does not exist.

Can anyone explain to me why that is, and how I could get the program to use the exception handling section?

Many thanks,
-free-
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jun 2003
Location: West Palm Beach, FL
Posts: 2,668
Talking

Try this:

Code:
DECLARE
mySchema all_tables.owner%type := '&mySchema';
num number(15);
BEGIN
select count(*) into num from &&mySchema..myTable;
--[...]
EXCEPTION
When OTHERS Then dbms_output.put_line('Oops!');
END;


PS: If you supply incorrect scema, it will not go to exception because it will fail at compilation level.
__________________
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Mar 2008
Posts: 89
Quote:
Originally Posted by LKBrwn_DBA
Try this:

Code:
DECLARE
mySchema all_tables.owner%type := '&mySchema';
num number(15);
BEGIN
select count(*) into num from &&mySchema..myTable;
--[...]
EXCEPTION
When OTHERS Then dbms_output.put_line('Oops!');
END;


PS: If you supply incorrect scema, it will not go to exception because it will fail at compilation level.
The double ampersand is needed if i need to make mu variable globally available, i.e. if i want to run it in sqlplus. I just need to run it from a client application like PL/SQL Developer, where I do not need the double ampers.

However, the hint with the compilation level seems to be my issue...
Does this mean there is no way I can catch the error if the schema name is wrong?

Thanks so far!
-free-
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jun 2003
Location: West Palm Beach, FL
Posts: 2,668
Talking

Double ampersand indicates to REUSE the value of the parameter.

There is no way you can catch the error inside the procedure if the schema name is wrong.

You could catch the error outside the procedure with this statement:

Code:
WHENEVER SQLERROR ...etc...
__________________
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Mar 2008
Posts: 89
Quote:
Originally Posted by LKBrwn_DBA
Double ampersand indicates to REUSE the value of the parameter.

There is no way you can catch the error inside the procedure if the schema name is wrong.

You could catch the error outside the procedure with this statement:

Code:
WHENEVER SQLERROR ...etc...
ah ok... then i'll try and wrap the queries in question in a procedure, instead of having them as the main executive block.
Alrtight, will try.

Thanks for the help.
-free-
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On