Cannot perform a dml operation inside a query ошибка

I am using a Data Analysis tool and the requirement I have was to accept a value from the user, pass that as a parameter and store it in a table. Pretty straighforward so I sat to write this

create or replace
procedure complex(datainput in VARCHAR2)
is
begin
insert into dumtab values (datainput);
end complex;

I executed this in SQL Developer using the following statement

begin
complex('SomeValue');  
end;

It worked fine, and the value was inserted into the table. However, the above statements are not supported in the Data Analysis tool, so I resorted to use a function instead. The following is the code of the function, it compiles.

create or replace
function supercomplex(datainput in VARCHAR2)
return varchar2
is
begin
insert into dumtab values (datainput);
return 'done';
end supercomplex;

Once again I tried executing it in SQL Developer, but I got cannot perform a DML operation inside a query upon executing the following code

select supercomplex('somevalue') from dual;

My question is
— I need a statement that can run the mentioned function in SQL Developer or
— A function that can perform what I am looking for which can be executed by the select statement.
— If it is not possible to do what I’m asking, I would like a reason so I can inform my manager as I am very new (like a week old?) to PL/SQL so I am not aware of the rules and syntaxes.

P.S. How I wish this was C++ or even Java :(

EDIT

I need to run the function on SQL Developer because before running it in DMine (which is the tool) in order to test if it is valid or not. Anything invalid in SQL is also invalid in DMine, but not the other way around.

Thanks for the help, I understood the situation and as to why it is illegal/not recommended

Источник

I cannot convince why I can’t add DML operation inside Oracle Function especially inside cursor loop. I feel Oracle don’t support DML operation inside cursor loop.

How can I do If I need to insert into table inside cursor loop? Create new store procedure inside it or something else?

Error Message : cannot perform DML operation inside a query

Here is my function,

CREATE OR REPLACE FUNCTION TEST_FUNC(U_ID IN VARCHAR2)
RETURN VARCHAR2
IS
  V_MESSAGE VARCHAR2(30);
  CURSOR C_PERSON (V_ID VARCHAR2) IS
         SELECT NAME_UPPER
         FROM TBL_PERSON
         WHERE NAME_UPPER = V_ID;                  
BEGIN
   FOR C_PERSON_CURSOR IN C_PERSON(U_ID) 
   LOOP
       INSERT INTO TMP_PERSON(NAME) VALUES (C_PERSON_CURSOR.NAME_UPPER);
   END LOOP;

   RETURN V_MESSAGE;

EXCEPTION
WHEN OTHERS THEN
    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

asked Nov 4, 2010 at 8:54

PPShein's user avatar

You can use DML inside a PL/SQL function — no problem. However, the function can only be called from PL/SQL, not from SQL — i.e. it can be called like this:

declare
   l_message varchar2(30);
begin
   l_message := test_func('123');
end;

… but not like this:

select test_func(empno) from emp;

That leads to the error message you posted.

Many people (including me) don’t like functions that have «side effects» like this, but that is a matter of best practice and standards, not a technical issue.

answered Nov 4, 2010 at 9:17

Tony Andrews's user avatar

Tony AndrewsTony Andrews

129k21 gold badges220 silver badges259 bronze badges

1

You can perform DML operations inside an Oracle PL/SQL function and, although this is generally not a good practice, call it from SQL. The function has to be marked with a pragma AUTONOMOUS_TRANSACTION and the transaction has to be committed or rolled back before exiting the function (see AUTONOMOUS_TRANSACTION Pragma).

You should be aware that this kind of function called from SQL can dramatically degrade your queries performances. I recommend you use it only for audit purposes.

Here is an example script starting from your function:

CREATE TABLE TBL_PERSON (NAME_UPPER VARCHAR2(30));
CREATE TABLE TMP_PERSON (NAME VARCHAR2(30));

INSERT INTO TBL_PERSON (NAME_UPPER) VALUES ('KING');

CREATE OR REPLACE FUNCTION TEST_FUNC(U_ID IN VARCHAR2)
RETURN VARCHAR2
IS
  PRAGMA AUTONOMOUS_TRANSACTION; -- Needed to be called from SQL

  V_MESSAGE VARCHAR2(2000);
  CURSOR C_PERSON (V_ID VARCHAR2) IS
         SELECT NAME_UPPER
         FROM TBL_PERSON
         WHERE NAME_UPPER = V_ID;                  
BEGIN
   FOR C_PERSON_CURSOR IN C_PERSON(U_ID) 
   LOOP
       INSERT INTO TMP_PERSON(NAME) VALUES (C_PERSON_CURSOR.NAME_UPPER);

       V_MESSAGE := SQL%ROWCOUNT
          || ' Person record successfully inserted into TMP_PERSON table';
   END LOOP;

   COMMIT; -- The current autonomous transaction need to be commited
           -- before exiting the function.

   RETURN V_MESSAGE;

EXCEPTION
WHEN OTHERS THEN
    ROLLBACK;
    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/

PROMPT Call the TEST_FUNC function and insert a new record into TMP_PERSON table
SELECT TEST_FUNC('KING') FROM DUAL;

PROMPT Content of the TMP_PERSON table
COL NAME FOR A30
SELECT * FROM TMP_PERSON;

When running the previous script we get the following output:

Table created.

Table created.

1 row created.

Function created.

Calling the TEST_FUNC function and insert a new record into TMP_PERSON table

TEST_FUNC('KING')
------------------------------------------------------------
1 Person record successfully inserted into TMP_PERSON table

Content of the TMP_PERSON table

NAME
------------------------------
KING

answered Nov 4, 2010 at 10:38

Bruno Gautier's user avatar

1

Источник

May 10, 2021

I got ” ORA-14551: cannot perform a DML operation inside a query ”  error in Oracle database.

ORA-14551: cannot perform a DML operation inside a query

Details of error are as follows.

ORA-14551 cannot perform a DML operation inside a query

Cause: DML operation like insert, update, delete or select-for-update cannot be performed 
inside a query or under a PDML slave.

Action: Ensure that the offending DML operation is not performed or use an autonomous
 transaction to perform the DML operation within the query or PDML slave.

cannot perform a DML operation inside a query

This ORA-14551 error is related with the DML operation like insert, update, delete or select-for-update cannot be performed inside a query or under a PDML slave.

Ensure that the offending DML operation is not performed or use an autonomous transaction to perform the DML operation within the query or PDML slave.

You should use the PRAGMA AUTONOMOUS_TRANSACTION;

You should give commit explicitly inside the function;

OR if you got this error when you run function as follows.

SQL> select testfunction('test') from dual;

Then run it as follows.

SQL> var myvar NUMBER;
SQL> call testfunction('test') into :myvar;  

Call completed.

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 1,737 views last month,  1 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Источник

i_am_kisly

4 / 4 / 0

Регистрация: 26.08.2014

Сообщений: 110

1

09.06.2017, 11:43. Показов 14273. Ответов 5

Метки oracle 11g (Все метки)

Студворк — интернет-сервис помощи студентам

Всем привет. Столкнулся с ошибкой
ORA-14551: невозможно выполнение операции DML внутри запроса
У меня есть таблица TABLE1 c COLUMN1. В COLUMN1 записано некоторое число.

Есть функция которая инкрементирует данные в COLUMN1

SQL 
1
2
3
4
5
6

CREATE OR REPLACE FUNCTION FUNCTION1 RETURN NUMBER AS 
BEGIN
    UPDATE TABLE1 SET COLUMN1 = COLUMN1 + 1;
    COMMIT;
    RETURN NULL;
END FUNCTION1;

Я вызываю ее в селекте и получаю ошибку

SQL 
1

SELECT FUNCTION1() FROM DUAL

Код

ORA-14551: невозможно выполнение операции DML внутри запроса 
ORA-06512: на  "FUNCTION1", line 3
14551. 00000 -  "cannot perform a DML operation inside a query "
*Cause:    DML operation like insert, update, delete or select-for-update
           cannot be performed inside a query or under a PDML slave.
*Action:   Ensure that the offending DML operation is not performed or
           use an autonomous transaction to perform the DML operation within
           the query or PDML slave.

Раньше не сталкивался. Куда бежать, на что обратить внимание ?



0

Модератор

4206 / 3046 / 581

Регистрация: 21.01.2011

Сообщений: 13,190

09.06.2017, 12:10

2

Цитата
Сообщение от i_am_kisly

Куда бежать, на что обратить внимание

Чего же здесь непонятного? SELECT — это получение данных, а не их изменение. Вызывай функцию в PL/SQL блоке.
В сообщении еще сказано, что можно использовать автономную транзакцию, но с этим средством надо обращаться осторожно, поэтому в данном случае я бы не советовал.
Кстати, в оф. доке перечислены ограничения на функции, которые предполагается вызывать в SELECT.



0

4 / 4 / 0

Регистрация: 26.08.2014

Сообщений: 110

09.06.2017, 12:21

 [ТС]

3

Я хочу сделать что-то вроде «select trigger», чтобы иметь счетчик который будет инкрементироваться когда пользователь делает селект.



0

Модератор

4206 / 3046 / 581

Регистрация: 21.01.2011

Сообщений: 13,190

09.06.2017, 12:35

4

Цитата
Сообщение от i_am_kisly
Посмотреть сообщение

который будет инкрементироваться когда пользователь делает селект

Зачем, если не секрет?



0

4 / 4 / 0

Регистрация: 26.08.2014

Сообщений: 110

09.06.2017, 13:07

 [ТС]

5

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



0

Модератор

4206 / 3046 / 581

Регистрация: 21.01.2011

Сообщений: 13,190

09.06.2017, 13:42

6

Цитата
Сообщение от i_am_kisly
Посмотреть сообщение

получать статистику

Штатное средство для этого — включение аудита в БД. Можно также посмотреть в сторону RLS (Row Level Security) — пакет dbms_rls



0
Источник

General

Cloud Infrastructure

  • Whether you’re building something giant, or just aren’t quite sure where to start, come in here for broad discussions about all things Cloud!

  • Foundational cloud building blocks, including compute instances, storage and backup, virtual networking and security lists, and operating system — all your general cloud discussions.

  • Site-to-site VPN, FastConnect, WAF, Public DNS, Public IP Addressing, E-mail Delivery, and more.

  • Working with Containers, Kubernetes clusters (OKE), Kubernetes operators, the registry, artifacts, Serverless Functions, GraalVM, Verrazzano, Cluster API, Service Mesh, API Gateway, and related technologies.

  • Users, groups, policies, federation, compartments, authentication.

  • Cloud Guard, Security Advisor, Security Zones, Scanning, Vault, Bastion, Compliance & Audit, application performance monitoring, logging and analytics, events, operational insights, management agent, and related technologies.

  • AWS? Azure? GCP? OCI? Running your workloads across multiple vendor’s cloud environments.

  • Cost analysis, cost management, budgets, rewards programs, account & tenancy management, organizations & licenses

  • Running prepared Oracle and third-party applications

Data Management & Integration

  • All things Oracle Database, incuding Autonomous, DB Systems, Exadata, Data Safe, Multilingual Engine, Graph, Relational Duality, and more.

    221.5k Posts

    1.1m Comments

  • Free Oracle Database from release 23c onward.

  • Develop server-side Java applications that take advantage of the scalability and performance of Oracle Database.

  • All things MySQL and MySQL HeatWave, on Oracle Cloud or anywhere else it runs.

  • Flexible, non-relational database that can accommodate a wide variety of data models

  • The forum for 21c and prior versions of XE.

    8.0k Posts

    31.0k Comments

  • All about relational database languages, queries, and extensions.

    185.1k Posts

    1.0m Comments

  • SQL Developer, SQL Developer Data Modeler, SQLcl

    21.7k Posts

    74.7k Comments

  • A family of embedded key-value database libraries providing scalable high-performance data management services to applications.

    6.7k Posts

    22.0k Comments

  • All things specific to Oracle GoldenGate: GoldenGate for Oracle, GoldenGate for Non-Oracle, GoldenGate for BigData, GoldenGate Foundation Suites, GoldenGate Stream Analytics and OCI GoldenGate

    4.9k Posts

    19.0k Comments

  • All things specific to GG Free: Installation, licensing, any pipeline creation using the GG Free UI, relationship GG Free UI to MSA UI, etc.

  • All things about the Oracle SQL Developer Command Line (SQLcl)

  • Create, browse and edit logical, relational, physical, multi-dimensional, and data type models with a powerful graphical tool.

    4.0k Posts

    10.5k Comments

  • Discussions about the web version of SQL Developer.

  • Storing and managing structured data in your Oracle database

    3.2k Posts

    10.3k Comments

Development

  • APEX is Oracle’s strategic low-code application platform used by customers across every industry and geography.

    131.4k Posts

    475.0k Comments

  • Data Science, Data Labeling, models and model training, data science & machine learning development

  • Wide-ranging discussion on all things about developing software and implementing solutions on Oracle Cloud, including development environments.

  • In-depth discussion about using a your favorite frameworks and languages to build your apps on and with Oracle Cloud

  • Automating the development and delivery of your applications.

    65.2k Posts

    134.9k Comments

  • OCI API, CLI & SDK, plus automations like Ansible and Terraform

  • Create web and mobile apps directly from your browser in a visual environment.

  • General discussion of open source software, including Oracle’s open source projects.

  • Discussions about the Oracle Data Provider for .NET (ODP.NET)

    7.8k Posts

    20.9k Comments

  • Tools for connecting to and managing an Oracle Database or Oracle Autonomous Database within VS Code

  • Tools for connecting to and managing an Oracle Database or Oracle Autonomous Database within Visual Studio

  • Oracle Forms and Reports. Oracle Forms remains an widely used application development technology for PL/SQL based applications.

    71.3k Posts

    253.1k Comments

Java

  • The standard in community-driven enterprise software developed using the Java Community Process.

    168.8k Posts

    441.0k Comments

  • Accessing your databases from a Java client application using JDBC.

    32.7k Posts

    103.6k Comments

  • Develop highly functional, reliable, and portable applications for today’s most powerful embedded systems

    8.1k Posts

    24.2k Comments

  • 22.1k Posts

    74.7k Comments

  • Your favorite Java IDEs and Frameworks to help you create and deploy your Java applications.

    129.0k Posts

    408.0k Comments

  • Oracle’s implementation of the JVM

    13.8k Posts

    40.8k Comments

  • Writing applications in the Java programming language

    86.3k Posts

    466.1k Comments

  • Create and deploy portable code for desktop and server enbironments

    94.2k Posts

    274.7k Comments

  • Cryptography, public key infrastructure, secure communication, authentication, access control, and more.

    13.8k Posts

    37.3k Comments

  • Java User Groups (JUGs) are volunteer organizations that strive to distribute Java-related knowledge around the world. They provide a meeting place for Java users to get information, share resources and solutions, increase networking, expand Java Technology expertise, and above all, drink beer, eat pizza and have fun. The JUG Community is the meeting point for JUGs, helping promote the expansion of the worldwide Java Community

  • Just getting started? We’re here to help!

    65.0k Posts

    400.9k Comments

Communities

  • This is a category dedicated the community and collaboration amongst developer communities

  • If you’ve built something you are proud of, come share it with the community — for feedback or for fun!

Data and Analytics

  • Big Data Service & Big Data Appliance, Data Catalog, Data Integration, Data Flow

  • OCI AI Language, Speech, Vision, Anomaly Detection Services; Oracle Digital Assistant

  • Media Flow & Media Streams

  • A scalable and secure Oracle Cloud service that provides a full set of capabilities to explore and perform collaborative analytics.

Learning & Certification

  • Get hands-on with Oracle technologies at your own pace.

Oracle Applications

  • Deliver insurance solutions and digital customer experiences that build your customers’ confidence and loyalty with an insurance management system.

  • Formerly Oracle Policy Automation, decision automation software products for modeling and deploying business rules within the enterprise.

  • An integrated set of business applications for automating customer relationship management (CRM), enterprise resource planning (ERP) and supply chain management (SCM) processes.

    13.3k Posts

    27.7k Comments

  • A hybrid search-analytical database that organizes complex and varied data from disparate source systems into a flexible data model.

  • A full suite of integrated applications that can address your business needs for Human Capital Management (HCM) and Enterprise Resource Planning (ERP).

    10.8k Posts

    23.2k Comments

  • Customer relationship management system used to help companies connect and manage sales, marketing, and customer support.

  • Keeping track of your assets. Includes Oracle Content Management, formerly known as Oracle Content and Experience.

On-Premises Infrastructure

  • 133.0k Posts

    408.5k Comments

  • Application Development Software

    35.9k Posts

    104.7k Comments

  • 102.9k Posts

    367.1k Comments

  • 17.6k Posts

    49.9k Comments

  • 8.1k Posts

    16.5k Comments

  • 63.5k Posts

    165.9k Comments

  • 102.4k Posts

    238.6k Comments

  • 41.0k Posts

    114.9k Comments

International Languages

  • Local community discussions in the Chinese language.

    2.3k Posts

    15.9k Comments

  • Local community discussions in the German language.

  • Local community discussions in the Japanese language.

  • Local community discussions in the Portuguese language.

Источник

Возможно, вам также будет интересно:

  • Cannot open user default database login failed ошибка 4064
  • Cannot open file fsgame ltx сталкер чистое небо как исправить ошибку
  • Cannot open file fsgame ltx check your working folder чистое небо как исправить ошибку
  • Cannot modify a column which maps to a non key preserved table ошибка
  • Cannot load serious sam 3 ошибка

    • Понравилась статья? Поделить с друзьями:
    0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии