Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    2

    Unanswered: sqlsrv2000 to 9i migr. temp table conversion?

    Hi,
    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.

    Thanks for any help,
    Gar

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Use global temporary tables. See 'create table' in the sql manual.

    Alan

  3. #3
    Join Date
    Aug 2003
    Posts
    2
    Thanks Alan,
    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.

    Regards,
    Gar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •