https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions026.htm
SELECT CONCAT(CONCAT(””,’dec15090300275@visayas.com’),””) FROM DUAL;
‘dec15090300275@visayas.com’
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; /
cd $ORACLE_HOME/demo/schema
SELECT * FROM ( SELECT ord.*, row_number() over (ORDER BY ord.LOCATIONID ASC) line_number FROM TBLMLOCATION ord ) WHERE line_number BETWEEN 1 AND 10 ORDER BY line_number;
http://javarevisited.blogspot.in/2012/12/oracle-pagination-sql-query-example-for-java.html