About dynamic sql

[danny yang]

My question is that i write a procedure to get table_name, number of rows, number of blocks from some schema and i want to using dynamic sql to insert these information into a table which name is table_sum and its description like below list

table_name varchar2(10),
number_of_rows number(6),
number_of_blocks number(8),
modify_date date

i try the below instruction , but it not work ??

cursor_id:=dbms_sql.open_cursor ;
instruction :=' insert into table_sum values (' || table_name || ',' || num_rows || ',' || num_blocks || ',' || to_char(sysdate,'yyyy/mm/dd') || ')' ;
commit;
dbms_sql.parse(cursor_id,instruction,dbms_sql.v7);
rtn := dbms_sql.execute(cursor_id);
dbms_sql.close_cursor (cursor_id);

-- rem table_name, num_rows, num_blocks is parameter

please kindly help , thanx

[SCORBET]

Hello Danny,

I think you need to add some quotes around text field:

example:
instruction :=' insert into table_sum values (' || table_name || ',' || num_rows || ',' || num_blocks || ',' || to_char(sysdate,'yyyy/mm/dd') || ')' ;

Becomes:

instruction :=' insert into table_sum values (''' || table_name || ''',' || num_rows || ',' || num_blocks || ',''' || to_char(sysdate,'yyyy/mm/dd') || ''')' ;

In fact you need to give varchar2 parameter and if you forget '' characters (double ') Oracle thinks that it is a column name!!!!

I don't understand why you don't have any error. Are you sure you don't catch exception?

Have a nice day,
Best Regards,
Stephane.

[bkanti]

You can do it in a simpler way thru SQL*Plus
Step 1. Generate a script to analyze the tables in the schema
spool ana_table.sql
select 'analyze table '||tname||' estimate statistics sample 20 percent'
from tab
/
spool off

Step 2.
Analyze the tables in the schema
@ana_table.sql

Step 3.
Create table result_tab
(table_name,
num_rows,
num_blocks,
last_upd_date)
as
(SELECT table_name,
num_rows,
blocks,
SYSDATE
FROM dba_tables
WHERE owner = (schema_name))
/

[danny yang]

thanx