Results 1 to 5 of 5
  1. #1
    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-

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,672

    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

  3. #3
    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-

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,672

    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

  5. #5
    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-

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •