One is All You Need

 Overview

In this example, we will demonstrate how Ajax can be use to efficiently populate a select list item in an Apex page on the fly “at run time”. Of course, this can be also done by using some simple JavaScript code BUT Ajax will be more efficient because it does not require reloading the whole Apex page every time the JavaScript event is fired. On the other hand, using just JavaScript will require the entire Apex page to reload, which is not a good practice in today’s dynamic world.

Imagine an Apex form that contains 100 items (Text fields, Select lists, Radio buttons, Check boxes), and that there is a JavaScript event defined for one item. The event dynamically sets the value of another item in that form. Every time this event is fired, Apex will need to reload the entire form and re-populate all fields with their old values (if any); this can be time consuming and may also cause some issues “side effects”.

 Example:

Consider the following sample table that basically contains data about movies, and categorize movies into different Genres.

	MOVIE_ID	GENRE_ID	GENRE	MOVIE_NAME
	1	1	Action	Romeo Must Die
	2	2	Comedy	Meet Dave
	3	2	Comedy	Kit Kittredge an American Girl
	4	1	Action	Wanted
	5	3	Drama	Hancock
	6	1	Action	Dark Knight

     row(s) 1 - 6 of 6

 Requirements:

 We need to create a simple Apex page with the following items:
- A select list that contains all the movies (Movie_name)
- A select list that contains the movies’ genres (Genre)
- A text field that represents the genre ID ( example: 1,2 or 3) .

The behavior of these items should be as follows:
- When the value of the Genre select list is changed, the Movies’ select list is auto populated with the corresponding movie(s) that belong to the selected genre.
-When the value of the Genre ID Text field is changed (1,2 or 3), the Movies’ select list is auto populated with the corresponding movie(s) that belong to the selected genre ID.

Implementation: 

1) Create the Genre ID text field (P1_GENRE_ID) and add the following code to the HTML Form Element
    Attributes:

     onKeyUp=“get_List_XML (this,’ P1_MOVIE_LIST ‘)”  

2) Create the Genre select list (P1_GENRE_LIST)  and add the following code to the HTML Form Element   
       Attributes:    
    
     onchange=“get_List_XML (this,’P1_MOVIE_LIST‘)”   

    And this code to the list of values definition:

    select distinct  genre display_val,genre_id return_val
    from movies

 3) Create the Movies select list (P1_MOVIE_LIST) and add the following code to the list
    of values definition:

    select movie_name, movie_id
   from movies
   where genre_id= :P1_GENRE_ID

4) Create an On Demand Process (POPULATE_LIST_XML) as follows:

declare 
l_counter number;  
l_o_name  varchar2(2000);  
begin  
    owa_util.mime_header(’text/xml’, FALSE );   
    htp.p(’Cache-Control: no-cache’);   
    htp.p(’Pragma: no-cache’);   
    owa_util.http_header_close;  
    htp.prn(’<select>’);  
     for rec in (select “MOVIES”.”GENRE” as “GENRE”,   
      “MOVIES”.”MOVIE_NAME” as “MOVIE_NAME”,   
      “MOVIES”.”MOVIE_ID” as “MOVIE_ID”   
       from “MOVIES” “MOVIES” 
       where “MOVIES”.”GENRE_ID” = :TEMP_ITEM)  
      loop  
       htp.prn(’<option value=”‘ || rec.movie_id || ‘”>’ || rec.movie_name || ‘</option>’);  
     end loop;   
      htp.prn(’</select>’);  
end;

5) Add the following JavaScript code to the source of any exiting HTML region in the page:

 <center><b>This example demonstrates the use of Ajax to dynamically populate a select list on the fly.</center><br>

Type in the Genre ID in the Genre ID text field or select a Genre from the Genre select list <br> to automatically populate the Movies list (( without re-loading this page! ))

<br><br>
<script language=”JavaScript1.1″ type=”text/javascript”>   
 function get_List_XML (source_item,target_item){    
    var v_Target = html_GetElement(target_item);
    var v_Source = html_GetElement(source_item);
    var ajaxResult = new htmldb_Get
    (null,&APP_ID.,’APPLICATION_PROCESS=POPULATE_LIST_XML’,0);  
    ajaxResult.add(’TEMP_ITEM’,source_item.value);  

    var v_result_xml = ajaxResult.get(’XML’);  
    if(v_Source == document.getElementById(’P1_GENRE_ID’) )
    document.getElementById(’P1_GENRE_LIST’).selectedIndex=0;
    else
    document.getElementById(’P1_GENRE_ID’).value=”;

    if(v_result_xml && v_Target){
    var options_Contents = v_result_xml.getElementsByTagName(”option”);
    v_Count = options_Contents.length;
    v_Target.length = 0;     for(var i=0;i<v_Count;i++) {
      var v_opt_xml = v_result_xml.getElementsByTagName(”option”)[i];   
      writeToSelectList (v_Target, v_opt_xml.getAttribute
      (’value’),v_opt_xml.firstChild.nodeValue)   }   
    }   
 }  

   function writeToSelectList(target_item, option_val, option_content) {   
    var v_Opt = document.createElement(”option”);   
    v_Opt.option_value = option_val;  

    if(document.all){ 
        target_item.options.add(v_Opt);   
        v_Opt.innerText = option_content;   
     } else {   
       v_Opt.appendChild(document.createTextNode(option_content));   
       target_item.appendChild(v_Opt);   
    }   
  }  
</script>

You can run the Apex page for this example by visiting my online Apex account. 

I got a little press in the July/August 2008 edition of Oracle magazine about my presentation at Collaborate and I thought it would be prudent to shamelessly refer you to my presentation. Additionally, I will be presenting this same topic at the Northern California Oracle Users Group Summer Conference on August 21 in San Ramon.

Aces in the Hole

Greg

Greg Pike

Step by Step Guide for Installing a Data Guard/ Data Guard Broker Environment

This focuses on setting up a test environment on a single node. The DBA can adapt this to set up a production environment.

By Roger Lenihan

Step 1:

Install an Oracle database and validate it is in Archive log Mode.

If Archive log is disabled enable it before continuing.

SQL> archive log list

Database log mode             Archive Mode

Automatic archival            Enabled

Archive destination           C:\oracle\product\10.2.0\archive

Oldest online log sequence    66

Next log sequence to archive  68

Current log sequence          68

Step 2:

Enable force logging mode.

SQL> alter database force logging;

Database altered.

Step 3:

Identify all the datafiles to copy for a cold back up or do an RMAN backup.

Since this is test environment I will shutdown the database and do a cold backup.

SQL> select name from v$datafile;

NAME

———————————————————————-

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\EXAMPLE01.DBF

SQL>

Step 4:

In a windows environment create the service for the standby database. You will also have to create the folders and environment for the standby database.

C:\>oradim -new -sid stby -intpwd teststby -startmode manual

C:\oracle\product\10.2.0\oradata\teststby – Create this folder

C:\oracle\product\10.2.0\admin\teststby – Create this folder and all the folders underneath. Like bdump, udump, etc. Or just copy the production one.

Note: Since this is a dataguard environment on a single test node I do not have to install all the Oracle software again.

Step 5:

Shutdown Primary database and move the datafiles

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Move the datafiles from step 3 into the oradata standby environment.

C:\oracle\product\10.2.0\oradata\test

C:\oracle\product\10.2.0\oradata\teststby

Step 6:

Create a standby control file and start the database

SQL> startup mount;

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS

    ‘ C:\oracle\product\10.2.0\oradata\teststby\teststby.ctl’;

Database altered.

SQL> alter database open;

Step 7:

Create the Primary and Standby init.ora/spfile to facilitate Dataguard.

SQL> CREATE PFILE= ‘C:\oracle\product\10.2.0\testinit.ora’

from spfile;

File created.

Primary Database:

Make the corresponding changes: 

*.aq_tm_processes=1

*.background_dump_dest='C:\oracle\product\10.2.0/admin/test/bdump'

*.compatible='10.2.0.1.0'

*.control_files='C:\oracle\product\10.2.0/oradata/test/\control01.ctl'

*.core_dump_dest='C:\oracle\product\10.2.0/admin/test/cdump'

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='test'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'

*.fast_start_mttr_target=300

*.instance_name='test'

*.db_unique_name=’test’

*.job_queue_processes=10

*.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(test,teststb)’

*.LOG_ARCHIVE_DEST_1=’location=C:\oracle\archive arch mandatory
valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=test’

*.LOG_ARCHIVE_DEST_2=’service=teststby lgwr sync affirm net_timeout=30
valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=teststby’

*.log_archive_dest_state_1=’ENABLE’

*.log_archive_dest_state_2=’ENABLE’

*.open_cursors=300

*.pga_aggregate_target=25165824

*.processes=150

*.query_rewrite_enabled='FALSE'

*.remote_login_passwordfile='EXCLUSIVE'

*.shared_pool_size=50331648

*.sort_area_size=524288

*.star_transformation_enabled='FALSE'

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_retention=10800

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='C:\oracle\product\10.2.0/admin/test/udump'

*.sga_target=611319808

*.fal_server=teststby

*.fal_client=test

*.db_file_name_convert=’C:\oracle\product\10.2.0\oradata\teststby’,
‘C:\oracle\product\10.2.0\oradata\test’

*.log_file_name_convert=’C:\oracle\product\10.2.0\oradata\teststby’,
‘C:\oracle\product\10.2.0\oradata\test’

Standby Database:

Create a standby init.ora from a copy of the primary one and make the necessary changes below.

*.aq_tm_processes=1

*.background_dump_dest=’C:\oracle\product\10.2.0/admin/teststby/bdump’

*.compatible=’10.2.0.1.0′

*.control_files=’C:\oracle\product\10.2.0/oradata/teststby/\control01.ctl’

*.core_dump_dest=’C:\oracle\product\10.2.0/admin/teststby/cdump’

*.db_domain=”

*.db_file_multiblock_read_count=16

*.db_name=’test’

*.DB_UNIQUE_NAME=’teststby’

*.dispatchers=’(PROTOCOL=TCP) (SERVICE=testXDB)’

*.fast_start_mttr_target=300

*.instance_name=’teststby’

*.job_queue_processes=10

*.LOG_ARCHIVE_DEST_1=’location=C:\oracle\archive2 arch mandatory valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=teststby’

*.LOG_ARCHIVE_DEST_2=’service=test lgwr sync affirm net_timeout=30 valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=test’

*.log_archive_dest_state_1=’ENABLE’

*.log_archive_dest_state_2=’ENABLE’

*.open_cursors=300

*.pga_aggregate_target=25165824

*.remote_login_passwordfile=’EXCLUSIVE’

*.processes=150

*.query_rewrite_enabled=’FALSE’

*.shared_pool_size=50331648

*.sort_area_size=524288

*.star_transformation_enabled=’FALSE’

*.timed_statistics=TRUE

*.undo_management=’AUTO’

*.undo_retention=10800

*.undo_tablespace=’UNDOTBS1′

*.user_dump_dest=’C:\oracle\product\10.2.0/admin/teststby/udump’

*.sga_target=611319808

*.standby_archive_dest=’location=c:\oracle\product\10.2.0\archive’

*.db_file_name_convert=’C:\oracle\product\10.2.0\oradata\test’,

‘C:\oracle\product\10.2.0\oradata\teststby’

*.log_file_name_convert=’C:\oracle\product\10.2.0\oradata\test’,

‘C:\oracle\product\10.2.0\oradata\teststby’

*.standby_file_management=AUTO

*.log_archive_config=’DG_CONFIG=(test,teststby)’

*.service_names=teststby

*.fal_server=test

*.fal_client=teststby

Step 8:

Convert the control file created from step 6 to the name and location of control file parameter in the new standby init.ora.

C:\oracle\product\10.2.0\oradata\teststby\teststby.ctl

to

C:\oracle\product\10.2.0\oradata\teststby\control01.ctl

Step 9:

Configure the Listener.ora file. Since the example is on the same node I have both the standby and primary in the listener.ora. If this was a production environment you would create the listener for primary and standby separate.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = RLLAPTOP01)(PORT = 1521))

)

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = C:\oracle\product\10.2.0)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = test)

(ORACLE_HOME = C:\oracle\product\10.2.0)

(SID_NAME = test)

)

(SID_DESC =

(GLOBAL_DBNAME = teststby)

(ORACLE_HOME = C:\oracle\product\10.2.0)

(SID_NAME = teststby)

)

(SID_DESC =

(GLOBAL_DBNAME = teststby_DGMGRL)

(ORACLE_HOME = C:\oracle\product\10.2.0)

(SID_NAME = teststby)

)

(SID_DESC =

(GLOBAL_DBNAME = test_DGMGRL)

(ORACLE_HOME = C:\oracle\product\10.2.0)

(SID_NAME = test)

)

)

Note: The DGMGRL entries are for the Data Guard Broker to be able to start and stop the databases on switchovers.

Step 10:

Stop and restart the listener

C:\>lsnrctl stop

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 30-MAR-2008 10:40

:55

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))

The command completed successfully

C:\>lsnrctl start

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 30-MAR-2008 10:41

:32

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Starting tnslsnr: please wait…

TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production

System parameter file is C:\oracle\product\10.2.0\db_1\network\admin\listener.or

a

Log messages written to C:\oracle\product\10.2.0\db_1\network\log\listener.log

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc

)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rllaptop01)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ

ction

Start Date 30-MAR-2008 10:41:34

Uptime 0 days 0 hr. 0 min. 1 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File C:\oracle\product\10.2.0\db_1\network\admin\listener.o

ra

Listener Log File C:\oracle\product\10.2.0\db_1\network\log\listener.log

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rllaptop01)(PORT=1521)))

Services Summary…

Service “PLSExtProc” has 1 instance(s).

Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…

Service “test” has 1 instance(s).

Instance “test”, status UNKNOWN, has 1 handler(s) for this service…

Service “test_DGMGRL” has 1 instance(s).

Instance “test”, status UNKNOWN, has 1 handler(s) for this service…

Service “teststby” has 1 instance(s).

Instance “teststby”, status UNKNOWN, has 1 handler(s) for this service…

Service “teststby_DGMGRL” has 1 instance(s).

Instance “teststby”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

Step 11:

Add the standby database to the tnsnames.ora file.

teststby =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = RLLAPTOP01)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = teststby)

)

)

Step 12:

Shutdown the primary database and start again with the new init.ora.

Convert it back to a spfile.

C:\> set oracle_sid=test

C:\> sqlplus

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup pfile=C:\oracle\product\10.2.0\testinit.ora;

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 83889028 bytes

Database Buffers 520093696 bytes

Redo Buffers 7135232 bytes

Database mounted.

Database opened.

SQL> create spfile from pfile=c:\oracle\product\10.2.0\testinit.ora;

Database altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> Startup;

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 83889028 bytes

Database Buffers 520093696 bytes

Redo Buffers 7135232 bytes

Database mounted.

Database opened.

Note: You have to use a spfile to use Data Guard Broker.

Step 13:

Create standby redo logs if you want to run the configuration in MaxAvailibility or MaxProtection. Basically you have to create them if you want to run in SYNC mode.

SQL> Select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME

———- ———- ———- ———- ———- ——– —————- ————- ———–

1 1 108 52428800 1 NO CURRENT 658611 4/9/2008 10

2 1 106 52428800 1 YES ACTIVE 658530 4/9/2008 10

3 1 107 52428800 1 YES ACTIVE 658552 4/9/2008 10

SQL> Select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE

———- ——- ——- ——————————————————————————– ———————

3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO03.LOG NO

2 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO02.LOG NO

1 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO01.LOG NO

Now we create the standby redologs

SQL> alter database add standby logfile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDOSTB01.LOG’
size 52428800;

SQL> alter database add standby logfile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDOSTB02.LOG’
size 52428800;

SQL> alter database add standby logfile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDOSTB03.LOG’
size 52428800;

Once you mount standby you will have to open it in read only and issue the above commands
in the standby database location. 

i.e –

SQL> alter database add standby logfile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTSTBY\REDOSTB01.LOG’ size 52428800;

--- Do this for all 3

Step 14:

Start up the standby database using its init.ora file that you created in step 7.

Convert the init.ora file to a spfile.

Shut down the database and restart it using the spfile.

C:\> set oracle_sid=teststby

C:\> sqlplus

SQL> startup nomount pfile=C:\oracle\product\10.2.0\teststbyinit.ora;

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 67111812 bytes

Database Buffers 536870912 bytes

Redo Buffers 7135232 bytes

SQL> alter database mount standby database;

Database altered.

SQL> create spfile from pfile=c:\oracle\product\10.2.0\teststbyinit.ora;

Database altered.

SQL> Shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> Startup nomount;

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 67111812 bytes

Database Buffers 536870912 bytes

Redo Buffers 7135232 bytes

Now the standby database is started under the spfile.

Step 15:

Mount the standby database and recover it.

This will bring the standby database into standby mode.

SQL> alter database mount standby database;

Database altered.

SQL> recover managed standby database disconnect from session;

Media recovery complete.

SQL>

—- Go back to step 13 and add standby redo logs on standby database.

Step 16:

Now that Data Guard is all configured, we set up the Broker component.

First, we create the configuration.

C:\>dgmgrl

DGMGRL for 32-bit Windows: Version 10.2.0.1.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.

DGMGRL> connect sys@test

Password:

Connected.

DGMGRL> CREATE CONFIGURATION ‘DGBTEST’ AS

PRIMARY DATABASE IS ‘TEST’

CONNECT IDENTIFIER IS TEST;

Configuration “DGBTEST’ created with primary database “TEST”

Step 17:

We add the standby database to the broker configuration.

DGMGRL> ADD DATABASE ‘TESTSTBY’ AS

CONNECT IDENTIFIER IS TESTSTBY

MAINTAINED AS PHYSICAL;

Database “TESTSTBY” added.

Step 18:

Enable the configuration.

DGMGRL> enable configuration;

Enabled.

DGMGRL>

Step 19:

Verify the broker configuration is set up correctly and running.

DGMGRL> show configuration

Configuration

Name: dgbtest

Enabled: YES

Protection Mode: MaxAvailability

Fast-Start Failover: DISABLED

Databases:

test - Primary database

teststby - Physical standby database

Current status for “dgbtest”:

SUCCESS

DGMGRL>

Now we have a fully functional Data Guard and Broker configuration. If you have any questions or any ideas to make this document better please contact me at rlenihan@piocon.com

I just recently authored a PL/SQL Server Page designed to funnel large amounts of data via the htp.p function to a web application. Because the volume of data could be very large, I wanted to minimize the individual calls to the htp.p function (which can only take a maximum of 32767 characters per call).

The answer was obviously CLOBS! I proceeded to accumulate the full multi-megabyte response in a CLOB by concatenating the individual VARCHAR2 results line by line as they came out of the database.

Now I’ll be the first to admit that my experience with LOBs in Oracle has been very limited. And with the onset of SecureFiles in 11g, maybe I will never really become very proficient with LOBs. But in my LOB naivety, I assumed that if it COULD be done, it would probably work fine. You would think that after 15 years of Oracle development, I would know better…nope.

DECLARE
    bigLob CLOB;
    CURSOR getVarchars2 IS
         SELECT singleColumn FROM table;
BEGIN
FOR v_rec IN getVarchars2 LOOP
    bigClob := bigClob||v_rec.singleRow||linebreak;
END LOOP;
    doStuffWithTheCLOB(bigCLOB);
END;

It looks so innocent. Append the VARCHAR2 to the end of the CLOB. Let the database do the data type conversion from VARCHAR2 to CLOB. No problema! 10 rows, 100 rows, 1000 rows, all working just fine. Then came the request for 44K rows of data. The database became very angry and would not cooperate (“Stop asking for 44k rows!”). The user community became very angry and would not cooperate (“We need our 44k rows!”).

The solution is of course found in an obscure part of the Oracle documentation.

Here are the relevant entries:
• When Possible, Read/Write Large Data Chunks at a Time: Because LOBs are big, you can obtain the best performance by reading and writing large pieces of a LOB value at a time. This helps in several respects:
    o Writing to the LOB creates a new version of the LOB chunk. Therefore, writing small amounts at a time will incur the cost of a new version for each small write. If logging is on, then the chunk is also stored in the redo log.

• If you use the newly provided enhanced SQL semantics functionality in your applications, then there will be many more temporary LOBs created silently in SQL and PL/SQL than before. Ensure that temporary tablespace for storing these temporary LOBs is large enough for your applications. In particular, these temporary LOBs are silently created when you use the following:
     o SQL functions on LOBs
     o PL/SQL built-in character functions on LOBs
     o Variable assignments from VARCHAR2/RAW to CLOBs/BLOBs, respectively.
     o Perform a LONG-to-LOB migration

I highly recommend looking at this chapter in the LOB documentation prior to working with LOBs in SQL and PL/SQL.

Greg Pike

I just returned from Collaborate 2008 in beautiful Denver, Colorado where I gave a presentation extolling the virtues of the OTN SQL and PL/SQL Forum as a learning tool for beginners and experts alike. The presentation was designed to both inform the audience about the incredible wealth of knowledge found on the Forums as well as to highlight the most experienced and prodigious contributors along with their unique solutions.

I focused a little on the Oracle Ace program (and invited Ace Director Dan Norris to give an overview) in an attempt to demonstrate the extraordinary talent and experience of the moderators. Finally, I ended with a case study of a single post and the myriad of solutions that the Forum Gurus brought to bear to solve the problem. The presentation itself can be downloaded here, but as with most PowerPoints, it’s just not that informative without the commentary. If you do view it, please play it in slide show mode as it contains significant animations.

The presentation was well attended (especially for a last-minute Collaborate addition) and the attendees were attentive and full of questions. Several people found me later and reported returning to the Forums for the first time in years to both read and contribute. Mission accomplished!

I want to thank all of the contributors to the SQL and PL/SQL Forum and especially those that provided the content for my case study.

Greg Pike

Greg and I will be speaking at IOUG Collaborate 08! Come hear us or the rest of the Piocon crew and stop by Piocon’s Booth # 1619.

Monday, April 14
Session 501	9:15 - 11:30 AM    Dan Norris and Matt Topper SSL, Load Balancers, Rewrite, Redirect and More Advanced Configuration
Tuesday, April 15
Session 2035	9:45 - 10:45 PM    Greg Pike Aces in the Hole: Learning Advanced SQL Techniques from the OTN Forum Pros
Session 711	12:15 - 1:15 PM    Dan Norris RAC SIG Experts Panel
Session 110	1:45 - 2:45 PM    Roger Lenihan  Remove Spreadsheets, Checklists and Stress Out Of A Switchover, Use Data Guard Broker
Session 433	1:45 - 2:45 PM    Rohit Badiyani, Karen Smudde, Winslow Troy Fantastic Four:  ADF Faces, WebCenter, AJAX and SOA
Session 341	3:30 - 4:30 PM    Dan Norris Building A RAC Test Environment On VMWare For Free
Session 513	3:30 - 4:30 PM    Greg Pike Leveraging Oracle Portal as an Enterprise Identity Management Repository
Wednesday, April 16
Session 517	8:30 - 9:30 AM    Dan Norris Oracle Adaptive Access Manager: What, Why, How
Session 131	9:45 - 10:45 AM    Jeremy Simmons Oracle Application Server High-Availability Options
Session 712	1:30 - 2:30 PM    Dan Norris Oracle RAC SIG Birds-of-a-Feather
Session 232	4:30 - 5:30 PM    Matthew Vranicar 2008:  A BI Odyssey
Thursday, April 17
Session 379	9:45 - 10:45 AM    Dan Norris To RAC or Not To RAC: What's Best For HA?

SingleQuery was out of commission for a short (actually, long!) while but we’ve fixed the issues and upgraded to Wordpress 2.5!

Look for more posts soon.

Jeremy

CREATE TABLE emp
( empno    NUMBER(4,0),
  ename    VARCHAR2(10 BYTE),
  job      VARCHAR2(9 BYTE),
  mgr      NUMBER(4,0),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2,0)
); 

2.Insert Script

The script below can be used to insert data into the table created in step 1.

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7369','SMITH','CLERK','7902',TO_DATE('17-Dec-80','DD-MON-RR'),'800','0','30');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7499','ALLEN','SALESMAN','7698',TO_DATE('20-Feb-81','DD-MON-RR'),'1600','300','30');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7521','WARD','SALESMAN','7698',TO_DATE('22-Feb-81','DD-MON-RR'),'1250','500','30');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7525','JACK','SALESMAN','7690',TO_DATE('26-Sep-81','DD-MON-RR'),'1550','500','20');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7527','JONES','CLERK','7622',TO_DATE('22-Feb-81','DD-MON-RR'),'1000','300','20');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7521','KANYE','PRESIDENT','',TO_DATE('28-Feb-81','DD-MON-RR'),'5000','500','20');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7500','SAM','SALESMAN','7689',TO_DATE('23-Feb-81','DD-MON-RR'),'1257','500','10');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7400','JOE','MANAGER','7607',TO_DATE('22-Feb-81','DD-MON-RR'),'1540','300','10');

INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES ('7521','WARD','SALESMAN','7698',TO_DATE('22-Feb-81','DD-MON-RR'),'1250','500','10');
COMMIT;
/

3.Pivot Query

SELECT *
FROM (SELECT deptno,sal
      FROM emp
      WHERE deptno > 0
)
PIVOT (SUM(sal) AS SUM_AMOUNT FOR (deptno) IN (10 AS ACCOUNTING ,20 AS RESEARCH,30 AS SALES,40 AS OPERATIONS));

/*
General Form of query using PIVOT clause
SELECT * FROM (INNER QUERY)
PIVOT (
AGGREGATE_FUNCTION
FOR (COLUMN TO BE PIVOTED) IN (LIST OF VALUES));
*/

A single Pivot clause can also support multiple Aggregate Functions.
PIVOT CLAUSE provides great support to businesses which would like to do a detailed analysis on their Sales methodology and help them make future decisions on their sales and marketing strategies.

Using the UNPIVOT clause

The UNPIVOT clause rotates columns into rows.UNPIVOT clause is useful when a user has to execute a query that returns many columns , using the UNPIVOT clause the user can view those columns as rows.

Lets Illustrate the concept discussed above

1.Create a table pivot_emp_data using the query in step 3 as

CREATE TABLE pivot_emp_data AS
SELECT *
FROM (SELECT deptno,sal
              FROM emp
              WHERE deptno > 0
)
PIVOT (SUM(sal) AS SUM_AMOUNT
FOR (deptno)
IN (10 AS ACCOUNTING ,20 AS RESEARCH,30 AS SALES,40 AS OPERATIONS);

2.Once the table has been created we can run the query below

SELECT *
  FROM pivot_emp_data
  UNPIVOT
(sal FOR deptno
IN(ACCOUNTING_SUM_AMOUNT,RESEARCH_SUM_AMOUNT,SALES_SUM_AMOUNT,OPERATIONS_SUM_AMOUNT))

The Query rotates the pivoted Data .Also , do realize that If you are working with pivoted data, an UNPIVOT operation cannot reverse any aggregations that have been made by PIVOT or any other means.

One of the big announcements at Oracle OpenWorld last week was Oracle VM. I’m a big fan of virtualization and have worked a lot with VMWare, but honestly I’ve never paid much attention to Xen (which Oracle VM is based on).

Here’s some item’s I found interesting:

Oracle VM (based on Xen) requires the linux guest OS to be modified with paravirtualization drivers, Windows requires Hardware Assist and will likely run slower then OEL or RedHat until suitable paravirtualization drivers are delivered.

From: VMWare’s “A Performance Comparison of Hypervisors”

The full virtualization approach allows datacenters to run an unmodified guest operating system, thus maintaining the existing investments in operating systems and applications and providing a nondisruptive migration to virtualized environments. VMware uses a combination of direct execution and binary translation techniques to achieve full virtualization of an x86 system

The paravirtualization approach modifies the guest operating system to eliminate the need for binary translation. Therefore it offers potential performance advantages for certain workloads but requires using specially modified operating system kernels. The Xen open source project [and there by Oracle VM] was designed initially to support paravirtualized operating systems. While it is possible to modify open source operating systems, such as Linux and OpenBSD, it is not possible to modify “closed” source operating systems such as Microsoft Windows . It is also not practical to modify older versions of open source operating systems that are already in use. As it turns out, Microsoft Windows is the most widely deployed operating system in enterprise datacenters. For such unmodified guest operating systems, a virtualization hypervisor must either adopt the full virtualization approach or rely on hardware virtualization in the processor architecture.

The hardware virtualization support enabled by AMD-V and Intel VT technologies introduces virtualization in the x86 processor architecture itself. While first-generation hardware assist support includes CPU virtualization only, later generations are expected to include memory and I/O virtualization as well. The emergence of virtualization hardware assist reduces the need to paravirtualize guest operating systems. In fact, Xen vendors such as Virtual Iron have announced that they are supporting only full virtualization using AMD-V and Intel VT processors and are not supporting paravirtualization

It looks like paravirtualization and hardware assist are the future for the hypervisor until then check out:

Ten Reasons Why Oracle Databases Run Best on VMware (this is an interesting read but it does point out that a lot of important features relative to Oracle are not coming until the 3.5 ESX release)

Dan and I were at a client whose custom OC4J Containers running in Windows 2003 SE on VMWare were crashing randomly. To make the situation more complex the default containers (Home and WebCenter) from Oracle were not crashing.

Eventually we correlated the bulk of the OC4J crashes to admins logging out of VMWare Console and Remote Desktop with /console option.

Turns out that when you create a custom OC4J container in Application Server Control Oracle does not include -Xrs as a Java option even though it is included with the standard containers they deliver with 10.1.3.x.

The -Xrs option tells the JVM to ignore most signals from the OS level otherwise the JVM will exit thereby unexpectedly terminating the OC4J container and all running threads running within it.

Based on this Metalink article this appears to be a known issue since at least 2003. Maybe in 11g Oracle will include this option by default.
Subject: Unexpected JVM Termination in Response to Logout From Windows Console
Doc ID: Note:245609.1
Last Revision Date: 11-AUG-2003

Below is a snippet of the Java application launcher detail on the -Xrs option

-Xrs
Reduces usage of operating-system signals by the Java virtual machine (JVM). This option is available beginning with J2SE 1.3.1.

In J2SE 1.3.0, the Shutdown Hooks facility was added to allow orderly shutdown of a Java application. The intent was to allow user cleanup code (such as closing database connections) to run at shutdown, even if the JVM terminates abruptly.

The JVM watches for console control events to implement shutdown hooks for abnormal JVM termination. Specifically, the JVM registers a console control handler which begins shutdown-hook processing and returns TRUE for CTRL_C_EVENT, CTRL_CLOSE_EVENT, CTRL_LOGOFF_EVENT, and CTRL_SHUTDOWN_EVENT.

The JVM uses a similar mechanism to implement the pre-1.2 feature of dumping thread stacks for debugging purposes. Sun’s JVM uses CTRL_BREAK_EVENT to perform thread dumps.

If the JVM is run as a service (for example, the servlet engine for a web server), it can receive CTRL_LOGOFF_EVENT but should not initiate shutdown since the operating system will not actually terminate the process. To avoid possible interference such as this, the -Xrs command-line option has been added beginning with J2SE 1.3.1. When the -Xrs option is used on Sun’s JVM, the JVM does not install a console control handler, implying that it does not watch for or process CTRL_C_EVENT, CTRL_CLOSE_EVENT, CTRL_LOGOFF_EVENT, or CTRL_SHUTDOWN_EVENT.

There are two consequences of specifying -Xrs:

* Ctrl-Break thread dumps are not available.
* User code is responsible for causing shutdown hooks to run, for example by calling System.exit() when the JVM is to be terminated.

Jeremy Simmons