Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Talking Unanswered: execute immediate

    I have a new user,
    my problem is a dynamic pl/sql:

    I have a temporary table:

    ************************
    CREATE TABLE Z_USER_TMP
    (
    NAME VARCHAR2(50),
    OFFICE VARCHAR2(30),
    LOGIN VARCHAR2(20),
    LOG_OK VARCHAR2(10)
    )
    ************************

    and a procedure:

    ************************
    procedure USR_LIST is
    table_name varchar2(20);
    user_name varchar2(20);
    rows_processed INTEGER;
    ssql varchar2(2000);

    begin
    table_name := 'U10_USER';
    delete from Z_USER_TMP;

    for ISTA in (select * from ISTANCE where AGITI = 1)
    loop
    user_name := ISTA.owner;
    DBMS_OUTPUT.PUT_LINE(user_name);
    ssql := 'insert into Z_USER_TMP (NAME, OFFICE, LOGIN, LOG_OK) select U10_NAME, U10_BUERO, U10_DB_USER, SUBSTR(U10_DB_USER, 5, 3) FROM ' || user_name || '.' || table_name;
    execute immediate ssql;
    end loop;
    commit;

    end USR_LIST;

    ************************

    Oracle error: table or view not exist

    ERRORE alla riga 1:
    ORA-00942: tabella o vista inesistente
    ORA-06512: a "INFOSYS.STAT", line 136
    ORA-06512: a line 1


    I don't understand this problem...

    ciao

  2. #2
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: execute immediate

    If you are running that procedure with another user, the another user needs privileges on that table.

    Did you do it ?
    Joel Pérez

  3. #3
    Join Date
    Jan 2004
    Posts
    2

    Re: execute immediate

    Originally posted by joelperez
    If you are running that procedure with another user, the another user needs privileges on that table.

    Did you do it ?

    ------------------

    I have running this whit the user system, but the problem is forever...

  4. #4
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    (I believe) that the systemn user is not what we think of as a superuser. It still needs permission to look at tables in another schema. creating a public synonym on the table might help.

  5. #5
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: execute immediate

    Originally posted by mutti
    Originally posted by joelperez
    If you are running that procedure with another user, the another user needs privileges on that table.

    Did you do it ?

    ------------------

    I have running this whit the user system, but the problem is forever...
    In spite of that user system can see that tables if you want to apply that arquitecture that you want , you will have to grant those priveleges inclusive to system. I have had that case a lot of times...
    Joel Pérez

Posting Permissions

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