Category Archives: SQL/PL-SQL

START NEW VALUE OF SEQUENCE WITHOUT DROP THE SEQUENCE

Posted on by
Reply 
SQL> CREATE OR REPLACE PROCEDURE SEQUENCE_NEWVALUE(
  2  seqname VARCHAR2,
  3  newvalue NUMBER) AS
  4  ln NUMBER;
  5  ib NUMBER;
  6  BEGIN
  7  SELECT last_number, increment_by
  8  INTO ln, ib
  9  FROM USER_sequences
 10  WHERE  sequence_name = upper(seqname);
 11  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seqname ||
 12  ' INCREMENT BY ' || (newvalue - ln);
 13  EXECUTE IMMEDIATE 'SELECT ' || seqname ||
 14  '.NEXTVAL FROM DUAL' INTO ln;
 15  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seqname
 16  || ' INCREMENT BY ' || ib;
 17  END;
 18  /

Procedure created.

SQL> CREATE SEQUENCE TEST;

Sequence created.

SQL> SET LONG 10000
SQL> SET PAGESIZE 0
SQL> SELECT DBMS_METADATA.GET_DDL('SEQUENCE','TEST') FROM DUAL;

   CREATE SEQUENCE  "NETVERTEX"."TEST"  MINVALUE 1 MAXVALUE 99999999999999999999
99999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE


SQL> SET SERVEROUTPUT ON
SQL> EXEC SEQUENCE_NEWVALUE('TEST',100);

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_METADATA.GET_DDL('SEQUENCE','TEST') FROM DUAL;

   CREATE SEQUENCE  "NETVERTEX"."TEST"  MINVALUE 1 MAXVALUE 99999999999999999999
99999999 INCREMENT BY 1 START WITH 100 CACHE 20 NOORDER  NOCYCLE


SQL> SELECT TEST.NEXTVAL FROM DUAL;
       100

SQL> /
       101

SQL> /
       102

🙂

NUMTODSINTERVAL

Posted on by
Reply

NUMTODSINTERVAL converts n to an INTERVAL DAY TO SECOND literal.

The argument n can be any NUMBER value or an expression that can be implicitly converted to a NUMBER value.
The argument interval_unit can be of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype.
The value for interval_unit specifies the unit of n and must resolve to one of the following string values:

‘DAY’

‘HOUR’

‘MINUTE’

‘SECOND’

CREATE OR REPLACE FUNCTION FN_ADD_TIMESTAMP(
       P_TIMESTAMP IN TIMESTAMP,
       P_AMOUNT IN NUMBER,
       P_UNITS IN VARCHAR2)
       RETURN TIMESTAMP
    IS
       C_DATE CONSTANT DATE := CAST(P_TIMESTAMP AS DATE);
       L_TIMESTAMP TIMESTAMP;
    BEGIN
       CASE
          WHEN P_UNITS IN ('SECOND', 'MINUTE', 'HOUR', 'DAY') THEN
             L_TIMESTAMP := P_TIMESTAMP + NUMTODSINTERVAL(P_AMOUNT, P_UNITS);
          WHEN P_UNITS = 'WEEK' THEN
             L_TIMESTAMP := P_TIMESTAMP +
                NUMTODSINTERVAL(P_AMOUNT * 7, 'DAY');
          WHEN P_UNITS = 'MONTH' THEN
             L_TIMESTAMP := P_TIMESTAMP +
                NUMTODSINTERVAL(ADD_MONTHS(C_DATE, P_AMOUNT) - C_DATE, 'DAY');
          WHEN P_UNITS = 'YEAR' THEN
             L_TIMESTAMP := P_TIMESTAMP +
                NUMTODSINTERVAL(ADD_MONTHS(C_DATE, P_AMOUNT * 12) - C_DATE, 'DAY');
       END CASE;

       RETURN L_TIMESTAMP;
    END FN_ADD_TIMESTAMP;
/

SQL> SELECT TO_TIMESTAMP(TRUNC(SYSDATE + 1),’DD/MM/RRRR HH:MI:SS’) + numtodsinterval(30,’MINUTE’) FROM DUAL;

TO_TIMESTAMP(TRUNC(SYSDATE+1),’DD/MM/RRRRHH:MI:SS’)+NUMTODSINTERVAL(30,’MIN
—————————————————————————
02-AUG-14 12.30.00.000000000 AM

SQL> select fn_add_timestamp(TO_TIMESTAMP(TRUNC(SYSDATE + 1),’DD/MM/RRRR HH:MI:SS’),30,’MINUTE’) from dual;

FN_ADD_TIMESTAMP(TO_TIMESTAMP(TRUNC(SYSDATE+1),’DD/MM/RRRRHH:MI:SS’),30,’MI
—————————————————————————
02-AUG-14 12.30.00.000000000 AM

http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions108.htm#SQLRF00682
http://www.techonthenet.com/oracle/functions/numtodsinterval.php

PL-SQL – FOR Loop batch operation

Posted on by
Reply

By Ref – http://www.oracle.com/technetwork/issue-archive/2008/08-nov/o68plsql-088608.html
Code Listing 4: Using the LIMIT clause

We modify the procedure and customized it for better use case

CREATE OR REPLACE PROCEDURE bulk_with_limit
IS
  limit_in PLS_INTEGER DEFAULT 100;
  CURSOR mytable_cur
  IS
    SELECT ID FROM MYTABLE WHERE DTTIME < CURRENT_TIMESTAMP;
    TYPE mytable_tt
    IS
    TABLE OF MYTABLE.BODID%TYPE INDEX BY PLS_INTEGER;
  l_mytable mytable_tt;
BEGIN
  OPEN mytable_cur;
  LOOP
    FETCH mytable_cur BULK COLLECT INTO l_mytable LIMIT limit_in;
    FOR indx IN 1 .. l_mytable.COUNT
    LOOP
      DELETE FROM MYTABLE WHERE ID = l_mytable(indx);
      DBMS_OUTPUT.PUT_LINE('Deletting : '||l_mytable(indx));
    END LOOP;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Commit');
    EXIT
  WHEN mytable_cur%NOTFOUND;
  END LOOP;
  CLOSE mytable_cur;
END bulk_with_limit;
/