Month: May 2007

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

constraints for referential integrity

On the developpez.net forums I answered a question about referential integrity. How can you delete/update a parent row when the child exist ? SQL> create table continent( 2 name varchar2(10), 3 constraint continent_pk primary key(name)); Table created. SQL> create table country( 2 name varchar2(10), 3 continent varchar2(10), 4 constraint country_pk 5 primary key(name), 6 constraint […]

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

Export to Excel

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

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

csv part 3

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

csv format with select *

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;