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  |
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 #485736 is a reply to message #485734] |
Thu, 09 December 2010 12:34  |
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 |
 |
Barbara Boehmer
Messages: 9106
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 |
|
|
|
|
|
Goto Forum:
Current Time: Fri Jun 20 13:56:27 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
