Oracle : number check in database

if ( (LENGTH(TRIM(TRANSLATE(contentcode, ‘+0123456789′,’ ‘))) is not null) or (LENGTH(TRIM(TRANSLATE(otherfield, ‘+0123456789′,’ ‘))) is not null)) then
dbms_output.put_line(‘contentcode or otherfield is invalid’);

Advertisements

oracle : external table

1. in browser type url ip-Of-Server:1158/em/console/

1. in browser type url ip-Of-Server:1158/em/console/
2. login there
3. go to Schema-> Directory Objects
4. click the “create” Button
5. fill in the form

6. * Name	DIR_WLIST
   * Path	/tmp/test_v_dir

   where Name= name of virtual directory and Path= path to the directory

7. now thru ftp client (or putty ) create a file test.txt in /tmp/test_v_dir/
8. then go to sqlplus
9. type in follwing query
10. create table XTERN_test( student_name varchar2(100) )
    organization external (
    type oracle_loader
    default directory DIR_WLIST
    access parameters (
    records delimited by newline
    )
    location (‘test.txt’)
    )
    reject limit unlimited;

Time difference between two date columns

Solution 1

SQL> CREATE TABLE dates (date1 DATE, date2 DATE);

Table created.

SQL>
SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1);

1 row created.

SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/24);

1 row created.

SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/60/24);

1 row created.

SQL> SELECT (date1 - date2) FROM dates;

DATE1-DATE2
-----------
          1
 .041666667
 .000694444

Solution 2

SQL> SELECT floor(((date1-date2)*24*60*60)/3600)
2 || ' HOURS ' ||
3 floor((((date1-date2)*24*60*60) -
4 floor(((date1-date2)*24*60*60)/3600)*3600)/60)
5 || ' MINUTES ' ||
6 round((((date1-date2)*24*60*60) -
7 floor(((date1-date2)*24*60*60)/3600)*3600 -
8 (floor((((date1-date2)*24*60*60) -
9 floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60) ))
10 || ' SECS ' time_difference 11 FROM dates;
TIME_DIFFERENCE
--------------------------------------------------------------------------------
24 HOURS 0 MINUTES 0 SECS
1 HOURS 0 MINUTES 0 SECS 0
HOURS 1 MINUTES 0 SECS.

SQL> SELECT (date1 - date2) FROM dates;

DATE1-DATE2
-----------
          1
 .041666667
 .000694444

Solution 3

SQL> SELECT to_number( to_char(to_date('1','J') +
  2         (date1 - date2), 'J') - 1)  days,
  3         to_char(to_date('00:00:00','HH24:MI:SS') +
  4         (date1 - date2), 'HH24:MI:SS') time
  5   FROM dates;

      DAYS TIME
---------- --------
         1 00:00:00
         0 01:00:00
         0 00:01:00

Solution 4

NUMTODSINTERVAL: This function is new to Oracle 9i. It takes two arguments numtodsinterval(x,c) where x is a number and c is a character string denoting the units of x. Valid units are ‘DAY’, ‘HOUR’, ‘MINUTE’ and ‘SECOND’.

This function converts the number x into an INTERVAL DAY TO SECOND datatype.

SQL> select numtodsinterval(date1-date2,'day') time_difference from dates;

TIME_DIFFERENCE
----------------------------------------------------------------
+000000001 00:00:00.000000000
+000000000 01:00:00.000000000
+000000000 00:01:00.000000000

Solution 5

this works for both Date and timestamp columns

CREATE OR REPLACE FUNCTION datediff
( time1 TIMESTAMP,  time2 TIMESTAMP )
-- RETURN NUMBER
RETURN number
AS sec NUMBER;

BEGIN

SELECT (extract(DAY FROM time2-time1)*24*60*60)+
(extract(HOUR FROM time2-time1)*60*60)+
(extract(MINUTE FROM time2-time1)*60)+
extract(SECOND FROM time2-time1)
into sec FROM dual;

RETURN sec;

END;

How to understand SQL joins

SQL is a functional language. Try to think of a SELECT statement as a function. That is, a mathematical function, or mapping, which — this is important — maps an input to an output. When you select data from a table, think of the table as a source. Data streams out of the table. If it helps you, think of a little grinding cog icon. Then it streams out of the cog onto your screen as a familiar tabular result set. The cog is the SELECT statement, the function. It transforms the data. Maybe it just passes it straight through, but it really is a mapping of input to output. (By the way, if you take this approach when programming in XSLT or LISP, you will grasp things much more easily.)

A join is a SELECT statement with multiple data sources. The data streams from those sources into your cog icon, and a single stream flows out again. A SELECT statement always has one and only one output. (Why? Think of a function… think back to your math classes). Joins are functions that perform matching between data streams. The matching is necessary to merge the multiple input streams into a single output.

Let’s look at two tables of data, apples and oranges.

apples

Variety	Price
Fuji	5.00
Gala	6.00

oranges

Variety	Price
Valencia	4.00
Navel	5.00

Here is an example SELECT statement:

select apples.Variety, oranges.Price from apples 
 inner join oranges on apples.Price = oranges.Price

Here is (conceptually) what happens when we join these tables:

1. Choose a left-hand table (the first table in the SELECT statement).
2. For each row in the right-hand table, take the entire left-hand table and stack its rows next to the row in the right-hand table.
   

   apples and oranges

   Variety	Price	Variety	Price
   Fuji	5.00	Valencia	4.00
   Gala	6.00
   Fuji	5.00	Navel	5.00
   Gala	6.00

3. Fill in the missing rows in the right-hand table by duplicating them into the empty spaces.
   

   apples and oranges

   Variety	Price	Variety	Price
   Fuji	5.00	Valencia	4.00
   Gala	6.00	Valencia	4.00
   Fuji	5.00	Navel	5.00
   Gala	6.00	Navel	5.00

4. The result is a large table containing the cross-product or Cartesian productof the two data sets. Now satisfy the matching criteria by applying them as a predicate to each row in this new data set. If the predicate is true for the row, include it, otherwise exclude it. The result contains a single row:
   

   apples and oranges

   Variety	Price	Variety	Price
   Fuji	5.00	Navel	5.00

5. Now choose only the desired columns from the result:
   

   apples and oranges

   Variety	Price
   Fuji	5.00

This may not be what a given query optimizer really does to execute a join, but the result is the same regardless of the algorithm. If a query optimizer does something different, it is for efficiency, not correctness. Every join always involves a cross product followed by choosing the desired data from the result.