oracle11g - Call Function within User-Defined Function in SQL -

oracle11g - Call Function within User-Defined Function in SQL -

i want include next function within of user-defined function in oracle.

dbms_stats.gather_table_stats(schema_in,table_in)

schema_in , table_in arguments user-defined function. however, next error.

ora-14552: cannot perform ddl, commit or rollback within query or dml

how can resolve this? below sql script.

create or replace function get_columns (schema_in in varchar2, name_in in varchar2) homecoming varchar2 l_text varchar2(32767) := null; begin dbms_stats.gather_table_stats(schema_in,name_in); cur_rec in (select column_name user_tab_columns table_name = name_in , num_nulls = 0) loop l_text := l_text || ',' || cur_rec.column_name; end loop; return(ltrim(l_text,',')); end;

gather_table_stats procedure, not function. , procedure includes transaction command logic (presumably, commit @ least). cannot, therefore, phone call in function called sql. phone call function pl/sql rather sql,

declare l_text varchar2(4000); begin l_text := get_columns( <<schema>>, <<table>> ); end;

i would, however, very, dubious approach you're taking.

first, dbms_stats gathers statistics used optimizer. using statistics in other contexts dangerous. dbms_stats calls involve level of indeterminism-- you're gathering info sample of rows , extrapolating. appropriate giving optimizer info can justice things how many rows table scan return. may not appropriate if you're trying differentiate between column never null , 1 null. samples may grab null value, others may not. may seem work correctly months or years , start fail either consistently or intermittantly.

second, when gather fresh statistics, you're potentially forcing oracle hard parses on existing sql statements reference table. can major performance nail if in middle of day. if happen forcefulness query plan alter in bad way, you'll cause dba great deal of grief. if dba gathering statistics in particular way (locking statistics on tables, forcing histograms on others, forcing lack of histograms on others, etc.) deal performance issues, it's highly you'll either working @ cross purposes or actively breaking other.

third, if column never has null values, ought marked not null. can @ info dictionary see columns nullable , not without bothering gather statistics.

sql oracle11g