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

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

csv with XML revisited…

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

Oracle Certification

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

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;

v$sql and bind variable

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