Checking NULL in PL/SQL

CREATE OR REPLACE FUNCTION date_null(
    iv_number IN DATE,dt IN DATE)
  RETURN integer
  IS
  d integer;
BEGIN

  IF iv_number is NULL THEN  d :=1;
  ELSE
    d :=2;
    
  END IF ;
   RETURN d;
   
END;

Functions in PL/SQL

 function definition



CREATE OR REPLACE FUNCTION larger_date(
    iv_number IN DATE,dt IN DATE)
  RETURN DATE IS
  d date;
BEGIN

  IF iv_number > dt THEN  d :=iv_number -7;
  ELSE
    d :=dt;
    
  END IF ;
   RETURN d;
   
END;


How to call in SQL

SELECT  larger_date

(sysdate-1,sysdate+1)

FROM DUAL;

Change HTTP port in OracleXE

SQL> begin
 2    dbms_xdb.sethttpport('9080');
 3  end;
 4  /

SYS.DBMS_SCHEDULER

BEGIN
    SYS.DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"SYSTEM"."MULT2"',
            job_type => 'PLSQL_BLOCK',
            job_action => 'BEGIN 
 INSERT INTO HR.HEXA VALUES(''IND'',''KER'');
 INSERT INTO HR.HEXA VALUES(''NETHERLAND'',''HOLLAND'');
COMMIT;
END;',
            number_of_arguments => 0,
            start_date => SYSTIMESTAMP,
            repeat_interval => 'FREQ=MINUTELY',
            end_date => NULL,
            job_class => '"SYS"."DEFAULT_JOB_CLASS"',
            enabled => FALSE,
            auto_drop => FALSE,
            comments => 'MULT2 Descri');


    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE( 
             name => '"SYSTEM"."MULT2"', 
             attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);
  
  
    SYS.DBMS_SCHEDULER.enable(
             name => '"SYSTEM"."MULT2"');
END; 
/

Scheduled JOB, Oracle DB

BEGIN
dbms_scheduler.create_schedule(  
schedule_name  => 'INTERVAL_EVERY_1_MINUTES',  
  start_date    => trunc(sysdate),  
  repeat_interval => 'freq=MINUTELY',  
  comments     => 'Runtime: Every minut');
 
  dbms_scheduler.create_program  
(program_name=> 'PROG_DO_HR_INSERT',  
 program_type=> 'PLSQL_BLOCK',  
 program_action=> 'BEGIN  INSERT INTO HR.HEXA VALUES('actiont','FRM MINUT SCHEDULE-R'); COMMIT; END',  
 enabled=>true,  
 comments=>'Procedure to save sample Data to \hexa table every Minut'  
 );
 
  dbms_scheduler.create_job  
 (job_name => '30_OCT_2011_Job1',  
  program_name=> 'PROG_DO_HR_INSERT',  
  schedule_name=>'INTERVAL_EVERY_1_MINUTES',  
  enabled=>true,  
  auto_drop=>false,  
  comments=>'Job to  save sample Data to \hexa table every Minut');
 COMMIT; 
 END; 

SCHEDULER_JOB in oracle data base

SELECT JOB_NAME,JOB_TYPE,JOB_ACTION,SCHEDULE_NAME,SCHEDULE_TYPE,JOB_CLASS,STATE FROM  DBA_SCHEDULER_JOBS WHERE JOB_NAME='JobName';
--------------------------------------------------------------------------------------


SELECT owner, program_name, enabled FROM dba_scheduler_programs;

sequence(Oracle) Alternate to auto_increment(mysql)

   create a sequence

 create sequence test_d_seq  increment by 1 START WITH 1;

  insert value

insert into test_d values(test_d_seq.NEXTVAL,'Gex');

treeview in oracle

select LEVEL,ID,NAME,REPORT_TO
from top_dogs
START WITH report_to IS NULL
CONNECT BY PRIOR id=report_to

select LPAD(NAME,length(NAME)+LEVEL*2,'_')
from top_dogs
START WITH report_to IS NULL
CONNECT BY PRIOR id=report_to

top-n Analysis in oracle

select * from (select * from top_dogs order by salary DESC)where ROWNUM<=3
top 3 salary earners

Simple PL/SQL program

SQL> VARIABLE RESULT NUMBER;
SQL> BEGIN
2 SELECT max(salary) INTO :RESULT FROM top_dogs;
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> print RESULT;

RESULT
----------
250000
SQL>