Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question, Sameer.

Asked: May 02, 2019 - 7:38 am UTC

Answered by: Chris Saxon - Last updated: May 02, 2019 - 10:01 am UTC

Category: PL/SQL - Version: 12c

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: Parsing freeform data in flat files

You Asked

Sir,

1) Can I perform DML (Insert, Update, Delete) also into a PL/SQL function.....?
2) Can I call a Trigger Explicitly.

and we said...

1. Yes. You may want to do this to return auto-generated primary key or other defaults to the client.

But this does mean you can't call the function from SQL.

create table t (
  c1 int generated as identity
);

create or replace function f
  return int as
  retval int;
begin

  insert into t 
  values ( default )
  returning c1 into retval;
  
  return retval;
end f;
/

select f from dual;

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

exec dbms_output.put_line ( 'val = ' || f );

val = 1

select * from t;

C1   
    1 


2. No

create or replace trigger trg
before insert on t
for each row
begin
  dbms_output.put_line ( 'fired' ); 
end;
/

exec trg;

BEGIN trg; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'TRG' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

exec chris.trg;

BEGIN chris.trg; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'CHRIS.TRG' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

insert into t values ( default );

fired

1 row created.


Is this answer out of date? If it is, please let us know via a Review

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.