Re: Help with dynamic insert

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 Received on Mon Feb 03 2003 - 21:23:32 CST