-
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
-
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.
-
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))
/
-
