DBMS_STATS and method_opt

From: Scrier <x3ce4unm3_at_yahoo.com>
Date: 29 Apr 2004 09:38:36 -0700
Message-ID: <6cc528c9.0404290838.5c4f4586@posting.google.com>

I have coded a routine that uses jobs to run dbms_stats on stale tables. For each table, I store the optimum method_opt in a table called stat_control. However, my job to issue the dbms_stats command is failing as below (in the trace file): ORA-12012: error on auto execute of job 75 ORA-20000: Cannot parse for clause: 'FOR ALL INDEXED COLUMNS SIZE=AUTO'

ORA-06512: at "SYS.DBMS_STATS", line 9136
ORA-06512: at "SYS.DBMS_STATS", line 9150
ORA-06512: at "WINKID1.STATS_ACTION", line 94
ORA-06512: at line 1

In the table, I've stored the string for method_opt with the tics (') in place. I've tried removing the tics to no avail. My actual call looks like this:
sys.dbms_stats.gather_table_stats(l_rec.owner, l_rec.object_name, method_opt=>l_rec.method_opt,cascade=>true, degree=>l_rec.degree_opt);

Isn't there a way to have the named parameter listed like that? Do I need to use positional parameters (pass all the NULLs/default values) as the only solution to this problem?

I am not sure that my table description would be of any use, but here it is:
desc stat_control;

Name                                      Null?    Type
----------------------------------------- -------- ---------------
STAT_ID                                   NOT NULL NUMBER
OWNER                                              VARCHAR2(30)
OBJECT_NAME                                        VARCHAR2(30)
METHOD_OPT                                         VARCHAR2(200)
DEGREE_OPT                                         NUMBER

A sample method_opt (right now - I've tried without the tics too) is select method_opt from stat_control where rownum < 2; METHOD_OPT

---

'FOR ALL INDEXED COLUMNS SIZE=AUTO' If you need to see the whole package to help me out, then I can do that too (warning there are other tables and some sequences involved to get me to this final step).

I appreciate any insight anyone has to offer. I'm on 9.2.0.4 and trying this on both AIX 4.3.3 and Windows 2000 SP3.

Thanks,
Scrier Received on Thu Apr 29 2004 - 11:38:36 CDT