Home » RDBMS Server » Server Administration » Buffer setting in Job Scheduler?
Buffer setting in Job Scheduler? [message #221611] Tue, 27 February 2007 12:20 Go to next message
jprose
Messages: 3
Registered: February 2007
Junior Member
I'd like to run a PL/SQL statement that is about 236 lines long as a scheduled job in Oracle. It runs fine in SQL/Plus, but when I create a job using the same statement, I get an ORA-06502 character string buffer too small.

As a test, I put a shorter statement in the job and it worked fine. I continued to add comment lines to it and when it reached 103 lines, I received the same error. I took one comment line out and the job works again.

I won't trouble anyone with the specific code because it seems that length of the statement is the culprit on this one.

Is there a limit on the length of query or PL/SQL statement you can run as a job? Maybe a parameter somewhere that I'm missing?

Thanks for your time,
JP
Re: Buffer setting in Job Scheduler? [message #221621 is a reply to message #221611] Tue, 27 February 2007 15:01 Go to previous message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member

SQL> desc dba_jobs
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 JOB                                       NOT NULL NUMBER
 LOG_USER                                  NOT NULL VARCHAR2(30)
 PRIV_USER                                 NOT NULL VARCHAR2(30)
 SCHEMA_USER                               NOT NULL VARCHAR2(30)
 LAST_DATE                                          DATE
 LAST_SEC                                           VARCHAR2(8)
 THIS_DATE                                          DATE
 THIS_SEC                                           VARCHAR2(8)
 NEXT_DATE                                 NOT NULL DATE
 NEXT_SEC                                           VARCHAR2(8)
 TOTAL_TIME                                         NUMBER
 BROKEN                                             VARCHAR2(1)
 INTERVAL                                  NOT NULL VARCHAR2(200)
 FAILURES                                           NUMBER
 WHAT                                               VARCHAR2(4000)
 NLS_ENV                                            VARCHAR2(4000)
 MISC_ENV                                           RAW(32)
 INSTANCE                                           NUMBER

looks to me like a 4000 character limit; which is not surprising.
Plz create a stored procedure & get on with other things.
Previous Topic: Timezone and DST patches on Window Servers
Next Topic: Schema creation
Goto Forum:
  


Current Time: Sat Dec 03 00:52:35 CST 2016

Total time taken to generate the page: 0.09228 seconds