Unanswered: sqlsrv2000 to 9i migr. temp table conversion?
I am working on the migration of a sqlsrvr2000 database to 9i. The stored proc conversion effort includes 75 relatively complex procs that use temporary table processing to handle intermediate resultsets.
I am trying to determine how much standard SQL DML I can use against PL/SQL tables. According to OMWB documentation this approach is limited.
I know you can use UPDATE table SET ROW = v_rec WHERE ...
but when I try using PL/SQL tables containing a single column record type in joins I get a full compile but get an end of file error when running the proc.
Has anyone got any good hints as to the best way to migrate temporary tables?
Where simple I'll use ordinary SQL, joins and views. For complex processing there's a rule against using any form of temporary table so I may need to use cursor processing, pl/sql tables etc.
The use of any form of temporary table is disallowed by our standards.
We will migrate to standard Oracle SQL where possible. Use of the WITH clause will help with reducing the need for storing subqueries in temporary tables. If this doesn't work we will use cursor processing.