Add Sec/Min/Hour Functionality in oracle

Interval Function can be use to achieve the functionality to add or subtract Sec/Min/our intervals in Date.


SELECT SYSDATE,
       SYSDATE + (1 / (24 * 60 * 60)) "1Sec Addition",
       sysdate + interval '1' second "1SecAddition Intervalfunc",
       SYSDATE + (1 / (24 * 60)) "1Min Addition",
       sysdate + interval '1' minute "1MinAddition Intervalfunc",
       SYSDATE + (1 / 24) "1HR Addition",
       sysdate + interval '1' hour "1hourAddition Intervalfunc"
  FROM DUAL

Generating a Random Number by Using DBMS_RANDOM Function

Generating a Random Number by Using DBMS_RANDOM Function


select dbms_random.value , --- Positive Number between (0,1)
      dbms_random.value (n,m), --- Positive decimal Number between (n,m), where n,m are Numbers
      trunc(dbms_random.value (n,m)), --- returns only Integer value between (n,m)
      dbms_random.string ('A',n),  -- alpha characters only (mixed case both upper and lower)
      dbms_random.string ('p',n),  -- any printable char (ASCII subset) including Special characters
      dbms_random.string ('U',n), --upper case alpha characters only and with range of n
      dbms_random.string ('L',n), --Lower case alpha characters only and with range of n
      dbms_random.string ('X',n),  -- any alpha-numeric characters (upper case only)
      sysdate + trunc(dbms_random.value (1,n)) ------- Random dates 
from dual
connect by level <=m  --- m rows

Difference between CHR(10) Line Feed (\n) and CHR(13) Carriage Return (\r)

 CHR(13) is carriage return. CHR(10) is line feed.

They are different. Most of the editors will treat them as new line, but not all

The new line is actually based on your OS - If I am not wrong for Unix it is CHR(10).
For MAc it is CHR(13). For Windows it is combination of both.

So it all depends on, what editor, what OS - how they will interpret these characters..

This difference is viewable during you do file transfer between different OS.

Example :---

SQL> select
       replace('a,b,c,d,e',',',CHR(10)) x1,
       replace('a,b,c,d,e',',',CHR(13)) x2,
       replace('a,b,c,d,e',',',CHR(13)||CHR(10)) x3,
       replace('a,b,c,d,e',',',CHR(10)||CHR(13)) x4
   from
   dual;

X1        X2        X3            X4                                                                                                        
--------- --------- ------------- -------------                                                                                             
a         a b
c
d
e a             a                                                                                                         
b                   b             
b                                                                                                        
c                   c             
c                                                                                                        
d                   d             
d                                                                                                        
e                   e              e       

Use of CHR(0) String

Chr(0) is one of the ascii characters Where as NULL is undefined or unknown value.

SELECT ascii(chr(0))  Value FROM DUAL;

Value
-------------
0



Chr(0) is different from NULL.

SELECT DECODE(CHR(0), NULL, 'NULL', 'NOT NULL') Value from dual;

Value
--------
NOT NULL




SELECT length(chr(0)) FROM dual;

LENGTH(CHR(0))
--------------
1

SELECT length(NULL) FROM dual;

LENGTH(NULL)


You can use chr(0) to diplay a blank line with DBMS_OUTPUT.PUT_LINE.

Example

SQL> begin
dbms_output.put_line('This is first line');
dbms_output.put_line( Chr(0));
dbms_output.put_line('This is second line');
end;
/
This is first line

This is second line

PL/SQL procedure successfully completed.

Actual Usage of Date Functions

========== Extract Time Upto Miliseconds from Systimetsamp or current_timestamp===

select systimestamp ,
to_char(systimestamp,'HH24:MI:SS-FF') Systimestamp_ExtractMilisecond,
CURRENT_TIMESTAMP,
to_char(CURRENT_TIMESTAMP,'HH24:MI:SS-FF') Currtimestamp_ExtractMS from dual


=========financial year dates=============

SELECT TO_DATE ('01-APR-' || TO_CHAR (ADD_MONTHS (TRUNC (to_date(:p_from_date,'DD-MON-RRRR')),-12), 'YYYY'))  FY_START from dual

SELECT TO_DATE ('31-MAR-' || TO_CHAR (ADD_MONTHS (TRUNC (to_date(:p_from_date,'DD-MON-RRRR')),-1), 'YYYY') )  FY_end from dual  





How to get first day and last date of week, month, quarter, year in Oracle
--First day of current week(sunday)
select TRUNC(SYSDATE, 'Day') from dual;
--FIRST DAY OF CURRENT FINANCIAL YEAR
select ('01-APR-' || TO_CHAR (sysdate-1, 'YYYY'))  from dual
--First day of next week(sunday)
select TRUNC(SYSDATE+7 , 'Day') from dual;
--First day of previous week(sunday)
select TRUNC(SYSDATE-7 , 'Day') from dual;
--First day of current month
select TRUNC(SYSDATE , 'Month') from dual;
--First day of previous month
select TRUNC(TRUNC(SYSDATE , 'Month')-1 , 'Month') from dual;
--First day of next month
select TRUNC(LAST_DAY(SYSDATE)+1 , 'Month') from dual;
--First day of current year
select TRUNC(SYSDATE , 'Year') from dual;
--First day of previous year
select TRUNC(TRUNC(SYSDATE , 'Year')-1 , 'Year') from dual;
--First day of next year
select ADD_MONTHS(TRUNC(SYSDATE , 'Year'),12) from dual;
-- First Day of Current quater
select TRUNC(SYSDATE , 'Q') from dual;
--  First Day of Previous Quarter
select ADD_MONTHS(TRUNC(SYSDATE , 'Q'),-3) from dual;
--  First Day of Next Quarter
select ADD_MONTHS(TRUNC(SYSDATE , 'Q'),3) from dual;


-------first date of previous 3 months

 select ADD_MONTHS(LAST_DAY(TRUNC(TRUNC(to_date(sysdate,'DD-MON-RRRR') , 'Month')-1 , 'Month')),-3)+1 from dual


--Last day of current week(sunday)
select TRUNC(SYSDATE, 'Day')+6 from dual;
--Last day of next week(sunday)
select TRUNC(SYSDATE+7 , 'Day')+6 from dual;
--Last day of previous week(sunday)
select TRUNC(SYSDATE-7 , 'Day')+6 from dual;
--Last day of current month
select LAST_DAY(TRUNC(SYSDATE , 'Month')) from dual;
--Last day of previous month
select LAST_DAY(TRUNC(TRUNC(SYSDATE , 'Month')-1 , 'Month')) from dual;
--Last day of next month
select LAST_DAY(TRUNC(LAST_DAY(SYSDATE)+1 , 'Month')) from dual;
--Last day of current year
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Year'),11)) from dual;
--Last day of previous year
select LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE , 'Year')-1 , 'Year'),11)) from dual;
--Last day of next year
select LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE , 'Year')-1 , 'Year'),-13)) from dual;
-- Last Day of Current quater
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Q'),2)) from dual;
--  Last Day of Previous Quarter
select TRUNC(SYSDATE , 'Q')-1 from dual;
--  Last Day of Next Quarter
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Q'),5)) from dual;
 

What is the purpose of MO_GLOBAL.SET_POLICY_CONTEXT?

 To Retrieve Rows From Table / Views Or Synonym For An ORG_ID

For example View are : PO_HEADERS, AP_INVOICES

To fetch data from these View , we have to use Procedure

mo_global.set_policy_context(p_access_code,p_org_id);

This procedure has two parameters
p_access_mode
Pass a value "S" in case you want your current session to work against Single ORG_ID
Pass a value of "M" in case you want your current session to work against multiple ORG_ID's

For R12

p_org_id
Only applicable if p_access_mode is passed value of "S"

For Example:

begin

mo_global.set_policy_context('S',722);

end;

Above code will fetch only the rows belongs to org id 722

begin
mo_global.init('AR');
end;

begin

mo_global.set_policy_context('M',Null);

end;

Above code will fetch dat for multiorg's irrespective of operating units.


For 11i


begin
fnd_client_info.set_org_context(ORG_ID);

end;

Use of INSTR and SUBSTR Functions

INSTR ---------------------(string to search, search pattern [, start [,occurrence]])

SUBSTR------------------ (original string, begin [,how far])

---------------------Data Till First Space--------------------------


SELECT substr(Name,1,instr(Name,' ',1,1)-1) from hr_operating_units
 
select substr('Oracle welcomes',1,instr('Oracle welcomes',' ',1,1)-1) from dual;

select substr('Oracle welcomes you',1,instr('Oracle welcomes you',' ',1,2)-1) from dual;------------------Data Till Second Sapce