Oracle: Getting the DDL for scheduler-jobs

Getting the DDL for tables, views etc. is easy using the package DBMS_METADATA:

DBMS_METADATA.GET_DDL (
object_type     IN VARCHAR2,
name            IN VARCHAR2,
schema          IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

For example, to extract the DDL for table HELP in the schema SYSTEM we use this:

SELECT DBMS_METADATA.GET_DDL('TABLE', 'HELP', 'SYSTEM') FROM DUAL;

and we get that:

"
  CREATE TABLE "SYSTEM"."HELP" 
   (	"TOPIC" VARCHAR2(50) NOT NULL ENABLE, 
	"SEQ" NUMBER NOT NULL ENABLE, 
	"INFO" VARCHAR2(80), 
	 CONSTRAINT "HELP_TOPIC_SEQ" PRIMARY KEY ("TOPIC", "SEQ")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE
   ) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 49152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" "

But if we try to adapt this for to get the DDL of a scheduler-job, we’ll get stuck:

select dbms_metadata.get_ddl('JOB','JOB01') from dual;

ORA-31604: invalid NAME parameter "NAME" for object type JOB in function SET_FILTER
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 116
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4517
ORA-06512: at "SYS.DBMS_METADATA_INT", line 8252
ORA-06512: at "SYS.DBMS_METADATA", line 2752
ORA-06512: at "SYS.DBMS_METADATA", line 2639
ORA-06512: at "SYS.DBMS_METADATA", line 4220
ORA-06512: at line 1
31604. 00000 -  "invalid %s parameter \"%s\" for object type %s in function %s"
*Cause:    The specified parameter value is not valid for this object type.
*Action:   Correct the parameter and try the call again.

The same for scheduler-programs or -schedules:

select dbms_metadata.get_ddl('SCHEDULE','EVERY_MONDAY_800PM') from dual;

ORA-31600: invalid input value SCHEDULE for parameter OBJECT_TYPE in function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 2576
ORA-06512: at "SYS.DBMS_METADATA", line 2627
ORA-06512: at "SYS.DBMS_METADATA", line 4220
ORA-06512: at line 1
31600. 00000 -  "invalid input value %s for parameter %s in function %s"
*Cause:    A NULL or invalid value was supplied for the parameter.
*Action:   Correct the input value and try the call again.

The trick is to use “PROCOBJ” (stands for ‘procedural object’) as object-type for all scheduler-objects:

SELECT DBMS_METADATA.GET_DDL('PROCOBJ', ['JOBNAME' | 'SCHEDULE' | 'PROGRAM' ]) FROM DUAL;

select dbms_metadata.get_ddl('PROCOBJ','REBUILD_INDEXES') from dual;

"  
BEGIN 
dbms_scheduler.create_job('"REBUILD_INDEXES"',
job_type=>'STORED_PROCEDURE', 
job_action=>'REBUILDINDEXES', 
number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('07-MAR-2014 12.00.00,000000000 AM CET','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), 
repeat_interval=> 'FREQ=DAILY; BYHOUR=20', 
end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', 
enabled=>FALSE, 
auto_drop=>FALSE,
comments=> 'Rebuild all indexes dependent on the Index-to-Table-Size-Ratio'
);
dbms_scheduler.enable('"REBUILD_INDEXES"');
COMMIT; 
END; 
"

Edit 2014-08-05:
Please note that it is not possible to get the DDL for scheduler-jobs owned by user SYS.

[oracle@testsrv ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 5 13:27:34 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select owner, job_name, job_type from dba_scheduler_jobs where job_name='PURGE_AUDIT_TRAIL';

OWNER                          JOB_NAME                       JOB_TYPE
------------------------------ ------------------------------ ----------------
SYS                            PURGE_AUDIT_TRAIL              PLSQL_BLOCK

SQL>

SQL> SELECT DBMS_METADATA.GET_DDL('PROCOBJ','PURGE_AUDIT_TRAIL','SYS') FROM DUAL;
ERROR:
ORA-31603: object "PURGE_AUDIT_TRAIL" of type PROCOBJ not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7589
ORA-06512: at line 1

no rows selected

SQL>

As per MOS-Notes this is “because by using PROCOBJ the package [DBMS_METADATA] is using similar functionality as which datapump export would use and as known SYS objects are marked as non-exportable” (Doc ID 567504.1).
This note also states, that the lack of exporting Scheduler-DDL by using “JOB” as object-type is filed as a bug at Oracle. So there is hope, that one day we will have a more intuitive way for extracting Scheduler-DDL.

PL/SQL: Building an odd/even-random-number-generator

If you just need any integer random number, this would do the job:

select round(dbms_random.value(1,100),0) as random_number from dual;

The “dbms_random.value(1,100)” generates a random (float) number in the intervall 1 to 100. Actually the upper bound (here: 100) is not included, but as we round up the result to get an integer, we’ll still have it when rounding up something like 99,672517251 to 100.

Now, if we just want even random numbers (for what purpose ever), we’ll have to set up a loop that goes on generating random numbers until it spits out an even one. To check for “even” we use the modulo-function. Modulo divides a given number by a given divisor and just prints out the remainder. So if we divide a number by 2 and the remainder is 0 we know it’s an even number. Our loop looks like this:

begin
  declare
    n_randomvalue number default null;
begin
  loop
    select round(dbms_random.value(1,100),0) into n_randomvalue from dual;      
    exit when ( mod(n_randomvalue,2)=0 );
    dbms_output.put_line('generated value is: ' || n_randomvalue);
  end loop;
  dbms_output.put_line('your random-value: ' || n_randomvalue);
end;
end;

To just get odd numbers we change the “mod(n_randomvalue,2)=0” to “mod(n_randomvalue,2)=1”.

Ok, now let’s make it a little more generic and put it in a function:

create or replace
FUNCTION generateRandomNumber(
      v_oddeven VARCHAR2,
      n_start   NUMBER,
      n_end     NUMBER )
    RETURN NUMBER
  AS
      n_randomvalue NUMBER DEFAULT NULL;
      n_modval      NUMBER DEFAULT NULL;
    Begin
      -- check input-parameters:
      IF ( lower(v_oddeven)    = 'odd' ) THEN
        n_modval              := 1;
      Elsif ( Lower(V_Oddeven) = 'even' ) Then
        n_modval              := 0;
      else
        raise_application_error (-20001, 'INVALID_ARGUMENT #1! 
VALID ARGUMENTS ARE: ODD, EVEN');
      End If;
      If Not ( ( 0 <= N_Start ) And ( N_Start < N_End ) ) Then
        raise_application_error (-20002, 'INVALID_INTERVALL! 
INTERVALL-START MUST BE GREATER 0 AND LOWER THAN INTERVALL-END.');
      End If;
      -- create random-number:
      LOOP
        SELECT ROUND(dbms_random.value(n_start,n_end),0) INTO n_randomvalue FROM dual;
        EXIT WHEN ( MOD(N_RANDOMVALUE,2)=N_MODVAL );
      END LOOP;      
      RETURN N_RANDOMVALUE;
    END;

…end test it:

SQL> select generateRandomNumber('even',1,100) from dual;

GENERATERANDOMNUMBER('EVEN',1,100)
----------------------------------
                                 4

SQL> select generateRandomNumber('odd',1,100) from dual;

GENERATERANDOMNUMBER('ODD',1,100)
---------------------------------
                               83

SQL>

Works. Now let’s check the “error-handling” too:

SQL> select generateRandomNumber('evenX',1,100) from dual;
select generateRandomNumber('evenX',1,100) from dual
       *
ERROR at line 1:
ORA-20001: INVALID_ARGUMENT #1! VALID ARGUMENTS ARE: ODD, EVEN
ORA-06512: at "TEST.GENERATERANDOMNUMBER", line 20

SQL> select generateRandomNumber('even',100, 1) from dual;
select generateRandomNumber('even',100, 1) from dual
       *
ERROR at line 1:
ORA-20002: INVALID_INTERVALL! INTERVALL-START MUST BE GREATER 0 AND LOWER THAN
INTERVALL-END.
ORA-06512: at "TEST.GENERATERANDOMNUMBER", line 23

SQL>

…as expected.

For more info on generating random-values with PL/SQL see here:
PL/SQL: Generate random values

Edit 2012-11-06:
Thanks to commentator David we now know a smarter, better performing way:

To get an odd random-number in the range 1-99 we can achieve this with just one single call to DBMS_RANDOM with this calculation:

select round(dbms_random.value(0,49),0)*2+1 as random_number from dual;

And to get even random-numbers in the range 2-100 we can use this:

select round(dbms_random.value(1,50),0)*2 as random_number from dual;

Oracle: Let users kill their sessions autonomously

From time to time my developers are haunted by hanging DB-sessions on their Dev-Instances. To let them kill their lost sessions in that cases I created this procedure as SYS and granted them the “EXECUTE”-Privilege on it:

create or replace
PROCEDURE kill_usersession(
    n_sid    IN NUMBER,
    n_serial IN NUMBER)
AS
BEGIN
  DECLARE
    v_myuser   VARCHAR2(30) DEFAULT '';
    v_killuser VARCHAR2(30) DEFAULT '';
  BEGIN
    SELECT sys_context('USERENV','SESSION_USER') INTO v_myuser FROM dual;
    SELECT username
    INTO v_killuser
    FROM v$session
    WHERE sid    =n_sid
    AND serial#  =n_serial;
    IF ( v_killuser=v_myuser ) THEN
      EXECUTE immediate('ALTER SYSTEM KILL SESSION ''' || n_sid || ',' || n_serial || '''');
    ELSE
      raise_application_error(-20000, 'Username mismatch: You can not kill the session of another schema!');
    END IF;
  EXCEPTION
  WHEN OTHERS THEN
    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
  END;
END kill_usersession;

The procedure checks if the executing username equals the to-be-killed username so that users can only terminate sessions of their own schema.

Oracle: ORA-00600 on creating trigger on 11.2

Here’s a quick note about a recent problem:

Developer tried to run a db-script on an Oracle Database 11.2.0.2 but got stuck every time at this really unspectacular “CREATE TRIGGER”

CREATE OR REPLACE TRIGGER "TR_TB_ELEMENT" 
BEFORE INSERT ON "TB_ELEMENT"
FOR EACH ROW BEGIN
  IF INSERTING THEN
    IF :NEW."EL_ID" IS NULL THEN
      SELECT SEQ_TB_ELEMENT.NEXTVAL INTO :NEW."EL_ID" FROM DUAL;
    END IF;
  END IF;
END;

with this error-message:

ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kqlidchg1], [], [], [], [], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated
00603. 00000 -  "ORACLE server session terminated by fatal error"
*Cause:    An ORACLE server session is in an unrecoverable state.
*Action:   Login to ORACLE again so a new server session will be created

At the blog of Dale Ward, who once had the same problem, I found as a workaround to disable PL/Scope. This can be done at session-level with this:

ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';

After this was done, my trigger was created and compiled with no errors. To re-enable the PL/Scope set this:

ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL'; 

A commentator at Dale’s blog noticed, that it was possible to create a trigger with TOAD while using SQL Developer (like me) raised the above error. So I verified this hint using SQL-Plus and, lo and behold, the trigger compiled with no error!

Further investigations revealed that my DB is running with the (default) init-parameter “plscope_settings=IDENTIFIERS:NONE”, while my SQL-Developer (v3.0) started every session with the default-setting “plscope_settings=IDENTIFIERS:ALL” (see menu EXTRAS>PREFERENCES>DATABASE>PL/SQL-COMPILER).

References in Metalink/MOS:
Bug 10174125
Bug 10094416
ID 1341014.1 (“Bug 10094416 is fixed in 12.1 release.”)

PL/SQL: Generate random values

When you need random values in the Oracle-world you can get them by using the package DBMS_RANDOM. If not already there, you can install the package with the script “dbmsrand.sql” in “ORACLE_HOME/RDBMS/ADMIN”.

The package offers the two functions VALUE and STRING to generate random values for numbers, strings or mixed cases.

When called without any parameter DBMS_RANDOM.VALUE delivers values between 0 and 1:

select dbms_random.value num from dual connect by level <5;

       NUM
----------
0,44323453888163585343305347384952888762
0,27061509862121968713431350682787319082
0,50719289299767089592254463623923727779
0,42666962176478158926225395583206159572

The “connect by level <5" can be omitted as it’s used here just to present more than one example-row.

You can also define a range of values from which to get random values (here it is the range from “1 <= x < 100"):

select dbms_random.value(1,100) num from dual connect by level <5;
       NUM
----------
74,68117799104242371065908850142160589183
25,20810462171063688648794689871086371162
64,05205571240469040169889477158705792932
77,16423950749394682603580224777136966975

To get rid of the fractional digits you can ROUND the values:

select round(dbms_random.value(1,100),0) num from dual connect by level <5;
       NUM
----------
34
81
35
72

Keep in mind, that the generated values are always BELOW the upper bound (it is NOT included in the range).

To limit the result to even numbers you could wrap it with modulo:

select * from (
select round(dbms_random.value(1,100),0) num from dual connect by level <5
)
where mod(num,2)=0;

To just get odd numbers you have to set “mod(num,2)=1”. But don’t trust that this example will get you ALWAYS a number or a constant number of rows! The modulo is just checked against the result-set of the inner query. If that result-set doesn’t contain a even (or odd) number, it will return nothing.

For random strings there are a few options:

 'u', 'U' - returning string in uppercase alpha characters
 'l', 'L' - returning string in lowercase alpha characters
 'a', 'A' - returning string in mixed case alpha characters
 'x', 'X' - returning string in uppercase alpha-numeric characters
 'p', 'P' - returning string in any printable characters.
select dbms_random.string('u',10) rndvalue from dual connect by level <5;

RNDVALUE
----------
QHQPNZTPMJ
HNHFPOKTXU
CGDQFAKMSA
OMOJDCBQVZ
select dbms_random.string('l',10) rndvalue from dual connect by level <5;

RNDVALUE
----------
fdoeswqqpe
zkicmviacg
zhbbtkqjwl
npwmzvcuhu
select dbms_random.string('a',10) rndvalue from dual connect by level <5;

RNDVALUE
----------
hAVpQccUja
UUwcLqQEZB
rsVpKjKNKx
EJKyUflprQ
select dbms_random.string('x',10) rndvalue from dual connect by level <5;

RNDVALUE
----------
6DV2519VE8
D2XV0YJ7JK
A58FMX7FWS
LIEZB4BBX4
select dbms_random.string('p',10) rndvalue from dual connect by level <5;

RNDVALUE
----------
%VttvY &F@
k33]'bp7fQ
V)w6Nno"sO
7'7p62:'zp

DBMS_RANDOM documentation:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_random.htm#sthref4646

See here how to build an odd/even-random-number-generator:
PL/SQL: Building an odd/even-random-number-generator