Author: Laurent Schneider

the following works only on 10gR2 Table PRODUCTCATEGORY: CATEGORY (PK) Fruit Fish Table TMP_PRODUCT: PRODUCT CATEGORY Banana Fruit Chicken Meat Table PRODUCT: PRODUCT (PK) CATEGORY (FK) Now I want to load product SQL> insert into product select * from tmp_product; insert into product select * from tmp_product * ERROR at line 1: ORA-02291: integrity constraint […]

With merge you can insert, update or both I want to import changes from one table in another table. Table T1: ID NAME 1 Apple 2 Orange Table T2: ID NAME 1 Pineapple 3 Lemon To insert the missing row from T2 into T1, I could need the following subquery with INSERT. insert into t1 […]

One more neat solution from Michaels about exporting the data to Excel in this otn post set feed off markup html on spool on alter session set nls_date_format=’YYYY-MM-DD’; spool emp.xls select * from emp; spool off set markup html off spool off And it perfectly opens in Excel. No hassle with separator, no time lost […]

There were a similar question in the otn forums today. Ok, when I have to run a script in production, the operators complain about errors like ORA-00942 table or view does not exist. Of course I can provide some documentation to explain what can be ignored, but then they then tend to ignore all ORA […]

write to a clob SQL> var x clob SQL> begin 2 dbms_lob.createtemporary(:x,true); 3 dbms_lob.writeappend(:x,12,’Hello World!’); 4 end; 5 / PL/SQL procedure successfully completed. SQL> print x X ————————————————– Hello World! read from a clob SQL> var c varchar2(10) SQL> var n number SQL> exec :n := 5 /* read 5 characters, if possible */ PL/SQL […]

In part 1, I tried with pure SQL+XML, but empty columns were missing. In Part 2, I had a mix between PL/SQL and XML functions. Now I would like to publish the solution Vadim Tropashko posted in the OTN Forums. It is a NO XML pipelined function. I did a bit formatting in it. And […]

If you want to be the very first Application Server OCP you can register today on 2test.com for the 1Z1-312 beta exam. The first possible date seems to be May 29th, 2007

Special thanks to Tom for pointing and Michaels for fixing the missing manager of King in my previous post : csv format with select Ok, here is a my PL/SQL table function. create or replace type tv as table of varchar2(4000); / create or replace function CSV(sqlQuery varchar2) return tv pipelined is ctx dbms_xmlgen.ctxhandle; begin […]

I have been waiting for three years for the OCM 10g upgrade certification. Still waiting… OCM DBA 10g Upgrade Gavin just posted about the Oracle Expert Program The beta phase has started for 1Z1-046 Managing Oracle on Linux for DBAs and 1Z1-048 Administering Real Application Clusters. Those exams will lead to Linux and RAC Certified […]

One more trick with xml. I want to get a semi-column separated format without having to specify the columns alter session set nls_date_format=’YYYY-MM-DD’; Session altered. select regexp_replace(column_value,’ *<[^>]*>[^>]*>’,’;’) from table(xmlsequence(cursor(select * from emp))); ;7369;SMITH;CLERK;7902;1980-12-17;800;20; ;7499;ALLEN;SALESMAN;7698;1981-02-20;1600;300;30; ;7521;WARD;SALESMAN;7698;1981-02-22;1250;500;30; ;7566;JONES;MANAGER;7839;1981-04-02;2975;20; ;7654;MARTIN;SALESMAN;7698;1981-09-28;1250;1400;30; ;7698;BLAKE;MANAGER;7839;1981-05-01;2850;30; ;7782;CLARK;MANAGER;7839;1981-06-09;2450;10; ;7788;SCOTT;ANALYST;7566;1987-04-19;3000;20; ;7839;KING;PRESIDENT;1981-11-17;5000;10; ;7844;TURNER;SALESMAN;7698;1981-09-08;1500;0;30; ;7876;ADAMS;CLERK;7788;1987-05-23;1100;20; ;7900;JAMES;CLERK;7698;1981-12-03;950;30; ;7902;FORD;ANALYST;7566;1981-12-03;3000;20; ;7934;MILLER;CLERK;7782;1982-01-23;1300;10;

When you see something like select * from t where x = :1 you may wonder what is :1 Ok, here is a quick join I tested in 10gR2 SQL> var y varchar2(255) SQL> exec :y:=’SCOTT’ PL/SQL procedure successfully completed. SQL> select job from emp where ename=:y; JOB ——— ANALYST SQL> select sql_text,name,value_string,datatype_string 2 from […]

I am data-modeling those days. Each table has it own description table (yes, it is Java). To keep it simple, FRUIT[id,description] and MEAT[id,description] (35 of them right now). There must be one table which contains all descriptions in it GLOBAL[type,id,description]. I wish to preserve the data quality. Both the little (FRUIT,MEAT) and the big (GLOBAL) […]

How do i store the counts of all tables … My answer to the question above using dbms_xmlgen SQL> select 2 table_name, 3 to_number( 4 extractvalue( 5 xmltype( 6 dbms_xmlgen.getxml(‘select count(*) c from ‘||table_name)) 7 ,’/ROWSET/ROW/C’)) count 8 from user_tables; TABLE_NAME COUNT —————————— —— DEPT 4 EMP 14 BONUS 0 SALGRADE 5

I just downloaded and installed the Critical Patch Update April 2007 $ lsnrctl stop … The command completed successfully $ sqlplus / as sysdba SQL> shutdown immediate … ORACLE instance shut down. SQL> quit $ cd /tmp/5901891 $ ORACLE_HOME/OPatch/opatch apply … Finish at Wed Apr 18 10:28:17 CEST 2007 $ lsnrctl start … The command […]

Anagram Help – Oracle9i Enterprise … I just posted a solution on the OTN forums where I am using a regular table function and a recursive procedure to generate anagrams select * from table(anagram.f(‘ABC’)); COLUMN_VALUE ———— ABC ACB BAC BCA CAB CBA

If you want to keep some backups forever, you maybe tried RMAN> backup database keep forever logs; Starting backup at 13.04.2007 13:58:04 … backup will never be obsolete archived logs required to recover from this backup will expire when this backup expires … Finished backup at 13.04.2007 13:58:23 but the problem is that the archivelogs […]

You cannot have more than 1000 columns in a view, but what about a query? I tried select 1,2,3,4,….,10000 from dual; it seems to work fine. However, when increasing, I am facing various errors : select 1,2,3,4,….,17000 from dual; * ERROR at line 1: ORA-00600: internal error code, arguments: [15201], [], [], [], [], [], […]

Imagine you have a view and you want to have a parameter in your view. You cannot have a bind variable in your view. But you could have a function which return a package variable. And this package variable could be set manually for your session Here we go SQL> create or replace package p […]

In Solaris, the ps output is truncated, so if you have a command with many long parameters, you will not see them all. If you are interested to see the parameters of a java program, /usr/bin/ps -ef will not give you the expected output $ ps -ef | grep java ldapusr 10744 10692 0 09:50:23 […]

First you need to install OID. Check the Installation Guide, the Doc and download the Software. If you do not need the dbconsole, stop it (emctl stop dbconsole) and remove the oracle_home/hostname_sid directory Once you have a running OID, test it with ldapsearch. For this workshop, I use two servers and two usernames. Having the […]

In case you do not know it yet, having a bitmap on columns like GENDER(male/female) is a very bad practice in OLTP, because each insert does lock the whole table create table t(name varchar2(10), gender varchar2(10)); create bitmap index bi on t(gender); +——————————–+ +——————————–+ | Session 1 | | Session 2 | +——————————–+ +——————————–+ | […]

I have been asked yesterday how to read and write blobs in the database. With java : read from an input stream InputStream myBlobInputStream = connection . createStatement() . executeQuery(“select myBlob from t”) . getBlob(1) . getBinaryStream(); write to an output stream OutputStream myBlobStream = connection . createStatement() . executeQuery(“select myBlob from t for update”) […]

Maybe you have a firewall in your company, only your PC can access the production server, only you have the root password in prod, and your company spend a lot of money in security, but you dare using xhost +. This is a huge security hole, because it gives anyone access to your X resources, […]

Today I configured my database to identify users with certificates. Check my previous post listener with tcps to find out how to configure a listener with SSL, which is a requisite. Ok, I have a listener.ora and a tnsnames.ora with SSL. I do not need a sqlnet.ora, the default values work. listener.ora LISTENER= (DESCRIPTION_LIST= (DESCRIPTION= […]

yesterday I wrote howto listener ssl. To check the protocol : SQL> select sys_context( ‘USERENV’,’NETWORK_PROTOCOL’) PROTOCOL from dual; PROTOCOL ——– tcps

How can you use SSL to encrypt your network traffic? Here is how I did it. Install Oracle Certification Authority 10.1.4 you need a CA to approve a certification request Install Oracle Database 10gR2 Enterprise Edition with Advanced Security Options Start Wallet Manager from Database Oracle Home, start $ORACLE_HOME/bin/owm create a new Wallet define a […]

Check the latest issue on oracle.com/oramag

emgrid 10.2.0.3 is not the second patchset of 10gR2, it is Oracle Enterprise Manager Grid Control Release 3. Well, it is still called a patch set and must be applied to 10.2.0.1 or 10.2.0.2. But it is a new release ! You can download the soft and the doc on otn http://www.oracle.com/technology/software/products/oem/index.html Out of the […]

My customer wants to receive alerts if a database has not been backed up (either unsuccessful backup or no backup) for ages. As the customer have Oracle Enterprise Manager Grid Control 10gR2, I started using User Defined Metric (UDM) yesterday. Thanks Troy for his comment at OEM Generic Service I would do 2 checks, backup […]

The critical patch update for january is released. However, not for every version. Already in CPU Oct 2006, many versions were delayed. The release date of 10gR1 Tru64 for example was Nov 15, but the note has not been updated for months. 9.2.0.8 Linux was released so late (29 Dec), that the CPU2006OCT-9208 is considered […]

In the Critical Patch Update 2007 January Pre-Release Announcement, you may wonder why there is a 8.1.7.4 CPU patch. The reason is, Oracle 8.1.7.4 is still supported for some application supported by Oracle like Siebel, E-Business and Clinical. Have a look at Metalink Note 148054.1