Re: referring to a temp table in a procedure?

L. Tseng wrote:
>
> I don't know if this is a trivial question but I am hitting
> the wall now. Any help will be very appreciated!!!
>
> What I want to do is
>
> In a strored procedure,
>
> create a temp table;
>
> insert some rows to the temp table;
>
> create a cursor on the temp table;
>
> Loop thru the temp table with the DBMS_OUTPUT;
>
> It seems to me DBMS_SQL is the only way to go but...
>
> Can what I want be achieved? how?
>
> Thank you very much.
>
> Leslie
Let me suggest to you that you only need dbms_sql to create and drop the tables:
1. You know in advance what the table structure will look like. You can have your
temp table in the development environment at the point where you gen the form.
2. This would allow you to use normal cursor declare and processing in forms makeing
the coding easier:

declare

   declare cursors etc against temp table begin

   check for existence of temp table. If it exists, use dbms_sql to drop it.

   use dbms_sql to create temp table.
   load temp table
   cursor loop through temp table doing whatever you need    use dbms_sql to drop the temp table.
end;

We use this sort of thing all of the time. By having it in the dev environ, the
forms gen without problems. By dropping and creating the table on the fly, we
don't take up the space in production and yet are able to accomplish what we
need.

Loyal Received on Mon Nov 18 1996 - 00:00:00 CET