Generate date wise serial number in oracle

Step 1:- Create Table

CREATE TABLE "TEST"

(

  ID VARCHAR(50),                                                                                                                                        

  C_DATE VARCHAR(10) DEFAULT(TO_CHAR(SYSDATE, 'dd-MM-yyyy'))

);

Step 2:- Create function

CREATE OR REPLACE FUNCTION TEST_FN_GET_DATEWISE_SR_No

RETURN NUMBER

AS

V_SR_No NUMBER;

BEGIN

  SELECT NVL(MAX(ID),0)

  INTO V_SR_No

  FROM "TEST"

  WHERE C_DATE = TO_CHAR(SYSDATE, 'dd-MM-yyyy');

  RETURN (V_SR_No+1);

END TEST_FN_GET_DATEWISE_SR_No;

Step 3:- Insert record in Test table

INSERT INTO "TEST"(ID) VALUES(TEST_FN_GET_DATEWISE_SR_No());

Step 4:- Select record from Test Table

SELECT * FROM "TEST"

Create a Job in PL SQL

Step 1:- Create a table

CREATE TABLE tblTest(

  ID VARCHAR(50),

  DATE_VALUE VARCHAR(50)

);

Step 2:- Create a store procedure

CREATE OR REPLACE PROCEDURE spTest AS

BEGIN

  INSERT INTO tbltest ("ID", date_value) VALUES (SYS_GUID(), SYSDATE);

END;

Step 3:- Create a Job it will run the "spTest" store procedure

begin

  sys.dbms_scheduler.create_job(job_name        => 'TESTJOB', --Job Name

                                job_type        => 'STORED_PROCEDURE', --Job Type i.e. PL/SQL Block,Store Procedure,Executable,Chain

                                job_action      => 'spTest', --Procedure Name

                                start_date      => to_date('28-06-2017 00:00:00',

                                                           'dd-mm-yyyy hh24:mi:ss'), -- Job Start Date

                                repeat_interval => 'Freq=Minutely;Interval=1', -- Job Frequency i.e.

                                                                               -- yearly,Monthly,Weekly,Daily,Hourly

                                                                               -- Minutely,Secondly and Job Running Interval

                                end_date  => to_date('29-06-2017 00:00:00',

                                                     'dd-mm-yyyy hh24:mi:ss'), -- Job End Date

                                job_class => 'DEFAULT_JOB_CLASS',

                                enabled   => true,

                                auto_drop => false,

                                comments  => '');

end;

Step 4:- Select a record

select * from tbltest;

Note:- Job will run each minute because we have set the Job Frequency "Minutely" and Interval "1"

Step 5:- Drop the Job

BEGIN

  dbms_scheduler.drop_job(job_name => 'TESTJOB');

END;

--Using UI Interface

Step 1:- Right on Job => New 

Step 2:- DBMS Scheduler Screen will display

Step 3:- Fill Necessary field of DBMS Scheduler

Step 4:- Click on Apply Button to Create the Job

Step 5:- Expand the Job Folder from left pane where you can view your Job

Split comma separated string in PL/SQL?

-- Oracle 10G or 11G

-- built-in Apex function apex_util.string_to_table()

DECLARE

  V_String VARCHAR2(100) := 'A,B,C,D';

  V_Array  apex_application_global.vc_arr2;

BEGIN

  V_Array := apex_util.string_to_table(V_String, ',');

  FOR i IN 1 .. V_Array.COUNT LOOP

    DBMS_OUTPUT.Put_Line(V_Array(i));

    -- insert table syntax

  END LOOP;

END;

-- Using REGEXP_SUBSTR()

SELECT REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) AS data

  FROM dual

CONNECT BY REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) IS NOT NULL;

-- Using xmltable()

-- Split number to table

-- Note: It's only work for number

SELECT TO_NUMBER(COLUMN_VALUE) as data from xmltable('1,2,3,4,5');

-- VARRAY(10) :- Size of array

-- VARCHAR2(20) :- Size of String Value

DECLARE

  TYPE V_Array IS VARRAY(10) OF VARCHAR2(10);

  V_String V_Array;

BEGIN

  V_String := V_Array('A', 11, 22, 'B');

  FOR i IN 1 .. V_String.Count LOOP

    dbms_output.put_line(V_String(i));

    -- insert table syntax

  END LOOP;

END;

ORA-20000: ORU-10027: buffer overflow, limit of 10000 bytes

PL/SQL Version 12c

This error occurred when I am trying to print the output of cursor to output window where output window default buffer size is 10000 bytes and cursor returning more than 20000 records.

DECLARE

  vCur      SYS_REFCURSOR;

  Column1   VARCHAR2(30);

BEGIN

  OPEN vCur FOR

    SELECT Column1 FROM TEST t;

    DBMS_OUTPUT.ENABLE(500000);

  LOOP

    FETCH vCur

      INTO Column1;

    EXIT WHEN vCur%NOTFOUND;

    dbms_output.put_line(Column1);

  END LOOP;

END;

Use following to increse buffer size in query statement.

-- Number is used to increse buffer size as specified number

DBMS_OUTPUT.ENABLE(500000);

-- NULL is used to increse buffer size unlimited

DBMS_OUTPUT.ENABLE(NULL);

Note:-

1. Depending on Oracle version, DBMS_OUTPUT has different default buffer size.

2. Its reduces the query performance, after analyzing output result comment DBMS_OUTPUT line in Store Procedure.

How can I get the number of days between 2 dates in Oracle?

when subtracting two dates in Oracle By default its return number of days.

--Passing one date parameter and subtracting from current date

select TRUNC(sysdate) - to_date('1/3/2017', 'dd/MM/yyyy') AS DiffDate

  from dual;

--Passing both date parameter and subtracting lower date from greater date

SELECT to_date('2017-03-06', 'YYYY-MM-DD') -

       to_date('2017-03-01', 'YYYY-MM-DD') AS DiffDate

  from dual;

--To get the number of days different in SQL statement

SELECT TRUNC(sysdate) -

       to_date(to_char(created, 'dd/MM/yyyy'), 'dd/MM/yyyy') As Datediff

  FROM dba_objects

 WHERE object_name = 'TABLE_NAME'

   AND owner = 'TABLE_OWNER'

   AND object_type = 'TABLE';