Re: Help with dynamic insert

DA Morgan <damorgan_at_exesolutions.com> wrote in message news:<3E3F3234.41E2C6D7_at_exesolutions.com>...
> Ryan Gaffuri wrote:
>
> > Trying to help someone do this. Its not for the application. He is
> > trying to fix some crystal reports and wants a utility.
> >
> > Basically he is going to create a table or a view at runtime. The view
> > will be based on an existing table but will have no data. He then
> > wants to populate all the fields in that view with 8's. However, he
> > wants the 8's to be as long as the field length. Columns will either
> > be Number or Varchar.
> >
> > For example, you have a field length of 10. Add 88888888 and so on.
> >
> > Im having trouble with coming up with a simple insert statement.
> >
> > I can build v_fields from a loop and add commas, but cycling through
> > USER_TAB_COLUMNS
> >
> > EXECUTE IMMEDIATE 'INSERT INTO TABLE '||
> > '('||v_fields||')'||
> > 'VALUES'( <now what do I here to make it dynamic?>)'
>
> The reason you are having a problem with it is that (A) it is a terrible
> thing to do and (B) it is impossible.
>
> A.
> You should never create tables or views on-the-fly in Oracle. It is bad
> design, greatly inhibits perofance, and destroys scalability. And there is
> no excuse for doing it .... ever. Look up global temporary tables.
>
> B.
> What you need to do is access the information present in user_tab_columns.
> And to do this in the context of a SQL statement is not possible. This
> needs to be done with a stored procedure or anonymous block.
>
> That said ... this seems more academic exercise than real requirement. Of
> what possible use, in a work environment, is filling every field with
> eights?
>
> Daniel Morgan

im contracting and some guy i work with wanted it done. you know how it goes when you contract...

i think i figured out how to do it. Ill build a comma delimited file for my columns from user tab columns. Then for the values part of the insert, ill use data_length to run a loop for the number of '8's i want to do.

thanks daniel. your always very helpful. Received on Tue Feb 04 2003 - 06:13:16 CST