Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Sameer.

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

Last updated: May 02, 2019 - 10:01 am UTC

Version: 12c

Viewed 1000+ times

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 Chris 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 Comment

More to Explore

PL/SQL demos

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

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library