all-IT

If you set the program to a backend (ie database), we can let the program already there to do some tasks.

A good example of how the calculation date. As in Java, a "seizure" is can be very easy for Oracle in the database query (using SQL) do.

If you want to, for example, the time difference between now and the end of the month would be determined, the following possible solution:

  , SELECT SYSDATE "today",
    "Monatsende" , LAST_DAY (SYSDATE) "month end",
    SYSDATE "Tage bis zum Monatsende" LAST_DAY (SYSDATE) - SYSDATE "days until the end of the month"
    FROM DUAL;

 Today Monatsen days until the end of the month
 ---------------------------------------
 09 30 . 09 . 09 1 29th 09th 09 30th 09th 09 1 

The only drawback is the SQL function last_day not in any database system.

If you set the program to a backend (ie database), we can let the program already there to do some tasks. A good example of how the calculation date. As in Java, a "seizure" is can be very easy for Oracle in the database query (using SQL) do. If you want to, for example, the time difference between now and the end of the month to determine would be the following approach is possible: SELECT SYSDATE "today", LAST_DAY (SYSDATE) "Month End", LAST_DAY (SYSDATE) - SYSDATE "days until the end of the month 'FROM DUAL; today Monatsen days end of the month 29.09.09 30.09.09 1 --------------------------------------- The only disadvantage is the SQL function last_day not in any database system.

gklinkmann written by \ \ tags: Oracle

One of the best features of the Oracle database is the ability to pair two of the other remote databases via a DB link.
Sun queries on remote table not a problem:

  FROM tabellenname@dblinkname; SELECT * FROM tablename @ dblinkname; 

What I still do not know that this works only as long as no praise in the table (columns of type clob or blob) are included. If it still does, you get a ORA-22 992 can not use LOB locators selected from remote tables.

According to Oracle are allowed only with remote praise namely the following statements:

  * FROM table1@remote_site; CREATE TABLE t AS SELECT * FROM table1 @ remote_site;
 * FROM table1@remote_site; INSERT INTO t SELECT * FROM table1 @ remote_site;
 lobcol = ( SELECT lobcol FROM table1@remote_site ) ; UPDATE t SET lobcol = (SELECT FROM lobcol remote_site table1 @);
 INSERT INTO table1 remote_site @ ...
 UPDATE table1 @ remote_site ...
 DELETE FROM table1 @ remote_site ... 

Other hand, are prohibited:

  table1@remote_site; SELECT FROM lobcol remote_site table1 @;
 type1 . lobattr FROM INSERT INTO SELECT lobtable type1. Lobattr FROM 
     @ table1 remote_site;
 getlength ( lobcol ) FROM table1@remote_site; . SELECT DBMS_LOB GetLength (lobcol) remote_site FROM table1 @; 

What can you do?

In praise binary (eg images) there is, in my view no other way than with the above statements (as create, update) in the local database copy.
For read access to Character praise (eg long texts), it may suffice to convert the data to the remote database using a function to the data type VARCHAR2 and encapsulate a view that uses this function to the outside. Maybe something like this:

remote database:

  CREATE OR REPLACE VIEW viw_clob_tabelle
 AS
 SELECT col1
 substr ( clobSpalte , 4000 , 1 ) clobText , Dbms_lob. Substr (clobSpalte, 4000, 1) clobText
 FROM clobTabelle; 

local database:

  clobText FROM viw_clob_tabelle@dblinkname; SELECT col1 clobText, FROM dblinkname viw_clob_tabelle @; 

However, while only the first 4000 characters of the CLOB's to be read, because that is the length limitation of data type VARCHAR2, by dbms_lob.substr is returned. But often this is sufficient.

One of the best features of the Oracle database is the ability to pair two of the other remote databases via a DB link. Sun queries on remote table not a problem: select * from tablename @ dblinkname; What I have not until now know that this works only as long as no praise in the table (columns of type clob or blob) included. If it still does, you get a ORA-22 992 can not use LOB locators selected from remote tables. According to Oracle, with praise that is remote only allows the following statements: CREATE TABLE t AS SELECT * FROM table1 @ remote_site INSERT INTO t SELECT * FROM table1 @ remote_site; UPDATE t SET (remote_site lobcol SELECT FROM table1 @) lobcol = INSERT INTO table1 @ remote_site ... UPDATE table1 @ remote_site ... DELETE FROM table1 @ remote_site ... Prohibitions are against it: SELECT FROM lobcol remote_site table1 @ INSERT INTO SELECT lobtable type1.lobattr FROM table1 @ remote_site; SELECT DBMS_LOB.GETLENGTH (lobcol) remote_site FROM table1 @; What can you do? In praise binary (eg images) there is, in my view no other way than with the above statements (as create, update) in the local database copy. For read access to Character praise (eg long texts), it may suffice to convert the data to the remote database using a function to the data type VARCHAR2 and encapsulate a view that uses this function to the outside. Maybe something like this: remote database: create or replace view viw_clob_tabelle as select col1, dbms_lob.substr (clobSpalte, 4000.1) clobText from clobTabelle; local database: select col1, clobText from dblinkname viw_clob_tabelle @; However, this method only the first 4000 characters of the CLOB read it, because that is the length limitation of VARCHAR2 data type that is returned by dbms_lob.substr. But often this is sufficient.

gklinkmann written by \ \ tags: Oracle

The Unix timestamp (which is used in many applications) and Oracle's date format is unfortunately very different. While the Unix seconds 01/01/1970 counts, starting with Oracle's calendar already 4000 BC. About. 2 times a year I need this conversion, and every time I ask myself what it was like. Today it is worth to me a blog entry.
There are different approaches to move from Unix Timestamp to Oracle Date format. Here are two choices:

  TO_DATE ( '19700101000000' , 'YYYYMMDDHH24MISS' ) SELECT TO_CHAR (TO_DATE ('19700101000000 ',' YYYYMMDDHH24MISS ')
    1094165422 , 'SECOND' ) , 'dd.mm.yyyy hh24:mi' ) datum NUMTODSINTERVAL + (1094165422, 'SECOND'), 'dd.mm.yyyy HH24: MI') date
 FROM dual;

 2004 22 : 50 02 09 2004 22.. 50 

There is no ready-made function with error handling it here .

If you ask Tom to asktom.oracle.com , so you will also find a solution for different time zones:

  - Normal
 to_date ( '01011970' , 'ddmmyyyy' ) SELECT to_char (to_date ('01011970 ',' ddmmyyyy ') 
    / 24 / 60 / 60 * 1094165422 , 'dd.mm.yyyy hh24:mi' ) FROM dual + 1/24/60/60 * 1094165422, 'dd.mm.yyyy HH24: MI') FROM dual

 2004 22 : 50 02 09 2004 22.. 50

 - With a different time zone
 new_time ( to_date ( '01011970' , 'ddmmyyyy' ) SELECT to_char (new_time (to_date ('01011970 ',' ddmmyyyy ') 
    / 24 / 60 / 60 * 1094165422 , 'GMT' , 'EDT' ) , 'dd.mm.yyyy hh24:mi' ) FROM dual + 1/24/60/60 * 1094165422, 'GMT', 'EDT'), 'dd.mm.yyyy HH24: MI') FROM dual

 2004 18 : 50 02 09 2004 18.. 50 

The Unix timestamp (which is used in many applications) and Oracle's date format is unfortunately very different. While the Unix seconds 01/01/1970 counts, starting with Oracle's calendar already 4000 BC. About. 2 times a year I need this conversion, and every time I ask myself what it was like. Today it is worth to me a blog entry. There are different approaches to move from Unix Timestamp to Oracle Date format. Here are two choices: select to_char (TO_DATE ('19700101000000 ',' YYYYMMDDHH24MISS ') + NUMTODSINTERVAL (1094165422,' SECOND '),' dd.mm.yyyy HH24: MI ') date from dual; 02/09/2004 22:50 There is no ready-made function with error handling here. If you ask Tom to asktom.oracle.com, so you will also find a solution for different time zones: - normal SELECT to_char (to_date ('01011970 ',' ddmmyyyy ') + 1/24/60/60 * 1094165422,' dd . mm.yyyy HH24: MI ') from dual 02/09/2004 22:50 - with a different time zone SELECT to_char (new_time (to_date ('01011970', 'ddmmyyyy') + 1/24/60/60 * 1094165422, 'GMT ',' EDT '),' dd.mm.yyyy HH24: MI ') from dual 02.09.2004 18:50

gklinkmann written by \ \ tags: Linux , Oracle

log4j is in the Java environment to the "quasi - standard" for grown-logging applications. Basically, anything is possible with Java. But if you work with an Oracle database, it is sometimes better performance with certain features in PL / SQL (Oracle's database programming language) to implement.
Thanks log4plsql one must not give up the familiar comfort at the output of log messages.
Continue reading »

log4j is in the Java environment to the "quasi - standard" for grown-logging applications. Basically, anything is possible with Java. But if you work with an Oracle database, it is sometimes better performance with certain features in PL / SQL (Oracle's database programming language) to implement. Thanks log4plsql one must not give up the familiar comfort at the output of log messages. The installation from the installation script is very simple. After using all of the usual methods of log4j as procedures and functions of the PLOG packages available. And the kicker is that link with a bit more configuration effort required for a complete log4j and can use. The Open Source Logging Framework log4plsql (Apache Software License) can be downloaded from sourceforge. The current version 1.1.3.2 supports all major versions of Oracle Database (including Oracle 10).

gklinkmann written by \ \ tags: Oracle