Execute immediate create table as select oracle ошибка

ORA-01031: Insufficient Privileges means that the current user did not use the right privilege to process the SQL statement.

Since this error can be seen almost in every kind of SQL statement, sometimes you would never know what privilege you lack. So I do my best to collect cases for you.

There’re several error patterns of ORA-01031 in this post. You may click whichever situation you encountered.

1. Select (Query)
2. Create Table
3. Create Index
4. Create View
5. Alter Table (Add Constraint)
6. Alter User
7. Password Change
8. Insert, Update and Delete
9. EXECUTE IMMEDIATE
10. DGMGRL
11. Alter Pluggable Database Close
12. PDB Clone

Select (Query)

Tried to select other’s table, we got ORA-01031: insufficient privileges.

SQL> show user
USER is "HR"
SQL> select distinct gender from oe.customers;
select distinct gender from oe.customers
                               *
ERROR at line 1:
ORA-01031: insufficient privileges

Theoretically, if we can’t see other’s table, we got ORA-00942: table or view does not exist. But the error message indicates us that we don’t use the right privilege to do it. Why? We’d better do some tests.

The first test is that, can we describe the table’s definition?

SQL> desc oe.customers;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
...
 GENDER                                             VARCHAR2(1)
...

Yes, we can see its metadata, but not data.

So what object privileges we have now? Let’s check them by a privileged user.

SQL> show user
USER is "SYSTEM"
SQL> select privilege from dba_tab_privs where owner = 'OE' and table_name = 'CUSTOMERS' and grantee = 'HR' order by 1;

PRIVILEGE
----------------------------------------
DELETE
INSERT
UPDATE

OK, we can INSERT, UPDATE and DELETE, but no SELECT privilege. This is really weird.

Solution to ORA-01031

To solve insufficient privilege in querying, we should grant SELECT privilege to the user.

SQL> grant select on oe.customers to hr;

Grant succeeded.

Then we query the table again.

SQL> select distinct gender from oe.customers;

G
-
M
F

OK, the problem is solved.

ORA-01031 is very common when a new user wants to create a table. Let’s see an example.

SQL> conn / as sysdba
Connected.
SQL> create user thomas identified by thomas;

User created.

SQL> grant create session to thomas;

Grant succeeded.

As you can see, a new user THOMAS is created, but we only grant CREATE SESSION to him, which allows him to connect to the database. Let’s see what will happen if the new user wants to create a table.

C:Usersedchen>sqlplus thomas/thomas@orcl
...
SQL> create table test1 (id number, e_id number);
create table test1 (id number, e_id number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

Immediately, ORA-01031: insufficient privileges shows up, which tells THOMAS he doesn’t have the right privilege to do that.

Solution to ORA-01031

The solution is simple, just grant CREATE TABLE to user, a schema-based privilege or CREATE ANY TABLE, a system-wide privilege.

SQL> conn / as sysdba
Connected.
SQL> grant create table to thomas;

Grant succeeded.

Then tell Thomas to try it again.

SQL> create table test1 (id number, e_id number);

Table created.

If you use EXECUTE IMMEDIATE to run CREATE TABLE in a stored procedure, you may check ORA-01031 in EXECUTE IMMEDIATE section in this post.

Create Index

In the above section, we have granted CREATE TABLE to THOMAS, which enables Thomas the ability to CREATE INDEX in his schema.

Please note that, CREATE INDEX is not a valid privilege, but CREATE ANY INDEX is.

Let’s see an example and then we guess what privilege we need.

Suppose Thomas wants to create an index for SH.CUSTOMERS in Thomas’s schema, so we grant SELECT on that table (object privilege) by instinct.

SQL> grant select on sh.customers to thomas;

Grant succeeded.

Then Thomas tries to create an index on that table.

SQL> create index customer_id_gen_idx on sh.customers (cust_id, cust_gender);
create index oe.customer_id_gen_idx on sh.customers (cust_id, cust_gender)
                                          *
ERROR at line 1:
ORA-01031: insufficient privileges

Solution to ORA-01031

This is because SELECT on that table is not enough, you should additionally grant INDEX on that table to user, which is an object privilege.

SQL> grant index on sh.customers to thomas;

Grant succeeded.

Try again.

SQL> create index customer_id_gen_idx on sh.customers (cust_id, cust_gender);

Index created.

Even though the case is possible in reality, we seldom create index for other user’s table in our schema.

Create View

If you have read the above section, then you have known that you have to grant CREATE VIEW to the user who complain ORA-01031.

SQL> grant create view to thomas;

Grant succeeded.

If the user still got ORA-01031 after granting CREATE VIEW to him, it must be a deeper problem. That’s why this section is little longer.

Inherit Privilege from Role

Some privileges inherited from the role might not work in some situation, especially when accessing intermediate kinds of object, like views or store procedures.

Here is a case that can reproduce the error.

We grant role RESOURCE to THOMAS. Then we grant the system privilege SELECT ANY TABLE to the role RESOURCE.

SQL> grant resource to thomas;

Grant succeeded.

SQL> grant select any table to resource;

Grant succeeded.

So we can expect that the user THOMAS inherits the system privilege from RESOURCE. That is, THOMAS can select any other’s table.

Let’s do the first test. Use THOMAS to select other user’s table SH.SALE.

SQL> select count(*) from sh.sales;

  COUNT(*)
----------
    918843

Good, it acts as we expected, although THOMAS has not any object privilege on SH.SALE.

Let’s do the second test. Use THOMAS to create a view which is based on other user’s table SH.SALE.

SQL> create view sh_sales_v as select * from sh.sales;
create view sh_sales_v as select * from sh.sales
                                           *
ERROR at line 1:
ORA-01031: insufficient privileges

What happened? THOMAS has CREATE VIEW and inherit SELECT ANY TABLE from RESOURCE, it should have no problem.

The result implies that the role’s privileges does not reach underlying objects through intermediate objects like views.

Solution to ORA-01031

The solution to this problem is to grant SELECT on the table to user directly.

First, grant the object privilege explicitly to resolve the problem.

SQL> grant select on sh.sales to thomas;

Grant succeeded.

Then tell Thomas to create view again.

SQL> create view sh_sales_v as select * from sh.sales;

View created.

Now, it’s no problem.

Please notice that, if you create a synonym on SH.SALES, it will succeed whether the explicit object privilege is granted directly or not.

Alter Table (ADD CONSTRAINT)

In the above section, we have granted CREATE TABLE to THOMAS. Implicitly, he also has the right to ALTER TABLE on schema-level. So the cause of ORA-01031 in ALTER TABLE is not so obvious as we thought.

Please note that, ALTER TABLE is not a privilege, but ALTER ANY TABLE is. That’s why there’s no such GRANT ALTER TABLE TO statement.

Let’s see an example. User Thomas wants to add a constraint so as to make a reference to another user’s data, so we grant SELECT on that table to THOMAS by instinct.

SQL> grant select on hr.employees to thomas;

Then we make the reference.

SQL> alter table test1 add constraint fk_eid foreign key (e_id) references hr.employees (employee_id);
alter table test1 add constraint fk_eid foreign key (e_id) references hr.employees (employee_id)
                                                                         *
ERROR at line 1:
ORA-01031: insufficient privileges

We got ORA-01031.

Solution to ORA-01031

The right privilege to reference other’s data is not SELECT, it’s REFERENCES.

We should grant REFERENCES on the table to user either by HR or privileged users.

SQL> grant references on hr.employees to thomas;

Grant succeeded.

Now Thomas can finish his job.

SQL> alter table test1 add constraint fk_eid foreign key (e_id) references hr.employees (employee_id);

Table altered.

Alter User

Tried to add some quota on tablespace to itself, but it lacks of some privileges.

SQL> show user
USER is "HR"
SQL> alter user hr quota unlimited on users;
alter user hr quota unlimited on users
*
ERROR at line 1:
ORA-01031: insufficient privileges

The right privilege in this case is ALTER USER.

SQL> show user
USER is "SYSTEM"
SQL> grant alter user to hr;

Grant succeeded.

Then we do it again.

SQL> show user
USER is "HR"
SQL> alter user hr quota unlimited on users;

User altered.

SQL> alter user oe account lock;

User altered.

As you can see, with ALTER USER privilege, the user not only can grant some quota to itself, but also can change other’s status.

Password Change

Tried to change other’s password by SQL*Plus command password, but the user has inadequate privileges.

SQL> show user
USER is "HR"
SQL> password oe
Changing password for oe
New password:
Retype new password:
ERROR:
ORA-01031: insufficient privileges

Password unchanged

Since password command is actually an ALTER USER statement, the correct privilege to change other’s password is ALTER USER.

Insert, Update and Delete

You may have the right to select other’s table.

SQL> conn sh/sh
Connected.
SQL> select * from hr.t1;

        ID
----------
         1
         2
         3

But you may not have the right to modify the table. For example, INSERT INTO some data like this:

SQL> insert into hr.t1 values (4);
insert into hr.t1 values (4)
               *
ERROR at line 1:
ORA-01031: insufficient privileges

This is because you lack INSERT, UPDATE or DELETE privilege to modify on that table which is usually owned by others.

Solution to ORA-01031

Clearly, the right privilege is INSERT, UPDATE or DELETE at object-level. You may ask for DBA or the object owner to grant the privilege to you.

SQL> conn hr/hr
Connected.
SQL> grant insert,update,delete on hr.t1 to sh;

Grant succeeded.

As we can see, the grantor grants 3 object privileges INSERT, UPDATE and DELETE on the table to the grantee at a time.

In some cases, you may consider to grant all possible object privileges to user, for example:

SQL> grant all on hr.t1 to sh;

Grant succeeded.

In the statement, ALL is a keyword which means all possible privileges on specified object. For a table, it naturally includes not only SELECT, but also INSERT, UPDATE and DELETE.

As a result, we can insert some rows.

SQL> conn sh/sh
Connected.
SQL> insert into hr.t1 values (4);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from hr.t1;

        ID
----------
         1
         2
         3
         4

That is to say, not only SELECT, but also INSERT, UPDATE or DELETE privilege you should have to manipulate tables owned by other users.

Let’s see how I reproduce ORA-01031 for statements using EXECUTE IMMEDIATE by the following example.

Inherit Privilege from Role

In the above section, I have granted role RESOURCE to THOMAS. Now I grant CREATE ANY DIRECTORY and DROP ANY DIRECTORY to the role RESOURCE.

SQL> grant create any directory, drop any directory to resource;

Grant succeeded.

So we can expect that user THOMAS can also do such operations by inheriting all privileges from RESOURCE.

Things look fine when we use THOMAS to create or drop directories.

SQL> create directory tmp_path as '/u02/tmp';

Directory created.

SQL> drop directory tmp_path;

Directory dropped.

SQL> create directory tmp_path as '/u02/tmp';

Directory created

Now, Thomas would like to create directories in stored procedures which is also called named PL/SQL blocks or programming units.

First of all, DBA have to grant CREATE PROCEDURE to him before Thomas doing anything.

SQL> grant create procedure to thomas;

Grant succeeded.

Then Thomas create a procedure like this:

SQL> create or replace procedure drop_create_tmp_dir is
begin
execute immediate 'drop directory tmp_path';
execute immediate 'create or replace directory tmp_path as ''/u02/tmp''';
end drop_create_tmp_dir;
/
  2    3    4    5    6
Procedure created.

It seems no problem. But when we execute the stored procedure (named PL/SQL), we got ORA-01031 at line 3.

SQL> exec drop_create_tmp_dir;
BEGIN drop_create_tmp_dir; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "THOMAS.DROP_CREATE_TMP_DIR", line 3
ORA-06512: at line 1

Rationale

This is because the system privileges inherited from role cannot be used in named stored procedures with definer’s right.

Solutions to ORA-01031

Now we have several options, the first one is to grant all necessary privilege to the user directly, the second one is to use invoker’s right, and the last one is to use anonymous PL/SQL blocks.

1. Directly Granting to User

THOMAS should directly get the system privilege from DBA, not inherit from role.

SQL> grant create any directory, drop any directory to thomas;

Grant succeeded.

Back to THOMAS, we can execute it again.

SQL> exec drop_create_tmp_dir;

PL/SQL procedure successfully completed.

The better thing is that we don’t have to recompile the procedure.

2. Use Invoker’s Right

Another way to solve ORA-01031 for statements in EXECUTE IMMEDIATE is to use invoker’s right to define the procedure.

Let’s revert the granting by SYS.

SQL> revoke create any directory, drop any directory from thomas;

Revoke succeeded.

Then we created the procedure with AUTHID CURRENT_USER clause.

SQL> create or replace procedure drop_create_tmp_dir authid current_user is
begin
execute immediate 'drop directory tmp_path';
execute immediate 'create or replace directory tmp_path as ''/u02/tmp''';
end drop_create_tmp_dir;
/
  2    3    4    5    6
Procedure created.

Try to execute the procedure by THOMAS.

SQL> exec drop_create_tmp_dir;

PL/SQL procedure successfully completed.

By invoker’s right, we can use role’s privileges.

3. Anonymous PL/SQL Block

What we mean in the above is that role privileges cannot penetrate NAMED stored procedures. That is to say, you can use role privileges in anonymous PL/SQL blocks. For instance, we can rewrite the stored procedure to an anonymous PL/SQL as this:

begin
execute immediate 'drop directory tmp_path';
execute immediate 'create or replace directory tmp_path as ''/u02/tmp''';
end;
/

You can save and use it as a normal SQL script file.

For the same reason, CREATE TABLE in EXECUTE IMMEDIATE can also throw ORA-01031.

DGMGRL

DGMGRL allows user to query the status of all nodes involved through the local authentication without problem, but it might fail to switchover to a standby database or convert to a snapshot standby.

DGMGRL Switchover

Let’s see a switchover in 11g, it will fail when you connect DGMGRL with local authentication.

[oracle@primary01 ~]$ dgmgrl /
...
DGMGRL> switchover to standb
Performing switchover NOW, please wait...
Operation requires shutdown of instance "primdb2" on database "primdb"
Shutting down instance "primdb2"...
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the SWITCHOVER command:
        shut down instance "primdb2" of database "primdb"

DGMGRL>

But if you connect DGMGRL with the database password, the switchover will succeed.

[oracle@primary01 ~]$ dgmgrl sys/password@primdb1
...
DGMGRL> switchover to standb
Performing switchover NOW, please wait...
Operation requires shutdown of instance "primdb2" on database "primdb"
Shutting down instance "primdb2"...
Database closed.
Database dismounted.
ORACLE instance shut down.
New primary database "standb" is opening...
Operation requires shutdown of instance "primdb1" on database "primdb"
Shutting down instance "primdb1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "primdb1" on database "primdb"
Starting instance "primdb1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "standb"
DGMGRL>

DGMGRL Convert

Same error happened in a conversion.

[oracle@primary01 ~]$ dgmgrl /
...
DGMGRL> CONVERT DATABASE standb TO SNAPSHOT STANDBY;
Converting database "standb" to a Snapshot Standby database, please wait...
Operation requires shutdown of instance "standb2" on database "standb"
Shutting down instance "standb2"...
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the CONVERT command:
        shut down instance "standb2" of database "standb"

Solution to ORA-01031

You must use the database authentication to convert a standby database.

[oracle@primary01 ~]$ dgmgrl sys/password@primdb1
...
DGMGRL> CONVERT DATABASE standb TO SNAPSHOT STANDBY;
Converting database "standb" to a Snapshot Standby database, please wait...
Operation requires shutdown of instance "standb2" on database "standb"
Shutting down instance "standb2"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Continuing to convert database "standb" ...
Database "standb" converted successfully
...

For the same reason, the broker is unable to startup the new standby database during a switchover and throws ORA-01017 due to OS authentication.

Alter Pluggable Database Close

We saw an error when we tried to close a pluggable database (PDB) by a normal user.

SQL> conn hr/password@orclpdb
Connected.
SQL> show user
USER is "HR"
SQL> alter pluggable database close;
alter pluggable database close
*
ERROR at line 1:
ORA-01031: insufficient privileges

To solve ORA-01031, we take two steps to make the user be able to close a PDB.

1. Grant SYSDBA to the User

Please make sure that you login as SYS and are in the right container.

SQL> show user
USER is "SYS"
SQL> show con_namev
CON_NAME
------------------------------
ORCLPDB

Then we grant SYSDBA privilege to the user.

SQL> grant sysdba to hr;

Grant succeeded.

2. Connect as SYSDBA

The user should use SYSDBA privilege to connect to the PDB.

SQL> conn hr/password@orclpdb as sysdba
Connected.
SQL> alter pluggable database close;

Pluggable database altered.

Actually, the normal user has become a SYS which of course has the ability to maintain database.

PDB Clone

When you try to clone a remote PDB via a database link, you may see ORA-17628 and ORA-01031 at that moment. I have talk about it in that post.

Я хочу читать разные операторы sql из поля CLOB в HEX-код. Затем я хочу вернуть HEX-код обратно в sql-скрипт в varchar2 и выполнить его. Генерация HEX-кода и кастинг работает, но не выполняется. Может ли кто-нибудь помочь мне, если и как возможно немедленное выполнение?
Следующий пример
Мой операт…

В продолжение этой темы
with a as (
select * from hubspot.information_schema.tables
where table_catalog = ‘HUBSPOT’ AND TABLE_SCHEMA = ‘MONGODB’ and table_name != ‘_SDC_REJECTED’ and table_type = ‘BASE TABLE’
),
b as (
select * ,
$$SELECT * FROM HUBSPOT.MONGODB.TABLE_NAME$$ t,
replace(…

Мой пользователь является владельцем простой схемы в экземпляре Oracle в моем задании, назовем его USER E, с некоторыми ограниченными привилегиями. Также у меня есть USER E_ETL для получения информации о другой базе данных с технологией ETL. Мой пользователь E является владельцем некоторых таблиц …

Я пытаюсь написать сценарий, который завершит сеансы. Я получаю неправильное завершение команды SQL при попытке EXECUTE IMMEDIATE, и я не могу понять, что я делаю неправильно и почему я получаю сообщение об ошибке. я удалил ; сформируйте строку cur_show_connection, и это тоже не сработало.
|| »’…

Я создаю хранимую процедуру BQ для усечения всех таблиц в наборе данных. У меня есть двухэтапный процесс. Шаг 1 идентифицирует все соответствующие таблицы. Ожидается, что шаг 2 будет перебирать каждую таблицу и усекать.
У меня есть следующий код для этого:
for record in
( select TABLE_NAME
fr…

Я получаю сообщение об ошибке ORA-00904: ggCategory: недопустимый идентификатор.
Если я запускаю select как обычно, он работает без проблем и возвращает правильные значения. Кто-нибудь знает, где ошибка синтаксиса?
execute immediate ‘create table TEST_TABLE as (
select
category.name l_…

Это мой блок plsql, динамический SQL. Я не могу найти причину, по которой возникает ошибка «нет ORA-01008: не все переменные связаны ORA-06512: в строке 23».
Я не могу найти ошибку в инструкции EXECUTE IMMEDIATE.
DECLARE
form_name VARCHAR2(225) := ‘MUST AS’;
ad_no VARCHAR…

Я создал ниже параметризованную хранимую процедуру Oracle, в которой я пытаюсь предоставить привилегию таблицы Truncate другому пользователю, но получаю ошибку как wrong number or types of arguments in call to DO_TRUNCATE.
create or replace procedure «DWH_02″.»DO_TRUNCATE» (truncate_tablename NVARCH…

Я столкнулся с проблемой, когда пытаюсь создать таблицу динамически, используя DBMS_CLOUD.CREATE_EXTERNAL_TABLE внутри хранимой процедуры или упакованной программы. dbms_ouput.put_line динамического кода создаст правильный код, который я могу скопировать и запустить в новом сеансе без проблем, но н…

Привет всем. Я пытаюсь вывести результаты этого запроса в таблицу на GBQ. Я связал предыдущий вопрос для справки
GBQ Convert Data types en Mass
Начальный код
execute immediate (select »’
select `Group`, »’ || (select string_agg(‘cast(‘ || Fruit || ‘ as Numeric) as ‘ || Fruit ) from (
select regex…

У меня есть немедленное выполнение в моем пакете, и когда v_object_name представляет собой что-то вроде 20200823_AGL, я получаю эту ошибку:
ORA-00903: Неверное имя таблицы
Как я могу это исправить?
EXECUTE IMMEDIATE ‘SELECT MAX(LAST_UPDATE),COUNT(*) FROM ‘ || v_object_name || »
….

Я безуспешно пытался выполнить требование, когда мне нужно передать таблицу, содержащую 3 поля: имя объекта, клиент и местоположение из AMDP в хранимую процедуру. Хранимая процедура должна использоваться для возврата таблицы, в которой хранятся тот же заказчик, местоположение и мин (дата).
Мин (дат….

Folks !
Мне нужны идеи со следующей проблемой:
У меня две таблицы:
Таблица 1:
+——-+————+———+
| ID | field_name | value |
+——-+————+———+
| 1 | usd | 10.08 |
| 1 | gross_amt | 52.0 |
| 1 | jpy | 30.05 |
| 2 | usd | ….

В моей хранимой процедуре я использую «EXECUTE IMMEDIATE» для выполнения оператора в строковом формате, но это не дает результата. Я добавил прока ниже:
CREATE OR REPLACE PROCEDURE sp_TEST(tablenamestring Varchar2) IS
ownerName VARCHAR2(5000);
sqlString VARCHAR2(2000);
BEGIN
FOR ROW_IDX IN ( SE….

Я пытаюсь запустить приведенный ниже сценарий. Мой скрипт отлично работает без опции «WITH ADMIN OPTION» в EXECUTE IMMEDIATE. Но при использовании «WITH ADMIN OPTION» я получаю ошибку ниже.
«Отчет об ошибке — ORA-00900: недопустимый оператор SQL ORA-06512: в строке 17 00900. 00000 -« недопустимый …

Итак, у меня есть такой запрос:
select ‘alter index ‘||a.index_owner||’.’||a.index_name|| ‘ rebuild partition ‘||a.partition_name
from dba_ind_partitions a
where a.index_name in (‘IDX_PI_T_BSCS_CONTRACT_HISTOR2’, ‘IDX_PI_T_BSCS_CONTRACT_HISTOR3’, ‘IDX_PI_T_BSCS_RATEPLAN_HIST_C1’)
and a.status = ‘….

В этом учебном материале вы узнаете, как использовать динамический SQL в Oracle/PLSQL с синтаксисом и примерами.

Описание

• Динамический SQL делает ваши программы более гибкими, создавая и обрабатывая SQL предложения во время выполнения программ.
• С динамическим SQL вы можете напрямую выполнять большинство типов операторов SQL, включая определение данных и операторы управления данными.
• Вы можете строить запросы, в которых вы заранее не знаете имен таблиц, предложений WHERE и другой информации.

Оператор EXECUTE IMMEDIATE

Oracle/PLSQL оператор EXECUTE IMMEDIATE подготавливает (анализирует) и немедленно выполняет динамический SQL-запрос или анонимный PL/SQL блок.
Основным аргументом EXECUTE IMMEDIATE является строка, содержащая SQL-запрос для выполнения. Вы можете создать строку, используя конкатенацию, или использовать предопределенную строку.
Динамическая строка может содержать любой оператор SQL (без последней точки с запятой), за исключением многострочных запросов или любой PL/SQL блок (с последней точкой с запятой).
Строка dynamic_string также может содержать заполнители, произвольные имена, которым предшествует двоеточие, для аргументов связывания bind_argument. В этом случае вы указываете, какие переменные PL/SQL соответствуют заполнителям, с помощью операторов INTO, USING и RETURNING INTO. Во время выполнения аргументы связывания заменяют соответствующие заполнители в динамической строке. Каждый заполнитель должен быть связан с аргументом связывания в предложении USING и/или предложении RETURNING INTO.

Синтаксис

Синтаксис Oracle/PLSQL оператора EXECUTE IMMEDIATE для передачи значения в переменную или строку:

EXECUTE IMMEDIATE dynamic_string
[ INTO {[define_variable[, define_variable] … | record_name}]
[USING [IN | OUT | IN OUT] bind_argument ]
returning_clause;

или синтаксис Oracle/PLSQL оператора EXECUTE IMMEDIATE для передачи значения в коллекцию

EXECUTE IMMEDIATE dynamic_string
[[ BULK COLLECT] INTO {host_array_name | collection_name}]
[USING [IN | OUT | IN OUT] bind_argument]
returning_clause;

Параметры или аргументы

dynamic_string

Строковый литерал, переменная или выражение, представляющее один оператор SQL или блок PL/SQL. Он должен иметь тип CHAR или VARCHAR2, а не NCHAR или NVARCHAR2.

BULK COLLECT

Сохраняет значения результатов в одной или нескольких коллекциях для более быстрых запросов, чем циклы с операторами FETCH.

INTO

Используется только для однострочных запросов, в этом разделе указываются переменные или записи, в которые извлекаются значения столбцов. Для каждого значения, полученного запросом, в предложении INTO должна быть соответствующая тип-совместимая переменная или поле.

define_variable

Переменная, в которой сохраняется значение выбранного столбца.

record_name

Пользовательская запись или запись %ROWTYPE, в которой сохраняется выбранная строка.

bind_argument

Выражение, значение которого передается в динамический оператор SQL, или переменная, в которой сохраняется значение, возвращаемое динамическим оператором SQL.

collection_name

Объявленная коллекция, в которую извлекаются значения select_item из dynamic_string. Для каждого select_item должна быть соответствующая, совместимая с типом коллекция в списке.

host_array_name

Массив (объявленный в хост-среде PL/SQL и переданный PL/SQL как переменная связывания), в который извлекаются значения select_item. Для каждого select_item должен быть соответствующий, совместимый с типом массив в списке. Массивы хоста должны начинаться с двоеточия.

USING

По умолчанию — IN. Определяет список входных и/или выходных аргументов привязки.

returning_clause

Возвращает значения из вставленных строк, устраняя необходимость SELECT строки после. Вы можете извлечь значения столбца в переменные или в коллекции. Вы не можете использовать предложение RETURNING для удаленной или параллельной вставки. Если инструкция не влияет ни на какие строки, значения переменных, указанных в предложении RETURNING, не определены.

Примеры:

• Некоторые примеры динамического SQL
• Пример процедуры динамического SQL, которая принимает имя таблицы и предложение WHERE
• Указание режимов параметров для переменных связывания в строках динамического SQL
• Построение динамического запроса с помощью динамического SQL
• Примеры динамического SQL для типов объектов и коллекций
• Использование Bulk (множественного) SQL в динамическом SQL
• Использование динамического SQL с Bulk SQL
• Примеры динамический SQL с предложением BULK COLLECT INTO
• Пример динамический SQL с предложением RETURNING BULK COLLECT INTO
• Пример динамический SQL внутри оператора FORALL
• Рекомендации по динамическому SQL
• Когда использовать или пропустить точку с запятой с помощью динамического SQL
• Повышение производительности динамического SQL с помощью переменных связывания
• Передача имен объектов схемы в качестве параметров
• Использование дублирующих заполнителей с динамическим SQL
• Использование атрибутов курсора с динамическим SQL
• Передача NULL в динамический SQL
• Использование связей базы данных с динамическим SQL
• Использование прав Invoker с динамическим SQL
• Как избежать тупиков с помощью динамического SQL

Некоторые примеры динамического SQL

Рассмотрим несколько примеров использования Oracle/PLSQL оператора EXECUTE IMMEDIATE, чтобы понять как использовать EXECUTE IMMEDIATE в Oracle/PLSQL.
Описание команд в комментариях (—).

Пример процедуры динамического SQL, которая принимает имя таблицы и предложение WHERE

В этом примере автономная процедура принимает имя таблицы базы данных и необязательное условие предложения WHERE. Если вы пропустите условие, процедура удалит все строки из таблицы. В противном случае процедура удаляет только те строки, которые соответствуют условию.

С предложением USING режимом по умолчанию является IN, поэтому вам не нужно указывать режим параметров для аргументов связывания ввода.
С предложением RETURNING INTO режим имеет значение OUT, поэтому вы не можете указать режим параметров для выходных аргументов связывания.
Вы должны указать режим параметров в более сложных случаях, таких как этот, где вы вызываете процедуру из динамического блока PL/SQL:

Пример

Чтобы вызвать процедуру из динамического блока PL/SQL, необходимо указать режим IN OUT для аргумента связывания, связанного с формальным параметром deptno, следующим образом:

Построение динамического запроса с помощью динамического SQL

Для обработки динамического многострочного запроса вы используете три оператора: OPEN-FOR, FETCH и CLOSE.
Сначала вы открываете переменную курсора для многострочного запроса. Затем вы выбираете строки из набора результатов по одной за раз.
Когда все строки обработаны, вы закрываете (CLOSE) курсорную переменную.

В следующем примере показано, как вы можете извлечь строки из результирующего набора динамического многострочного запроса в запись:

Примеры динамического SQL для типов объектов и коллекций

Следующий пример иллюстрирует использование объектов и коллекций. Предположим, вы определили тип объекта Person и VARRAY тип Hobbies следующим образом:

Используя динамический SQL, вы можете создать пакет, который использует эти типы:

Из анонимного блока вы можете вызвать процедуры из пакета TEAMS:

Использование Bulk (множественного) SQL в динамическом SQL

SQL Bulk связывает целые коллекции, а не только отдельные элементы. Этот метод повышает производительность за счет минимизации количества переключений контекста между механизмами PL/SQL и SQL. Вы можете использовать один оператор вместо цикла, который выдает оператор SQL на каждой итерации.

Используя следующие команды, предложения и атрибут курсора, ваши приложения могут создавать объемные операторы SQL, а затем выполнять их динамически во время выполнения:

• BULK FETCH предложение
• BULK EXECUTE IMMEDIATE предложение
• FORALL предложение
• COLLECT INTO выражение
• RETURNING INTO выражение
• %BULK_ROWCOUNT атрибут курсора

Использование динамического SQL с Bulk SQL

Массовое (множественное) связывание позволяет Oracle связать переменную в операторе SQL с коллекцией значений.
Тип коллекции может быть любым типом коллекции Oracle/PLSQL (index-by table, nested table или varray).
Элементы коллекции должны иметь тип данных SQL, такой как CHAR, DATE или NUMBER.
Три оператора поддерживают динамическое массовое связывание: EXECUTE IMMEDIATE, FETCH и FORALL.

EXECUTE IMMEDIATE

• Вы можете использовать предложение BULK COLLECT INTO с оператором EXECUTE IMMEDIATE, чтобы хранить значения из каждого столбца набора результатов запроса в отдельной коллекции.
• Вы можете использовать предложение RETURNING BULK COLLECT INTO с оператором EXECUTE IMMEDIATE, чтобы сохранить результаты оператора INSERT, UPDATE или DELETE в наборе коллекций.

FETCH

• Вы можете использовать предложение BULK COLLECT INTO с оператором FETCH для хранения значений из каждого столбца курсора в отдельной коллекции.

FORALL

• Вы можете совместить EXECUTE IMMEDIATE инструкцию с RETURNING BULK COLLECT INTO внутри инструкции FORALL. Вы можете хранить результаты всех операторов INSERT, UPDATE или DELETE в наборе коллекций.
• Вы можете передать индексированные элементы коллекции в инструкцию EXECUTE IMMEDIATE через предложение USING.
• Вы не можете объединить индексированные элементы непосредственно в строковый аргумент для EXECUTE IMMEDIATE; например, вы не можете создать коллекцию имен таблиц и написать оператор FORALL, где каждая итерация применяется к другой таблице.

Примеры динамический SQL с предложением BULK COLLECT INTO

Вы можете связать определенные переменные в динамическом запросе, используя предложение BULK COLLECT INTO. Как показано в следующем примере, вы можете использовать это предложение в массовом выражении FETCH или в массовом выражении EXECUTE IMMEDIATE:

Пример динамический SQL с предложением RETURNING BULK COLLECT INTO

Только операторы INSERT, UPDATE и DELETE могут иметь выходные переменные связывания. Вы массово связываете их в EXECUTE IMMEDIATE с предложением RETURNING BULK COLLECT INTO.
Например:

Пример динамический SQL внутри оператора FORALL

Чтобы связать входные переменные в операторе SQL, вы можете использовать оператор FORALL и предложение USING, как показано ниже. Оператор SQL не может быть запросом.
Например:

Рекомендации по динамическому SQL

В этом разделе показано, как в полной мере использовать преимущества динамического SQL и как избежать некоторых распространенных ошибок.

Когда использовать или пропустить точку с запятой с помощью динамического SQL

При построении одного оператора SQL в строке не ставьте точку с запятой в конце.
При создании анонимного блока PL/SQL добавьте точку с запятой в конце каждого оператора PL/SQL и в конце анонимного блока.
Например:

Повышение производительности динамического SQL с помощью переменных связывания

Когда код ваших операторов INSERT, UPDATE, DELETE, и SELECT находится непосредственно в PL/SQL, PL/SQL превращает переменные в переменные связывания автоматически, чтобы операторы эффективно работали с SQL. Когда вы создаете такие операторы в динамическом SQL, вам нужно указать переменные связывания самостоятельно, чтобы получить ту же производительность.

В приведенном ниже примере Oracle открывает отдельный курсор для каждого отдельного значения emp_id. Это может привести к конфликту ресурсов и снижению производительности, поскольку каждый оператор анализируется и кэшируется.

Вы можете улучшить производительность, используя переменную связывания, которая позволяет Oracle повторно использовать один и тот же курсор для разных значений emp_id:

Передача имен объектов схемы в качестве параметров

Предположим, вам нужна процедура, которая принимает имя любой таблицы базы данных, а затем удаляет эту таблицу из вашей схемы. Вы должны создать строку с оператором, который включает имена объектов, а затем использовать EXECUTE IMMEDIATE для выполнения оператора:

Используйте конкатенацию для построения строки, а не пытайтесь передать имя таблицы как переменную связывания через предложение USING.

Использование дублирующих заполнителей с динамическим SQL

Заполнители в динамическом операторе SQL связаны с аргументами связывания в предложении USING по позиции, а не по имени. Если вы укажете последовательность заполнителей, например :a, :a, :b, :b, вы должны включить в предложение USING четыре элемента. Например, учитывая динамическую строку:

тот факт, что имя x повторяется, не имеет значения. Вы можете кодировать соответствующее предложение USING с четырьмя различными переменными связывания:

Если динамический оператор представляет PL/SQL блок, то правила для дублирующих заполнителей различны. Каждый уникальный заполнитель отображается на один элемент в предложении USING. Если один и тот же заполнитель появляется два или более раз, все ссылки на это имя соответствуют одному аргументу связывания в предложении USING.
В следующем примере все ссылки на заполнитель x связаны с первым аргументом связывания a, а второй уникальный заполнитель y связан со вторым аргументом связывания b.
Например:

Использование атрибутов курсора с динамическим SQL

SQL атрибуты курсора %FOUND, %ISOPEN, %NOTFOUND и %ROWCOUNT работают в динамическом SQL при выдаче INSERT, UPDATE, DELETE или однорядного предложения SELECT:

Аналогично, при добавлении к имени переменной курсора, атрибуты курсора возвращают информацию о выполнении многострочного запроса:

Передача NULL в динамический SQL

Литерал NULL не допускается в предложении USING. Чтобы обойти это ограничение, замените ключевое слово NULL неинициализированной переменной:

Использование связей базы данных с динамическим SQL

Подпрограммы PL/SQL могут выполнять динамические операторы SQL, которые используют ссылки на базы данных для ссылки на объекты в удаленных базах данных:

Цели удаленных вызовов процедур (RPC) могут содержать динамические операторы SQL. Например, предположим, что следующая автономная функция, которая возвращает количество строк в таблице, находится в базе данных Чикаго:

Из анонимного блока вы можете вызвать функцию удаленно, как показано ниже:

Использование прав Invoker с динамическим SQL

Динамический SQL позволяет писать процедуры управления схемами, которые можно централизовать в одной схеме, а также вызывать из других схем и работать с объектами в этих схемах.

Например, эта процедура может удалить любой объект базы данных:

Допустим, эта процедура является частью схемы HR. Без этого условия AUTHID процедура всегда удаляла бы объекты в схеме HR, независимо от того, кто ее вызывает. Даже если вы передадите полное имя объекта, эта процедура не будет иметь прав для внесения изменений в другие схемы.

AUTHID оператор поднимает оба этих ограничения. Он позволяет процедуре запускаться с привилегиями пользователя, который ее вызывает, и делает неквалифицированные ссылки на объекты в схеме этого пользователя.

Как избежать тупиков с помощью динамического SQL

В некоторых ситуациях выполнение оператора определения данных SQL приводит к тупику. Например, приведенная ниже процедура вызывает взаимоблокировку, поскольку она пытается удалить саму себя. Для того чтобы избежать тупиков, никогда не пытайтесь выполнить команды ALTER или DROP подпрограммы или пакета в то время как вы все еще используете его.

Call EXECUTE IMMEDIATE in Pl/SQL block

   <source lang="sql">

SQL>
SQL> CREATE OR REPLACE FUNCTION value_in (varname IN VARCHAR)

 2     RETURN VARCHAR2
 3  IS
 4     retval VARCHAR2(2000);
 5  BEGIN
 6     EXECUTE IMMEDIATE "BEGIN :val := " || varname || "; END;" USING OUT retval;
 7     RETURN retval;
 8  END;
 9  /

Function created.
SQL></source>

Call function and get result by using «EXECUTE IMMEDIATE»

   <source lang="sql">

SQL>
SQL> CREATE OR REPLACE FUNCTION grpval (

 2     tab IN VARCHAR2,
 3     col IN VARCHAR2,
 4     grpfunc IN VARCHAR2,
 5     whr IN VARCHAR2 := NULL)
 6  RETURN VARCHAR2
 7  IS
 8     retval VARCHAR2(32767);
 9  BEGIN
10     EXECUTE IMMEDIATE
11        "SELECT " || yourfunction || "(" || col || ")
12           FROM " || tab || " WHERE " || NVL (whr, "1=1")
13        INTO retval;
14     RETURN retval;
15  END;
16  /

Function created.
SQL>
SQL></source>

Catch exception from «EXECUTE IMMEDIATE»

   <source lang="sql">

SQL>
SQL> CREATE OR REPLACE FUNCTION tabcount (tab IN VARCHAR2, whr IN VARCHAR2 := NULL)

 2     RETURN PLS_INTEGER AUTHID CURRENT_USER
 3  IS
 4     str      VARCHAR2 (32767) := "SELECT COUNT(*) FROM " || tab;
 5     retval   PLS_INTEGER;
 6  BEGIN
 7     IF whr IS NOT NULL
 8     THEN
 9        str := str || " WHERE " || whr;
10     END IF;
11
12     EXECUTE IMMEDIATE str
13                  INTO retval;
14  EXCEPTION
15     WHEN OTHERS
16     THEN
17        DBMS_OUTPUT.put_line ("TABCOUNT ERROR: " || DBMS_UTILITY.FORMAT_ERROR_STACK);
18        DBMS_OUTPUT.put_line (str);
19        RETURN NULL;
20  END;
21  /

SP2-0806: Function created with compilation warnings
SQL></source>

Create a function to count table row

   <source lang="sql">

SQL>
SQL>
SQL> CREATE TABLE product (

 2       product_name     VARCHAR2(25) PRIMARY KEY,
 3       product_price    NUMBER(4,2),
 4       quantity_on_hand NUMBER(5,0),
 5       last_stock_date  DATE
 6       );

Table created.
SQL>
SQL>
SQL> INSERT INTO product VALUES («Product 1», 99, 1, «15-JAN-03»);
1 row created.
SQL> INSERT INTO product VALUES («Product 2», 75, 1000, «15-JAN-02»);
1 row created.
SQL> INSERT INTO product VALUES («Product 3», 50, 100, «15-JAN-03»);
1 row created.
SQL> INSERT INTO product VALUES («Product 4», 25, 10000, null);
1 row created.
SQL> INSERT INTO product VALUES («Product 5», 9.95,1234, «15-JAN-04»);
1 row created.
SQL> INSERT INTO product VALUES («Product 6», 45, 1, TO_DATE(«December 31, 2008, 11:30 P.M.»,»Month dd, YYYY, HH:MI P.M.»));
1 row created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION tabcount (nm IN VARCHAR2)RETURN PLS_INTEGER

 2  IS
 3
 4     retval PLS_INTEGER;
 5
 6  BEGIN
 7
 8     EXECUTE IMMEDIATE "SELECT COUNT(*) FROM " || nm INTO retval;
 9
10     RETURN retval;
11  END;
12  /

Function created.
SQL>
SQL>
SQL> select tabcount(«product») from dual;
TABCOUNT(«PRODUCT»)

---

                 6

SQL>
SQL> drop table product;
Table dropped.</source>

Define a procedure to drop a database object

   <source lang="sql">

SQL>
SQL>
SQL> CREATE TABLE product (

 2       product_name     VARCHAR2(25) PRIMARY KEY,
 3       product_price    NUMBER(4,2),
 4       quantity_on_hand NUMBER(5,0),
 5       last_stock_date  DATE
 6       );

Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE dropit (ittype IN VARCHAR2, itname IN VARCHAR2)

 2  IS
 3  BEGIN
 4     EXECUTE IMMEDIATE "drop " || ittype || " " || itname;
 5  END;
 6  /

Procedure created.
SQL>
SQL> EXEC dropit(«table»,»product»);
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL></source>

Drop user, create user and grant permission with PL/SQL code

   <source lang="sql">

SQL>
SQL> DEF username = plsql
SQL> DEF default_ts = USERS
SQL> DEF temp_ts = TEMP
SQL>
SQL> SET FEEDBACK OFF SERVEROUTPUT ON VERIFY OFF TERMOUT OFF
SQL>
SQL> DECLARE

 2     v_count       INTEGER        := 0;
 3     v_statement   VARCHAR2 (500);
 4  BEGIN
 5
 6     SELECT COUNT (1) INTO v_count FROM dba_users
 7      WHERE username = UPPER ("&username");
 8
 9     IF v_count != 0
10     THEN
11        EXECUTE IMMEDIATE ("DROP USER &username CASCADE");
12     END IF;
13
14     v_count := 0;
15
16     v_statement :=
17           "CREATE USER &username IDENTIFIED BY oracle"
18        || " DEFAULT TABLESPACE &default_ts"
19        || " TEMPORARY TABLESPACE &temp_ts"
20        || " QUOTA UNLIMITED ON &default_ts"
21        || " ACCOUNT UNLOCK";
22
23     EXECUTE IMMEDIATE (v_statement);
24
25     -- Grant permissions
26     EXECUTE IMMEDIATE ("GRANT connect, resource TO &username");
27     EXECUTE IMMEDIATE ("GRANT CTXAPP TO &username");
28
29     DBMS_OUTPUT.put_line ("  ");
30     DBMS_OUTPUT.put_line ("User &username created successfully");
31     DBMS_OUTPUT.put_line ("  ");
32
33  EXCEPTION
34     WHEN OTHERS
35     THEN
36        DBMS_OUTPUT.put_line (SQLERRM);
37        DBMS_OUTPUT.put_line ("   ");
38  END;
39  /

User plsql created successfully
SQL>
SQL> SET FEEDBACK ON TERMOUT ON
SQL>
SQL>
SQL></source>

EXECUTE IMMEDIATE dynamic sql to alter session

   <source lang="sql">

SQL>
SQL>
SQL> DECLARE

 2     lv_sql_txt VARCHAR2(200);
 3  BEGIN
 4     EXECUTE IMMEDIATE "ALTER SESSION SET SQL_TRACE=TRUE";
 5     lv_sql_txt := "ALTER SESSION SET SORT_AREA_SIZE = 1000000";
 6     EXECUTE IMMEDIATE lv_sql_txt;
 7  END;
 8  /

PL/SQL procedure successfully completed.
SQL></source>

«execute immediate in» action

   <source lang="sql">

SQL> — create demo table
SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created.
SQL>
SQL> — prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created.
SQL>
SQL>
SQL>
SQL> — display data in the table
SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION

---

———- ———- ——— ——— ———- ———- —————

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> create or replace procedure p_backupEmp is

 2      v_name_tx VARCHAR2(30);
 3  begin
 4  --    v_name_tx:="employee"||TO_CHAR(sysdate,"YYYYMMDDHH24MISS");
 5      v_name_tx:="employee001";
 6      execute immediate  "create table "||v_name_tx||
 7         " as select * from employee";
 8  end;
 9  /

Procedure created.
SQL>
SQL> call p_backupEmp();
SQL>
SQL> — clean the table
SQL> drop table Employee

 2  /

Table dropped.
SQL></source>

execute immediate into

   <source lang="sql">

SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,

 2                    ENAME VARCHAR2(10),
 3                    JOB VARCHAR2(9),
 4                    MGR NUMBER(4),
 5                    HIREDATE DATE,
 6                    SAL NUMBER(7, 2),
 7                    COMM NUMBER(7, 2),
 8                    DEPTNO NUMBER(2));

Table created.
SQL>
SQL>
SQL> INSERT INTO EMP VALUES (7369, «SMITH», «CLERK», 7902, TO_DATE(«17-DEC-1980», «DD-MON-YYYY»), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, «ALLEN», «SALESMAN», 7698, TO_DATE(«20-FEB-1981», «DD-MON-YYYY»), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, «WARD», «SALESMAN», 7698, TO_DATE(«22-FEB-1981», «DD-MON-YYYY»), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, «JONES», «MANAGER», 7839, TO_DATE(«2-APR-1981», «DD-MON-YYYY»), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, «MARTIN», «SALESMAN», 7698,TO_DATE(«28-SEP-1981», «DD-MON-YYYY»), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, «BLAKE», «MANAGER», 7839,TO_DATE(«1-MAY-1981», «DD-MON-YYYY»), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, «CLARK», «MANAGER», 7839,TO_DATE(«9-JUN-1981», «DD-MON-YYYY»), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, «SCOTT», «ANALYST», 7566,TO_DATE(«09-DEC-1982», «DD-MON-YYYY»), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, «KING», «PRESIDENT», NULL,TO_DATE(«17-NOV-1981», «DD-MON-YYYY»), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, «TURNER», «SALESMAN», 7698,TO_DATE(«8-SEP-1981», «DD-MON-YYYY»), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, «ADAMS», «CLERK», 7788,TO_DATE(«12-JAN-1983», «DD-MON-YYYY»), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, «JAMES», «CLERK», 7698,TO_DATE(«3-DEC-1981», «DD-MON-YYYY»), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, «FORD», «ANALYST», 7566,TO_DATE(«3-DEC-1981», «DD-MON-YYYY»), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, «MILLER», «CLERK», 7782,TO_DATE(«23-JAN-1982», «DD-MON-YYYY»), 1300, NULL, 10);
1 row created.
SQL>
SQL> set echo on
SQL>
SQL> create or replace function get_row_cnts( p_tname in varchar2 ) return number

 2  as
 3     l_cnt number;
 4  begin
 5          execute immediate "select count(*) from " || p_tname into l_cnt;
 6          return l_cnt;
 7  end;
 8  /

Function created.
SQL>
SQL> set serveroutput on
SQL> exec dbms_output.put_line( get_row_cnts(«emp») );
14
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.</source>

EXECUTE IMMEDIATE USING IN

   <source lang="sql">

SQL>
SQL> CREATE TABLE employee(

 2       employee_id VARCHAR2(3) PRIMARY KEY,
 3       first_name  VARCHAR2(15),
 4       last_name   VARCHAR2(20),
 5       hire_date   DATE
 6       );

Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE run_9am_procedure (

 2     id_in     IN   employee.employee_id%TYPE,
 3     hour_in   IN   INTEGER
 4  )
 5  IS
 6     v_apptcount   INTEGER;
 7     v_name        VARCHAR2 (100);
 8  BEGIN
 9     EXECUTE IMMEDIATE "BEGIN "
10                       || TO_CHAR (SYSDATE, "DAY")
11                       || "_set_schedule (:id, :hour, :name, :appts); END;"
12        USING IN id_in, IN hour_in, OUT v_name, OUT v_apptcount;
13
14     DBMS_OUTPUT.put_line (v_name|| " has "|| v_apptcount|| " appointments on "|| TO_CHAR (SYSDATE));
15  END;
16  /

Procedure created.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL></source>

Execute sql statement in a procedure

   <source lang="sql">

SQL>
SQL> CREATE OR REPLACE PROCEDURE runddl (ddl_in in VARCHAR2)

 2     AUTHID CURRENT_USER
 3  IS
 4  BEGIN
 5     EXECUTE IMMEDIATE ddl_in;
 6  END;
 7  /

Procedure created.
SQL>
SQL>
SQL> EXEC runddl(«select sysdate from dual»);
PL/SQL procedure successfully completed.</source>

Quotation string

   <source lang="sql">

SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION qstring (str_in IN VARCHAR2, qchar_in VARCHAR2 := «|»)

 2     RETURN VARCHAR2
 3  IS
 4     retval VARCHAR2(32767);
 5  BEGIN
 6     EXECUTE IMMEDIATE
 7        "BEGIN :var := q""" || qchar_in || str_in || qchar_in || """; END;"
 8        USING OUT retval;
 9     RETURN retval;
10  END;
11  /

Function created.
SQL>
SQL>
SQL></source>

select into rowtype then use it in «execute immediate»

   <source lang="sql">

SQL>
SQL> create table job_parameters

 2  ( jobid number primary key,
 3    iterations number,
 4    table_idx number );

Table created.
SQL>
SQL> create or replace procedure dont_bind( p_job in number )

 2  as
 3      l_rec job_parameters%rowtype;
 4  begin
 5      select * into l_rec from job_parameters where jobid = p_job;
 6      for i in 1 .. l_rec.iterations
 7      loop
 8          execute immediate "insert into t" || l_rec.table_idx || " values ( " ||  i || " )";
 9          commit;
10      end loop;
11      delete from job_parameters where jobid = p_job;
12  end;
13  /

Procedure created.
SQL>
SQL> drop table job_parameters;
Table dropped.
SQL>
SQL></source>

Simple EXECUTE IMMEDIATE

The EXECUTE IMMEDIATE command can be

1. a VARCHAR2 variable,
2. a literal quoted string, or
3. any string expression.

   <source lang="sql">

begin

   execute immediate "whatever_text_string_you_want";

end;</source>

Update row with «execute immediate»

   <source lang="sql">

SQL> set echo on
SQL>
SQL> create or replace

 2  function update_row( p_owner    in varchar2,
 3                       p_newDname in varchar2,
 4                       p_newLoc   in varchar2,
 5                       p_deptno   in varchar2,
 6                       p_rowid    out varchar2 )
 7  return number
 8  is
 9  begin
10      execute immediate "update " || p_owner || ".dept
11                      set dname = :bv1, loc = :bv2
12                    where deptno = to_number(:pk)
13                returning rowid into :out"
14      using p_newDname, p_newLoc, p_deptno
15      returning into p_rowid;
16
17      return sql%rowcount;
18  end;
19  /

Function created.
SQL>
SQL> set serveroutput on
SQL> declare

 2      l_rowid   varchar(50);
 3      l_rows    number;
 4  begin
 5      l_rows := update_row( "SCOTT", "CONSULTING", "WASHINGTON", "10", l_rowid );
 6      dbms_output.put_line( "Updated " || l_rows || " rows" );
 7      dbms_output.put_line( "its rowid was " || l_rowid );
 8  end;
 9  /

declare

ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at «sqle.UPDATE_ROW», line 9
ORA-06512: at line 5

SQL>
SQL></source>

Use «EXECUTE IMMEDIATE» to execute an update statement

   <source lang="sql">

SQL>
SQL> CREATE OR REPLACE FUNCTION updnval (

 2     col        IN   VARCHAR2
 3   , val        IN   NUMBER
 4   , start_in   IN   DATE
 5   , end_in     IN   DATE
 6  )
 7     RETURN PLS_INTEGER
 8  IS
 9  BEGIN
10     EXECUTE IMMEDIATE    "UPDATE employee SET "
11                       || col
12                       || " = :the_value
13          WHERE hire_date BETWEEN :lo AND :hi"
14                 USING val, start_in, end_in;
15
16     RETURN SQL%ROWCOUNT;
17  END;
18  /

Function created.
SQL>
SQL></source>

Use «execute immediate» to run a insert statement

   <source lang="sql">

SQL>
SQL> create table t ( x int );
Table created.
SQL>
SQL> alter session set sql_trace=true;
Session altered.
SQL>
SQL> begin

 2      for i in 1 .. 1000
 3      loop
 4          execute immediate "insert into t values ( " || i || ")";
 5      end loop;
 6  end;
 7  /

PL/SQL procedure successfully completed.
SQL>
SQL> drop table t;
Table dropped.
SQL></source>

Use procedure to create an index dynamically

   <source lang="sql">

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE runddl (ddl_in in VARCHAR2)

 2     AUTHID CURRENT_USER
 3  IS
 4  BEGIN
 5     EXECUTE IMMEDIATE ddl_in;
 6  END;
 7  /

Procedure created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE creindx(index_in IN VARCHAR2, tab_in IN VARCHAR2, col_in IN VARCHAR2)

 2  IS
 3     DDL_statement VARCHAR2(200):= "CREATE INDEX " || index_in || " ON " || tab_in ||" ( " || col_in || ")";
 4  BEGIN
 5     runddl (DDL_statement);
 6  END;
 7  /

Procedure created.
SQL>
SQL>
SQL></source>

Wrap «EXECUTE IMMEDIATE» for current user

   <source lang="sql">

SQL>
SQL> CREATE OR REPLACE PROCEDURE runddl (ddl_in in VARCHAR2)

 2     AUTHID CURRENT_USER
 3  IS
 4  BEGIN
 5     EXECUTE IMMEDIATE ddl_in;
 6  END;
 7  /

Procedure created.
SQL>
SQL></source>

Wrap statement with «BEGIN…END»

   <source lang="sql">

SQL>
SQL> CREATE OR REPLACE PROCEDURE dynPLSQL (blk IN VARCHAR2)

 2  IS
 3  BEGIN
 4     EXECUTE IMMEDIATE "BEGIN " || RTRIM (blk, ";") || "; END;";
 5  END;
 6  /

Procedure created.
SQL>
SQL>
SQL></source>

В этом учебном материале вы узнаете, как использовать динамический SQL в Oracle/PLSQL с синтаксисом и примерами.

Описание

• Динамический SQL делает ваши программы более гибкими, создавая и обрабатывая SQL предложения во время выполнения программ.
• С динамическим SQL вы можете напрямую выполнять большинство типов операторов SQL, включая определение данных и операторы управления данными.
• Вы можете строить запросы, в которых вы заранее не знаете имен таблиц, предложений WHERE и другой информации.

Оператор EXECUTE IMMEDIATE

Oracle/PLSQL оператор EXECUTE IMMEDIATE подготавливает (анализирует) и немедленно выполняет динамический SQL-запрос или анонимный PL/SQL блок.
Основным аргументом EXECUTE IMMEDIATE является строка, содержащая SQL-запрос для выполнения. Вы можете создать строку, используя конкатенацию, или использовать предопределенную строку.
Динамическая строка может содержать любой оператор SQL (без последней точки с запятой), за исключением многострочных запросов или любой PL/SQL блок (с последней точкой с запятой).
Строка dynamic_string также может содержать заполнители, произвольные имена, которым предшествует двоеточие, для аргументов связывания bind_argument. В этом случае вы указываете, какие переменные PL/SQL соответствуют заполнителям, с помощью операторов INTO, USING и RETURNING INTO. Во время выполнения аргументы связывания заменяют соответствующие заполнители в динамической строке. Каждый заполнитель должен быть связан с аргументом связывания в предложении USING и/или предложении RETURNING INTO.

Синтаксис

Синтаксис Oracle/PLSQL оператора EXECUTE IMMEDIATE для передачи значения в переменную или строку:

EXECUTE IMMEDIATE dynamic_string
[ INTO {[define_variable[, define_variable] … | record_name}]
[USING [IN | OUT | IN OUT] bind_argument ]
returning_clause;

или синтаксис Oracle/PLSQL оператора EXECUTE IMMEDIATE для передачи значения в коллекцию

EXECUTE IMMEDIATE dynamic_string
[[ BULK COLLECT] INTO {host_array_name | collection_name}]
[USING [IN | OUT | IN OUT] bind_argument]
returning_clause;

Параметры или аргументы

dynamic_string

Строковый литерал, переменная или выражение, представляющее один оператор SQL или блок PL/SQL. Он должен иметь тип CHAR или VARCHAR2, а не NCHAR или NVARCHAR2.

BULK COLLECT

Сохраняет значения результатов в одной или нескольких коллекциях для более быстрых запросов, чем циклы с операторами FETCH.

INTO

Используется только для однострочных запросов, в этом разделе указываются переменные или записи, в которые извлекаются значения столбцов. Для каждого значения, полученного запросом, в предложении INTO должна быть соответствующая тип-совместимая переменная или поле.

define_variable

Переменная, в которой сохраняется значение выбранного столбца.

record_name

Пользовательская запись или запись %ROWTYPE, в которой сохраняется выбранная строка.

bind_argument

Выражение, значение которого передается в динамический оператор SQL, или переменная, в которой сохраняется значение, возвращаемое динамическим оператором SQL.

collection_name

Объявленная коллекция, в которую извлекаются значения select_item из dynamic_string. Для каждого select_item должна быть соответствующая, совместимая с типом коллекция в списке.

host_array_name

Массив (объявленный в хост-среде PL/SQL и переданный PL/SQL как переменная связывания), в который извлекаются значения select_item. Для каждого select_item должен быть соответствующий, совместимый с типом массив в списке. Массивы хоста должны начинаться с двоеточия.

USING

По умолчанию — IN. Определяет список входных и/или выходных аргументов привязки.

returning_clause

Возвращает значения из вставленных строк, устраняя необходимость SELECT строки после. Вы можете извлечь значения столбца в переменные или в коллекции. Вы не можете использовать предложение RETURNING для удаленной или параллельной вставки. Если инструкция не влияет ни на какие строки, значения переменных, указанных в предложении RETURNING, не определены.

Примеры:

• Некоторые примеры динамического SQL
• Пример процедуры динамического SQL, которая принимает имя таблицы и предложение WHERE
• Указание режимов параметров для переменных связывания в строках динамического SQL
• Построение динамического запроса с помощью динамического SQL
• Примеры динамического SQL для типов объектов и коллекций
• Использование Bulk (множественного) SQL в динамическом SQL
• Использование динамического SQL с Bulk SQL
• Примеры динамический SQL с предложением BULK COLLECT INTO
• Пример динамический SQL с предложением RETURNING BULK COLLECT INTO
• Пример динамический SQL внутри оператора FORALL
• Рекомендации по динамическому SQL
• Когда использовать или пропустить точку с запятой с помощью динамического SQL
• Повышение производительности динамического SQL с помощью переменных связывания
• Передача имен объектов схемы в качестве параметров
• Использование дублирующих заполнителей с динамическим SQL
• Использование атрибутов курсора с динамическим SQL
• Передача NULL в динамический SQL
• Использование связей базы данных с динамическим SQL
• Использование прав Invoker с динамическим SQL
• Как избежать тупиков с помощью динамического SQL

Некоторые примеры динамического SQL

Рассмотрим несколько примеров использования Oracle/PLSQL оператора EXECUTE IMMEDIATE, чтобы понять как использовать EXECUTE IMMEDIATE в Oracle/PLSQL.
Описание команд в комментариях (—).

Пример процедуры динамического SQL, которая принимает имя таблицы и предложение WHERE

В этом примере автономная процедура принимает имя таблицы базы данных и необязательное условие предложения WHERE. Если вы пропустите условие, процедура удалит все строки из таблицы. В противном случае процедура удаляет только те строки, которые соответствуют условию.

С предложением USING режимом по умолчанию является IN, поэтому вам не нужно указывать режим параметров для аргументов связывания ввода.
С предложением RETURNING INTO режим имеет значение OUT, поэтому вы не можете указать режим параметров для выходных аргументов связывания.
Вы должны указать режим параметров в более сложных случаях, таких как этот, где вы вызываете процедуру из динамического блока PL/SQL:

Пример

Чтобы вызвать процедуру из динамического блока PL/SQL, необходимо указать режим IN OUT для аргумента связывания, связанного с формальным параметром deptno, следующим образом:

Построение динамического запроса с помощью динамического SQL

Для обработки динамического многострочного запроса вы используете три оператора: OPEN-FOR, FETCH и CLOSE.
Сначала вы открываете переменную курсора для многострочного запроса. Затем вы выбираете строки из набора результатов по одной за раз.
Когда все строки обработаны, вы закрываете (CLOSE) курсорную переменную.

В следующем примере показано, как вы можете извлечь строки из результирующего набора динамического многострочного запроса в запись:

Примеры динамического SQL для типов объектов и коллекций

Следующий пример иллюстрирует использование объектов и коллекций. Предположим, вы определили тип объекта Person и VARRAY тип Hobbies следующим образом:

Используя динамический SQL, вы можете создать пакет, который использует эти типы:

Из анонимного блока вы можете вызвать процедуры из пакета TEAMS:

Использование Bulk (множественного) SQL в динамическом SQL

SQL Bulk связывает целые коллекции, а не только отдельные элементы. Этот метод повышает производительность за счет минимизации количества переключений контекста между механизмами PL/SQL и SQL. Вы можете использовать один оператор вместо цикла, который выдает оператор SQL на каждой итерации.

Используя следующие команды, предложения и атрибут курсора, ваши приложения могут создавать объемные операторы SQL, а затем выполнять их динамически во время выполнения:

• BULK FETCH предложение
• BULK EXECUTE IMMEDIATE предложение
• FORALL предложение
• COLLECT INTO выражение
• RETURNING INTO выражение
• %BULK_ROWCOUNT атрибут курсора

Использование динамического SQL с Bulk SQL

Массовое (множественное) связывание позволяет Oracle связать переменную в операторе SQL с коллекцией значений.
Тип коллекции может быть любым типом коллекции Oracle/PLSQL (index-by table, nested table или varray).
Элементы коллекции должны иметь тип данных SQL, такой как CHAR, DATE или NUMBER.
Три оператора поддерживают динамическое массовое связывание: EXECUTE IMMEDIATE, FETCH и FORALL.

EXECUTE IMMEDIATE

• Вы можете использовать предложение BULK COLLECT INTO с оператором EXECUTE IMMEDIATE, чтобы хранить значения из каждого столбца набора результатов запроса в отдельной коллекции.
• Вы можете использовать предложение RETURNING BULK COLLECT INTO с оператором EXECUTE IMMEDIATE, чтобы сохранить результаты оператора INSERT, UPDATE или DELETE в наборе коллекций.

FETCH

• Вы можете использовать предложение BULK COLLECT INTO с оператором FETCH для хранения значений из каждого столбца курсора в отдельной коллекции.

FORALL

• Вы можете совместить EXECUTE IMMEDIATE инструкцию с RETURNING BULK COLLECT INTO внутри инструкции FORALL. Вы можете хранить результаты всех операторов INSERT, UPDATE или DELETE в наборе коллекций.
• Вы можете передать индексированные элементы коллекции в инструкцию EXECUTE IMMEDIATE через предложение USING.
• Вы не можете объединить индексированные элементы непосредственно в строковый аргумент для EXECUTE IMMEDIATE; например, вы не можете создать коллекцию имен таблиц и написать оператор FORALL, где каждая итерация применяется к другой таблице.

Примеры динамический SQL с предложением BULK COLLECT INTO

Вы можете связать определенные переменные в динамическом запросе, используя предложение BULK COLLECT INTO. Как показано в следующем примере, вы можете использовать это предложение в массовом выражении FETCH или в массовом выражении EXECUTE IMMEDIATE:

Пример динамический SQL с предложением RETURNING BULK COLLECT INTO

Только операторы INSERT, UPDATE и DELETE могут иметь выходные переменные связывания. Вы массово связываете их в EXECUTE IMMEDIATE с предложением RETURNING BULK COLLECT INTO.
Например:

Пример динамический SQL внутри оператора FORALL

Чтобы связать входные переменные в операторе SQL, вы можете использовать оператор FORALL и предложение USING, как показано ниже. Оператор SQL не может быть запросом.
Например:

Рекомендации по динамическому SQL

В этом разделе показано, как в полной мере использовать преимущества динамического SQL и как избежать некоторых распространенных ошибок.

Когда использовать или пропустить точку с запятой с помощью динамического SQL

При построении одного оператора SQL в строке не ставьте точку с запятой в конце.
При создании анонимного блока PL/SQL добавьте точку с запятой в конце каждого оператора PL/SQL и в конце анонимного блока.
Например:

Повышение производительности динамического SQL с помощью переменных связывания

Когда код ваших операторов INSERT, UPDATE, DELETE, и SELECT находится непосредственно в PL/SQL, PL/SQL превращает переменные в переменные связывания автоматически, чтобы операторы эффективно работали с SQL. Когда вы создаете такие операторы в динамическом SQL, вам нужно указать переменные связывания самостоятельно, чтобы получить ту же производительность.

В приведенном ниже примере Oracle открывает отдельный курсор для каждого отдельного значения emp_id. Это может привести к конфликту ресурсов и снижению производительности, поскольку каждый оператор анализируется и кэшируется.

Вы можете улучшить производительность, используя переменную связывания, которая позволяет Oracle повторно использовать один и тот же курсор для разных значений emp_id:

Передача имен объектов схемы в качестве параметров

Предположим, вам нужна процедура, которая принимает имя любой таблицы базы данных, а затем удаляет эту таблицу из вашей схемы. Вы должны создать строку с оператором, который включает имена объектов, а затем использовать EXECUTE IMMEDIATE для выполнения оператора:

Используйте конкатенацию для построения строки, а не пытайтесь передать имя таблицы как переменную связывания через предложение USING.

Использование дублирующих заполнителей с динамическим SQL

Заполнители в динамическом операторе SQL связаны с аргументами связывания в предложении USING по позиции, а не по имени. Если вы укажете последовательность заполнителей, например :a, :a, :b, :b, вы должны включить в предложение USING четыре элемента. Например, учитывая динамическую строку:

тот факт, что имя x повторяется, не имеет значения. Вы можете кодировать соответствующее предложение USING с четырьмя различными переменными связывания:

Если динамический оператор представляет PL/SQL блок, то правила для дублирующих заполнителей различны. Каждый уникальный заполнитель отображается на один элемент в предложении USING. Если один и тот же заполнитель появляется два или более раз, все ссылки на это имя соответствуют одному аргументу связывания в предложении USING.
В следующем примере все ссылки на заполнитель x связаны с первым аргументом связывания a, а второй уникальный заполнитель y связан со вторым аргументом связывания b.
Например:

Использование атрибутов курсора с динамическим SQL

SQL атрибуты курсора %FOUND, %ISOPEN, %NOTFOUND и %ROWCOUNT работают в динамическом SQL при выдаче INSERT, UPDATE, DELETE или однорядного предложения SELECT:

Аналогично, при добавлении к имени переменной курсора, атрибуты курсора возвращают информацию о выполнении многострочного запроса:

Передача NULL в динамический SQL

Литерал NULL не допускается в предложении USING. Чтобы обойти это ограничение, замените ключевое слово NULL неинициализированной переменной:

Использование связей базы данных с динамическим SQL

Подпрограммы PL/SQL могут выполнять динамические операторы SQL, которые используют ссылки на базы данных для ссылки на объекты в удаленных базах данных:

Цели удаленных вызовов процедур (RPC) могут содержать динамические операторы SQL. Например, предположим, что следующая автономная функция, которая возвращает количество строк в таблице, находится в базе данных Чикаго:

Из анонимного блока вы можете вызвать функцию удаленно, как показано ниже:

Использование прав Invoker с динамическим SQL

Динамический SQL позволяет писать процедуры управления схемами, которые можно централизовать в одной схеме, а также вызывать из других схем и работать с объектами в этих схемах.

Например, эта процедура может удалить любой объект базы данных:

Допустим, эта процедура является частью схемы HR. Без этого условия AUTHID процедура всегда удаляла бы объекты в схеме HR, независимо от того, кто ее вызывает. Даже если вы передадите полное имя объекта, эта процедура не будет иметь прав для внесения изменений в другие схемы.

AUTHID оператор поднимает оба этих ограничения. Он позволяет процедуре запускаться с привилегиями пользователя, который ее вызывает, и делает неквалифицированные ссылки на объекты в схеме этого пользователя.

Как избежать тупиков с помощью динамического SQL

В некоторых ситуациях выполнение оператора определения данных SQL приводит к тупику. Например, приведенная ниже процедура вызывает взаимоблокировку, поскольку она пытается удалить саму себя. Для того чтобы избежать тупиков, никогда не пытайтесь выполнить команды ALTER или DROP подпрограммы или пакета в то время как вы все еще используете его.

I’m getting a syntax error when trying to create table as with a decode, without the double quotes around «Y»  it will stop at the first encounter of Y. Any suggestions on how to get this to work ….

here is the error:

 1  declare
  2  l_cnt                  number(9) := 0;
  3  l_str                  varchar2(100);
  4  l_sql_stmt  varchar2(300);
  5  l_drop_stmt  varchar2(200);

  6  begin
  7  — create proj_cred
  8     dbms_output.put_line(sysdate || ‘ Create proj_cred table’);
  9   l_sql_stmt:= ‘create table proj_cred as ‘||
 10    ‘select proj.cust_code, proj_code, ‘||
 11    ‘decode(proj.cred_code, 10, «Y», 20, «Y», 30, «Y», 90, «Y», «N») cred_code ‘||
 12    ‘from proj, cuco ‘||
 13    ‘where proj.cred_code is not null ‘||
 14    ‘and   proj.cust_code = cuco.cust_code ‘||
 15    ‘and   proj.cred_code != cuco.cred_code ‘||
 16    ‘order by 1’;

 17   execute immediate l_sql_stmt;
 18  —
 19* end;
SQL> /
06/12/2009 10:01:43 Create proj_cred table
declare
*
ERROR at line 1:
ORA-00904: «N»: invalid identifier
ORA-06512: at line 17

--whenever sqlerror exit failure
set linesize 1000
set verify off
set pagesize 0
set trimspool on
set serveroutput ON size 1000000 format WRAPPED
set timing on
alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss';
-----------------------------------------------------------------------------
define spool_fldr = e:oraclescriptslogs
define spool_nm = load_cust
-----------------------------------------------------------------------------
spool &spool_fldr&spool_nm..log
-----------------------------------------------------------------------------
declare
l_cnt                  number(9) := 0;
l_str                  varchar2(100);
l_sql_stmt		varchar2(300);
l_drop_stmt		varchar2(200);
 
begin
-- create proj_cred
 
   dbms_output.put_line(sysdate || ' Create proj_cred table');
 
   l_sql_stmt:= 'create table proj_cred as '||
		'select proj.cust_code, proj_code, '||
		'decode(proj.cred_code, 10, "Y", 20, "Y", 30, "Y", 90, "Y", '"N"') cred_code '||
		'from proj, cuco '||
		'where proj.cred_code is not null '||
		'and   proj.cust_code = cuco.cust_code '||
		'and   proj.cred_code != cuco.cred_code '||
		'order by 1';
 
	execute immediate l_sql_stmt;
 
--
 
end;
/
spool off;
--exit;

Open in new window

ORA-01031: Insufficient Privileges means that the current user did not use the right privilege to process the SQL statement.

Since this error can be seen almost in every kind of SQL statement, sometimes you would never know what privilege you lack. So I do my best to collect cases for you.

There’re several error patterns of ORA-01031 in this post. You may click whichever situation you encountered.

1. Select (Query)
2. Create Table
3. Create Index
4. Create View
5. Create Synonym
6. Insert, Update and Delete
7. Alter Table (Add Constraint)
8. Alter Table (Other’s Table)
9. Alter User
10. Analyze Table
11. Password Change
12. EXECUTE IMMEDIATE
13. DGMGRL
14. Alter Pluggable Database Close
15. PDB Clone
16. GRANT System Privilege

Select (Query)

Tried to select other’s table, we got ORA-01031: insufficient privileges.

SQL> show user
USER is "HR"
SQL> select distinct gender from oe.customers;
select distinct gender from oe.customers
                               *
ERROR at line 1:
ORA-01031: insufficient privileges

Theoretically, if we can’t see other’s table, we got ORA-00942: table or view does not exist. But the error message indicates us that we don’t use the right privilege to do it. Why? We’d better do some tests.

The first test is that, can we describe the table’s definition?

SQL> desc oe.customers;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
...
 GENDER                                             VARCHAR2(1)
...

Yes, we can see its metadata, but not data.

So what object privileges we have now? Let’s check them by a privileged user.

SQL> show user
USER is "SYSTEM"
SQL> select privilege from dba_tab_privs where owner = 'OE' and table_name = 'CUSTOMERS' and grantee = 'HR' order by 1;

PRIVILEGE
----------------------------------------
DELETE
INSERT
UPDATE

OK, we can INSERT, UPDATE and DELETE, but no SELECT privilege. This is really weird.

Solution to ORA-01031

To solve insufficient privilege in querying, we should grant SELECT privilege to the user.

SQL> grant select on oe.customers to hr;

Grant succeeded.

Then we query the table again.

SQL> select distinct gender from oe.customers;

G
-
M
F

OK, the problem is solved.

Create Table

Let’s see what will happen if the new user wants to create a table.

C:UsersAdministrator>sqlplus thomas/thomas@orcl
...
SQL> create table test1 (id number, e_id number);
create table test1 (id number, e_id number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

Immediately, ORA-01031: insufficient privileges shows up, which tells the user who doesn’t have the right privilege to do that.

Solution to ORA-01031

The solution is simple, just grant CREATE TABLE to user, a schema-based privilege or CREATE ANY TABLE, a system-wide privilege.

SQL> conn / as sysdba
Connected.
SQL> grant create table to thomas;

Grant succeeded.

Then tell him to try it again.

SQL> create table test1 (id number, e_id number);

Table created.

If you use EXECUTE IMMEDIATE to run CREATE TABLE in a stored procedure, you may check ORA-01031 in EXECUTE IMMEDIATE section in this post.

Create Index

In the above section, we have granted CREATE TABLE to the new user, which naturally enables it to CREATE INDEX in his own schema.

Please note that, CREATE INDEX is not a valid privilege, but CREATE ANY INDEX is.

Let’s see an example and then we guess what privilege we need.

Suppose the new user wants to create an index for SH.CUSTOMERS in his own schema, so we grant SELECT on that table (object privilege) by instinct.

SQL> grant select on sh.customers to thomas;

Grant succeeded.

Then it tries to create an index on that table.

SQL> create index customer_id_gen_idx on sh.customers (cust_id, cust_gender);
create index oe.customer_id_gen_idx on sh.customers (cust_id, cust_gender)
                                          *
ERROR at line 1:
ORA-01031: insufficient privileges

Solution to ORA-01031

This is because SELECT on that table is not enough, you should additionally grant INDEX on that table to user, which is an object privilege.

SQL> grant index on sh.customers to thomas;

Grant succeeded.

Try again.

SQL> create index customer_id_gen_idx on sh.customers (cust_id, cust_gender);

Index created.

Even though the case is possible in reality, we seldom create index for other user’s table in our schema.

Create View

If you have read the above section, then you have known that you have to grant CREATE VIEW to the user in order to solve ORA-01031.

SQL> grant create view to thomas;

Grant succeeded.

If the user still got ORA-01031 after granting CREATE VIEW to him, it must be a deeper problem. That’s why this section is little longer.

Inherit Privilege from Role

Some privileges inherit from role do not work in some situation, especially when accessing intermediate kinds of object, like views or store procedures.

Here is a case that can reproduce the error.

We grant role RESOURCE to the user. Then we grant the system privilege SELECT ANY TABLE to the role RESOURCE.

SQL> grant resource to thomas;

Grant succeeded.

SQL> grant select any table to resource;

Grant succeeded.

So we can expect that the user inherits the system privilege from RESOURCE. That is, it can select any other’s table.

Let’s do the first test. Use it to select other user’s table SH.SALE.

SQL> select count(*) from sh.sales;

  COUNT(*)
----------
    918843

Good, it acts as we expect, although the user has not any object privilege on SH.SALE.

Let’s do the second test. We use it to create a view which is based on other user’s table SH.SALE.

SQL> create view sh_sales_v as select * from sh.sales;
create view sh_sales_v as select * from sh.sales
                                           *
ERROR at line 1:
ORA-01031: insufficient privileges

What happened? the user has CREATE VIEW and inherit SELECT ANY TABLE from RESOURCE, it should have no problem.

The result implies that the role’s privileges does not reach underlying objects through intermediate objects like views.

Solution to ORA-01031

The solution to this problem is to grant SELECT on the table to user directly.

First, grant the object privilege explicitly to resolve the problem.

SQL> grant select on sh.sales to thomas;

Grant succeeded.

Then tell Thomas to create view again.

SQL> create view sh_sales_v as select * from sh.sales;

View created.

Now, it’s no problem.

Please notice that, if you create a synonym on SH.SALES, it will succeed whether the explicit object privilege is granted directly or not.

Create Synonym

Tried to create a private synonym, but it failed with ORA-01031.

SQL> show user
USER is "HR"
SQL> create synonym customers for oe.customers;
create synonym customers for oe.customers;
*
ERROR at line 1:
ORA-01031: insufficient privileges

To solve the problem, just simply grant CREATE SYNONYM to the user.

SQL> grant create synonym to hr;

Grant succeeded.

Then create it again.

SQL> create synonym customers for oe.customers;

Synonym created.

Problem solved.

Insert, Update and Delete

You may have the right to select other’s table.

SQL> conn sh/sh
Connected.
SQL> select * from hr.t1;

        ID
----------
         1
         2
         3

But you may not have the right to perform some Data Manipulation Language (DML) operations on the table. For example, INSERT INTO some data like this:

SQL> insert into hr.t1 values (4);
insert into hr.t1 values (4)
               *
ERROR at line 1:
ORA-01031: insufficient privileges

This is because you lack INSERT, UPDATE or DELETE privilege to modify on that table which is usually owned by others.

Solution to ORA-01031

Clearly, the right privilege is INSERT, UPDATE or DELETE at object-level. You may ask for DBA or the object owner to grant the privilege to you.

Grant DML

We may grant individual privileges to the user.

SQL> conn hr/hr
Connected.
SQL> grant insert,update,delete on hr.t1 to sh;

Grant succeeded.

As we can see, the grantor grants 3 object privileges INSERT, UPDATE and DELETE on the table to the grantee at a time.

Grant All

In some cases, you may consider to grant all possible object privileges to user, for example:

SQL> grant all on hr.t1 to sh;

Grant succeeded.

In the statement, ALL is a keyword which means all possible privileges on specified object. For a table, it naturally includes not only SELECT, but also INSERT, UPDATE and DELETE.

As a result, we can insert some rows.

SQL> conn sh/sh
Connected.
SQL> insert into hr.t1 values (4);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from hr.t1;

        ID
----------
         1
         2
         3
         4

That is to say, not only SELECT, but also INSERT, UPDATE or DELETE privilege you should have to manipulate tables owned by other users.

Alter Table (ADD CONSTRAINT)

In the above section, we have granted CREATE TABLE to the user. Implicitly, he also has the right to ALTER TABLE on schema-level. So the cause of ORA-01031 in ALTER TABLE is not so obvious as we thought.

Please note that, ALTER TABLE is not a privilege, but ALTER ANY TABLE is. That’s why there’s no such GRANT ALTER TABLE TO statement.

Let’s see an example. User Thomas wants to add a constraint so as to make a reference to another user’s data, so we grant SELECT on that table to the user by instinct.

SQL> grant select on hr.employees to thomas;

Then we make the reference.

SQL> alter table test1 add constraint fk_eid foreign key (e_id) references hr.employees (employee_id);
alter table test1 add constraint fk_eid foreign key (e_id) references hr.employees (employee_id)
                                                                         *
ERROR at line 1:
ORA-01031: insufficient privileges

We got ORA-01031.

Solution to ORA-01031

The right privilege to reference other’s data is not SELECT, it’s REFERENCES.

We should grant REFERENCES on the table to user either by HR or privileged users.

SQL> grant references on hr.employees to thomas;

Grant succeeded.

Now Thomas can finish his job.

SQL> alter table test1 add constraint fk_eid foreign key (e_id) references hr.employees (employee_id);

Table altered.

Alter Table (Other’s Table)

If you were trying to alter other user’s table without a proper privilege, you may get ORA-01031 like this:

SQL> show user
USER is "HR"
SQL> alter table sh.customers add (col varchar2(10));
alter table sh.customers add (col varchar2(10))
*
ERROR at line 1:
ORA-01031: insufficient privileges

Although you have some other object privilege like SELECT or UPDATE on the table, you still cannot add a column for other user’s table.

Solution

The right privilege to alter other’s table is not UPDATE, it’s ALTER. The single word, the single word can be an object privilege.

SQL> grant alter on sh.customers to hr;

Grant succeeded.

Then do it again.

SQL> alter table sh.customers add (col varchar2(10));

Table altered.

System privilege ALTER ANY TABLE shall also work, but it’s unnecessary for most cases.

Alter User

Tried to add some quota on tablespace to itself, but it lacks of some privileges.

SQL> show user
USER is "HR"
SQL> alter user hr quota unlimited on users;
alter user hr quota unlimited on users
*
ERROR at line 1:
ORA-01031: insufficient privileges

Solution

The right privilege in this case is ALTER USER.

SQL> show user
USER is "SYSTEM"
SQL> grant alter user to hr;

Grant succeeded.

Then we do it again.

SQL> show user
USER is "HR"
SQL> alter user hr quota unlimited on users;

User altered.

SQL> alter user oe account lock;

User altered.

As you can see, with ALTER USER privilege, the user not only can grant some quota to itself, but also can change other’s status.

Analyze Table

We saw ORA-01031 when we tried to analyze other user’s table.

SQL> show user
USER is "HR"
SQL> analyze table sh.customers validate structure;
analyze table sh.customers validate structure
                 *
ERROR at line 1:
ORA-01031: insufficient privileges

Solution

To enable an user to analyze and gather statistics, the correct privilege is ANALYZE ANY.

SQL> show user
USER is "SYS"
SQL> grant analyze any to hr;

Grant succeeded.

Then analyze the table again.

SQL> analyze table sh.customers validate structure;

Table analyzed.

We solved it.

Password Change

Tried to change other’s password by SQL*Plus command password, but the user has inadequate privileges.

SQL> show user
USER is "HR"
SQL> password oe
Changing password for oe
New password:
Retype new password:
ERROR:
ORA-01031: insufficient privileges

Password unchanged

Since password command is actually an ALTER USER statement, the correct privilege to change other’s password is ALTER USER.

SQL> grant alter user to hr;

Grant succeeded.

Then do it again.

Let’s see how I reproduce ORA-01031 for statements using EXECUTE IMMEDIATE by the following example.

Inherit Privilege from Role

In the above section, I have granted role RESOURCE to THOMAS. Now I grant CREATE ANY DIRECTORY and DROP ANY DIRECTORY to the role RESOURCE.

SQL> grant create any directory, drop any directory to resource;

Grant succeeded.

So we can expect that the user can also do such operations by inheriting all privileges from RESOURCE.

Things look fine when we use the user to create or drop directories.

SQL> create directory tmp_path as '/u02/tmp';

Directory created.

SQL> drop directory tmp_path;

Directory dropped.

SQL> create directory tmp_path as '/u02/tmp';

Directory created

Now, Thomas would like to create directories in stored procedures which is also called named PL/SQL blocks or programming units.

First of all, DBA have to grant CREATE PROCEDURE to him before Thomas doing anything.

SQL> grant create procedure to thomas;

Grant succeeded.

Then Thomas create a procedure like this:

SQL> create or replace procedure drop_create_tmp_dir is
begin
execute immediate 'drop directory tmp_path';
execute immediate 'create or replace directory tmp_path as ''/u02/tmp''';
end drop_create_tmp_dir;
/
  2    3    4    5    6
Procedure created.

It seems no problem. But when we execute the stored procedure (named PL/SQL), we got ORA-01031 at line 3.

SQL> exec drop_create_tmp_dir;
BEGIN drop_create_tmp_dir; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "THOMAS.DROP_CREATE_TMP_DIR", line 3
ORA-06512: at line 1

Rationale

This is because the system privileges inherit from role cannot be used in named stored procedures with definer’s right.

Solutions to ORA-01031

Now we have several options, the first one is to grant all necessary privilege to the user directly, the second one is to use invoker’s right, and the last one is to use anonymous PL/SQL blocks.

1. Directly Granting to User

The user should directly get the system privilege from DBA, not inherit from role.

SQL> grant create any directory, drop any directory to thomas;

Grant succeeded.

Back to THOMAS, we can execute it again.

SQL> exec drop_create_tmp_dir;

PL/SQL procedure successfully completed.

The better thing is that we don’t have to recompile the procedure.

2. Use Invoker’s Right

Another way to solve ORA-01031 for statements in EXECUTE IMMEDIATE is to use invoker’s right to define the procedure.

Let’s revert the granting by SYS.

SQL> revoke create any directory, drop any directory from thomas;

Revoke succeeded.

Then we created the procedure with AUTHID CURRENT_USER clause.

SQL> create or replace procedure drop_create_tmp_dir authid current_user is
begin
execute immediate 'drop directory tmp_path';
execute immediate 'create or replace directory tmp_path as ''/u02/tmp''';
end drop_create_tmp_dir;
/
  2    3    4    5    6
Procedure created.

Try to execute the procedure by the user.

SQL> exec drop_create_tmp_dir;

PL/SQL procedure successfully completed.

By invoker’s right, we can use role’s privileges.

3. Anonymous PL/SQL Block

What we mean in the above is that role privileges cannot penetrate NAMED stored procedures. That is to say, you can use role privileges in anonymous PL/SQL blocks. For instance, we can rewrite the stored procedure to an anonymous PL/SQL as this:

begin
execute immediate 'drop directory tmp_path';
execute immediate 'create or replace directory tmp_path as ''/u02/tmp''';
end;
/

You can save and use it as a normal SQL script file.

For the same reason, CREATE TABLE in EXECUTE IMMEDIATE can also throw ORA-01031.

DGMGRL

DGMGRL allows user to query the status of all nodes involved through the local authentication without problem, but it might fail to switchover to a standby database or convert to a snapshot standby.

DGMGRL Switchover

Let’s see a switchover in 11g, it will fail when you connect DGMGRL with local authentication.

[oracle@primary01 ~]$ dgmgrl /
...
DGMGRL> switchover to standb
Performing switchover NOW, please wait...
Operation requires shutdown of instance "primdb2" on database "primdb"
Shutting down instance "primdb2"...
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the SWITCHOVER command:
        shut down instance "primdb2" of database "primdb"

DGMGRL>

But if you connect DGMGRL with the database password, the switchover will succeed.

[oracle@primary01 ~]$ dgmgrl sys/password@primdb1
...
DGMGRL> switchover to standb
Performing switchover NOW, please wait...
Operation requires shutdown of instance "primdb2" on database "primdb"
Shutting down instance "primdb2"...
Database closed.
Database dismounted.
ORACLE instance shut down.
New primary database "standb" is opening...
Operation requires shutdown of instance "primdb1" on database "primdb"
Shutting down instance "primdb1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "primdb1" on database "primdb"
Starting instance "primdb1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "standb"
DGMGRL>

DGMGRL Convert

Same error happened in a conversion.

[oracle@primary01 ~]$ dgmgrl /
...
DGMGRL> CONVERT DATABASE standb TO SNAPSHOT STANDBY;
Converting database "standb" to a Snapshot Standby database, please wait...
Operation requires shutdown of instance "standb2" on database "standb"
Shutting down instance "standb2"...
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the CONVERT command:
        shut down instance "standb2" of database "standb"

Solution to ORA-01031

You must use the database authentication to convert a standby database.

[oracle@primary01 ~]$ dgmgrl sys/password@primdb1
...
DGMGRL> CONVERT DATABASE standb TO SNAPSHOT STANDBY;
Converting database "standb" to a Snapshot Standby database, please wait...
Operation requires shutdown of instance "standb2" on database "standb"
Shutting down instance "standb2"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Continuing to convert database "standb" ...
Database "standb" converted successfully
...

For the same reason, the broker is unable to startup the new standby database during a switchover and throws ORA-01017 due to OS authentication.

Alter Pluggable Database Close

We saw an error when we tried to close a pluggable database (PDB) by a normal user.

SQL> conn hr/password@orclpdb
Connected.
SQL> show user
USER is "HR"
SQL> alter pluggable database close;
alter pluggable database close
*
ERROR at line 1:
ORA-01031: insufficient privileges

To solve ORA-01031, we take two steps to make the user be able to close a PDB.

1. Grant SYSDBA to the User

Please make sure that you login as SYS and are in the right container.

SQL> show user
USER is "SYS"
SQL> show con_namev
CON_NAME
------------------------------
ORCLPDB

Then we grant SYSDBA privilege to the user.

SQL> grant sysdba to hr;

Grant succeeded.

2. Connect as SYSDBA

The user should use SYSDBA privilege to connect to the PDB.

SQL> conn hr/password@orclpdb as sysdba
Connected.
SQL> alter pluggable database close;

Pluggable database altered.

Actually, the normal user has become a SYS which of course has the ability to maintain database.

PDB Clone

When you try to clone a remote PDB via a database link, you may see ORA-17628 and ORA-01031 at that moment. I have talk about it in that post.

GRANT System Privilege

It failed with ORA-01031 when we tried to grant a system privilege to another user.

SQL> show user
USER is "HR"
SQL> grant create table to oe;
grant create table to oe
*
ERROR at line 1:
ORA-01031: insufficient privileges

To be able to grant any system privilege to any grantee, the grantor requires GRANT ANY PRIVILEGE privilege.

SQL> show user
USER is "SYSTEM"
SQL> grant grant any privilege to hr;

Grant succeeded.

That is to say, the insufficient privilege in this case is GRANT ANY PRIVILEGE.