how to spell 999999999 ?

begin dbms_output.put_line( to_char( timestamp ‘9999-12-31 23:59:59.999999999’, ‘FF9SP’)); end; / NINE HUNDRED NINETY-NINE MILLION NINE HUNDRED NINETY-NINE THOUSAND NINE HUNDRED NINETY-NINE Unfortunately, I could not get this in sql/10.2.0.2 select to_char( timestamp ‘9999-12-31 23:59:59.999999999’, ‘FF9SP’) X from dual; ORA-01877: string is too long for internal buffer Well, since the string is too long, let’s try with […]

ORA-01466: unable to read data – table definition has changed

I re-edited this post and it is unresolved yet. I thought it was related to system time, but apparently not 😮 SQL> create table t(x number); Table created. SQL> set transaction read only ; Transaction set. SQL> select * from t; select * from t * ERROR at line 1: ORA-01466: unable to read data […]

RAC exam

I have attend the RAC beta exam this afternoon. 181 questions in 3.5 hours, it is a lot of questions! I prepared by reading the 2-day dba RAC document. There are also some questions about Maximum Availability Architecture (MAA) with physical and logical standby. Also some question which are Linux speci I have been short […]

select from a comma-separated string

This is one question I solved today with XML on the developpez.net/forums I have a table T Un,Trois,Cinq,Six Un,Deux,Quatre Trois Sept,Huit Un,Six I want to select un,deux,trois,quatre,cinq,six,sept,huit. I could well have written a plsql function like create or replace type c as object (value varchar2(40)); / create or replace type t_c as table of c; […]

dbms_xplan and v$sql_plan

do not miss yas comment ! tested in 10.2 create or replace procedure explain_plan(hash_value number) is begin insert into plan_table select null, (select nvl(max(plan_id),0)+1 from plan_table), timestamp, remarks, operation, options, object_node, object_owner, object_name, object_alias, null, object_type, optimizer, search_columns, id, parent_id, depth, position, cost, cardinality, bytes, other_tag, partition_start, partition_stop, partition_id, other, other_xml, distribution, cpu_cost, io_cost, temp_space, […]

rename

Views: SQL> create view v1 as select * from dual; View created. SQL> rename v1 to v2; Table renamed. Tables: SQL> create table t1 as select * from dual; Table created. SQL> rename t1 to t2; Table renamed. Sequences: SQL> create sequence s1; Sequence created. SQL> rename s1 to s2; Table renamed. Synonyms: SQL> create […]

insert/update/delete/merge LOG ERRORS

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 […]

How to avoid ORA errors when dropping inexistant objects

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 […]

clob hello world

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 […]