This is a copy of http://asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html

Why do I get a "ORA-01031: insufficient privileges" or "PLS-00201: identifier 'x' must be declared" in my stored procedures?

Roles are never enabled during the execution of a procedure except in the special case of Invokers Rights which is a new feature in Oracle8i, release 8.1.This fact is documented application developers guide:

Privileges Required to Create Procedures and FunctionsTo create a stand-alone procedure or function, or package specification or
body, you must meet the following prerequisites:
• You must have the CREATE PROCEDURE system privilege to create a
procedure or package in your schema, or the CREATE ANY
PROCEDURE system privilege to create a procedure or package in
another user’s schema.
Attention: To create without errors, that is, to compile the procedure
or package successfully, requires the following additional privileges:
The owner of the procedure or package must have been explicitly
granted the necessary object privileges for all objects referenced within
the body of the code; the owner cannot have obtained required
privileges through roles.
If the privileges of a procedure’s or package’s owner change, the procedure
must be reauthenticated before it is executed. If a necessary privilege to a
referenced object is revoked from the owner of the procedure (or package), the
procedure cannot be executed.

</quote>Try this:

SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"

If you can do it in plus with no roles you can do it in a procedure.  If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure (unless you are using Invokers rights in Oracle8i.  See the PLSQL documentation for more information on this feature and make sure you understand the ramifications).  To be able to perform that operation in a typical procedure, you need to have that privelege granted directly to you. 

Почему PL/SQL в коде (представлении/процедуре/триггере/пакете) невозможно использовать чужие объекты. Roles and ORA-00942 table or view does not exist

  Этот вопрос находится в топе вопросов на SQL.RU по СУБД Oracle.
  Краткий ответ такой, потому что грант на эти объекты выдан через роль.
  Ответ в документации по Oracle: "Потмучто в именованном PL/SQL блоке все роли отключаются"
  Чуть более развёрнутый ответ приведён на самом SQL.RU: Почему в своем pl/sql-коде (представлении/процедуре/триггере/пакете) не удается использовать чужой объект - выдается ошибка ORA-00942 table or view does not exist - хотя в sql-запросе или в анонимном pl/sql/блоке все нормально, обращение к объекту другого пользователя проходит без проблем?
Ответ. Скорее всего на чужой объект есть привилегия, выданная только через роль. Для использования объектов другого пользователя или системных привелегий в своих процедурах/триггерах и т.д., а также в job-ах необходимо дать на них привилегию напрямую, не через роль.
Так же это правило следует учесть для динамического SQL: FAQ: ORA-01031: insufficient privileges при выполнении динамического SQL в процедурах (по этой ссылке приводится ещё более развёрнутый ответ)

Ну и самый подробный ответ на этот вопрос нам предлагает ASKTOM.ORACLE.COM.

Привожу перепечатку этого ответа, мало ли он исчезнет:

You Asked Hi Tom, We are using 2 schemas, user1, user2. User1 has user1_table, and created role role1. The following was issued: grant select, insert, update, delete on user1_table to role1; grant role1 to user2 User2 created private synonym user1_table for user1.user1_table and tried to create : create or replace procedure aa as aa user1_table.column%TYPE; begin null; end; / Warning: Procedure created with compilation errors. SQL> show err Errors for PROCEDURE AA: LINE/COL ERROR -------- ----------------------------------------------------------------- 2/4 PLS-00201: identifier 'USER1.USER1_TABLE' must be declared 2/4 PL/SQL: Item ignored Why user2 cannot access user1_table in his procedure, if he has rights on the this table via role1? We do not want to grant user1_table to user2 directly. Thanks, Dusan and we said... See http://asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html You have no choice but to grant select on user1_table to user2 directly. It is the only way to make it work. Why is it that roles are not used during the compilation of a procedure? It has to do with the dependency model. Oracle is not storing exactly WHY you are allowed to access T ¡V only that you are (directly able to -- not via a role). Any change to your privileges that might cause access to T to go away will cause the procedure to become invalid and necessitate its recompilation. Without roles ¡V that means only ¡§REVOKE SELECT ANY TABLE¡¨ or ¡§REVOKE SELECT ON T¡¨ from the definers account or from PUBLIC. With roles ¡V it greatly expands the number of times we would invalidate this procedure. If some role that was granted to some role that was granted to this user was modified, this procedure might go invalid, even if we did not rely on that privilege from that role. ROLES are designed to be very fluid when compared to GRANTS given to users as far as privilege sets go. For a minute, let¡¦s say that roles did give us privileges in stored objects. Now, most any time anything was revoked from ANY ROLE we had, or any role any role we have has (and so on -- roles can and are granted to roles) -- many of our objects would become invalid. Think about that -- REVOKE some privilege from a ROLE and suddenly your entire database must be recompiled! Consider the impact of revoking some system privilege from a ROLE, it would be like doing that to PUBLIC now -- don't do it, just think about it (if you do revoke some powerful system privilege from PUBLIC, do it on a test database). Revoking SELECT ANY TABLE from PUBLIC for example would cause virtually every procedure in the database to go invalid. If procedures relied on roles ¡V virtually every procedure in the database would constantly become invalid due to small changes in permissions. Since one of the major benefits of procedures is the ¡§compile once, run many¡¨ model ¡V this would be disastrous for performance. Also consider that roles may be ƒá Non-default: If I have a non-default role and I enable it and I compile a procedure that relies on those privileges, when I log out I no longer have that role -- should my procedure become invalid -- why? Why not? I could easily argue both sides. ƒá Password Protected: if someone changes the password on a ROLE, should everything that might need that role be recompiled? I might be granted that role but not knowing the new password ¡V I can no longer enable it. Should the privileges still be available? Why or Why not? Again, arguing either side of this is easy. There are cases for and against each. The bottom line with respect to roles in procedures with definers rights are: ƒá You have thousands or tens of thousands of end users. They don't create stored objects (they should not). We need roles to manage these people. Roles are designed for these people (end users). ƒá You have far fewer application schema's (things that hold stored objects). For these we want to be explicit as to exactly what privileges we need and why. In security terms this is called the concept of 'least privileges' -- you want to specifically say what privilege you need and why you need it. If you inherit lots of privileges from roles you cannot do that effectively. We can manage to be explicit since the number of development schemas is SMALL (but the number of end users is large)... ƒá Having the direct relationship between the definer and the procedure makes for a much more efficient database. We recompile objects only when we need to, not when we might need to. It is a large efficiency enhancement.

Oracle Database 11g: Новые возможности в PL/SQL

    Для людей желающих прочитать про плюшки, которые есть в Oracle 11g рекомендую почитать этот документ. В нём представлен список новых фишек, которые есть в Oracle 11g

Oracle Database 11g: Новые возможности в PL/SQL

    Также есть замечательный вебсайт, но он на английском, где также можно прочитать о новых возможностях разных версий:

oracle-developer.net

Динамический SQL

    Порой случается, что надо чтобы код выполнял запрос, который заранее точно не известен, для решения таких задач в СУБД Oracle существует 3 способа решения данной задачи.
    Здесь я только перечислю те решения, которыми мне доводилось пользоваться, подробности можно почерпнуть из документации Oracle.

Индекс для контекстного поиска на основе функции

   В Oracle есть замечательная возможность осуществлять контекстный поиск по тексту, который в явном виде не хранится в таблице. Для того, чтобы воспользоваться этой возможностью необходимо написать функцию, которая по rowid целевой таблицы построит соответсвующую ей текстовую строку строку.

Контекстный поиск

   Как-то столкнулся я с новой для меня задачей, реализовтаь контектный поиск внутри Oracle.
   На русском языке Google сходу выдаёт одну статью, которая конечно помогла мне, но в ней не было по шагам написано что надо сделать, чтобы всё заработало. поэтому здесь я будет краткая справка о том что делать и куда бежать. По сути - это будет пересказ оракловой статьи - Getting Started with Oracle Text.

Расширенные функции Oracle 10g по работе со стеком ошибок

    В Oracle 10g возможности для работы со стеком ошибок были расширены. В пакете DBMS_UTILITY появились функции, которые позволяют получить больше информации о том, как ведёт себя программа в случае возникновения исключительной ситуации.

Конвейер из табличных (pipelined) функций

    Как-то раньше мне не приходилось сталкиваться с задачей, когда приходилось внутри одной табличной функции запрашивать данные из другой табличной функции в передавать их выше, но в конце концов этот момент настал.
    Мне было необходимо перегрузить табличную функцию, а поскольку алгоритм обработки данных полностью повторял уже существующий я решил воспользоваться вызовом табличной функции.
    Вроде бы всё просто, делай селект типа:
    for foo in (select * from pipelined_function(x))
    loop
        pipe row (r.a, r.b, r.c ...);
    end loop;
    Но мне хотелось больше удобства, не хотел я писать кучу этих имён в pipe row, и начались поиски решения. Слава богу знаний хватило на то, чтобы прийти вот к такому решению:

Оператор Continue

Не знаю, многие ли слышали об этом, но в Oracle 11g появился оператор, который уже давно существует в других языках - Continue.

Суть у него проста, прервать текущую итерацию и перейти к следующей. Лично я серьёзно им никогда не пользовался, иногда применял в скриптовых языках, но дальше дело не доходило.

В любом случае, встречайте CONTINUE собственной персоной:

BEGIN

 FOR i IN 1..5 LOOP

   dbms_output.put_line('First statement, index is ['||i||'].');

    IF MOD(i,2) = 0 THEN

      CONTINUE;

    END IF;

   dbms_output.put_line('Second statement, index is ['||i||'].');

 END LOOP;

END;

First statement, index is [1].

Second statement, index is [1].

First statement, index is [2].

First statement, index is [3].

Second statement, index is [3].

First statement, index is [4].

First statement, index is [5].

Second statement, index is [5].

Алгоритм прост. Мы перебираем цифры от 1 до 5 и при помощи функции получения остатка от деления MOD проверяем их на чётность. Если остаток равен 0, то выполняем оператор CONTINUE, которые прерывает текущую итерацию и переходит к следующей, если число не чётное, то выполнение цикла продолжается дальше.

Подстановка текста подпрограмм (subprogram inlining) в Oracle 11g

В Oracle 11g появился новый механизм оптимизации программного кода - использование автоматической инлайн подстановки текста подпрограммы, вместо её вызова. При частых вызовах - эта штука очень хорошо позволяет экономить на накладных расходах. Надо заметить, что данный механизм работает только в случае выставления уровня оптимизации 2 и выше.