Re: Newbie Question

Dulacka wrote:
> I am a seasoned cobol programmer who is new to Oracle. My cobol
> experience is in the Unisys arena. ie I have been using a DMSII
> database (uses pointers).
>
> I have a basic understanding of Oracle and cursors but I am having
> difficulty understanding how to write a batch update program.
>
> For example:
>
> I have a table that contains 10 rows (table A.) Each row in table A
> will be added to another table (table B) and the original row (in
> tabel A) will be updated with todays date. If there are any errors
> adding the row to table B the transaction is aborted and the program
> continues with the next row in table A.
>
> Basically the program is reading a table of recurring invoices that
> are posted to the actual invoice table.
>
> If I use the same logic with Oracle the cursor is closed and the
> program ends abnormally.
>
> If I was to write this program from scratch how would I do it? Read
> rows from table A to a temp file and then read each record from the
> temp?
>
> Any help with this would be appreciated.
>
> Also, if any one could direct me to sample programs it would be
> greatly appreciated.
>
> Thanks in advance

Apart from the procedural section (which does resemble the way Cobol handles files/records), there's also SQL. And that works on sets. Your program could be as simple as: insert /* +append */ into b (select * from a); update a set a_date = sysdate;
commit;

This is a very crude and simple way, but demonstrates the power of sql. Better coding principle would be: insert into b ([comma separated list of columns affected]) select [corresponding columns from a] from a; In that case, any columns added to table b or a would not cause a disruption; the first example would, as '*' corresponds to all columns. BTW, the /* +append */ thing is called 'hint'. It will speed up inserts for large tables - not neccesary for the execution of the command, nor does it affect the logic. Morale: use the right tool: if you need PL/SQL, use it, if you don't, don't.

Hth, grtz, Frank Received on Sat Nov 30 2002 - 10:05:28 CST