Re: DYNAMIC SQL PROBLEM - help

> It compiles all right because it doesn't realise before runtime what you
> are trying to do ...
> In fact, you cannot 'bind' a table name or column name.

I disagree. One of the main advantages of dynamic sql is that it CAN bind table names at run time. Case in point, this code snippet is from another script I wrote that works just fine:

DBMS_SQL.PARSE (cursor1,

                'INSERT INTO id_temp_table (old) SELECT id FROM '||table_name_in,

                DBMS_SQL.NATIVE); I am using concatenation here, but same idea. BTW, I tried this exact same syntax in my current proc, does not work. I believe the problem is with the INTO part of the statement: 'SELECT AVG(VSIZE(col_name)) INTO avg_row_len FROM '||table_name_in;

You can only do
> it with data (something you can store in a column). If you want to
> dynamically change the table name, you must do something like
> sql_stmt := 'select * from ' || table_name_in ;

Creating a string beforehand of the above statement does not work either. Again, I don't think it likes the "INTO".

> and then parse the 'hard-coded' statement - yes, a new cursor every
> time.

> You do not actually need any dynamic SQL for what you are trying to do.
> You can simply look in the DBMS_UTILITY (or is it DBMS_DDL? you'll find
> it in $ORACLE_HOME/rdbms/admin/dbmsutil.sql anyway) for the suitable
> function to ANALYZE your table, and then do a SELECT on USER_TABLES (or
> DBA_TABLES, it depends) to get the average row length there ...

Yes, I agree with you here. But I was trying to avoid having to analyze my tables.

Regards,
Kathleen

>
> --
> Regards,
>
> Stéphane Faroult
> Oriole Corporation
> ------------------------------------------------------------------
> http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs
> ------------------------------------------------------------------
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Apr 12 1999 - 16:13:49 CDT