Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    15

    Unanswered: Created function granted to scott but not working

    Friends,

    Server: RHEL AS 3.0
    DB:9iR2

    As a system user....

    create or replace function count_rows(table_name varchar2)
    return varchar2 is
    i number;
    begin
    execute immediate 'select count(*) c from '||table_name into i;
    return i;
    end;
    /

    grant execute on count_rows to scott;

    as a scott user....i tried to execute the system created function count_rows

    select table_name,count_rows(table_name) from user_tables;

    then i received a invalid identifier error.

    i tried with the below query also....but no use...

    select table_name, system.count_rows(table_name) from user_tables;

    but when i run the function as a system user its executing without any problem/error.

    thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Obviously, as you didn't create a synonym in Scott's schema, you'll have to identify the function by owner's name. In your case, it is a SYSTEM (not that this is a particularly good idea - creating your objects in SYS (or SYSTEM) schema is, generally speaking, a BAD habit).

    The correct syntax would be, for example
    Code:
    SELECT system.count_rows('DICTIONARY') FROM dual;
    
    SELECT system.count_rows('SCOTT.EMP') FROM dual;
    
    SELECT system.count_rows('ANY_USER_NAME.ANY_TABLE_IN_ITS_SCHEMA') FROM dual;

  3. #3
    Join Date
    Mar 2004
    Posts
    15
    Actually i have a scott in different tablespace.
    i created a function in system tablespace.
    after that i want to check the number of rows in scott user.
    thats why i created a function.
    now i solved it....

    here is the code....

    create or replace function count_rows(table_name varchar2)
    return INTEGER AUTHID CURRENT_USER is
    i number;
    begin
    execute immediate 'select count(*) c from '||table_name into i;
    return i;
    end;



    then....as scott user

    SELECT table_name, system.count_rows(table_name) from user_tables ;

    thanks

Posting Permissions

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