Oracle : cannot perform a DML operation inside a query error-CodePudding

I have following query which works but in one part of it I want to call this function pbweb.adposter.MakeDeepLinkEx and get the value back as url1 but i get the following error:

EE in pbk26w60.GenerateDeepLink: ORA-14551: cannot perform a DML operation inside a query

I sort of understand why this is happening as the function must be trying to alter the db inside the select which is not permitted.

I get that I need to do something like :

declare
retvar varchar2(500);
begin
    retvar := pbweb.adposter.MakeDeepLinkEx('119686','1368256');
end;

which executes but I dont know where to go from here? I am not well versed in PLSQL and have been stuck with trying to fix this issue while the person who wrote all this is on holiday.

Is there any way to get this to return the value from MakeDeepLinkEx within the current query or without altering it to much? If i try add begin, end etc into the query it just breaks.

$sql = "SELECT a.ad_id,
                a.headline, a.pay_period_id,
                (select value from ad_fields where field_id='Jobtitle' and ad_id=a.ad_id) as job_title,
                (select value from ad_fields where field_id='Department' and ad_id=a.ad_id) as department,
                (select value from ad_fields where field_id='Job_Description' and ad_id=a.ad_id) as job_desc,
                (select value from ad_fields where field_id='Champney_Benefits' and ad_id=a.ad_id) as benefits,
                (select value from ad_fields where field_id='Champney_Salary' and ad_id=a.ad_id) as sal_desc,
                a.salary,
                a.salary_max,
              
                (select value from ad_fields where field_id='Location' and ad_id=a.ad_id) as location,
                a.postcode,
                a.email,
                :url || pbweb.pbk00w24.p_encode (
                        null,
                        null,
                        null, 
                        :acc_id , 
                        null, 
                        (select visibility_code from advertisement_visibilities where ad_id = a.ad_id and rownum = 1), 
                        a.ad_id, 
                        null, 
                        null, 
                        null, 
                        null
                ) || CHR(38) AS url,
                pbweb.adposter.MakeDeepLinkEx(a.user_id,a.ad_id) as url1
                from
                advertisements a
                JOIN advertisement_visibilities b ON a.ad_id=b.ad_id
                where a.acc_id = :acc_id AND a.status = 'P' AND start_dt < sysdate AND end_dt >= sysdate and b.visibility_code=:vis";


    $jobs = db_getAll($sql, array(':acc_id' => '202020', ':url' => ADVERT_URL, ':vis' => 'TJP'));

CodePudding user response：

Oracle works very hard to ensure that select queries are read-only. If you want to create a query that modifies data, you have to work exceptionally hard to bypass these safeguards. And be willing to put up with some pretty hacky solutions.

If you are going to do this, you absolutely must determine what MakeDeepLinkEx actually does and make sure that letting Oracle call this function with whatever parameters the optimizer thinks appropriate (including rows that are filtered out in the where clause) won't cause any problems to your system. You're asking Oracle to bypass some very important guardrails so it's up to you to make sure that the road is safe.

You can create a new function that uses an autonomous transaction

create or replace function MakeDeepLinkExAut( p_user_id integer,
                                              p_ad_id   integer )
  return varchar2
as
  l_retvar varchar2(500);
  pragma autonomous_transaction;
begin
  l_retvar := pbweb.adposter.MakeDeepLinkEx(p_user_id, p_ad_id);
  commit;
  return l_retvar;
end;

and then call this new function in your select statement.