CODEWITHSUNDEEP
oracle-databasefunctionproceduredml
michelle.ann.diaz
michelle.ann.diaz

Reputation: 67

Execute a procedure inside a function (ORA-14551 error)

This question is related to my previous question.

I have a procedure(PROC_YEARLYACTIVELIST2) that will show all records that is active on a specified year.
It will delete the previous contents of TBLACTIVELISTYEARLY2 and insert the result from PROC_YEARLYACTIVELIST2.

I created a function that will execute TBLACTIVELISTYEARLY2, Select all records from TBLACTIVELISTYEARLY and put it into CURSOR C_IH, and return the table for Crystal Report.

Below is just a part of the code:


DECLARE
  CURSOR C_IH IS SELECT * FROM tblActiveListYearly2;

  ctr INT;
  i NUMBER;  
  currDeploymentComputer COL_TYPE_DEPLOYMENT_COMPUTER := COL_TYPE_DEPLOYMENT_COMPUTER NULL);
  R_IH C_IH%ROWTYPE;

 BEGIN
 PROC_YEARLYACTIVELIST2(in_year);

  OPEN C_IH;
   i := 0;
    LOOP (....)

I've tried to call the function as

SELECT GETDEPLOYMENT_COMPUTER('2012') from dual;

And has an ORA-14551 error

ORA-14551: cannot perform a DML operation inside a query 
ORA-06512: at "NPLS.PROC_YEARLYACTIVELIST2", line 12
ORA-06512: at "NPLS.GETDEPLOYMENT_COMPUTER", line 3

Searched for it and found that it is because of the conflict with INSERT, UPDATE or DELETE and DUAL.

Is there any other way to execute my procedure in a function that will return a table?

THANKS!

Upvotes: 2

Views: 5768

Answers (3)

michelle.ann.diaz
michelle.ann.diaz

Reputation: 67

This was so complicated.
For the reason that I want to get records from a table to crystal report, I put what I select to a table.

Now, I realized that I can place the logic from my procedure into the function, put it into a SYS_REFCURSOR, return it and call the function using this query:

SELECT * FROM TABLE(GETDEPLOYMENT_COMPUTER('2012'));

Thanks, anyways, for those who response and helped me. :)

Upvotes: 0

Ben
Ben

Reputation: 52863

No; for very good reasons you cannot perform DML in a SELECT statement.

You're changing the data in the database and Oracle needs a read-consistent view of the data, i.e. it needs to know that what you're selecting is not being changed by what you're selecting.

What you're doing sounds highly unnecessary; you've got a 3 step process:

  1. Delete some data from a table
  2. Insert new data into that table
  3. Select data from the table.

Why not simply select the data you need; it'll be a lot quicker? If you have to pre-process the data then you can have a procedure that does this asynchronously to the selecting of the data.

I don't know anything about Crystal Reports but you can also do this in a PL/SQL block.

declare
   l_getdeployment my_variable_type;
begin
   l_getdeployment := GETDEPLOYMENT_COMPUTER('2012');
end;
/

Upvotes: 2

igr
igr

Reputation: 3499

I think you need to separate DML changes and reporting part. Procedure that does data changes should be called outside of your report function call...

Upvotes: 1

Related Questions