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:
mySchema all_tables.owner%type := '&mySchema';
select count(*) into num from &mySchema.myTable
When OTHERS Then dbms_output.put_line('Oops!');
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?