Home » SQL & PL/SQL » SQL & PL/SQL » ORA-14551: cannot perform a DML operation inside a query (SQL Developer(2.1.1.64)/Oracle 10gR2/Windows XP-SP3)
ORA-14551: cannot perform a DML operation inside a query [message #485732] Thu, 09 December 2010 12:20 Go to next message
oruorainfo
Messages: 46
Registered: May 2010
Member
Can anyone help please. Attempted to execute the Procs below with
Select OTMP_TCIS_RS.Get_UserInfo('EN') from dual; but i get the following error:
ORA-14551: cannot perform a DML operation inside a query.
The intention of the code is to perform an insert into my table based on passing in values via an object into Stored Procedure Apply_Users_Update

Many Thanks!

Package Definition
create or replace
PACKAGE OTMP_TCIS_RS AS

   --1 PROCEDURE Get_UserInfo
PROCEDURE Get_UserInfo(
    o_OutCode OUT INT,
    i_language IN VARCHAR2);
    
FUNCTION Get_UserInfo(    
     i_language IN VARCHAR2)
      RETURN NUMBER;
    
PROCEDURE Apply_Users_Update(
		o_OutCode   OUT INT,
		i_NewUser   IN SYSTEM_USERS);
      
END OTMP_TCIS_RS;


Package body
create or replace
PACKAGE BODY OTMP_TCIS_RS
AS
	
  --1 PROCEDURE Get_UserInfo Select OTMP_TCIS_RS.Get_UserInfo('EN') from dual;
PROCEDURE Get_UserInfo(
		o_OutCode OUT INT,
    i_language IN VARCHAR2)
    AS
    
	v_ProcedureName           VARCHAR2(255) :='OTMP_TCIS_RS' || '.Get_UserInfo';
	v_ErrorCode               NUMBER;
	v_ErrorMessage            VARCHAR2(255);
	v_Parameters              VARCHAR2(4000) := SUBSTR('<parameters>' || 
                                               '</parameters>', 1, 4000);
	v_SQLStatement            VARCHAR(5000) ;
	exception_test            EXCEPTION     ;
	exception_test_msg        VARCHAR2(1000):= 'Exception
        test';
        exception_validation      EXCEPTION;
        exception_validation_msg  VARCHAR2(1000):= 'Parameter
        validation failed';
	v_userid                  NUMBER;
        v_addresscode             NUMBER;
  BEGIN
	o_OutCode := 0;
	--uncomment ONLY for Exception Testing
	--RAISE exception_test;
    
    v_userid := 1338841;
    v_addresscode := 2218708;
    
    DECLARE 
        NewUser SYSTEM_USERS;
    
    BEGIN
        NewUser := SYSTEM_USERS(v_userid,249,Null,'TESTINGUSER','TEST','USER',SYSDATE,'ADMIN','NOT_SUSPENDED',
        SYSDATE,'uid=TAdmin,ou=Directory Administrators,o=tcis','false','HTML','EN','TESTER',
'{SHA}9CB0Tn9DTc4rg4ByXNTslTRRF2k=',
        'Testers Name',46,231,57,236,v_addresscode,null,null,null,null,null,null,null,null,null,null,null, null);  
        
        OTMP_TCIS_RS.Apply_Users_Update(o_OutCode, NewUser) ; 
        
        DBMS_OUTPUT.PUT_LINE('TEST');
    END;  
    
    EXCEPTION
      WHEN exception_test THEN
        o_OutCode := -20099;
        OTMP_UTIL.InsertErrorMessage(v_ProcedureName, o_OutCode, 
        exception_test_msg,	v_SQLStatement);
      WHEN exception_validation THEN
        o_OutCode := -20100;
        OTMP_UTIL.InsertErrorMessage(v_ProcedureName, o_OutCode,
        exception_validation_msg, v_Parameters);
      WHEN OTHERS THEN
        o_OutCode      := SQLCODE;
        v_ErrorCode    := SQLCODE;
        v_ErrorMessage := SUBSTR(SQLERRM, 1, 255);
        OTMP_UTIL.InsertErrorMessage(v_ProcedureName,
        v_ErrorCode, v_ErrorMessage, v_Parameters);

    

END Get_UserInfo;


FUNCTION Get_UserInfo(
		i_Language    VARCHAR2
    ) RETURN NUMBER AS
    
    v_result  NUMBER :=0;
    v_OutCode NUMBER;
    
BEGIN
	
  OTMP_TCIS_RS.Get_UserInfo(v_OutCode, i_Language);
	
  IF (v_OutCode IS NULL OR v_OutCode != 0) THEN
    v_result := -100;
    DBMS_OUTPUT.PUT_LINE('Earth Station....we have a
          problem');
  END IF;
  
  RETURN v_result;

END Get_UserInfo;



PROCEDURE Apply_Users_Update(
		o_OutCode   OUT INT,
		i_NewUser   IN SYSTEM_USERS
                            )
    IS
    
	v_ProcedureName VARCHAR2(255) :='OTMP_TCIS_RS' || '.Apply_Users_Update';
	v_ErrorCode     NUMBER;
	v_ErrorMessage  VARCHAR2(255);
        v_Parameters        VARCHAR2(4000) := SUBSTR 
        ('<parameters>' || 
                                             '</parameters>', 1,
        4000);
	v_SQLStatement            VARCHAR(5000) ;
	exception_test            EXCEPTION     ;
	exception_test_msg        VARCHAR2(1000):= 'Exception
        test';
        exception_validation      EXCEPTION;
        exception_validation_msg  VARCHAR2(1000):= 'Parameter   
        validation failed';
	
        v_USERID		    NUMBER;
        v_CODELOOKUPROLE	    NUMBER;
        v_ORGID			    NUMBER;
        v_USERNAME		    VARCHAR2(100 CHAR);
        v_GIVENNAME		    VARCHAR2(255 CHAR);
        v_SURNAME		    VARCHAR2(255 CHAR);
        v_MODIFYTIMESTAMP           DATE;
        v_MODIFIERSNAME		    VARCHAR2(255 CHAR);
        v_SUSPENDSTATUS		    VARCHAR2(20 CHAR);
        v_CREATETIMESTAMP	    DATE;
        v_CREATORSNAME		    VARCHAR2(255 CHAR);
        v_RECEIVEEMAIL	            CHAR(5 CHAR);
        v_EMAILFORMAT		    VARCHAR2(20 CHAR);
        v_PREFERREDLANGUAGE	    CHAR(2 CHAR);
        v_PASSWORDANSWER	    VARCHAR2(255 CHAR);
        v_USERPASSWORD	            VARCHAR2(255 CHAR);
        v_PASSWORDQUESTION          VARCHAR2(255 CHAR);
        v_LANGUAGETYPECODE          NUMBER;
        v_CODELOOKUPPREFIX          NUMBER;
        v_CODELOOKUPCOUNTRYCODE	    NUMBER;
        v_CODELOOKUPEMAILFORMAT     NUMBER;
        v_ADDRESSCODE	            NUMBER;
        v_USERIDREPLACEMENT	    NUMBER;
        v_DONOTCONTACTFG	    CHAR(1 CHAR);
        v_RETAINADDRESSFG	    CHAR(1 CHAR);
        v_CODELOOKUPGENDER          NUMBER;
        v_SEGMENTID	            NUMBER;
        v_AGERANGEID		    NUMBER;
        v_INCOMERANGEID	            NUMBER;
        v_EDULEVELID		    NUMBER;
        v_USESEGMENTHPIND	    CHAR(1 CHAR);
        v_REMEMBERMEIND	            CHAR(1 CHAR);
        v_LASTLOGINTS	            TIMESTAMP(6);
        v_REGBASISID	            NUMBER;  

BEGIN
	o_OutCode := 0;
	--uncomment ONLY for Exception Testing
	--RAISE exception_test;
    
  IF i_NewUser IS NULL THEN
      RAISE exception_validation;
  END IF;
  
    
  BEGIN  
      DBMS_OUTPUT.PUT_LINE(i_NewUser.SU_USERID||' '||i_NewUser.SU_CODELOOKUPROLE||' '||
i_NewUser.SU_USERNAME||' '||i_NewUser.SU_ORGID
        ||' '|| i_NewUser.SU_MODIFYTIMESTAMP);
      
        v_USERID		      :=        i_NewUser.SU_USERID;
        v_CODELOOKUPROLE 	      := 	i_NewUser.SU_CODELOOKUPROLE;
        v_ORGID			      :=	i_NewUser.SU_ORGID;
        v_USERNAME		      :=	i_NewUser.SU_USERNAME;
        v_GIVENNAME		      :=	i_NewUser.SU_GIVENNAME;
        v_SURNAME		      :=	i_NewUser.SU_SURNAME;
        v_MODIFYTIMESTAMP	      :=	i_NewUser.SU_MODIFYTIMESTAMP;
        v_MODIFIERSNAME		      :=	i_NewUser.SU_MODIFIERSNAME;
        v_SUSPENDSTATUS		      :=	i_NewUser.SU_SUSPENDSTATUS;
        v_CREATETIMESTAMP	      :=	i_NewUser.SU_CREATETIMESTAMP;
        v_CREATORSNAME		      :=	i_NewUser.SU_CREATORSNAME;
        v_RECEIVEEMAIL		      :=	i_NewUser.SU_RECEIVEEMAIL;
        v_EMAILFORMAT		      :=	i_NewUser.SU_EMAILFORMAT;
        v_PREFERREDLANGUAGE	      :=	i_NewUser.SU_PREFERREDLANGUAGE;
        v_PASSWORDANSWER	      :=	i_NewUser.SU_PASSWORDANSWER;
        v_USERPASSWORD		      :=	i_NewUser.SU_USERPASSWORD;
        v_PASSWORDQUESTION	      :=	i_NewUser.SU_PASSWORDQUESTION;
        v_LANGUAGETYPECODE	      :=	i_NewUser.SU_LANGUAGETYPECODE;
        v_CODELOOKUPPREFIX	      :=	i_NewUser.SU_CODELOOKUPPREFIX;
        v_CODELOOKUPCOUNTRYCODE	      :=	i_NewUser.SU_CODELOOKUPCOUNTRYCODE;
        v_CODELOOKUPEMAILFORMAT	      :=	i_NewUser.SU_CODELOOKUPEMAILFORMAT;
        v_ADDRESSCODE		      :=	i_NewUser.SU_ADDRESSCODE;
        v_USERIDREPLACEMENT	      :=	i_NewUser.SU_USERIDREPLACEMENT;
        v_DONOTCONTACTFG	      :=	i_NewUser.SU_DONOTCONTACTFG;
        v_RETAINADDRESSFG	      :=	i_NewUser.SU_RETAINADDRESSFG;
        v_CODELOOKUPGENDER	      :=	i_NewUser.SU_CODELOOKUPGENDER;
        v_SEGMENTID		      :=	i_NewUser.SU_SEGMENTID;
        v_AGERANGEID		      :=	i_NewUser.SU_AGERANGEID;
        v_INCOMERANGEID		      :=	i_NewUser.SU_INCOMERANGEID;
        v_EDULEVELID		      :=	i_NewUser.SU_EDULEVELID;
        v_USESEGMENTHPIND	      :=	i_NewUser.SU_USESEGMENTHPIND;
        v_REMEMBERMEIND		      :=	i_NewUser.SU_REMEMBERMEIND;
        v_LASTLOGINTS		      :=	i_NewUser.SU_LASTLOGINTS;
        v_REGBASISID		      :=	i_NewUser.SU_REGBASISID;
            
        INSERT INTO ROBIN_USERS(    USERID,
                                    CODELOOKUPROLE,
                                    ORGID,
                                    USERNAME,
                                    GIVENNAME,
                                    SURNAME,
                                    MODIFYTIMESTAMP,
                                    MODIFIERSNAME,
                                    SUSPENDSTATUS,
                                    CREATETIMESTAMP,
                                    CREATORSNAME,
                                    RECEIVEEMAIL,
                                    EMAILFORMAT,
                                    PREFERREDLANGUAGE,
                                    PASSWORDANSWER,
                                    USERPASSWORD,
                                    PASSWORDQUESTION,
                                    LANGUAGETYPECODE,
                                    CODELOOKUPPREFIX,
                                    CODELOOKUPCOUNTRYCODE,
                                    CODELOOKUPEMAILFORMAT,
                                    ADDRESSCODE,
                                    USERIDREPLACEMENT,
                                    DONOTCONTACTFG,
                                    RETAINADDRESSFG,
                                    CODELOOKUPGENDER,
                                    SEGMENTID,
                                    AGERANGEID,
                                    INCOMERANGEID,
                                    EDULEVELID,
                                    USESEGMENTHPIND,
                                    REMEMBERMEIND,
                                    LASTLOGINTS,
                                    REGBASISID)           
                            VALUES ( 
                                    v_USERID,
                                    v_CODELOOKUPROLE,
                                    v_ORGID,
                                    v_USERNAME,
                                    v_GIVENNAME,
                                    v_SURNAME,
                                    v_MODIFYTIMESTAMP,
                                    v_MODIFIERSNAME,
                                    v_SUSPENDSTATUS,
                                    v_CREATETIMESTAMP,
                                    v_CREATORSNAME,
                                    v_RECEIVEEMAIL,
                                    v_EMAILFORMAT,
                                    v_PREFERREDLANGUAGE,
                                    v_PASSWORDANSWER,
                                    v_USERPASSWORD,
                                    v_PASSWORDQUESTION,
                                    v_LANGUAGETYPECODE,
                                    v_CODELOOKUPPREFIX,
                                    v_CODELOOKUPCOUNTRYCODE,
                                    v_CODELOOKUPEMAILFORMAT,
                                    v_ADDRESSCODE,
                                    v_USERIDREPLACEMENT,
                                    v_DONOTCONTACTFG,
                                    v_RETAINADDRESSFG,
                                    v_CODELOOKUPGENDER,
                                    v_SEGMENTID,
                                    v_AGERANGEID,
                                    v_INCOMERANGEID,
                                    v_EDULEVELID,
                                    v_USESEGMENTHPIND,
                                    v_REMEMBERMEIND,
                                    v_LASTLOGINTS,
                                    v_REGBASISID);    
          COMMIT;
    
    EXCEPTION
      WHEN exception_test THEN
        o_OutCode := -20099;
        OTMP_UTIL.InsertErrorMessage(v_ProcedureName, o_OutCode, exception_test_msg,	v_SQLStatement);
      WHEN exception_validation THEN
        o_OutCode := -20100;
        OTMP_UTIL.InsertErrorMessage(v_ProcedureName, o_OutCode, exception_validation_msg, v_Parameters);
      WHEN OTHERS THEN
        o_OutCode      := SQLCODE;
        v_ErrorCode    := SQLCODE;
        v_ErrorMessage := SUBSTR(SQLERRM, 1, 255);
        OTMP_UTIL.InsertErrorMessage(v_ProcedureName, v_ErrorCode, v_ErrorMessage, v_Parameters);
  
  END;    

END Apply_Users_Update;

END OTMP_TCIS_RS;

[Updated on: Thu, 09 December 2010 12:52] by Moderator

Report message to a moderator

Re: ORA-14551: cannot perform a DML operation inside a query [message #485733 is a reply to message #485732] Thu, 09 December 2010 12:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
var n number;
exec :n := OTMP_TCIS_RS.Get_UserInfo('EN') 
print n


Regards
Michel
Re: ORA-14551: cannot perform a DML operation inside a query [message #485734 is a reply to message #485732] Thu, 09 December 2010 12:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
4551, 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.
Re: ORA-14551: cannot perform a DML operation inside a query [message #485736 is a reply to message #485734] Thu, 09 December 2010 12:34 Go to previous messageGo to next message
oruorainfo
Messages: 46
Registered: May 2010
Member
Thank you BlackSwan, i too am aware of this post from the internet. I did quite a bit of searching on the net before posting. As you can tell from the code, it is a simple INSERT statement, performed on it's own. Autonomous transactions are specialized transactions, not to be used in every situation. It is not required in this instance. Thanks.
Re: ORA-14551: cannot perform a DML operation inside a query [message #485737 is a reply to message #485734] Thu, 09 December 2010 12:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
Senior Member
When you run this query:

Select OTMP_TCIS_RS.Get_UserInfo('EN') from dual;

the get_userinfo function calls the get_userinfo procedure, which runs apply_users_update, which does an insert, which is DML (data manipulation language). So, you cannot use a select statement that contains anything that ultimately does an insert or update or delete. BlackSwan has provided the full error text and Michel Cadot has provided the proper way to execute the function, executing pl/sql from sql*plus to avoid the problem, instead of using it in a sql query. Another pl/sql method would be:

set serveroutput on
declare
  n number;
begin
  n := OTMP_TCIS_RS.Get_UserInfo('EN');
  dbms_output.put_line (n);
end;
/

[Updated on: Thu, 09 December 2010 12:40]

Report message to a moderator

Re: ORA-14551: cannot perform a DML operation inside a query [message #485739 is a reply to message #485737] Thu, 09 December 2010 12:51 Go to previous message
oruorainfo
Messages: 46
Registered: May 2010
Member
Thank you kindly Barbara for that explanation, "now", i truly understand what's going wrong.
Thank you too Michel for outlining the correct "exec" to use to execute it, that helps too, and Thanks again BlackSwan for the details on the error message.

Much appreciated, everyone! Have a great week!

Robin.
Previous Topic: Oracle Table - Finding the transaction logs
Next Topic: Help needed to create stored procedure (2 merged)
Goto Forum:
  


Current Time: Mon Jun 09 09:35:01 CDT 2025