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; […]
Month: May 2007
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 […]
MERGE syntax
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 […]
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 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 […]
1Z1-312 Oracle Application Server 10g: Administration II
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
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 […]