Hi Tom,
1st of all regrads and Thank you very much for your answers to questions put by aspirants.
I often look into your articles to find related answer whenever I need an unusual requirement.
My requirement is to call a procedure/function with ROWTYPE parameters from FORALL statement, though to my knowledge only DML statements can be executed from FORALL.
I have used 3 different tables t1, tt_1 and tt_11 where t1 and tt_1 have the same structure.
create table tt_11 ( id number);
create or replace function pr_tt ( p1 t1%ROWTYPE)
return number
is
begin
INSERT INTO tt_1 VALUES p1;
dbms_output.put_line(' rows '||sql%rowcount);
return 1;
end pr_tt;
declare
type t1_typ is table of t1%rowtype;
t1_tb t1_typ := t1_typ();
begin
select * BULK COLLECT INTO t1_tb from t1;
FORALL rec IN 1..t1_tb.COUNT
pr_tt (t1_tb(rec)); -- this is my main requirement
-- since only DML statement can be executed from FORALL i tried the below
FORALL rec IN 1..t1_tb.COUNT
insert into tt_11 values pr_tt (t1_tb(rec));
end;
for the 1st FORALL I ended up resulting
ORA-06550: line 7, column 5:
PLS-00103: Encountered the symbol "PR_TT" when expecting one of the following:
. ( * @ % & - + / at mod remainder rem select update with
<an exponent (**)> delete insert || execute multiset save
merge
for the 2nd FORALL I ended up resulting
ORA-06550: line 7, column 29:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 7, column 5:
PL/SQL: SQL Statement ignored
Thanks in advance for you help.
Regards
Rashmi.
No, this will not work, array processing would not help you here anyway.
For a plsql function to be callable from SQL - it cannot have any side effects (modifications). You would have to use an autonomous transaction in this case since ou insert - but that would be the kiss of death, if the forall statement needed to rollback (or in fact, the transaction needed to roll back) none of the committed autonomous transactions would roll back - you'd have a data integrity MESS. So, this function cannot (should not) be called from SQL.
To call a plsql function from SQL requires a context switch between SQL and PLSQL. You would be switching from PLSQL to call SQL in bulk, but the SQL in bulk would be row by row switching from SQL back to PLSQL which would then context switch back to SQL to do its insert. You would gain nothing really.
Please call the function in a loop - saving the return value in an array and then you can forall i insert the returned values.
Since it appears you just want to call the procedure over and over - just call the procedure(function) over and over.
FORALL rec IN 1..t1_tb.COUNT
pr_tt (t1_tb(rec)); -- this is my main requirement
should just be:
for i in 1 .. t1_tb.count
loop
result := pr_tt( .... );
end loop;
OR, rewrite your function to take the table as input.
forall just wouldn't make sense to call a function repeatedly, it would do nothing for you since it is just plsql to plsql. Just call the function repeatedly or more optimally - refactor the function to accept the table type as input.
Or - most optimally - get rid of the procedural code altogether and just do everything you need in a single, big sql statement ;)