Oracle extract

Definition:
There are two EXTRACT functions available in Oracle. They both work with inputs of different types and return output of different types. 

1. The Oracle PL/SQL EXTRACT(date time) function extracts a value from a date or interval value.
2. The Oracle PL/SQL EXTRACT(XML) function is used to select a node (or set of nodes) along with its leaf nodes.

EXTRACT(date time)

The Oracle EXTRACT(Date Time) function splits a datetime or interval expression and returns the value of the specified datetime field (YEAR, MONTH, DAY from DATE and TIMEZONE_HOUR, TIMEZONE_MINUTE from TIMEZONE). It is useful while working with datetime values in tables with large volumes of data.

Example Syntax:

EXTRACT (

{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }

| { TIMEZONE_HOUR | TIMEZONE_MINUTE }

| { TIMEZONE_REGION | TIMEZONE_ABBR }

FROM { date_value | interval_value } )

Example Usage:

SELECT EXTRACT(YEAR FROM DATE '2010-01-12') FROM DUAL;



EXTRACT(YEARFROMDATE'2010-01-12')

---------------------------------

2010

SELECT EXTRACT(DAY FROM DATE '2003-08-22') FROM DUAL;



EXTRACT(DAYFROMDATE'2010-01-12')

---------------------------------

12

The SQL query below returns the month that was current (in effect) 40 days ago:

SELECT EXTRACT(MONTH FROM SYSDATE-40) FROM DUAL;

EXTRACT(xml)

The EXTRACT(XML) function is used to select a node (or set of nodes) along with its leaf nodes. It takes two parameters, object_value and XPath, and returns XMLType output. The XPath parameter can be an absolute or relative type.

Example Syntax:

EXTRACT(xml-type-instance, 'xpath')

EXTRACT(xml-type-instance, 'xpath', 'namespace')

Example Usage:

SQL> SELECT EXTRACT(OBJECT_VALUE, '/Orders/MailAddressTo')

FROM ORDERS_XML;



EXTRACT(OBJECT_VALUE,'/ORDERS/MAILADDRESSTO')

------------------------------------------------------------------------------

<MailAddressTo id="PA"><Company>Google</Company>

<Country>United States</Country>

<City>NewYork</City><State>NY</State><Zipcode>12345</Zipcode>

</MailAddressTo>



SQL> SELECT EXTRACT(OBJECT_VALUE,  './/Company')

FROM ORDERS_XML;



EXTRACT(OBJECT_VALUE,'.//COMPANY')

---------------------------------------------------------

<Company>Google</Company><Company>PSOUG</Company>

examples for extract hour, minutes, seconds

SELECT (EXTRACT (DAY FROM (end_ts-start_ts))*24*60*60+
EXTRACT (HOUR FROM (end_ts-start_ts))*60*60+
EXTRACT (MINUTE FROM (end_ts-start_ts))*60+
EXTRACT (SECOND FROM (end_ts-start_ts)))/60
FROM TS;

sql> select to_char(sysdate, 'hh24:mi:ss') "Current Time",
  2     extract(hour from to_timestamp(to_char(sysdate, 'dd-mon-yy hh24:mi:ss'))) "Hour",
  3     extract(minute from to_timestamp(to_char(sysdate, 'dd-mon-yy hh24:mi:ss'))) "Minute",
  4     extract(second from to_timestamp(to_char(sysdate, 'dd-mon-yy hh24:mi:ss'))) "Second"
  5     from dual;

 
SQL> select to_char(sysdate, 'hh24:mi:ss') "Current Time",
  2     to_char(sysdate, 'hh24') "Hour",
  3     to_char(sysdate, 'mi') "Minute",
  4     to_char(sysdate, 'ss') "Second"
  5     from dual;

Oracle SYSTIMESTAMP

Purpose

SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE.

Examples

The following example returns the system timestamp:

SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
------------------------------------------------------------------
28-MAR-00 12.38.55.538741 PM -08:00

The following example shows how to explicitly specify fractional seconds:

SELECT TO_CHAR(SYSTIMESTAMP, 'SSSSS.FF') FROM DUAL;

TO_CHAR(SYSTIME
---------------
55615.449255

The following example returns the current timestamp in a specified time zone:

SELECT SYSTIMESTAMP AT TIME ZONE 'UTC' FROM DUAL;
 
SYSTIMESTAMPATTIMEZONE'UTC'
---------------------------------------------------------------------------
08-07-21 20:39:52,743557 UTC

The output format in this example depends on the NLS_TIMESTAMP_TZ_FORMAT for the session.

Oracle Date / Time Arithmetic

If you store date and time information in Oracle, you have two different options for the column's datatype - DATE and TIMESTAMP. DATE is the datatype that we are all familiar with when we think about representing date and time values. It has the ability to store the month, day, year, century, hours, minutes, and seconds. It is typically good for representing data for when something has happened or should happen in the future. The problem with the DATE datatype is its' granularity when trying to determine a time interval between two events when the events happen within a second of each other. This issue is solved with the TIMESTAMP datatype. In order to represent the date stored in a more readable format, the TO_CHAR function has traditionally been wrapped around the date: SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "hiredate"   FROM emp; hiredate ------------------- 17.12.1980:00:00:00 20.02.1981:00:00:00 You can add and subtract number constants as well as other dates from dates. Oracle interprets number constants in arithmetic date expressions as numbers of days. For example: SYSDATE + 1 is tomorrow SYSDATE - 7 is one week ago SYSDATE + (10/1440) is ten minutes from now. Subtracting the HIREDATE column of the EMP table from SYSDATE returns the number of days since each employee was hired. SELECT '03.12.2004:10:34:24' "Now", TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate", TO_DATE('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS') - hiredate "Hired since [Days]" FROM emp; Now Hiredate Hired since [Days] ------------------- ------------------- ------------------ 03.12.2004:10:34:24 17.12.1980:00:00:00 8752.44056 You cannot multiply or divide DATE values. Oracle provides functions for many common date operations. For example, the ADD_MONTHS function lets you add or subtract months from a date. The MONTHS_BETWEEN function returns the number of months between two dates. Subtraction between Dates The trouble people get into when using the DATE datatype is doing arithmetic on the column in order to figure out the number of years, weeks, days, hours, and seconds between two dates. What needs to be realized when doing the calculation is that when you do subtraction between dates, you get a number that represents the number of days. You should then multiply that number by the number of seconds in a day (86400) before you continue with calculations to determine the interval with which you are concerned. DEFINE Today = TO_DATE('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS') SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate",        TO_CHAR(&Today,'DD.MM.YYYY:HH24:MI:SS') "Today",        trunc(86400*(&Today-hiredate))-60*(trunc((86400*(&&Today-hiredate))/60)) "Sec",        trunc((86400*(&Today-hiredate))/60)-60*(trunc(((86400*(&&Today-hiredate))/60)/60)) "Min",        trunc(((86400*(&Today-hiredate))/60)/60)-24*(trunc((((86400*(&&Today-hiredate))/60)/60)/24)) "Hrs",        trunc((((86400*(&Today-hiredate))/60)/60)/24) "Days" FROM emp; Hiredate            Today               Sec Min Hrs  Days ------------------- ------------------- --- --- --- ----- 17.12.1980:00:00:00 03.12.2004:10:34:24  24  34  10  8752 Check out the above query for a possible solution on how to extract the individual time intervals for a subtraction of two dates. The fractions could be reduced but we wanted to show all the numbers to emphasize the calculation. If you want a solution which breaks the days in years and month you can use the following query. We will use a leap year date, 01/01/2000 for example, for temporary purposes. This date will provide accurate calculation for most cases. DEFINE DateDay = 8752.44056 SELECT     TO_NUMBER(SUBSTR(A,1,4)) - 2000 years,     TO_NUMBER(SUBSTR(A,6,2)) - 01 months,     TO_NUMBER(SUBSTR(A,9,2)) - 01 days,     SUBSTR(A,12,2) hours,     SUBSTR(A,15,2) minutes,     SUBSTR(A,18,2) seconds FROM (SELECT TO_CHAR(TO_DATE('20000101','YYYYMMDD')       + &DateDay,'YYYY MM DD HH24:MI:SS') A       FROM DUAL);      YEARS     MONTHS       DAYS HO MI SE ---------- ---------- ---------- -- -- --         23         11         17 10 34 24 The new TIMESTAMP datatype One of the main problems with the DATE datatype was its' inability to be granular enough to determine which event might have happened first in relation to another event. Oracle has expanded on the DATE datatype and has given us the TIMESTAMP datatype which stores all the information that the DATE datatype stores, but also includes fractional seconds. Convert DATE datatype to TIMESTAMP datatype If you want to convert a DATE datatype to a TIMESTAMP datatype format, just use the CAST function. As you can see, there is a fractional seconds part of '.000000' on the end of this conversion. This is only because when converting from the DATE datatype that does not have the fractional seconds it defaults to zeros and the display is defaulted to the default timestamp format (NLS_TIMESTAMP_FORMAT). If you are moving a DATE datatype column from one table to a TIMESTAMP datatype column of another table, all you need to do is a INSERT SELECT FROM and Oracle will do the conversion for you. CREATE TABLE date_table (   date1      DATE,   time1      TIMESTAMP,   time2      TIMESTAMP ); INSERT INTO date_table (date1, time1, time2)   VALUES (SYSDATE,           TO_TIMESTAMP ('17.12.1980:00:00:00','DD.MM.YYYY:HH24:MI:SS'),           TO_TIMESTAMP ('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')   ); COMMIT; SELECT CAST(date1 AS TIMESTAMP) "Date" FROM date_table; Date --------------------------------------------------------------------------- 03-DEC-04 11.36.45.000000 AM The TO_TIMESTAMP function The TO_TIMESTAMP function converts a string to a timestamp. The syntax for the to_timestamp function is: TO_TIMESTAMP ( string , [ format_mask ]  [ 'nlsparam' ]  ) string is the string that will be converted to a timestamp. format_mask is optional.  This is the format that will be used to convert string to a timestamp.  The following is a list of options for the format_mask parameter  These parameters can be used in many combinations. Parameter Explanation YYYY 4-digit year MM Month (01-12; JAN = 01). MON Abbreviated name of month. MONTH Name of month, padded with blanks to length of 9 characters. DD Day of month (1-31). HH Hour of day (1-12). HH12 Hour of day (1-12). HH24 Hour of day (0-23). MI Minute (0-59). SS Second (0-59).  Formatting of the TIMESTAMP datatype Formatting of the new TIMESTAMP datatype is the same as formatting the DATE datatype. Beware while the TO_CHAR function works with both datatypes, the TRUNC function will not work with a datatype of TIMESTAMP. This is a clear indication that the use of TIMESTAMP datatype should explicitly be used for date and times where a difference in time is of utmost importance, such that Oracle won't even let you compare like values. If you wanted to show the fractional seconds within a TIMESTAMP datatype, look at the 'FF3' to only showing 3 place holders for the fractional seconds. Formatting of the TIMESTAMP datatype: SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS') "Date"   FROM date_table; Date ------------------- 12/17/1980 00:00:00 Formatting of the TIMESTAMP datatype with fractional seconds: SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS:FF3') "Date"   FROM date_table; Date ----------------------------- 12/17/1980 00:00:00:000 Subtraction of two TIMESTAMP datatypes Calculating the time difference between two TIMESTAMP datatypes is much easier than the old DATE datatype. Look at what happens when you just do the same substraction as in the above queries: SELECT SUBSTR(time1,1,30) "Time1",        SUBSTR(time2,1,30) "Time2",        SUBSTR((time2-time1),1,30) "Time1 - Time2"  FROM date_table; Time1                          Time2                          Time1 - Time2 ------------------------------ ------------------------------ --------------------------- 17-DEC-80 12.00.00.000000 AM   03-DEC-04 10.34.24.000000 AM   +000008752 10:34:24.000000 As you can see, the results are much easier to recognize, 8752 days, 10 hours, 34 minutes, and 24 seconds. This means no more worries about how many seconds in a day and all those cumbersome calculations. And therefore the calculations for getting the weeks, days, hours, minutes, and seconds becomes a matter of picking out the number by using the SUBSTR function as can be seen next: SELECT SUBSTR(time1,1,30) "Time1",        SUBSTR(time2,1,30) "Time2",        SUBSTR((time2-time1), INSTR((time2-time1),' ')+7,2) "SS",        SUBSTR((time2-time1), INSTR((time2-time1),' ')+4,2) "MI",        SUBSTR((time2-time1), INSTR((time2-time1),' ')+1,2) "HH",        TRUNC(TO_NUMBER(SUBSTR((time2-time1),1, INSTR(time2-time1,' ')))) "Days"   FROM date_table; Time1                          Time2                          SS MI HH       Days ------------------------------ ------------------------------ -- -- -- ---------- 17-DEC-80 12.00.00.000000 AM   03-DEC-04 10.34.24.000000 AM   24 34 10       8752 NEXT_DAY and LAST_DAY functions The NEXT_DAY and LAST_DAY functions can be used to calculate for example «the last Saturday in any given month». You can simply get the last day in the month, subtract 7 days from that, and then use NEXT_DAY to find the next Saturday after that one. NEXT_DAY (date, char) NEXT_DAY returns the date of the first weekday named by char that is later than date. The return type is always DATE, regardless of the datatype of date. The argument char must be a day of the week in the date language of your session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument date. Example Return the date of the next Monday after now: SELECT TO_CHAR(NEXT_DAY(sysdate,'MON'),'DD.MM.YYYY') "Next Monday from now"   FROM DUAL; Next Monday -----------  06.12.2004 LAST_DAY(date) LAST_DAY returns the date of the last day of the month that contains date. The return type is always DATE, regardless of the datatype of date. Example The following statement determines how many days are left in the current month: SELECT SYSDATE,    LAST_DAY(SYSDATE) "Last",    LAST_DAY(SYSDATE) - SYSDATE "Days Left"    FROM DUAL; SYSDATE     Last     Days Left --------- --------- ---------- 03-DEC-04 31-DEC-04         28 Get the last date of a month: SELECT LAST_DAY (TO_DATE ('02','MM')) FROM dual;  LAST_DAY --------- 29-FEB-04 Return the last Saturday of each month for a given year You can simply get the last day in the month, subtract 7 days from that, and then use NEXT_DAY to find the next Saturday after that one. DEFINE my_month = 12; SELECT TO_CHAR (   NEXT_DAY (      LAST_DAY (TO_DATE (&my_month,'MM' )) - 7,      TO_CHAR (TO_DATE ('29-01-1927', 'DD-MM-YYYY' ),'DAY')   ),'DD.MM.YYYY') "Last Saturday in December 2004" FROM dual; Last Saturday in December 2004 ------------------------------ 25.12.2004 Return the last Saturdays for the current year. SELECT TO_CHAR (   NEXT_DAY (     LAST_DAY (         ADD_MONTHS (TRUNC(SYSDATE,'Y'),ROWNUM-1))-7,         TO_CHAR (TO_DATE('29-01-1927', 'DD-MM-YYYY'),'DAY')   ), 'DD.MM.YYYY') "Last Saturdays in 2004"  FROM ALL_OBJECTS WHERE ROWNUM <= 12; Last Saturdays in 2004 ---------------------- 31.01.2004 28.02.2004 27.03.2004 24.04.2004 29.05.2004 26.06.2004 31.07.2004 28.08.2004 25.09.2004 30.10.2004 27.11.2004 25.12.2004 The "29-01-1927" is just a random date that we knew was a Saturday—any Saturday would do. This is done instead of using "SAT" in the query for international reasons, because in languages other than English, "SAT" isn't Saturday. This query should work in any language out there.

Oracle Convert datetime to number

you have to convert first to char with no spaces and then convert to number and here is the example for that

select to_number(to_char(sysdate,'hh24mi'))
from dual
;


Yasser

Oracle convert date to number:

to convert date to number you have to write this select

TO_NUMBER(TO_CHAR(d))

With this Oracle conversion the date is converted from data type char to a number.

Yasser

Forms: How To Create a Hierachical Tree form

https://sites.google.com/site/craigsoraclestuff/oracle-forms---how-to-s/forms-how-to-create-a-hierachical-tree-form

Creating a Customized Sign-on Form

Creating a Customized Sign-on Form

A customized sign-on form has been a major requirement for Oracle Forms users in most of the client sites where I have worked. A typical requirement of such a customized logon screen is the capability to choose from the various databases available for the database string. The default logon screen for Forms doesn't provide this feature. This section presents an easy solu tion that I have discovered . Figure 2.8 shows an example of how such a screen would look.

Figure 2.8. A customized sign-on form.

In this technique, you nullify the ON-LOGON trigger for the sign-on form conditionally and re-enable logging by using a call to LOGON with proper arguments, after the user completes data entry in the customized logon screen. The sign-on should be the very first form displayed in the application, even before the main menu. Invoke the sign-on form using Forms runtime in the normal manner, with username, password, and database string specified.

This design can be implemented with the following steps:

1. Create a control block named CTRL_BLK with two text items, USERNAME andPASSWORD ; a pop-up list, DATABASE_STRING ; and two push buttons , PB_LOGIN andPB_QUIT.
2. The desired functionality can be achieved by writing an ON-LOGON trigger at the form level and a WHEN-BUTTON-PRESSED trigger for the Login button. The ON-LOGON code is as follows :

   ON-LOGON 

    DEFAULT_VALUE('', 'GLOBAL.tologin'); 

    IF NAME_IN('GLOBAL.tologin') IS NULL THEN NULL;

    ELSE 

    DECLARE  ret_err_code NUMBER; 

    ret_err_text VARCHAR2(1000); 

   BEGIN  p_logon(:ctrl_blk.username,  :ctrl_blk.password,  :ctrl_blk.database_string,  ret_err_code,  ret_err_text);

    IF (ret_err_code <> 0) THEN  p_show_alert(ret_err_text); 

   RETURN; 

   END IF; 

   END;

    END IF;

   The WHEN-BUTTON-PRESSED trigger for the Login button would consist of the following code:

   BEGIN 

   ENTER; 

    IF FORM_SUCCESS THEN  LOGON(NULL, NULL, FALSE); 

    END IF; 

    END;

   The code for p_logon is as follows:

   PROCEDURE p_logon(un VARCHAR2,  pw VARCHAR2,  dcs VARCHAR2,  o_err_code OUT NUMBER,  o_err_text OUT VARCHAR2) IS  v_err_code NUMBER;  v_err_text VARCHAR2(1000);

    BEGIN 

    IF dcs IS NOT NULL THEN  LOGON(un,pw'@'dcs, FALSE);

    IF NOT FORM_SUCCESS THEN  v_err_code := DBMS_ERROR_CODE; 

    v_err_text := DBMS_ERROR_TEXT;  END IF;

    ELSE  LOGON(un,pw, FALSE);

    IF NOT FORM_SUCCESS THEN  v_err_code := DBMS_ERROR_CODE;

    v_err_text := DBMS_ERROR_TEXT; 

    END IF; 

   END IF; 

    IF v_err_code IS NULL THEN  v_err_code := -1; 

   END IF; 

    o_err_code := v_err_code;

    o_err_text := v_err_text; 

    END;

3. The logic for the Quit button is a simple call to EXIT_FORM.

A few points should be noted here:

• The use of the global variable. This is done to disable the login for the initial run form but enable subsequent logins.
• The NULL arguments to the LOGON built-in. This is done only to make the ON-LOGONtrigger fire the subsequent times.
• FORM_TRIGGER_FAILURE is not raised so that repeated clicks of the Login button behave the same as the very first click. Raising FORM_TRIGGER_FAILURE would freeze the login screen until the user corrects the error. This is fine but would defy the functionality of the default Oracle Forms login screen. The customized form would behave exactly as the default one would, but with more features.

On logon failure, the DBMS_ERROR_TEXT captures the error from the database side, and the procedure p_show_alert throws up this message in the form of an alert. For example, in the case of an invalid username or password, you would receive the alert shown in Figure 2.9.

Figure 2.9. This alert message is displayed in the case of an invalid username or password.

The code for the procedure p_show_alert is as follows:

PROCEDURE p_show_alert  (ip_errm VARCHAR2)  IS  alert_id ALERT; 

alert_button NuMBER;

 error_msg VARCHAR2(32767);

 BEGIN 

alert_id := FIND_ALERT('ALERT_ERROR'); 

 error_msg := ip_errm; 

SET_ALERT_PROPERTY(alert_id, ALERT_MESSAGE_TEXT, error_msg);  alert_button := SHOW_ALERT(alert_id);

 END p_show_alert;

An alternative way of implementing a customized sign-on form without using an ON-LOGONtrigger is to LOGOUT first and then LOGON. Then, the only trigger required is WHEN-BUTTON-PRESSED for the Login button, with code modified as follows:

BEGIN 

 ENTER;

 IF FORM_SUCCESS THEN  LOGOUT;

 p_logon(:ctrl_blk.username,  :ctrl_blk.password,  :ctrl_blk.database_string,  ret_err_code,  ret_err_text); 

 IF (ret_err_code <> 0) THEN  p_global_alert(NULL, ret_err_text, 'E', FALSE);

 Return; 

END IF; 

 END IF; 

END;