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.

Split comma separated string and pass to IN Clause in Oracle

--Step 1 :- Create table

CREATE TABLE TEST

(

  ID INTEGER,

  NAME VARCHAR2(50)

);

--Step 2 :- Insert records in table

INSERT INTO TEST VALUES('1','Ram');

INSERT INTO TEST VALUES('2','Shyam')

INSERT INTO TEST VALUES('3','Ghanshyam');

--Step 3 :- Select records from table

SELECT * FROM "TEST" t

ID	NAME
1	Ram
2	Shyam
3	Ghanshyam

--Step 4 :- Create dummy table

CREATE TABLE DUMMY

(

  ID INTEGER

);

Let's Test It...

-- Solution 1

DECLARE

  -- Comma seperated value

  V_INPUT VARCHAR2(50) := '1,2,3';

  V_COUNT INTEGER;

  V_ARRAY DBMS_UTILITY.LNAME_ARRAY;

BEGIN

  DBMS_UTILITY.COMMA_TO_TABLE(LIST   => REGEXP_REPLACE(V_INPUT,

                                                       '(^|,)',

                                                       '\1x'),

                              TABLEN => V_COUNT,

                              TAB    => V_ARRAY);

  FOR I IN 1 .. V_COUNT LOOP

    -- Insert splited value in DUMMY table

    INSERT INTO DUMMY ("ID") VALUES (SUBSTR(V_ARRAY(I), 2));

  END LOOP;

END;

--Use DUMMY table records in IN Clause

SELECT * FROM TEST WHERE ID IN (SELECT ID FROM DUMMY);

ID	NAME
1	Ram
2	Shyam
3	Ghanshyam

-- Solution 2

SELECT *

  FROM TEST

 WHERE ID IN

       (SELECT REGEXP_SUBSTR('1,2,3', '[^,]+', 1, LEVEL) "Result"

          FROM dual       

        CONNECT BY REGEXP_SUBSTR('1,2,3', '[^,]+', 1, LEVEL) IS NOT NULL);

ID	NAME
1	Ram
2	Shyam
3	Ghanshyam