You Asked
I have a object as follows:
create or replace type claims_only as object (fordnr varchar2(15),glaeubigernr number,fordergnr number);
and I have a a collection as:
create or replace type claims_only_collection as table of claims_only;
I have a table called forderung which has the 3 columns fordnr,glaeubigernr and fordergnr besides some other columns.
here is the table script
CREATE TABLE FORDERUNG (
GLAEUBIGERNR NUMBER NOT NULL,
FORDNR VARCHAR2 (15) NOT NULL,
FORDERGNR NUMBER NOT NULL,
MANDANTNR NUMBER,
VERTRAGSNR VARCHAR2 (15),
REFERENZNR VARCHAR2 (20),
ERLEDIGTMM NUMBER NOT NULL,
ERLEDIGTDATUM DATE,
ERLEDIGTGRUNDMM NUMBER,
SPARTE VARCHAR2 (4),
KATALOGNR NUMBER NOT NULL,
ANSPRVOMDATUM DATE,
ANSPRBISDATUM DATE,
ANSPRGRUNDMM NUMBER,
INKASSOMM VARCHAR2 (2),
ABTRETDATUM DATE,
ABTRETNR NUMBER,
GLWECHSELPARTID NUMBER,
GLWECHSELDATUM DATE,
VERBRKRGMM NUMBER NOT NULL,
VERTRAGSDATUM DATE,
EFFEKTIVZINSSATZ NUMBER (6,3),
VERTRSTREITGERMM NUMBER,
VERTRSTREITGERPLZ VARCHAR2 (6),
VERTRSTREITGERORT VARCHAR2 (31),
SONSTANSPR VARCHAR2 (174),
AUSNAHMEVORGGLSTMM NUMBER,
ZUSATZ VARCHAR2 (35),
VAAUSSCHLUSSMM NUMBER,
LEISTUNGSZUSATZ VARCHAR2 (35),
LEISTUNGSSTRASSE VARCHAR2 (35),
LEISTUNGSPLZ VARCHAR2 (6),
LEISTUNGSORT VARCHAR2 (28),
LEISTUNGSNATION VARCHAR2 (3),
WMM VARCHAR2 (3),
WMMUMSTELLDATUM DATE,
SOZIALGERICHTAVMM NUMBER,
UEBERNAHMEDATUM DATE,
EWBMETHODE NUMBER,
EWBMANUELLBETRAG NUMBER (19,7),
EWBMANUELLSATZ NUMBER (8,5),
EWBMETHODENTEXT VARCHAR2 (80),
EWBABSCHREIBMM NUMBER,
EWBERLEDIGTMM NUMBER,
EWBAENDSB NUMBER,
EWBAENDDATUM DATE,
SCHUFASTOPMM NUMBER,
STATUS VARCHAR2 (4),
STATUSVOMDATUM DATE,
STRITTIGMM NUMBER,
STRITTIGDATUM DATE,
EXTFORDNR VARCHAR2 (52),
EXTFORDERGNR NUMBER,
SBID NUMBER,
SBZUORDNUNGSMM NUMBER,
ABSCHREIBGRUNDMM_INK NUMBER,
ABSCHREIBGRUNDMM_MND NUMBER,
VERRECHNUNGMM NUMBER,
ABSCHREIBDATUM_INK DATE,
ABSCHREIBDATUM_MND DATE,
ABSCHREIBMM_INK NUMBER,
ABSCHREIBMM_MND NUMBER,
VEREGVERSIONNR NUMBER,
ERSTELLTVON NUMBER,
ERSTELLTAM DATE,
GEAENDERTVON NUMBER,
GEAENDERTAM DATE,
DLFLAG NUMBER (1),
TERFLAG NUMBER (1),
TERMODFLAG NUMBER (1),
TERCANFLAG NUMBER (1),
NEEDYCLAIMFLAG NUMBER (1),
SETTLEMENTFLAG NUMBER (1),
INDIVIDUALFLAG NUMBER (1)
);
One more column of interest which needs to be updated is sbid.
I need to update this sbid for all the records
thers lot of business logic for which i call 3-4 different procedures and get all the FORDNR,GLAEUBIGERNR,FORDERGNR in a collection and now i want to use forall while updating.
Assume i get the collection in variable l_claim_tranfer
where l_claim_tranfer is of type claims_only_collection
forall i in 1 .. l_claims_trans_actual.claim_transfer.count
update forderung
set sbid = 10
where fordnr = l_claim_tranfer(i).fordnr
and glaeubigernr = l_claim_tranfer(i).glaeubigernr
and fordergnr = l_claim_tranfer(i).fordergnr;
I get the following error:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
Pleaase help me Tom its very urgent
and Tom said...
two choices
1) use set operations instead of FORALL:
ops$tkyte@ORA9IR2> declare
2 l_claim_tranfer claims_only_collection := claims_only_collection();
3 begin
4 update forderung
5 set sbid = 10
6 where (fordnr, glaeubigernr , fordergnr ) in
7 ( select t.fordnr, t.glaeubigernr, t.fordergnr
8 from TABLE(cast(l_claim_tranfer as claims_only_collection)) t );
9 end;
10 /
PL/SQL procedure successfully completed.
see also:
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3779680732446#15740265481549 <code>
2) use arrays (tables) of SCALARS -- three collections, instead of one collection of records.
Rating
(15 ratings)
Is this answer out of date? If it is, please let us know via a Comment