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.