Re: Help Again, Something about Dynamic PL/SQL

From: Joe Long <joe.long.nospam_at_cticallcenter.com>
Date: Mon, 20 Dec 1999 10:20:00 -0600
Message-ID: <385E5730.4E14269F@cticallcenter.com>

I am still trying to figure out how to do that efficiently. Here is a couple of things I have done or am working on that may help give you some ideas:

In one case I successfully archived several tables using dynamic sql with a statement like:

  'INSERT into ' || dest_table ||
  ' SELECT * '
  'FROM ' || source_table ||
  ' WHERE .... '

When I tried to do the same thing by defining arrays for each column, fetching and returning them, then inserting them into the dest table, I ran into a weird problem with the date fields that I never did solve.

Currently I am working on a procedure that uses dynamic sql to return multiple rows from a table. First I defined a type RECORD that includes all of the columns in the table, then I defined a type INDEX_BY_TABLE that is a table of type RECORD indexed by binary integer. I and going to populate the INDEX_BY_TABLE with a cursor and then return it to the client app. I am still working on it so I can't tell you yet how well it works.

Hope some of this helps.

Joe Long

lkw wrote:
>
> the other thing is that...although return an array may help, but actually
> what i want is to return the entire row, which is the cursor i think...so
> izzit possible? (bcoz for array i need to specify the array size right? but
> different tables will have different numbers of coloumns, so i have to make
> it return cursor....)
Received on Mon Dec 20 1999 - 10:20:00 CST