TABLE OF CONTENTS 1) POP QUIZ (answer at the end) 2) Oracle’s Greatest Hits 3) Oracle Professional 4) SQL Tip 5) In the News 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What is the DBA_CAPTURE data dictionary view used for? --------------------------------------------------------- 2) Oracle’s Greatest Hits --------------------------------------------------------- No, this is not a list of the top music played during the latest Oracle conference. Oracle has recently compiled a very useful list of the most popular articles, software downloads, sample code and documentation of 2004. Visit this link to find out for yourself what the hype is all about: http://www.oracle.com/technology/community/greatest_hits/2004.html --------------------------------------------------------- 3) Oracle Professional --------------------------------------------------------- In the upcoming issue of Oracle Professional, Peter Hamilton investigates the advantages to be gained from creating collections of Oracle object types and introduces a generic vector collection, which allows mixed type lists to be created and manipulated within PL/SQL applications. Steven Feuerstein explains what a GUID is, how they can be very useful, and how you can work with them inside Oracle. And Darryl Hurley continues his discussion on Oracle Streams by discussing how data first gets detained via a capture process. Here’s how you can access Oracle Professional: http://www.pinpub.com/op/ --------------------------------------------------------- 4) SQL Tip --------------------------------------------------------- This month’s tip comes from Dan Clamage (http://www.clamage.com). Occasionally, you may find yourself wishing you could take the data within a table and generate Insert statements to recreate that data. Here's a utility I wrote to demo the process. You will discover that DBMS_OUTPUT quickly overflows -- 1M bytes really isn't that much. You can convert the DBMS_OUTPUT put_lines to UTL_FILE put_lines to remove this barrier. Note I only wrote it to cover the basic data types. The program also displays some intermediate generated code. The procedure put_long_line breaks up long strings into multiple lines, to prevent DBMS_OUTPUT line overflow. SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED CREATE OR REPLACE PROCEDURE dyn_insert_extract(p_table_name IN user_tab_columns.table_name%TYPE) IS -- constants c_date_fmt CONSTANT VARCHAR2(30) := 'YYYYMMDD HH24:MI:SS'; c_table_name CONSTANT user_tab_columns.table_name%TYPE := UPPER(p_table_name); -- user-defined types TYPE typ_ref IS REF CURSOR; TYPE typ_column_name IS TABLE OF user_tab_columns.column_name%TYPE; TYPE typ_data_type IS TABLE OF user_tab_columns.data_type%TYPE; CURSOR cur_cols(p_table_name user_tab_columns.table_name%TYPE) IS SELECT c.column_name, c.data_type FROM user_tab_columns c WHERE c.table_name = p_table_name ORDER BY c.column_id; -- composite variables v_ref typ_ref; arr_column_name typ_column_name := typ_column_name(); arr_data_type typ_data_type := typ_data_type(); -- scalar variables v_stmt VARCHAR2(32767); -- dynamic DDL, reuse for complete insert stmt -- the dynamic Insert statement (reuse with ~v every data row) v_dynins VARCHAR2(32767) := 'INSERT INTO ~t ( ~c ) VALUES ( ~v );'; v_cols VARCHAR2(4000); -- column list v_sep VARCHAR2(1); -- list separator v_colsep VARCHAR2(7); -- column separator -- dynamic query statement v_dynqry VARCHAR2(32767) := 'SELECT ~c FROM ~t'; -- dynamic query's column expression, then reused for dynamic cursor results v_dynqrycol VARCHAR2(32767); BEGIN -- set default date format string to make output easier v_stmt := 'ALTER SESSION SET nls_date_format=''' || c_date_fmt || ''''; EXECUTE IMMEDIATE v_stmt; put_long_line(v_stmt || ';'); OPEN cur_cols(c_table_name); FETCH cur_cols BULK COLLECT INTO arr_column_name, arr_data_type; CLOSE cur_cols; IF (arr_column_name.COUNT > 0) THEN -- something to do -- build column list FOR i IN arr_column_name.FIRST .. arr_column_name.LAST LOOP v_cols := v_cols || v_sep || arr_column_name(i); IF (arr_data_type(i) IN ('NUMBER', 'FLOAT', 'INTEGER')) THEN v_dynqrycol := v_dynqrycol || v_colsep || 'DECODE(' || arr_column_name(i) || ',NULL,''NULL'',' || arr_column_name(i) || ')'; ELSE -- string or DATE v_dynqrycol := v_dynqrycol || v_colsep || 'DECODE(' || arr_column_name(i) || ',NULL,''NULL'',''''''''||' || arr_column_name(i) || '||'''''''')'; END IF; v_sep := ','; v_colsep := '||'',''||'; END LOOP; v_dynins := REPLACE(v_dynins, '~t', c_table_name); v_dynins := REPLACE(v_dynins, '~c', v_cols); v_dynqry := REPLACE(v_dynqry, '~t', c_table_name); v_dynqry := REPLACE(v_dynqry, '~c', v_dynqrycol); put_long_line(v_dynins); put_long_line(v_dynqry); v_dynqrycol := NULL; -- reusing for fetched column values OPEN v_ref FOR v_dynqry; LOOP FETCH v_ref INTO v_dynqrycol; EXIT WHEN v_ref%NOTFOUND; v_stmt := REPLACE(v_dynins, '~v', v_dynqrycol); put_long_line(v_stmt); END LOOP; put_long_line('-- fetched ' || v_ref%ROWCOUNT || ' rows'); CLOSE v_ref; ELSE put_long_line('No columns for table [' || c_table_name || ']?'); END IF; -- something to do? END dyn_insert_extract; / -- test SQL> create table tbl_typ (x varchar2(10), y number(10), z date); Table created. SQL> insert into tbl_typ values ('ha',1,sysdate); 1 row created. SQL> insert into tbl_typ values ('ho',2,sysdate-1); 1 row created. SQL> insert into tbl_typ values (null,3,sysdate-2); 1 row created. SQL> insert into tbl_typ values ('he',null,sysdate-4); 1 row created. SQL> insert into tbl_typ values ('hi',5,null); 1 row created. SQL> insert into tbl_typ values (null,null,null); 1 row created. SQL> commit; Commit complete. SQL> begin 2 dyn_insert_extract('tbl_typ'); 3 end; 4 / ALTER SESSION SET nls_date_format='YYYYMMDD HH24:MI:SS'; INSERT INTO TBL_TYP ( X,Y,Z ) VALUES ( ~v ); SELECT DECODE(X,NULL,'NULL',''''||X||'''')||','||DECODE(Y,NULL,'NULL',Y)||', '||DECODE(Z,NULL,'NULL',''''||Z||'''') FROM TBL_TYP INSERT INTO TBL_TYP ( X,Y,Z ) VALUES ( 'ha',1,'20040813 14:45:02' ); INSERT INTO TBL_TYP ( X,Y,Z ) VALUES ( 'ho',2,'20040812 14:45:24' ); INSERT INTO TBL_TYP ( X,Y,Z ) VALUES ( NULL,3,'20040811 14:45:44' ); INSERT INTO TBL_TYP ( X,Y,Z ) VALUES ( 'he',NULL,'20040809 14:46:05' ); INSERT INTO TBL_TYP ( X,Y,Z ) VALUES ( 'hi',5,NULL ); INSERT INTO TBL_TYP ( X,Y,Z ) VALUES ( NULL,NULL,NULL ); -- fetched 6 rows PL/SQL procedure successfully completed. SET NULL {null} SQL> select * from tbl_typ; X Y Z ---------- ---------- ----------------- ha 1 20040813 14:45:02 ho 2 20040812 14:45:24 {null} 3 20040811 14:45:44 he {null} 20040809 14:46:05 hi 5 {null} {null} {null} {null} You should be able to take the Insert statements generated and run them as-is. -------------------------------------------------------- 5) In the News -------------------------------------------------------- Carly Fiorina left her post as chief executive of Hewlett-Packard Co. in the wake of disagreements with the board over strategy and after nearly 3 years of decline. 1 industry insider went as far as to say she was a failure: "She was a woman who had the wrong ideas. She went into the PC business against Dell, and that wasn't smart. She took her hand off the throttle of the high-margin stuff [the printer business]." Don’t feel bad for Carly, as it’s reported she will walk away with a $21.1 million dollar severance package. Now that will buy a lot of Dell computers! * * * * Intel recently announced that they will be releasing the new 600-series Pentium 4s later this month, which are the first Intel desktop chips that supports 64-bit memory addressability. Their rival, Advanced Micro Devices Inc. (AMD) launched similar technology back in September 2003 targeting business users. In a related story, engineers from IBM, Sony and Toshiba recently unveiled details for a new chip that can power anything from video games to personal computers. For more information visit: http://www.theglobeandmail.com/servlet/story/RTGAM.20050207.gtcell0207/ BNStory/Technology * * * * --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- The DBA_CAPTURE view displays information about all capture processes in the database, such as the name of the process, its status, the starting System change number (SCN) from which the capture process will start to capture changes, the SCN of the last captured message, etc. This is one of the views used when you are working with Oracle’s Streams technology. This technology enables you to share data and events in a stream, which can be propagated within a database or from one database to another. It provides the capabilities needed to build and operate distributed applications, data warehouses, and high availability solutions. This new technology is discussed in full in a new multi-part series by Darryl Hurley, beginning with the February version or Oracle Professional. Visit http://www.pinpub.com/op/ for more details.