+ Reply to Thread
Results 1 to 7 of 7

Simple dynamic query

  1. Simple dynamic query

    I have the following simple function defined (Windows XP Pro, Oracle 9.2.0.1):

    CREATE OR REPLACE FUNCTION tab_rows (table_name IN varchar2)
    RETURN number AS tab_rows number(20);
    sqlstr VARCHAR2(50);
    tCursor PLS_INTEGER;

    BEGIN sqlstr := 'select count(*) from ' || table_name;
    tCursor := dbms_sql.open_cursor;
    dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE);
    tab_rows := dbms_sql.execute(tCursor);
    dbms_sql.close_cursor(tCursor);
    RETURN(tab_rows);
    END tab_rows;


    First, I was getting this:
    ORA-00942: table or view does not exist
    ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
    ORA-06512: at "SYS.DBMS_SQL", line 32


    Stumped, I tried SQL*Plus. Here are the results I get from SQL*Plus:
    SQL> select tab_rows('emp') from dual;
    TAB_ROWS('EMP')
    ---------------
    0

    SQL> select count(*) from emp;

    COUNT(*)
    ----------
    13

    So, it seems that the function tab_rows has some error somewhere but where?

  2. Re: Simple dynamic query

    tunity5@yahoo.com wrote in
    news:32bcd267.0411200820.1b136dda@posting.google.com:

    > I have the following simple function defined (Windows XP Pro, Oracle
    > 9.2.0.1):
    >
    > CREATE OR REPLACE FUNCTION tab_rows (table_name IN varchar2)
    > RETURN number AS tab_rows number(20);
    > sqlstr VARCHAR2(50);
    > tCursor PLS_INTEGER;
    >
    > BEGIN sqlstr := 'select count(*) from ' || table_name;
    > tCursor := dbms_sql.open_cursor;
    > dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE);
    > tab_rows := dbms_sql.execute(tCursor);
    > dbms_sql.close_cursor(tCursor);
    > RETURN(tab_rows);
    > END tab_rows;
    >
    >
    > First, I was getting this:
    > ORA-00942: table or view does not exist
    > ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
    > ORA-06512: at "SYS.DBMS_SQL", line 32
    >
    >
    > Stumped, I tried SQL*Plus. Here are the results I get from SQL*Plus:
    > SQL> select tab_rows('emp') from dual;
    > TAB_ROWS('EMP')
    > ---------------
    > 0
    >
    > SQL> select count(*) from emp;
    >
    > COUNT(*)
    > ----------
    > 13
    >
    > So, it seems that the function tab_rows has some error somewhere but
    > where?
    >


    Privs acquired via roles work fat, dumb & happy from SQL*Plus.
    Privs acquired via roles do NOT work from within PL/SQL.
    It might help if you RTFM
    http://download-west.oracle.com/docs...a96524/toc.htm
    or do some independent research at http://asktom.oracle.com

    HTH & YMMV

  3. Re: Simple dynamic query

    tunity5@yahoo.com wrote in
    news:32bcd267.0411200820.1b136dda@posting.google.com:

    > I have the following simple function defined (Windows XP Pro, Oracle
    > 9.2.0.1):
    >
    > CREATE OR REPLACE FUNCTION tab_rows (table_name IN varchar2)
    > RETURN number AS tab_rows number(20);
    > sqlstr VARCHAR2(50);
    > tCursor PLS_INTEGER;
    >
    > BEGIN sqlstr := 'select count(*) from ' || table_name;
    > tCursor := dbms_sql.open_cursor;
    > dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE);
    > tab_rows := dbms_sql.execute(tCursor);
    > dbms_sql.close_cursor(tCursor);
    > RETURN(tab_rows);
    > END tab_rows;
    >
    >
    > First, I was getting this:
    > ORA-00942: table or view does not exist
    > ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
    > ORA-06512: at "SYS.DBMS_SQL", line 32
    >
    >
    > Stumped, I tried SQL*Plus. Here are the results I get from SQL*Plus:
    > SQL> select tab_rows('emp') from dual;
    > TAB_ROWS('EMP')
    > ---------------
    > 0
    >
    > SQL> select count(*) from emp;
    >
    > COUNT(*)
    > ----------
    > 13
    >
    > So, it seems that the function tab_rows has some error somewhere but
    > where?
    >


    Privs acquired via roles work fat, dumb & happy from SQL*Plus.
    Privs acquired via roles do NOT work from within PL/SQL.
    It might help if you RTFM
    http://download-west.oracle.com/docs...a96524/toc.htm
    or do some independent research at http://asktom.oracle.com

    HTH & YMMV

  4. Re: Simple dynamic query

    tunity5@yahoo.com wrote in
    news:32bcd267.0411200820.1b136dda@posting.google.com:

    > I have the following simple function defined (Windows XP Pro, Oracle
    > 9.2.0.1):
    >
    > CREATE OR REPLACE FUNCTION tab_rows (table_name IN varchar2)
    > RETURN number AS tab_rows number(20);
    > sqlstr VARCHAR2(50);
    > tCursor PLS_INTEGER;
    >
    > BEGIN sqlstr := 'select count(*) from ' || table_name;
    > tCursor := dbms_sql.open_cursor;
    > dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE);
    > tab_rows := dbms_sql.execute(tCursor);
    > dbms_sql.close_cursor(tCursor);
    > RETURN(tab_rows);
    > END tab_rows;
    >
    >
    > First, I was getting this:
    > ORA-00942: table or view does not exist
    > ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
    > ORA-06512: at "SYS.DBMS_SQL", line 32
    >
    >
    > Stumped, I tried SQL*Plus. Here are the results I get from SQL*Plus:
    > SQL> select tab_rows('emp') from dual;
    > TAB_ROWS('EMP')
    > ---------------
    > 0
    >
    > SQL> select count(*) from emp;
    >
    > COUNT(*)
    > ----------
    > 13
    >
    > So, it seems that the function tab_rows has some error somewhere but
    > where?
    >


    Privs acquired via roles work fat, dumb & happy from SQL*Plus.
    Privs acquired via roles do NOT work from within PL/SQL.
    It might help if you RTFM
    http://download-west.oracle.com/docs...a96524/toc.htm
    or do some independent research at http://asktom.oracle.com

    HTH & YMMV

  5. Re: Simple dynamic query

    tunity5@yahoo.com wrote in
    news:32bcd267.0411200820.1b136dda@posting.google.com:

    > I have the following simple function defined (Windows XP Pro, Oracle
    > 9.2.0.1):
    >
    > CREATE OR REPLACE FUNCTION tab_rows (table_name IN varchar2)
    > RETURN number AS tab_rows number(20);
    > sqlstr VARCHAR2(50);
    > tCursor PLS_INTEGER;
    >
    > BEGIN sqlstr := 'select count(*) from ' || table_name;
    > tCursor := dbms_sql.open_cursor;
    > dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE);
    > tab_rows := dbms_sql.execute(tCursor);
    > dbms_sql.close_cursor(tCursor);
    > RETURN(tab_rows);
    > END tab_rows;
    >
    >
    > First, I was getting this:
    > ORA-00942: table or view does not exist
    > ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
    > ORA-06512: at "SYS.DBMS_SQL", line 32
    >
    >
    > Stumped, I tried SQL*Plus. Here are the results I get from SQL*Plus:
    > SQL> select tab_rows('emp') from dual;
    > TAB_ROWS('EMP')
    > ---------------
    > 0
    >
    > SQL> select count(*) from emp;
    >
    > COUNT(*)
    > ----------
    > 13
    >
    > So, it seems that the function tab_rows has some error somewhere but
    > where?
    >


    Privs acquired via roles work fat, dumb & happy from SQL*Plus.
    Privs acquired via roles do NOT work from within PL/SQL.
    It might help if you RTFM
    http://download-west.oracle.com/docs...a96524/toc.htm
    or do some independent research at http://asktom.oracle.com

    HTH & YMMV

  6. Re: Simple dynamic query

    tunity5@yahoo.com wrote in
    news:32bcd267.0411200820.1b136dda@posting.google.com:

    > I have the following simple function defined (Windows XP Pro, Oracle
    > 9.2.0.1):
    >
    > CREATE OR REPLACE FUNCTION tab_rows (table_name IN varchar2)
    > RETURN number AS tab_rows number(20);
    > sqlstr VARCHAR2(50);
    > tCursor PLS_INTEGER;
    >
    > BEGIN sqlstr := 'select count(*) from ' || table_name;
    > tCursor := dbms_sql.open_cursor;
    > dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE);
    > tab_rows := dbms_sql.execute(tCursor);
    > dbms_sql.close_cursor(tCursor);
    > RETURN(tab_rows);
    > END tab_rows;
    >
    >
    > First, I was getting this:
    > ORA-00942: table or view does not exist
    > ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
    > ORA-06512: at "SYS.DBMS_SQL", line 32
    >
    >
    > Stumped, I tried SQL*Plus. Here are the results I get from SQL*Plus:
    > SQL> select tab_rows('emp') from dual;
    > TAB_ROWS('EMP')
    > ---------------
    > 0
    >
    > SQL> select count(*) from emp;
    >
    > COUNT(*)
    > ----------
    > 13
    >
    > So, it seems that the function tab_rows has some error somewhere but
    > where?
    >


    Privs acquired via roles work fat, dumb & happy from SQL*Plus.
    Privs acquired via roles do NOT work from within PL/SQL.
    It might help if you RTFM
    http://download-west.oracle.com/docs...a96524/toc.htm
    or do some independent research at http://asktom.oracle.com

    HTH & YMMV

  7. Re: Simple dynamic query

    tunity5@yahoo.com wrote in
    news:32bcd267.0411200820.1b136dda@posting.google.com:

    > I have the following simple function defined (Windows XP Pro, Oracle
    > 9.2.0.1):
    >
    > CREATE OR REPLACE FUNCTION tab_rows (table_name IN varchar2)
    > RETURN number AS tab_rows number(20);
    > sqlstr VARCHAR2(50);
    > tCursor PLS_INTEGER;
    >
    > BEGIN sqlstr := 'select count(*) from ' || table_name;
    > tCursor := dbms_sql.open_cursor;
    > dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE);
    > tab_rows := dbms_sql.execute(tCursor);
    > dbms_sql.close_cursor(tCursor);
    > RETURN(tab_rows);
    > END tab_rows;
    >
    >
    > First, I was getting this:
    > ORA-00942: table or view does not exist
    > ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
    > ORA-06512: at "SYS.DBMS_SQL", line 32
    >
    >
    > Stumped, I tried SQL*Plus. Here are the results I get from SQL*Plus:
    > SQL> select tab_rows('emp') from dual;
    > TAB_ROWS('EMP')
    > ---------------
    > 0
    >
    > SQL> select count(*) from emp;
    >
    > COUNT(*)
    > ----------
    > 13
    >
    > So, it seems that the function tab_rows has some error somewhere but
    > where?
    >


    Privs acquired via roles work fat, dumb & happy from SQL*Plus.
    Privs acquired via roles do NOT work from within PL/SQL.
    It might help if you RTFM
    http://download-west.oracle.com/docs...a96524/toc.htm
    or do some independent research at http://asktom.oracle.com

    HTH & YMMV

+ Reply to Thread