Forum Stats

  • 3,727,123 Users
  • 2,245,325 Discussions
  • 7,852,604 Comments

Discussions

ORA-00903 invalid table name

User_PGQWT
User_PGQWT Member Posts: 6 Red Ribbon
edited July 2016 in SQL & PL/SQL

What's wrong with this function please.  I'm getting ORA-00903: invalid table name, ORA-06512: at "CHE.F_UNAPP_COUNT10", line 24



CREATE OR REPLACE function CHE.f_unapp_count( parm_file in varchar2, parm_dist in varchar2)

  return  number is Result number(6);

  the_sql  varchar2(100);

  the_sql3  varchar2(100);

  where_clause varchar2(100);

 

the_sql:= 'select count(*) from ' || parm_file || ' into Result';

dbms_output.put_line('the_sql= '||the_sql);

where_clause  := ' WHERE dist = '''||parm_dist||'''';

dbms_output.put_line('where_clause= '||where_clause);

 

the_sql3:=' and dropped_date is null and dist_approved not in (''X'',''Y'',''y'')';

dbms_output.put_line('the_sql3= '||the_sql3);

 

execute immediate the_sql || where_clause || the_sql3;

   return(Result);

John StegemanWilliam RobertsonJarkko Turpeinen

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,283 Red Diamond
    edited July 2016

    Hi,

    3070500 wrote:
    
    What's wrong with this function please.  I'm getting ORA-00903: invalid table name, ORA-06512: at "CHE.F_UNAPP_COUNT10", line 24


    CREATE OR REPLACE function CHE.f_unapp_count( parm_file in varchar2, parm_dist in varchar2)   return  number is Result number(6);   the_sql  varchar2(100);   the_sql3  varchar2(100);   where_clause varchar2(100);   the_sql:= 'select count(*) from ' || parm_file || ' into Result'; dbms_output.put_line('the_sql= '||the_sql); where_clause  := ' WHERE dist = '''||parm_dist||''''; dbms_output.put_line('where_clause= '||where_clause);   the_sql3:=' and dropped_date is null and dist_approved not in (''X'',''Y'',''y'')'; dbms_output.put_line('the_sql3= '||the_sql3);   execute immediate the_sql || where_clause || the_sql3;    return(Result);

    Are you sure that's the code you're running, and the error message it gets?

    There are lots of syntax errors, such as no BEGIN statement.

    Whenever you have a question, post enough information so the people who want to help you can re-create the problem and test their ideas.  In this case, that means a complete function,  some code that calls the function, and CREATE TABLE and INSERT statements for any tables needed.  Also post the exact results you want to get from that call.  (You probably want to call it from a loop, to test different special cases.)

    What is the big picute here?  What is the business requiremnt you're trying to meet?

    Whatever you need to do, dyamic SQL is probably the 4th or 5th best way to do it.

    John Stegeman
  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited July 2016

    There are a few things wrong with it.

    The error is likely because the owner of the function doesn't have select permission on the table.

    Another error is your use of "into Result" - that needs to be a bind variable and your execute immediate should include "using result" to bind the PL/SQL variable "result"

    One can also legitimately argue that you also need to sanitize the inputs so that someone doesn't hit you with some SQL injection, although I don't see an immediate way someone could take advantage of SQL injection.

    One can also argue that parm_dist should be a bind variable and not a pasted-in literal.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited July 2016
    3070500 wrote:
    
    What's wrong with this function please.  I'm getting ORA-00903: invalid table name, ORA-06512: at "CHE.F_UNAPP_COUNT10", line 24


    CREATE OR REPLACE function CHE.f_unapp_count( parm_file in varchar2, parm_dist in varchar2)   return  number is Result number(6);   the_sql  varchar2(100);   the_sql3  varchar2(100);   where_clause varchar2(100);   the_sql:= 'select count(*) from ' || parm_file || ' into Result'; dbms_output.put_line('the_sql= '||the_sql); where_clause  := ' WHERE dist = '''||parm_dist||''''; dbms_output.put_line('where_clause= '||where_clause);   the_sql3:=' and dropped_date is null and dist_approved not in (''X'',''Y'',''y'')'; dbms_output.put_line('the_sql3= '||the_sql3);   execute immediate the_sql || where_clause || the_sql3;    return(Result);

    problem is with "into Results" since RESULTS does not exist within scope of EXECUTE IMMEDIATE

    https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=execute+immediate+return+value

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,283 Red Diamond
    edited July 2016

    Hi

    3070500 wrote:
    ...
    the_sql:= 'select count(*) from ' || parm_file || ' into Result';
    ...
    

    The INTO clause should come before the FROM clause.

    However, that's not your real problem.

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited July 2016
    your real problem

    Correct - those are more numerous

  • Unknown
    edited July 2016
    What's wrong with this function please.  I'm getting ORA-00903: invalid table name, ORA-06512: at "CHE.F_UNAPP_COUNT10", line 24

    No - you are NOT getting that exception for the code you posted.


    There are not 24 lines in the code you posted so there can't be an exception on line 24.


    Also the exception refers to "CHE.F_UNAPP_COUNT10" and there is NO SUCH object in the code you posted.


    CREATE OR REPLACE function CHE.f_unapp_count( parm_file in varchar2, parm_dist in varchar2) 

    See the name of that function? There is no '10' on the end of it like the exception message says.

  • Nimish Garg
    Nimish Garg Member Posts: 3,185 Gold Trophy
    edited July 2016

    I would suggest following changes

    1)

    the_sql:= 'select count(*) from ' || parm_file;
    


    2)

    the_sql := the_sql || where_clause || the_sql3
    execute immediate the_sql into a;
    
  • Jarkko Turpeinen
    Jarkko Turpeinen Member Posts: 1,792 Silver Trophy
    edited July 2016

    Hi,

    What's wrong with this function please.  I'm getting ORA-00903: invalid table name, ORA-06512: at "CHE.F_UNAPP_COUNT10", line 24 
    
    
    

    that's what you have to deal with dynamic hacks. Please, do not write dynamic SQL just because you might be saving few lines of code. Only use it when it is the only way!

    Imagine you have hundred dynamic SQL like that, here and there. In production someone happen to call that particular function with table name that was deprecated and dropped month earlier. Is code safe for deployment? Who can tell you that there's no 903 errors?

    With this example you catch 903 immediately on compile and don't stop production with 903 error.

    create or replace
    function che.f_unapp_count (
        p_table varchar2,
        p_dist  varchar2
    ) return number
    is
      l_rows  number  default 0;
    begin
    
    
      case lower(p_table)
    
        when 'your_table' then
    
    
          select
            count(*)
          into  l_rows
          from  your_table
          where dist          = p_dist
            and dropped_date  is null
            and dist_approved not in (
              'X',
              'Y',
              'y'
            );
           
        else
    
    
          raise_application_error(
            -20999,
            'Invalid p_table parameter ' || p_table
          );
         
      end case;
    
      return l_rows;
    
    end;
    /
    
    

    edit: compile reveals also other errors like 942 table or view does not exist

  • 836548
    836548 Member Posts: 286
    edited July 2016

    Could see many error in your program, not sure did you provided the working and compiled source code of function. Below have done some modifications on your program:

    create or replace function f_unapp_count( parm_file in varchar2, parm_dist in varchar2)

      return  number is Result number(6);

      the_sql  varchar2(100);

      the_sql3  varchar2(100);

      where_clause varchar2(100);

    BEGIN --Begin was missing

    the_sql := 'select count(1) from '|| parm_file  ; --here into is not required have used emp table just to run and see does it works

    dbms_output.put_line('the_sql= '||the_sql);

    where_clause  := ' WHERE sal = '''||parm_dist||'''';

    dbms_output.put_line('where_clause= '||where_clause);

    --Commented below code as not required, But you can un-comment and run it

    --the_sql3:=' and dropped_date is null and dist_approved not in (''X'',''Y'',''y'')';

    --dbms_output.put_line('the_sql3= '||the_sql3);

    execute immediate the_sql || where_clause into Result ; --change add into variable here not in select

       return(Result);

      END; -- missing

    The most important thing as i could see the function name was CHE.f_unapp_count, see does CHE schema has select privileges on this table and schema which table you are accessing.

  • Jarkko Turpeinen
    Jarkko Turpeinen Member Posts: 1,792 Silver Trophy
    edited July 2016

    If you really insist and don't take advice from us, here's sample how i write dynamic SQL code.

    Use single SQL string and bind variables and just replace the dynamic part of the string with required values. In this case it is @table that is replaced with p_table value (safety checked for sql injection).

    create or replace
    function f_unapp_count (
        p_table varchar2,
        p_dist  varchar2
    ) return number
    is
      l_rows  number        default 0;
      l_table varchar2(30)  default dbms_assert.simple_sql_name( p_table );
      l_sql   clob;
    begin
    
    
      l_sql := replace(q'[
    
        select
          count(*)
        from  @table
        where dist          = :p_dist
          and dropped_date  is null
          and dist_approved not in (
            'X',
            'Y',
            'y'
          )
    
        ]',   
        '@table', l_table
      );
    
      -- debug
      dbms_output.put_line(l_sql);
    
      execute immediate l_sql into l_rows using p_dist;
    
      return l_rows;
    
    end;
    /
    
    
    
    
  • Ahmed Haroon
    Ahmed Haroon Member Posts: 1,137
    edited July 2016

    @3070500

    Invalid Table Name:

    have you checked your table name with

    Select table_name

    from user_tables

    order by 1

    or

    Select table_name

    from all_tables

    where owner = 'CHE'

    order by 1

    check output thoroughly ( do not use where table_name = yourtable )

    one more thing, in the code above posted here, there is no Line 24 or i have overlooked something ?

  • Stefan Jager
    Stefan Jager Member Posts: 1,749 Silver Trophy
    edited July 2016
    John Stegeman wrote:
    There are a few things wrong with it.
    

    Now that's the understatement of the week ...

    William RobertsonJarkko Turpeinen
This discussion has been closed.