ORACLE: scheduled task (oracle scheduler)

ORACLE - Scheduled task (Oracle scheduler)

Oracle, PL/SQL

1. Create a program

Procedure name: READ_FILES_PROC

begin
dbms_scheduler.create_program(
       program_name => 'READ_FILES_PROG'
      ,program_type => 'STORED_PROCEDURE'
      ,program_action => 'READ_FILES_PROC'
      ,number_of_arguments => 0
      ,enabled => true
      ,comments => 'Read files from directory' );
end;

2. Create a schedule

Additional parameters info:

https://docs.oracle.com/cd/B28359_01/server.111/b28310/scheduse004.htm#ADMIN12412

BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
  schedule_name     => 'READ_FILES_SCH',
  start_date        => TO_TIMESTAMP('2014-06-28 06:15:00','YYYY-MM-DD HH24:Mi:SS'),
  end_date          => TO_DATE('9999-12-31','YYYY-MM-DD'),
  repeat_interval   => 'FREQ=MINUTELY; INTERVAL=15',
  comments          => 'Every 15 minutes');
END;

3. Create a job

begin
  dbms_scheduler.create_job(
       job_name => 'READ_FILES_JOB'
     , program_name =>'READ_FILES_PROG'
     , schedule_name =>'READ_FILES_SCH'
     , enabled => TRUE
     , comments => 'Read files every 15 minutes');
end;

4. Enable log

begin
DBMS_SCHEDULER.SET_ATTRIBUTE('READ_FILES_JOB','logging_level',DBMS_SCHEDULER.LOGGING_FULL);
end;

5.  Restart job and read log

Disable job

begin
DBMS_SCHEDULER.DISABLE('READ_FILES_JOB',TRUE);
end;

Enable job

begin
DBMS_SCHEDULER.ENABLE('READ_FILES_JOB');
end;

Display log

select * from dba_scheduler_jobs where owner like 'SCHEMA NAME';

SAP/ABAP: Connection to external database

SAP/ABAP: Connection to external database

SAP, ORACLE, ABAP

1. Transaction code: ST04 - Add connection

2. Press “Add DB Entry” and provide database information

3. Transaction code: DBCO and provide database password

Example INSERT INTO database

DATA:

     CONNECTION LIKE DBCON-CON_NAME VALUE 'TEST'.

Code section: Record processing:

EXEC SQL.

CONNECT TO :CONNECTION

ENDEXEC.

IF SY-SUBRC = 0.

EXEC SQL.

    SET CONNECTION :CONNECTION

ENDEXEC.

EXEC SQL.

INSERT INTO Table1 (Name)

VALUES ('Adam')

ENDEXEC.

EXEC SQL.

COMMIT

ENDEXEC.

EXEC SQL.

DISCONNECT :CONNECTION

ENDEXEC.

ENDIF.

Example SELECT FROM database

TYPES:

BEGIN OF TY_TABLE1,

NAME1 TYPE C LENGTH 40

END OF TY_TABLE1.

DATA:

C1 LIKE DBCON-CON_NAME VALUE 'TEST',

S_TABLE1 TYPE TY_TABLE1,

Z_NAME TYPE C LENGTH 40.

Code section: Record processing:

EXEC SQL.

     CONNECT TO :C1

ENDEXEC.

  IF SY-SUBRC = 0.

     EXEC SQL.

       SET CONNECTION :C1

     ENDEXEC.

     EXEC SQL.

       open dbcursor for select distinct Name

       from Table1

     ENDEXEC.

     DO.

       CLEAR S_TABLE1 .

       EXEC SQL.

         FETCH NEXT dbcursor INTO :S_TABLE1

       ENDEXEC.

       if sy-subrc = 0.

         Z_NAME = S_TABLE1-NAME1.

       else.

         exit.

       endif.

     ENDDO.

     EXEC SQL.

       close dbcursor

     ENDEXEC.

     EXEC SQL.

       DISCONNECT :C1

     ENDEXEC.

   ENDIF.

Transact-SQL: insert into two tables, use newly created values with output clause

Transact-SQL: insert into two tables, use newly created values with output clause

Microsoft SQL

Tables:

Table1 Columns: Id, Name

Table2 Columns: Id2, Surname, Table1Id

Data:

Name: Adam 

Surname: Sandler

Query:

INSERT INTO Table1(Name) 

OUTPUT inserted.Id, 'Sandler' INTO Table2 (Table1Id, Surname)  

VALUES('Adam');

Query will not work with foreign keys defined. In order to use it with foreign keys you need to switch off constrain check.

f.e.

ALTER TABLE Table2 NOCHECK CONSTRAINT ALL

GO

Query

ALTER TABLE Table2 CHECK CONSTRAINT ALL

GO

SQL: group by number of rows (divide result into sections with particular number of rows)

SQL: group by number of rows

Oracle

Example shows how to divide "group by clause"result into sections with particular number of rows.

Table:
select column1 from table1;

Query:

Group capacity: 4

with query1 as
    (
    select 
    column1,
    row_number()  OVER (PARTITION BY column1  ORDER BY column1)-1 as c1
    from table1
    )
    select 
    column1,
    count(column1)
    from query1
    group by column1, floor(c1/4)
    order by column1, count(column1) desc

Result: