https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions026.htm
SELECT CONCAT(CONCAT(””,’dec15090300275@visayas.com’),””) FROM DUAL;
‘dec15090300275@visayas.com’
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 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
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;
/
