I'm trying to create a chain step that's 30 bytes long. It is failing.
Set-up:
SQL> begin
2 dbms_scheduler.create_program(
3 program_name => lpad('A', 30, 'A')
4 , program_type => 'PLSQL_BLOCK'
5 , program_action => 'begin null; end;'
6 );
7 dbms_scheduler.create_chain('CHAIN_NAME');
8 end;
9 /
PL/SQL procedure successfully completed.
Creating the chain step:
SQL> begin
2 dbms_scheduler.define_chain_step(
3 chain_name => 'CHAIN_NAME'
4 , step_name => lpad('A', 30, 'B')
5 , program_name => lpad('A', 30, 'A')
6 );
7 end;
8 /
begin
*
ERROR at line 1:
ORA-27465: invalid value BBBBBBBBBBBBBBBBBBBBBBBBBBBBBA for attribute step_name
ORA-06512: at "SYS.DBMS_ISCHED", line 5057
ORA-06512: at "SYS.DBMS_ISCHED", line 1760
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1786
ORA-06512: at line 2
I have a byte-semantic database.
Describing DBA_SCHEDULER_CHAIN_STEPS the maximum length of a STEP_NAME is 128 bytes, i.e. 32 4-byte unicode characters:
SQL> desc dba_scheduler_chain_steps;
Name Null? Type
----------------------------------------- -------- --------------------
OWNER NOT NULL VARCHAR2(128)
CHAIN_NAME NOT NULL VARCHAR2(128)
STEP_NAME NOT NULL VARCHAR2(128)
...
If I look at the definition of DBA_SCHEDULER_CHAIN_STEPS then the STEP_NAME column comes from SYS.SCHEDULER$_STEP.VAR_NAME. The definition of this column is:
SQL> select data_type, data_length, char_length, char_used
2 from dba_tab_columns
3 where owner = 'SYS'
4 and table_name = 'SCHEDULER$_STEP'
5 and column_name = 'VAR_NAME';
DATA_TYPE DATA_LENGTH CHAR_LENGTH C
--------- ----------- ----------- -
VARCHAR2 128 128 B
In other words, the maximum size of the object name is 128 bytes (32 Unicode characters).
Section 29.2 "About Scheduler Objects and Their Naming" of the Oracle Database Administrators Guide says
Scheduler objects follow the naming rules for database objects exactly and share the SQL namespace with other database objects.
Names must be from 1 to 30 bytes long...
However, if I create a chain step with a name 24 bytes long and one with a name 25 bytes long the 24 byte name will succeed and the 25 byte will fail
SQL> begin
2 dbms_scheduler.define_chain_step(
3 chain_name => 'CHAIN_NAME'
4 , step_name => lpad('A', 24, 'B')
5 , program_name => lpad('A', 30, 'A')
6 );
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_scheduler.define_chain_step(
3 chain_name => 'CHAIN_NAME'
4 , step_name => lpad('A', 25, 'B')
5 , program_name => lpad('A', 30, 'A')
6 );
7 end;
8 /
begin
*
ERROR at line 1:
ORA-27465: invalid value BBBBBBBBBBBBBBBBBBBBBBBBA for attribute step_name
ORA-06512: at "SYS.DBMS_ISCHED", line 5057
ORA-06512: at "SYS.DBMS_ISCHED", line 1760
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1786
ORA-06512: at line 2
:-(
Why? Is this documented anywhere?
