tag:blogger.com,1999:blog-78261910653354736492024-03-21T08:35:20.385-07:00The DayneO blogThis blog contains software development tips and quirks, problems and solutions that I stumble upon from day to day.dayneohttp://www.blogger.com/profile/04380081381533944922noreply@blogger.comBlogger17125tag:blogger.com,1999:blog-7826191065335473649.post-54269829589513819552013-02-06T03:32:00.000-08:002013-02-06T03:32:56.591-08:00Solved: Error Snapshotting Volumes (DISK2VHD)<div style="text-align: right;">
Windows XP</div>
I encountered the error "Error Snapshotting Volumes" while using the <a href="http://technet.microsoft.com/en-us/sysinternals/ee656415.aspx" target="_blank">DISK2VHD</a> tool to virtualize an old XP dev environment. The error is DISK2VHD's own description of an error that occurs with the shadow copy features of XP. This error is not confined to the DISK2VHD tool, and may occur with anything that relies on the usage of the shadow copy features. Most often, these tools are backup programs.<br />
<br />
Fortunately, this is quite a well defined problem and Microsoft has a <a href="http://support.microsoft.com/kb/907574" target="_blank">knowledge base article</a> detailing the cause and resolution. The article goes into more depth about the symptoms and cause of the problem and does not actually mention the error message that I received from DISK2VHD, but it is in fact the root cause of the error and the resolution steps discussed solved my error on the first attempt.<br />
<br />
Here is the chewed version from the <a href="http://support.microsoft.com/kb/907574" target="_blank">knowledge base article</a>:<br />
<ol>
<li>Click Start, click Run, type regedit, and then click OK.</li>
<li>Locate and then click the following registry subkey:</li>
<li>HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\ContentIndex\Catalogs</li>
<li>Click the registry subkey for each program that is listed under the Catalogs registry subkey. For each registry subkey, verify whether the Location registry entry exists.</li>
<li>If the Location registry entry is missing for a selected program, verify whether the program is still installed.</li>
<li>If the program is not installed, right-click the registry subkey for the program under the Catalogs subkey, and then clickDelete. If the program is installed, correct the location information.</li>
<li>Restart the computer, and then use Windows Backup Utility to test this problem.</li>
</ol>
In my case, it was Visio that was previously uninstalled, but still had a registry entry that was running amok with my virtualization task. Simply removing the rogue registry entry got me on my way again and finally I have the dev environment virtualized.dayneohttp://www.blogger.com/profile/04380081381533944922noreply@blogger.com18tag:blogger.com,1999:blog-7826191065335473649.post-86405034873750900172012-11-05T05:40:00.000-08:002012-11-05T05:40:37.006-08:00Simplest way to debug your Oracle Java Stored Procedure<div style="text-align: right;">
Oracle 9i/10g/11g</div>
<div style="text-align: left;">
Today I came across a situation where I had to debug a Java Stored Procedure. I was under time pressure and just needed to understand where the procedure was failing. </div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
The simplest way of debugging pretty much any code is the good old print to standard output. And luckily for us, Oracle thought the same way and provided a super simple method of reading from the standard output stream by <a href="http://docs.oracle.com/cd/B19306_01/java.102/b14187/chthree.htm#CACIIEDH" target="_blank">redirecting the output</a>.</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
So my solution was easily implemented using the good old <a href="http://docs.oracle.com/javase/1.4.2/docs/api/java/io/PrintStream.html#println(java.lang.String)" target="_blank">System.out.println</a>, placing the print calls strategically throughout the Java procedure. The last little trick that allows you to actually see what was written, is to enable the output in SQL*Plus by turning the <a href="http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12040.htm#sthref2862" target="_blank">server output on</a> and setting the <a href="http://docs.oracle.com/cd/B19306_01/java.102/b14187/appendixa.htm#sthref800" target="_blank">DBMS_JAVA output size</a>.<br />
<a name='more'></a></div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
The following code demonstrates this:</div>
<pre class="CodeSnippet">dayneo@RMSD> CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED SimpleSample as
2 public class SimpleSample
3 {
4 public static void simplemsg()
5 {
6 System.out.println(new java.util.Date().toString() + ": Hello world!");
7 }
8 }
9 /
Java created.
dayneo@RMSD> create or replace procedure simplesample_says as
2 language java name 'SimpleSample.simplemsg()';
3 /
Procedure created.
dayneo@RMSD> set serveroutput on size 1000000
dayneo@RMSD> call dbms_java.set_output(1000000);
Call completed.
dayneo@RMSD> begin
2 simplesample_says();
3 end;
4 /
Mon Nov 05 15:37:03 GMT+02:00 2012: Hello world!
PL/SQL procedure successfully completed.</pre>
<br />dayneohttp://www.blogger.com/profile/04380081381533944922noreply@blogger.com4tag:blogger.com,1999:blog-7826191065335473649.post-62913386023868930882012-07-12T05:26:00.000-07:002012-07-12T05:26:39.775-07:00Base64 decode using PL/SQL<div style="text-align: right;">
Oracle 10g</div>
<div style="text-align: left;">
For those of us that deal with internet based communication, <a href="http://en.wikipedia.org/wiki/Base64" target="_blank">Base64</a> encode/decode is a must. It is a method for us to move binary data files over a text based communication channel.<br />
<br />
In my environment, I receive files over HTTP via the <a href="http://docs.oracle.com/cd/B14099_19/web.1012/b14010/concept.htm" target="_blank">MOD_PLSQL</a> gateway. MOD_PLSQL implements the bare necessities in terms of HTTP communication, so it comes as no surprise that it does not decode uploaded files if those files arrive as GZIP or Base64 encoded. If I think about it, it shouldn't decode them automatically, but at the very least it should record the encoding of the file for later use.<br />
<br />
Fortunately, I just happen to know that the 3rd party sends the file encoded using Base64. Only thing is, the PL/SQL package for decoding Base64 strings <a href="http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_encode.htm#i996851" target="_blank">UTL_ENCODE</a> only decodes RAW datatypes which limits us to 32767 characters. For Base64 strings larger than 32767 characters in size, we needed to role our own solution. So here is my version of decode to support decoding clob to blob.<br />
<a name='more'></a>An important point to note here is the chunk size (declared as l_BASE64_LN_LENGTH in my script) used when reading through the Base64 string. The size must be a multiple of two. </div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
<pre class="CodeSnippet">CREATE OR REPLACE function base64_decode(p_file in clob) return blob is
l_BASE64_LN_LENGTH constant pls_integer := 48;
l_result blob := empty_blob();
l_pos number := 1;
l_amount number;
l_sbuf varchar2(32767);
l_buffer raw(32767);
l_rbuf raw(32767);
l_length pls_integer;
begin
dbms_lob.createTemporary(l_result, true, dbms_lob.CALL);
l_length := dbms_lob.getLength(p_file);
while l_pos <= l_length loop
l_amount := l_BASE64_LN_LENGTH;
dbms_lob.read(p_file, l_amount, l_pos, l_sbuf);
l_rbuf := utl_raw.cast_to_raw(l_sbuf);
l_buffer := utl_encode.base64_decode(l_rbuf);
dbms_lob.writeappend(l_result,
utl_raw.length(l_buffer),
l_buffer);
l_pos := l_pos + l_BASE64_LN_LENGTH;
end loop;
return l_result;
end base64_decode;
/</pre>
</div>
<div style="text-align: left;">
<br /></div>dayneohttp://www.blogger.com/profile/04380081381533944922noreply@blogger.com0tag:blogger.com,1999:blog-7826191065335473649.post-14178828274011923872012-05-23T04:52:00.000-07:002012-06-19T02:35:02.538-07:00Solved "Setup was unable to open information file games.inf"<div style="text-align: right;">
Windows XP</div>
<div style="text-align: left;">
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-N0S6ElafYgo/T7zMcK9LHoI/AAAAAAAAA_g/PkAi1_DuBV8/s1600/error-msg.png" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="92" src="http://4.bp.blogspot.com/-N0S6ElafYgo/T7zMcK9LHoI/AAAAAAAAA_g/PkAi1_DuBV8/s320/error-msg.png" width="320" /></a></div>
Windows XP was actually a great OS, and I still use it extensively in testing on virtual machines. Of course, for my test environment, it did not make sense to install all of the components of XP, so I made sure I only installed the lightest components that I thought I needed. </div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
It was only later that I discovered that I could not add components back in because the Add/Remove Programs dialog would error with "Setup was unable to open information file games.inf". Fortunately, this problem is not that difficult to solve. </div>
<a name='more'></a>The problem is caused by the existence of entries in the optional components <a href="http://en.wikipedia.org/wiki/INF_file" target="_blank">INF</a> file located at C:\Windows\inf\sysoc.inf, for components that we actually didn't install in the first place. Fixing the problem is a simple process of removing these entries.<br />
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
To solve the problem:</div>
<ol>
<li>Open the file C:\Windows\inf\sysoc.inf using <a href="http://en.wikipedia.org/wiki/Notepad_(software)" target="_blank">notepad</a>.</li>
<li>Find the entry pertaining to the games.inf file. The entry should look like this:<br />Games=ocgen.dll,OcEntry,games.inf,,7</li>
<li>Place a <a href="http://en.wikipedia.org/wiki/Semicolon" target="_blank">semicolon</a> at the beginning of this entry. The entry should now look like this:<br /><b>;</b>Games=ocgen.dll,OcEntry,games.inf,,7<br />The semicolon is interpreted as a comment in INF files and will have the effect of removing the entry without actually deleting it. </li>
<li>Save the file.</li>
<li>Try the
Add/Remove Windows Components button again.<br />If it gives you an error again, repeat the process by commenting out each corresponding entry until you no longer receive the errors.</li>
</ol>
<div style="text-align: left;">
I had to repeat the process for 4 entries: games.inf; optional.inf; pinball.inf and igames.inf. My set of entries ended up as follows:</div>
<div style="text-align: left;">
</div>
<pre class="CodeSnippet">;Games=ocgen.dll,OcEntry,games.inf,,7
AccessUtil=ocgen.dll,OcEntry,accessor.inf,,7
CommApps=ocgen.dll,OcEntry,communic.inf,HIDE,7
MultiM=ocgen.dll,OcEntry,multimed.inf,HIDE,7
;AccessOpt=ocgen.dll,OcEntry,optional.inf,HIDE,7
;Pinball=ocgen.dll,OcEntry,pinball.inf,HIDE,7
MSWordPad=ocgen.dll,OcEntry,wordpad.inf,HIDE,7
;ZoneGames=zoneoc.dll,ZoneSetupProc,igames.inf,,7</pre>
<br />
After commenting out the offending lines, I was able to access the Windows Components and make the adjustments that I needed.<br />
<div style="text-align: left;">
<br /></div>dayneohttp://www.blogger.com/profile/04380081381533944922noreply@blogger.com0tag:blogger.com,1999:blog-7826191065335473649.post-5171247686261684122012-04-20T03:56:00.002-07:002012-08-13T06:42:42.690-07:00Example: How to print a Oracle report using a URL<div style="text-align: right;">
<br />
<div>
Oracle Reports 10g</div>
<div>
Windows Server 2008</div>
<div style="text-align: left;">
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-jTao1jQL1AM/T5E-9Y5nOdI/AAAAAAAAA8A/tV32CrK_ifA/s1600/network-web-printer.png" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" src="http://1.bp.blogspot.com/-jTao1jQL1AM/T5E-9Y5nOdI/AAAAAAAAA8A/tV32CrK_ifA/s1600/network-web-printer.png" /></a></div>
For those of us that use Oracle Reports in our environments, most of us (I assume), use the web browser to run the report. The report is then returned back to the browser window for viewing (typically). But what happens if I want to run the report directly to a printer without viewing it? </div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
Perhaps I want to have a payroll run that prints a few thousand pay sheets. Not something I want to preview or download to my browser. It's also not something I would print to my local printer that only does 15-20ppm.</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
Or, maybe I want a document that requires particular stationary, delicate watermarking and copy protection only available on a specialised corporate printer. Like perhaps a vehicle license document or access card printer.</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
Anyway, there is quite a bit of documentation about <a href="http://docs.oracle.com/html/B14048_02/pbr_uxprt.htm" target="_blank">printing from Oracle Reports on UNIX servers</a> (because it's so much more complex on UNIX systems), but basically none on Windows servers because it is so simple. Still, it took me a little while to find just the right parameters to set when running the report. So here it is then, a simple guide to printing Oracle Reports to a designated printer via a URL browser command....</div>
<a name='more'></a><div style="text-align: -webkit-auto;">
Firstly, I am only dealing with Windows servers here. You will need to refer to <a href="http://docs.oracle.com/html/B14048_02/pbr_uxprt.htm" target="_blank">Oracle documentation for information on setting up printing on Linux/UNIX</a> server environments. <span style="text-align: left;">Secondly, I would assume that you are able to run Oracle Reports via the standard <a href="http://docs.oracle.com/cd/E16764_01/bi.1111/b32121/pbr_run005.htm#i1009583" target="_blank">RWSERVLET commands from a Web Browser</a>. </span></div>
<div style="text-align: -webkit-auto;">
<span style="text-align: left;"><br /></span></div>
<div style="text-align: -webkit-auto;">
<span style="text-align: left;">For my simple example, I use the following URL which I am able to run in any web browser:</span><br />
<div style="text-align: left;">
<pre>http://windev:7777/reports/rwservlet?testkeymap&report=test_report&mimetype=application%2Fpdf&destype=cache&desformat=PDF</pre>
</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
This URL returns the Oracle Report back to my browser as a PDF document. For more information on running running Oracle Reports using a URL, see <a href="http://docs.oracle.com/html/B14048_02/pbr_run.htm#sthref1416" target="_blank">Running Report Reqeusts</a>. </div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
My report example makes use of a <a href="http://docs.oracle.com/cd/E16764_01/bi.1111/b32121/pbr_run013.htm" target="_blank">key map file</a> that fills in some parameters by default to make it easier for me to run reports using a URL. This is the CGICMD.DAT mapping entry:</div>
<div style="text-align: left;">
</div>
<pre>testkeymap: %* userid=username/password@tnsname</pre>
<br />
<div style="text-align: left;">
<b>Starting off with the configuration:</b></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-o31BXK1qcF4/T32S4i5vYbI/AAAAAAAAA6I/gfi8Z1F6sU8/s1600/devices+and+printers.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="239" src="http://2.bp.blogspot.com/-o31BXK1qcF4/T32S4i5vYbI/AAAAAAAAA6I/gfi8Z1F6sU8/s320/devices+and+printers.jpg" width="320" /></a></div>
<div style="text-align: left;">
If you want to run the report directly to a printer, you will need to add the printer to the server devices first. I added a Brother HL 7050 series printer just as a test for the printing. Now, the adding of printers is really simple on Windows and you will know you have it added correctly when you are able to print a test page to it from the servers "Printers and Devices" panel. </div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
I found there is a catch though.... By default, Windows will add the printer using it's product name as the actual device name in the "Printers and Devices" panel. This often includes spaces in the name which seems to be a no-no if you want to use it with Oracle Reports. My experience has been that you must <b>not have any spaces in the name of your printer</b>. I renamed my printer from "Brother HL-7050 series" to simply "BrotherHL-7050series", just removing the spaces. If you have spaces in the printer name, then you will likely receive the following error when running your report from your browser:</div>
<div style="text-align: left;">
</div>
<pre>REP-50159: Executed successfully but there were some errors when distribute the output</pre>
<br />
<div style="text-align: left;">
In terms of config, that's the only change I needed to make.</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
<b>Running the report to the printer:</b></div>
<div style="text-align: left;">
So, we already have our test URL we used to run the report back to my browser. I just need to provide the parameters to redirect the report to my printer. The parameters involved are simply:</div>
<div style="text-align: left;">
</div>
<ul>
<li><a href="http://docs.oracle.com/cd/E16764_01/bi.1111/b32121/pbr_cla005.htm#i637246" target="_blank">DESTYPE</a>: Set this to "printer" to tell RWSERVELET that we are sending the report to a printer. Strangely, this parameter name seems to be case sensitive, so use "DESTYPE" (in upper case) in your query string.</li>
<li><a href="http://docs.oracle.com/cd/E16764_01/bi.1111/b32121/pbr_cla005.htm#i637000" target="_blank">DESNAME</a>: Set this to the name of the printer that we set in the configuration (remember no spaces)</li>
</ul>
<br />
<div style="text-align: left;">
The revised URL will now look like this:</div>
<div style="text-align: left;">
<pre>http://windev:7777/reports/rwservlet?testkeymap&report=test_report&desformat=pdf&DESTYPE=printer&desname=BrotherHL-7050series</pre>
</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
Running the URL from any web browser should return a Success message and output at your printer.</div>
</div>
</div>
dayneohttp://www.blogger.com/profile/04380081381533944922noreply@blogger.com0tag:blogger.com,1999:blog-7826191065335473649.post-63536024451506889212012-03-23T02:43:00.000-07:002012-03-23T02:43:50.050-07:00Extending the simple MS Excel procedure to include headers<div style="text-align: right;">
Oracle 10g</div>
<div style="text-align: right;">
MS Excel</div>
In my previous post titled "<a href="http://blogs.dayneo.co.za/2012/02/simplest-example-of-writing-to-ms-excel.html" target="_blank">Simplest example of writing to MS Excel using an Oracle Stored Procedure</a>", I demonstrated what I believe to be the simplest way of outputting a file that can be used by MS Excel. But the example does not include the column headings.... Okay, thats simple enough. In this post, I will extend on the previous example to include the headings.
So without further ado....<br />
<a name='more'></a><br />
In the previous post, I created a stored procedure called ALL_OBJECTS_TO_CSV. This stored procedure was responsible for running a select statement on the ALL_OBJECTS view and to write the results as a <a href="http://en.wikipedia.org/wiki/Comma-separated_values" target="_blank">CSV file</a> to a share directory.<br />
<div>
<br /></div>
<div>
The stored procedure uses the <a href="http://www.google.co.za/url?sa=t&rct=j&q=oracle%20dbms_sql&source=web&cd=1&ved=0CCYQFjAA&url=http%3A%2F%2Fdocs.oracle.com%2Fcd%2FB19306_01%2Fappdev.102%2Fb14258%2Fd_sql.htm&ei=NQdNT6T4FIaemQWmvODuDw&usg=AFQjCNHms9IDpTAZ0nZ3uPoACgWiWN9yFw&sig2=ifkZ7hOGrIiOh9959vEzQQ" target="_blank">DBMS_SQL</a> package to execute the select statement and return the results. This can also help us in dynamically querying the column definitions that can be used to generate the column headers for the CSV file.</div>
<div>
<br /></div>
<div>
Line 43 of the stored procedure reads the column definitions from the cursor into the L_COL_DESC variable.</div>
<div>
<pre class="CodeSnippet">dbms_sql.describe_columns2(l_cur, l_col_cnt, l_col_desc);</pre>
</div>
<div>
Each of the column definitions contain the column names. So a simple loop over the column definitions can write the column headers to the file. I inserted the following additional code at line 45 to output the column headers:</div>
<pre class="CodeSnippet">45 -- Output column headers
46 l_buf := '';
47 for i in 1..l_col_cnt loop
48
49 l_buf := l_buf || '"'
50 || replace(l_col_desc(i).col_name, '"', '""'
51 || '"' || l_sep;
52
53 end loop;
54 l_buf := rtrim(l_buf, l_sep);
55 utl_file.put_line(l_hnd, l_buf);</pre>dayneohttp://www.blogger.com/profile/04380081381533944922noreply@blogger.com1tag:blogger.com,1999:blog-7826191065335473649.post-10612685885358214092012-02-28T09:21:00.001-08:002012-02-28T09:48:01.701-08:00Simplest example of writing to MS Excel using an Oracle Stored Procedure<div style="text-align: right;">
Oracle 10g<br />
MS Excel</div>
<div style="text-align: left;">
First of all, we need to define exactly what is meant by "writing to Excel worksheet". Most people just mean produce something that MS Excel can show as a worksheet. A CSV file is the simplest file that can be displayed by MS Excel and since it is a simple text markup, is the easiest to produce via PL/SQL.<br />
<br /></div>
<div style="text-align: left;">
Generating the file using a stored procedure is seriously simple. But you will need to also give thought to how you will make the file available to excel (how to distribute the file). In my example, I make the file available on a network share. The user will find the file available there and open it using MS Excel.<br />
<a name='more'></a></div>
<div style="text-align: left;">
<a href="http://4.bp.blogspot.com/-3BR5uBz0g24/T0znlzNUswI/AAAAAAAAAzk/BHW9ybMAbxo/s1600/shared-folder.png" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="242" src="http://4.bp.blogspot.com/-3BR5uBz0g24/T0znlzNUswI/AAAAAAAAAzk/BHW9ybMAbxo/s320/shared-folder.png" width="320" /></a><br />
The first thing we need is a directory that is accessible to both the user and the Oracle database. I chose C:\TEMP\ on the server and shared it so that I can access it remotely.</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
We then need to define a directory object in the Oracle database and assign permissions. This will make the directory available to our stored procedure to be able to write to it. To do this, we must use the <a href="http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5007.htm" target="_blank">CREATE DIRECTORY</a> command. Take note that you need CREATE ANY DIRECTORY privilege in order to run this command. Typically, only DBA will have this permission.<br />
<br />
Once the directory is created by the DBA, the read/write permissions must be granted to the Oracle user that will own the procedure that will generate the file. In my case, that user is CSVUSER.
<br />
<pre class="CodeSnippet">dayneo@SANDBOX> create directory TEMP_DIR AS 'C:\Temp'
2 /
Directory created.
dayneo@SANDBOX>
dayneo@SANDBOX> grant read, write on directory TEMP_DIR to csvuser
2 /
Grant succeeded.
dayneo@SANDBOX></pre>
<br /></div>
<div style="text-align: left;">
Now we need the oracle stored procedure that generates the file. I will keep it simple and select two columns from the ALL_OBJECTS view. We will use <a href="http://www.google.co.za/url?sa=t&rct=j&q=oracle%20utl_file&source=web&cd=1&ved=0CCsQFjAA&url=http%3A%2F%2Fdocs.oracle.com%2Fcd%2FB19306_01%2Fappdev.102%2Fb14258%2Fu_file.htm&ei=HwdNT7v8Fu3smAXbh6z-Dw&usg=AFQjCNF5BDPKlVZSjHTGPD-oDgI9MPEoEg&sig2=0e1kzpSada72mshNeDB4tA" target="_blank">UTL_FILE</a> and <a href="http://www.google.co.za/url?sa=t&rct=j&q=oracle%20dbms_sql&source=web&cd=1&ved=0CCYQFjAA&url=http%3A%2F%2Fdocs.oracle.com%2Fcd%2FB19306_01%2Fappdev.102%2Fb14258%2Fd_sql.htm&ei=NQdNT6T4FIaemQWmvODuDw&usg=AFQjCNHms9IDpTAZ0nZ3uPoACgWiWN9yFw&sig2=ifkZ7hOGrIiOh9959vEzQQ" target="_blank">DBMS_SQL</a> to produce a semi-generic CSV generator procedure (you will have to change the query and column definitions). The procedure will accept a parameter as a filter to the query and another for the name of the file I want generated. </div>
<pre class="CodeSnippet">csvuser@SANDBOX> create or replace procedure allobjects_to_csv(p_filename in varchar2,
2 p_object_type in varchar2 default 'TABLE') as
3
4 VARCHAR2_COLTYPE constant pls_integer := 1;
5 NUMBER_COLTYPE constant pls_integer := 2;
6 DATE_COLTYPE constant pls_integer := 12;
7 CTRLF constant varchar2(2) := chr(10) || chr(13);
8 WRITE_TEXT constant varchar2(1) := 'w';
9
10 l_cur number;
11 l_qry varchar2(32767);
12 l_chr varchar2(32767);
13 l_n number;
14
15 l_col_cnt pls_integer;
16 l_col_desc dbms_sql.desc_tab2;
17 l_vchar varchar2(32767);
18 l_vnum number;
19 l_vdate date;
20 l_buf varchar2(32767);
21 l_sep varchar2(1) := ','; -- the separater
22
23 l_hnd utl_file.file_type;
24
25 begin
26
27 -- open file for writing
28 l_hnd := utl_file.fopen('TEMP_DIR', p_filename, WRITE_TEXT, 32767);
29
30 -- #1. Query
31 l_qry := 'select owner || ''.'' || object_name object_name, object_type
32 from all_objects
33 where object_type like :bv
34 order by object_name';
35 l_cur := dbms_sql.open_cursor();
36 dbms_sql.parse(l_cur, l_qry, dbms_sql.NATIVE);
37 dbms_sql.define_column(l_cur, 1, l_chr, 61); -- TABLE_NAME
38 dbms_sql.define_column(l_cur, 2, l_chr, 19); -- TABLE_TYPE
39 dbms_sql.bind_variable(l_cur, ':bv', p_object_type);
40 l_n := DBMS_SQL.EXECUTE(l_cur);
41
42 -- Describe the resultset
43 dbms_sql.describe_columns2(l_cur, l_col_cnt, l_col_desc);
44
45 -- for each row
46 while dbms_sql.fetch_rows(l_cur) > 0 loop
47
48 l_buf := '';
49
50 -- for each column
51 for i in 1..l_col_cnt loop
52
53 -- TODO: Find all the various datatypes and handle them here
54 case l_col_desc(i).col_type
55 when VARCHAR2_COLTYPE then
56
57 -- quotes in string values must be double quoted.
58 -- string values should be quoted.
59 dbms_sql.column_value(l_cur, i, l_vchar);
60 l_buf := l_buf || '"' || replace(l_vchar, '"', '""') || '"' || l_sep;
61
62 when NUMBER_COLTYPE then
63
64 dbms_sql.column_value(l_cur, i, l_vnum);
65 -- you need to use the to_char to make sure you don't get
66 -- an implicit conversion from num to char using NLS_ params
67 -- NLS might define number with , separators which would break
68 -- the csv format.
69 l_buf := l_buf || to_char(l_vnum, '999999999990.0') || l_sep;
70
71 when DATE_COLTYPE then
72
73 dbms_sql.column_value(l_cur, i, l_vdate);
74 -- you need to use the to_char to make sure you don't get
75 -- an implicit conversion from date to char using NLS_ params
76 -- NLS might define date with , separators which would break
77 -- the csv format.
78 l_buf := l_buf || to_char(l_vdate, 'DD MON YYYY HH24:MI:SS' ) || l_sep;
79
80 else
81
82 raise_application_error(-20000,
83 'Unknown column type found for column "'
84 || l_col_desc(i).col_name
85 || '"');
86
87 end case;
88
89 end loop;
90
91 -- remove the trailing separator
92 l_buf := rtrim(l_buf, l_sep);
93
94 -- write line to file
95 utl_file.put_line(l_hnd, l_buf);
96
97 end loop;
98
99 utl_file.fclose(l_hnd);
100 dbms_sql.close_cursor(l_cur);
101
102 exception
103 when OTHERS then
104 if utl_file.is_open(l_hnd) then
105 utl_file.fclose(l_hnd);
106 end if;
107 if dbms_sql.is_open(l_cur) then
108 dbms_sql.close_cursor(l_cur);
109 end if;
110 raise;
111 end allobjects_to_csv;
112 /
Procedure created.
csvuser@SANDBOX>
csvuser@SANDBOX> SHOW ERRORS
No errors.
csvuser@SANDBOX> </pre>
<div style="text-align: left;">
<br />
Now we have a stored procedure that will output two columns of the ALL_OBJECTS view to a file on the C:\Temp directory. After running the procedure, I will find a file in the shared directory. Users can open the file using MS Excel.</div>
<pre class="CodeSnippet">csvuser@SANDBOX> begin
2
3 allobjects_to_csv('myfile.csv');
4
5 end;
6 /
PL/SQL procedure successfully completed.
csvuser@SANDBOX></pre>
<div style="text-align: left;">
<br />
The file is now available on the share directory we set up in the beginning. The file can be opened using a text editor such as Notepad to view the actual format that is output to the file. If you have MS Excel installed, then it will open the file by default.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-AH0KMyWj1dE/T00KHBuk9-I/AAAAAAAAAzs/iiHuHfPgyio/s1600/csv-in-share.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="202" src="http://2.bp.blogspot.com/-AH0KMyWj1dE/T00KHBuk9-I/AAAAAAAAAzs/iiHuHfPgyio/s400/csv-in-share.png" width="400" /></a></div>
<br />
<div>
<a href="http://2.bp.blogspot.com/-WKISoThtr7Y/T00K90l0LWI/AAAAAAAAAz8/J8f77UyimfQ/s1600/excel-worksheet.png" imageanchor="1" style="float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="320" src="http://2.bp.blogspot.com/-WKISoThtr7Y/T00K90l0LWI/AAAAAAAAAz8/J8f77UyimfQ/s320/excel-worksheet.png" width="300" /></a><a href="http://2.bp.blogspot.com/-WKISoThtr7Y/T00K90l0LWI/AAAAAAAAAz8/J8f77UyimfQ/s1600/excel-worksheet.png" imageanchor="1" style="float: right; margin-bottom: 1em; margin-left: 1em;"><br /></a>
<a href="http://2.bp.blogspot.com/-MjJsEdj9OG8/T00KwhiLLPI/AAAAAAAAAz0/aAOs9Y0v7tc/s1600/csv-in-notepad.png" imageanchor="1" style="float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="220" src="http://2.bp.blogspot.com/-MjJsEdj9OG8/T00KwhiLLPI/AAAAAAAAAz0/aAOs9Y0v7tc/s320/csv-in-notepad.png" width="300" /></a></div>
<br />
There are many ways of distributing the file instead of writing to a share directory. You could email the file, you could make available via web, or you could make it downloadable by some custom client side application. All of these options are available through PL/SQL.</div>dayneohttp://www.blogger.com/profile/04380081381533944922noreply@blogger.com4tag:blogger.com,1999:blog-7826191065335473649.post-40740885590491798372012-02-23T09:16:00.000-08:002012-02-23T09:17:20.959-08:00Solved problem with fonts in Oracle Reports 10g on Windows 2008 Server<div style="text-align: right;">
Oracle Reports 10g</div>
<div style="text-align: left;">
I have seen countless posts of people battling with fonts in Oracle Reports. I battled my Reports Server on Windows Server 2008 for two days trying to get the font to display. In the end, it turned out to be relatively simple, but not obvious. </div>
<div style="text-align: left;">
<br />
The trick with <a href="http://docs.oracle.com/html/B14048_02/pbr_font.htm" target="_blank">Oracle Reports fonts</a> is to make sure that you have got the font names right in the <a href="http://docs.oracle.com/html/B14048_02/pbr_font.htm#i1009745" target="_blank">font aliasing configuration file (uifont.ali)</a>. If you don't get the font names right, then the alias will not be used and your report will not use the correct font. Get the font names correct and the report will show as expected.<br />
<a name='more'></a></div>
<div style="text-align: left;">
<br /></div>
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: right; margin-left: 1em; text-align: right;"><tbody>
<tr><td style="text-align: center;"><a href="http://2.bp.blogspot.com/-tjMCGi_kCWw/T0ZmFsPsLuI/AAAAAAAAAzE/-mn2mGpLw8M/s1600/centurygothic-installed.png" imageanchor="1" style="clear: right; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="241" src="http://2.bp.blogspot.com/-tjMCGi_kCWw/T0ZmFsPsLuI/AAAAAAAAAzE/-mn2mGpLw8M/s320/centurygothic-installed.png" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Century Gothic font selected in the Fonts Control Panel applet.</td></tr>
</tbody></table>
<div style="text-align: left;">
First things first... I have an Oracle Reports Server 10g running on a Windows 2008 Server and was attempting to run a report as PDF. The font that I was trying to get to work on this server was Century Gothic. The report engine decided not to use the Century Gothic font as specified in the report definition even though the font was already registered with Windows. </div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
To check if your font is installed on the server, navigate Windows Start->Control Panel, and select fonts. Search the fonts panel by name.</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
If the font is installed on the server and the report is still not using it, the reports server must be using another font in its place. You will have to use <a href="http://docs.oracle.com/html/B14048_02/pbr_font.htm#i1006140" target="_blank">font aliasing</a> to get your font to display properly. </div>
<div style="text-align: left;">
<br /></div>
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: right; margin-left: 1em; text-align: right;"><tbody>
<tr><td style="text-align: center;"><a href="http://3.bp.blogspot.com/-r5LPr8GBzrE/T0ZrjWhwdjI/AAAAAAAAAzU/yqyLq3F3nq0/s1600/pdf-fonts-mssansserif.png" imageanchor="1" style="clear: right; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="320" src="http://3.bp.blogspot.com/-r5LPr8GBzrE/T0ZrjWhwdjI/AAAAAAAAAzU/yqyLq3F3nq0/s320/pdf-fonts-mssansserif.png" width="316" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">List of fonts used in PDF noting the presence <br />
of the MSSansSerif font</td></tr>
</tbody></table>
<div style="text-align: left;">
In order to implement font aliasing properly, you will need to know the exact name of the font that the reports engine is replacing your font with. This can be done quite easily by running the report to PDF, opening the PDF file and navigating File->Properties from the Acrobat Reader menu to produce the Document Properties dialog. Then click on the fonts tab to view the list of fonts used in the document. In my case there were three fonts present: <a href="http://en.wikipedia.org/wiki/Courier_(typeface)" target="_blank">Courier</a>; <a href="http://en.wikipedia.org/wiki/Helvetica" target="_blank">Helvetica</a>; and <a href="http://en.wikipedia.org/wiki/MS_Sans_Serif" target="_blank"><i>MSSansSerif</i></a>. </div>
<div style="text-align: left;">
<span style="font-size: x-small;">Hint: write yourself a simple report to test with to limit the number of fonts.</span>
</div>
<div style="text-align: left;">
</div>
<br />
In my case, I had three fonts present of which only the <i>MSSansSerif </i>was out of place. This would be the target of my font aliasing. This font must be aliased to Century Gothic in the uifont.ali. In addition, I wanted the Century Gothic font to be subset'd into the PDF.<br />
<br />
To accomplish this I copied the Century Gothic font file into the path (D:\Documents\NPAS\reports\GOTHIC.TTF) that was in my REPORT_PATH environment variable (REPORT_PATH=D:\Documents\NPAS\reports). This allows the report engine to read the font file for use in the report. I then had to edit the uifont.ali file (located at ${ORACLE_HOME}\tools\common) and add the alias declaration under the <a href="http://docs.oracle.com/cd/B10464_05/bi.904/b13673/pbr_pdf.htm#i1013607" target="_blank">[ PDF:Subset ] section</a>.<br />
<br />
And this is where the major hint comes in... Acrobat reader removes spaces from the names of the fonts. I am not sure if this is Acrobat readers fault, Oracle Reports fault, or just how its supposed to be, but, you can't just use the names as you see them in the font window as entries in the alias file. In my case Acrobat reported the font as <i>MSSansSerif</i> without the spaces. But the actual name should be <u>MS Sans Serif</u> as separate words. Using an alias of <i>MSSansSerif </i>did not work. It only worked once I used "MS Sans Serif". My final alias entry looked exactly like this:<br />
<br />
<div style="text-align: left;">
"MS Sans Serif" = "GOTHIC.TTF"</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
Don't forget to restart your Oracle Reports Server component after modifying the uifont.ali file. The changes will only be read from the file after restarting the component. </div>
<div style="text-align: left;">
<br /></div>
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: right; margin-left: 1em; text-align: right;"><tbody>
<tr><td style="text-align: center;"><a href="http://4.bp.blogspot.com/-xMPuuaGDarg/T0Zwz7fDmyI/AAAAAAAAAzc/IHai5JnlhPs/s1600/pdf-fonts-centurygothic.png" imageanchor="1" style="clear: right; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="320" src="http://4.bp.blogspot.com/-xMPuuaGDarg/T0Zwz7fDmyI/AAAAAAAAAzc/IHai5JnlhPs/s320/pdf-fonts-centurygothic.png" width="316" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">List of fonts used in PDF noting that the font is now reported as CenturyGothic</td></tr>
</tbody></table>
<div style="text-align: left;">
After restarting the Reports Server component and re-running the report, the Century Gothic font appeared correctly on screen. Also, viewing the fonts in Acrobat reader showed that the Century Gothic font was finally being applied. Note however, that Acrobat reader reports the font as <i>CenturyGothic </i>with no space between the words.</div>dayneohttp://www.blogger.com/profile/04380081381533944922noreply@blogger.com0tag:blogger.com,1999:blog-7826191065335473649.post-2863229693455052032012-02-13T08:23:00.000-08:002012-02-23T09:17:06.883-08:00Javascript not running in IE8<div style="text-align: right;">
IE8</div>
<div style="text-align: left;">
I encountered a really weird scenario today involving Internet Explorer 8 and javascript. On one particular users computer the browser would not execute any javascript. Even after making sure that javascript was enabled by every other means, the javascript just would not run.</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
While instpecting all the different IE settings I could find that I thought could influence script execution, I did notice that McAfee had installed <a href="http://www.ehow.com/facts_7949557_scriptproxy-mcafee.html" target="_blank">scriptproxy</a> as a browser extension. I assumed that this was the most likely culprit causing the unusual application behaviour. I tried disabling the extension, but of course it did not change the situation. </div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
The reason is that the scriptproxy works by tricking other COM based applications into thinking that it is the jscript or vbscript engine. It does this by changing the registry entries of the jscript and vbscript components so that they actually point back at scriptproxy. So even though I disabled the browser extension, the browser would still be trying to instanciate an instance of scriptproxy and not the jscript component.</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
Thankfully the system already had Microsoft anti-virus installed and I was given the go-ahead to remove the McAfee component from the computer. </div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
Unfortunately the McAfee uninstall does not put things back exactly where they were and I was forced to re-register the jscript component manually</div>
<pre class="CodeSnippet">regsvr32 jscript.dll</pre>
<div style="text-align: left;">
did the trick.</div>dayneohttp://www.blogger.com/profile/04380081381533944922noreply@blogger.com0tag:blogger.com,1999:blog-7826191065335473649.post-44013238162349406522012-02-09T11:04:00.000-08:002012-02-09T23:16:35.481-08:00AWR and Oracle Licensing<div style="text-align: right;">
Oracle 10g, 11g</div>
<div style="text-align: left;">
When 10g came along, we thought we were getting some great new performance monitoring and tuning tools to help us diagnose problems and generally make sure our database was running smoothly. This seemed to come in the form of the Automatic Workload Repository (AWR). </div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
All sorts of articles were written about how you can use AWR to troubleshoot various performance problems that you may be having. Many forums and articles showed off how STATSPACK had been eclipsed by AWR, and countless more have written examples on how to use AWR. Testing Oracle 10g for the first time, I was really excited about AWR. </div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
"Look how easy it is! And it's installed and available by default!" </div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
Well, yes it is, but actually.... <b><span style="font-size: large;">you're not allowed to use it!</span> </b></div>
<div style="text-align: left;">
</div>
<a name='more'></a>Yes, that's right. Even though:<br />
<div style="text-align: left;">
</div>
<ul>
<li>it is part of the Oracle kernel of all editions (Standard One, Standard and Enterprise), </li>
<li>and the AWR tables are installed by default, </li>
<li>and statistics capturing is turned on by default, </li>
<li>and there is absolutely nothing stopping you as DBA from accidentally or on purpose selecting from one of the DBA_HIST_* or V$ACTIVE_SESSION_HISTORY views</li>
</ul>
......... in terms of the licensing......... you're actually <b><span style="font-size: large;">not allowed to use it</span></b>.<br />
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
Want proof?</div>
<div style="text-align: left;">
</div>
<ul>
<li>See Oracle licensing information: <a href="http://docs.oracle.com/cd/B19306_01/license.102/b14199/options.htm#CIHGFIAF">Oracle® Database Licensing Information</a></li>
<li>See Burleson's note: <a href="http://www.dba-oracle.com/oracle_news/2004_2_17.htm">Oracle 10g charges extra for 10g automation features</a></li>
<li>Also noted in Tech Republic: <a href="http://www.techrepublic.com/article/dont-overlook-statspack-in-oracle-10g/6176013">Don't overlook STATSPACK in Oracle 10g</a></li>
<li>Also noted by Tom Kyte:
<a href="http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1270893600346813547#1274064800346406040">Performance tunining in 10g using STATSPACK utility</a></li>
</ul>
<br />
<div style="text-align: left;">
I can't believe it has actually taken me this long to read anything to do with this particular licensing fact. <a href="http://docs.oracle.com/cd/B19306_01/license.102/b14199/editions.htm#BABJICBB" target="_blank">But there it is</a>. Oracle Diagnostic pack, of which AWR is a part, is <b>only</b> available to the Enterprise Edition of the Oracle database 10g and 11g, and <b>only as an optional extra</b>.</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
Even though the views are installed, and everything works as it should, and you don't get any, "<i>you don't have this option installed</i>" messages..... you are still not allowed to use it unless you have Enterprise Edition and you have bought the additional diagnostic and tuning packs.<br />
<br />
In 11g, we start to see the first attempts by Oracle to block access to AWR. This comes in the form of the <a href="http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams036.htm">CONTROL_MANAGEMENT_PACK_ACCESS</a> database parameter introduced in 11g. This parameter is set to NONE by default and will cause your AWR reports to error. See the related blog entry: <a href="http://blogs.dayneo.co.za/2012/02/awr-report-missing-diagnostic.html">AWR Report missing diagnostic information with ORA-20023 errors</a></div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
<span style="font-size: large;"><b>Why did we all think we could use AWR in the first place?</b></span></div>
<div style="text-align: left;">
It has actually taken me quite a while to work it out. I had to remember how it was that I came to know about AWR. And then it occurred to me.... it was the Oracle Database Concepts guide. The concept guide notes in the very first sentence under <a href="http://docs.oracle.com/cd/B19306_01/server.102/b14220/mgmt_db.htm#sthref2194">Automatic Workload Repository</a> that:</div>
<blockquote class="tr_bq">
<span style="background-color: white; font-family: Tahoma, sans-serif; font-size: x-small;">Automatic Workload Repo</span><a href="http://www.blogger.com/blogger.g?blogID=7826191065335473649" id="sthref2195" name="sthref2195" style="background-color: white; font-family: Tahoma, sans-serif; font-size: small;"></a><span style="background-color: white; font-family: Tahoma, sans-serif; font-size: x-small;">sitory (AWR) is a built-in repository in every Oracle Database.</span></blockquote>
That does not sound like "optional extra only available to Enterprise Edition versions" to me. And from there, we simply started looking for "how to" articles, none of which ever mention anything to do with licensing.<br />
<br />
<span style="font-size: large;"><b>Conclusion</b></span><br />
So in conclusion.... you are likely violating license agreements on a daily basis. That's the bad news. Go home tonight and say a few thousand Hail Mary's, or do whatever you do to feel better about yourself.<br />
<br />
The good news is... you still have STATSPACK, which <a href="http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1270893600346813547">according to Tom Kyte</a> is free, and still compatible with Oracle 10g, and most likely with 11g. Install that tomorrow and all will be good with the world.dayneohttp://www.blogger.com/profile/04380081381533944922noreply@blogger.com3tag:blogger.com,1999:blog-7826191065335473649.post-34158294202576782142012-02-06T07:57:00.000-08:002012-02-23T09:18:05.393-08:00AWR Report missing diagnostic information with ORA-20023 errors<div style="text-align: right;">
Oracle 11g Release 1 </div>
<div style="text-align: left;">
I seem to be hitting all of the AWR report bugs lately. The latest one is that the AWR report is missing data and shows lots of errors of the form:</div>
<pre class="CodeSnippet">WARNING (-20023)
ORA-20023: Missing start and end values for time model stat: parse time elapsed</pre>
<div>
A bit of searching on Metalink reveals that this seems to be a bug (#7532789) and is discussed in document 1181573.1. The bug information seems to note Enteprise edition only, but I have found the problem on Standard edition too.<br />
<a name='more'></a><br />
While it might list this as a bug, it is actually saying that your system is in fact not configured to capture diagnostic information. The "bug" is that it gives a nasty error instead of telling you that there is no diagnostic information available because "diagnostic and tuning option is not available or not turned on".<br />
<br /></div>
<div>
Setting <a href="http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams036.htm" target="_blank">CONTROL_MANAGEMENT_PACK_ACCESS</a> to "DIAGNOSTIC+TUNING" will certainly solve the problem. But beware, you need to be licensed to make use of this functionality. While it might be built into the Oracle database on all database editions, you are not actually allowed to access it. It is an optional extra available with Enterprise Edition only. See <a href="http://docs.oracle.com/cd/B28359_01/license.111/b28287/options.htm#CIHIHDDJ" target="_blank">license information here</a> and my rant in <a href="http://blogs.dayneo.co.za/2012/02/awr-and-oracle-licensing.html" target="">AWR and Oracle Licensing</a>. It would probably be wise to contact your Oracle consultant and/or Oracle support before making this change.<br />
<br />
Anyway, if you have obtained the relevant licensing, then:</div>
<pre class="CodeSnippet">alter system set control_management_pack_access="DIAGNOSTIC+TUNING" scope=both;</pre>
<div>
<br />
For me, after running a few AWR snapshots, most of the data began to appear in the reports. However some of the report sections continued reporting errors. A restart of the database seemed to sort out the last remaining errors. I now have the full AWR report being produced.<br />
<br />
Obviously, the old snapshots that were made prior to setting CONTROL_MANAGEMENT_PACK_ACCESS will obviously still report errors. There's absolutely nothing you can do about that. But at least from now on, you will be able to see what your DB is up to.<br />
<br />
Aside:<br />
Interesting that they have made diagnostics an optional extra. It is so fundamental to the DBA. Unfortunately for us, procurement will have no idea what any of the options are and will inevitably purchase the "no frills" version. And then everyone will be wondering why Oracle doesn't work. Bad move by Oracle in my opinion. Oracle needs to give DBA's as much free help as possible to diagnose performance problems, and if they don't, the answer will simply be.... "Oracle is slow"!<br />
<br />
Special thanks to <a href="https://forums.oracle.com/forums/profile.jspa?userID=388343" target="_blank">Hans Forbrich</a> for pointing out the license requirements in his <a href="https://forums.oracle.com/forums/thread.jspa?messageID=10138463&#10138463" target="_blank">OTN thread reply</a>.</div>dayneohttp://www.blogger.com/profile/04380081381533944922noreply@blogger.com1tag:blogger.com,1999:blog-7826191065335473649.post-5909177628544927102012-02-06T05:55:00.000-08:002012-02-06T05:55:13.082-08:00ORU-10028: line length overflow, limit of 255 chars per line<div style="text-align: right;">
Oracle 9/10/11</div>
<div style="text-align: left;">
I wish Oracle would update their DBMS_OUTPUT package for the modern world. 255 characters per line just does not cut it anymore. </div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
There are quite a number of solutions around this and all of them involve writing your own wrapper for DBMS_OUTPUT. I quite like the <a href="http://orcasoracle.bloghi.com/2005/10/29/ora-20000-oru-10028-line-length-overflow-limit-of-255-chars-per-line.html" target="_blank">solution</a> provided by <a href="http://orcasoracle.bloghi.com/" target="_blank">Karl Reitschuster</a>. His solution is simple enough to incorporate into anonymous PL/SQL blocks, a factor that I found most attractive over the others.</div>dayneohttp://www.blogger.com/profile/04380081381533944922noreply@blogger.com0tag:blogger.com,1999:blog-7826191065335473649.post-17764123212416767012012-02-03T05:27:00.000-08:002012-02-03T07:20:17.349-08:00Evaluate relative paths using DOS functions (call:MakeAbsolute)<div style="text-align: right;">
Window 7, Windows XP
</div>
<div style="text-align: right;">
MS DOS</div>
<div style="text-align: right;">
<a href="http://www.dostips.com/DtCodeCmdLib.php#Function.MakeAbsolute" target="_blank">call:MakeAbsolute</a></div>
I often hear people harp on about linux shell scripting and how M$ DOS is no where near as powerful. Well that may be so, but DOS can still be really powerful in it's own right. I have found an awesome resource recentely; <a href="http://www.dostips.com/" target="_blank">DosTips - The DOS Batch Guide</a>. They have developed a set of DOS functions that make DOS a breeze and show how powerful DOS can be. They also have tutorials on writing your own DOS functions.<br />
<div>
<br /></div>
<div>
In my case, I needed a method of evaluating relative paths against a given absolute path. The <a href="http://www.dostips.com/DtCodeCmdLib.php#Function.MakeAbsolute" target="_blank">MakeAbsolute</a> function on the DosTips website really helped me out and is now incorporated into almost all of my DOS scripts.<br />
<a name='more'></a></div>
<div>
But before using any of their functions, I seriously recommend that you go through their <a href="http://www.dostips.com/DtTutoFunctions.php" target="_blank">functions tutorials</a>. You will need to know what needs to be included in your script and how to call the functions in general. I am not going to go into that, but will rather provide a few tips for the <a href="http://www.dostips.com/DtCodeCmdLib.php#Function.MakeAbsolute" target="_blank">MakeAboslute</a> function.<br />
<br />
So, the <a href="http://www.dostips.com/DtCodeCmdLib.php#Function.MakeAbsolute" target="_blank">MakeAbsolute</a> function...<br />
The function accepts two inputs:<br />
<ol>
<li>The name of the variable that holds the relative path that must be evaluated</li>
<li>The base path to which the relative path should be applied. (optional, defaults to the current working directory)</li>
</ol>
This might seem fairly simple at first, but there are a few tips to bare in mind:<br />
<ol>
<li>The first parameter to the function is the <b>name</b> of the variable that holds the relative path. So don't try to use % signs with it.<pre class="CodeSnippet">set relpath=subdir\
call:MakeAbsolute relpath "C:\Users"</pre>
Notice that the parameter is the name of the variable (i.e. relpath) and not the value (i.e. %relpath%). This is critical.
</li>
<li>Contrary to 1 above, the second parameter is the <b>value</b> of the base path. If you are using a variable for the base path, then you must use the % symbols for this parameter.<pre class="CodeSnippet">set relpath=subdir\
set basepath=C:\Users
call:MakeAbsolute relpath "%basepath%"</pre>
</li>
<li>Do not use a variable name of "src" as the first input. This will conflict with the MakeAbsolute functions internal variable declaration. Wierd things start happening.</li>
<li>When setting variables, do not use quotes with paths, not even when they have spaces in the path names.<pre class="CodeSnippet">set wontwork="quoted path"
set willwork=unquoted path</pre>
</li>
<li>I suggest that you always use quotes on the base path parameter (second parameter). If the variable used for the base path parameter has a value with spaces in it, then the function will not execute correctly if you do not include quotes around this value.<pre class="CodeSnippet">set relpath=subdir\
set basepath=C:\Program Files\
call:MakeAbsolute relpath "%basepath%"</pre>
Note that there are no quotes when setting the variable, but there must be quotes when using the variable.</li>
<li>If you do not provide a second input, the current working directory is used as base path.</li>
<li>Starting a relative path with a backslash or period or both (\ or . or .\) will all equate to the base path provided in the second parameter (or the current working directory if a base path has not been provided)</li>
<li>Forward slashes are converted to backslashes automagically</li>
<li>Last but quite conveniently; none of the paths provided to or returned from the function have to actually exist.</li>
</ol>
<div>
The following script provides some examples of supported relative paths:</div>
<pre class="CodeSnippet">@echo off
set scriptpath=%~dp0
set siblingfile=sibling.bat
set siblingfolder=sibling\
set fnwsfolder=folder name with spaces\
set descendantfolder=sibling\descendant\
set ancestorfolder=..\..\
set cousinfolder=..\uncle\cousin
call:MakeAbsolute siblingfile "%scriptpath%"
call:MakeAbsolute siblingfolder "%scriptpath%"
call:MakeAbsolute fnwsfolder "%scriptpath%"
call:MakeAbsolute descendantfolder "%scriptpath%"
call:MakeAbsolute ancestorfolder "%scriptpath%"
call:MakeAbsolute cousinfolder "%scriptpath%"
echo scriptpath: %scriptpath%
echo siblingfile: %siblingfile%
echo siblingfolder: %siblingfolder%
echo fnwsfolder: %fnwsfolder%
echo descendantfolder: %descendantfolder%
echo ancestorfolder: %ancestorfolder%
echo cousinfolder: %cousinfolder%
GOTO:EOF
::----------------------------------------------------------------------------------
:: Function declarations
:: Handy to read http://www.dostips.com/DtTutoFunctions.php for how dos functions
:: work.
::----------------------------------------------------------------------------------
:MakeAbsolute file base -- makes a file name absolute considering a base path
:: -- file [in,out] - variable with file name to be converted, or file name itself for result in stdout
:: -- base [in,opt] - base path, leave blank for current directory
:$created 20060101 :$changed 20080219 :$categories Path
:$source http://www.dostips.com
SETLOCAL ENABLEDELAYEDEXPANSION
set "src=%~1"
if defined %1 set "src=!%~1!"
set "bas=%~2"
if not defined bas set "bas=%cd%"
for /f "tokens=*" %%a in ("%bas%.\%src%") do set "src=%%~fa"
( ENDLOCAL & REM RETURN VALUES
IF defined %1 (SET %~1=%src%) ELSE ECHO.%src%
)
EXIT /b</pre>
<div>
This will produce output like the following:</div>
<pre class="CodeSnippet">C:\Users\dayneo\Documents\Visual Studio 2005\Projects\dosscript>myscript
scriptpath: C:\Users\dayneo\Documents\Visual Studio 2005\Projects\dosscrip
t\
siblingfile: C:\Users\dayneo\Documents\Visual Studio 2005\Projects\dosscrip
t\sibling.bat
siblingfolder: C:\Users\dayneo\Documents\Visual Studio 2005\Projects\dosscrip
t\sibling\
fnwsfolder: C:\Users\dayneo\Documents\Visual Studio 2005\Projects\dosscrip
t\folder name with spaces\
descendantfolder: C:\Users\dayneo\Documents\Visual Studio 2005\Projects\dosscrip
t\sibling\descendant\
ancestorfolder: C:\Users\dayneo\Documents\Visual Studio 2005\
cousinfolder: C:\Users\dayneo\Documents\Visual Studio 2005\Projects\uncle\co
usin
C:\Users\dayneo\Documents\Visual Studio 2005\Projects\dosscript></pre>
<div>
<br /></div>
</div>dayneohttp://www.blogger.com/profile/04380081381533944922noreply@blogger.com2tag:blogger.com,1999:blog-7826191065335473649.post-25031849077991520102012-02-02T06:02:00.000-08:002012-02-03T02:31:36.114-08:00Open the Windows Command prompt at the selected folder<div style="text-align: right;">
Windows 7</div>
<div style="text-align: left;">
I am always open to anything that will allow me to work more efficiently. Way back in Windows XP, the various sysutils would install a context menu into Windows Explorer that would allow us to open the command prompt with the selected folder set as the current directory. <a href="http://code.kliu.org/cmdopen/" target="_blank">ContextConsole Shell Extension</a> is another tool which will do the same for the Windows 7 environment.</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
However, with Windows 7, you no longer need to install additional extensions and software. Instead, the context menu option, "Open command window here", is available by holding down the shift key and right clicking on the folder.<br />
<br />
<a name='more'></a><br />
<a href="http://2.bp.blogspot.com/-4ImRhK_4c3Q/TyutCh4JUjI/AAAAAAAAAyk/VCyo5hdKCEM/s1600/open-command-window-here.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="240" src="http://2.bp.blogspot.com/-4ImRhK_4c3Q/TyutCh4JUjI/AAAAAAAAAyk/VCyo5hdKCEM/s320/open-command-window-here.jpg" width="320" /></a><br />
You can perform this function on any local folder. The command line window will open with the selected folder as the current working directory. You can also perform the SHIFT+RIGHT CLICK on the desktop. Clicking on "Open command window here" will open your command prompt at your desktop directory.<br />
<br />
DOS does not support <a href="http://en.wikipedia.org/wiki/Path_(computing)#Uniform_Naming_Convention" target="_blank">UNC paths</a> and so opening a folder on a UNC path using the "Open command window here" would be pretty much meaningless. So the guys at M$ have gone a little further on this one. When using "Open command window here" on a UNC pathed folder, the function will automatically map a drive letter to the first folder on the UNC path.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-tMZZmdyInsQ/Tyu0sUMOVpI/AAAAAAAAAy0/PYT1WOFENPo/s1600/open-unc-path.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="240" src="http://3.bp.blogspot.com/-tMZZmdyInsQ/Tyu0sUMOVpI/AAAAAAAAAy0/PYT1WOFENPo/s320/open-unc-path.jpg" width="320" /></a></div>
For example, I have a development server with a file share. A typical folder path might be "\\WINDEV\DevShare\Systems\MicroCombud". If I was to use "Open command window here" on the MicroCombud folder, the function would automatically map a Z: drive to "\\WINDEV\DevShare" and open the command prompt at "Z:\Systems\MicroCombud". The drive letter map will always map drives in descending order, starting at Z, depending on letter availability. It would be interesting to see what would happen if all drive letters were already used.</div>dayneohttp://www.blogger.com/profile/04380081381533944922noreply@blogger.com0tag:blogger.com,1999:blog-7826191065335473649.post-21692976546609326092012-01-18T08:06:00.000-08:002012-02-09T05:57:17.058-08:00AWR snapshot creation hangs<div style="text-align: right;">
Oracle 10g R1</div>
<div style="text-align: left;">
<br /></div>
I encountered a problem with the AWR snapshot in which the creation would hang. This phenomenon is due to an Oracle bug #7284976. The Metalink bug report indicates that it is applicable to DB version 10.2.0.3 Enterprise Edition and doesn't list any other affected versions, however I seem to be encountering this on DB version 10.1.0.4 Standard Edition running on IBM AIX platform.<br />
<br />
<a name='more'></a><br />
I first noticed the problem when I ran the AWR report and noticed that there were no new snapshots for the day. The snapshots were supposed to be generated every hour, but were not. I thought it was odd, so I checked the DB parameters which all seemed to be in order:<br />
<pre class="CodeSnippet">dayneo@RMSP> show parameters statistics
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
statistics_level string TYPICAL
timed_os_statistics integer 0
timed_statistics boolean TRUE</pre>
I then checked the AWR settings, and they were set to snapshot every hour. Lastly, I tried creating a snapshot using SQL/PLUS at which point the snapshot creation hung, never to return.<br />
<br />
Checking the session activity, I found that my SQL/PLUS session was waiting on "enq: WF - contention". I decided to check the locks just in case:<br />
<pre class="CodeSnippet">dayneo@RMSP> COLUMN blocking_sid FORMAT 999 HEADING 'Blocking SID' JUSTIFY right
dayneo@RMSP> COLUMN blocking_user FORMAT a20 HEADING 'Blocking User' JUSTIFY left
dayneo@RMSP> COLUMN blocked_sid FORMAT 999 HEADING 'Blocked SID' JUSTIFY right
dayneo@RMSP> COLUMN blocked_user FORMAT a20 HEADING 'Blocked User' JUSTIFY left
dayneo@RMSP> select s1.sid blocking_sid,
2 nvl2(s1.username, s1.username || '@' || s1.machine, s1.program) blocking_user,
3 s2.sid blocked_sid,
4 nvl2(s2.username, s2.username || '@' || s2.machine, s2.program) blocked_user
5 from v$lock l1, v$session s1,
6 v$lock l2, v$session s2
7 where s1.sid=l1.sid
8 and s2.sid=l2.sid
9 and l1.id1 = l2.id1
10 and l2.id2 = l2.id2
11 and l1.block = 1
12 and l2.request > 0
13 /
Blocking SID Blocking User Blocked SID Blocked User
------------ -------------------- ----------- --------------------
119 oracle@wcuss6 (m000) 21 DPOSYS@WORKGROUP\FBO
MB
133 oracle@wcuss6 (m001) 119 oracle@wcuss6 (m000)
119 oracle@wcuss6 (m000) 133 oracle@wcuss6 (m001)</pre>
The 21 SID is my SQL/PLUS session. Note that SID 119 is blocking it. Also note that SIDs 133 and 119 are blocking each other. This is where the real problem lies and is why the DB is not generating snapshots.<br />
<br />
The two SIDs that are blocking each other are both MMON slave processes. One of them is an "Auto-flush slave action" and the other is an "Auto-purge slave action". At this time, I do not have a solution for this. Some forums suggest bouncing the DB. I have not yet tried this as it does not seem to critical at this point.<br />
<br />
It would be interesting to know what sort of impact this problem has on DB performance. Will Oracle continue to "auto-tune" if the stats are not being recorded?dayneohttp://www.blogger.com/profile/04380081381533944922noreply@blogger.com1tag:blogger.com,1999:blog-7826191065335473649.post-78953238776949189882012-01-17T03:12:00.000-08:002012-01-17T06:05:58.336-08:00Great tools for identifying a font<a href="http://3.bp.blogspot.com/-4R3jbxdv6xY/TxVlILa7MoI/AAAAAAAAAx8/3ShRr42upeA/s1600/typewriter.jpg"><img style="position: relative; width: 100%; " src="http://3.bp.blogspot.com/-4R3jbxdv6xY/TxVlILa7MoI/AAAAAAAAAx8/3ShRr42upeA/s320/typewriter.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_5698572094686311042" /></a><div><div>I recently wanted to use a font I has seen used on a business card. I spent hours trawling through massive lists of fonts when it occurred to me that there are probably tools on the web that can help me find it faster.</div><div><br /></div><div>A quick google search provided many really good resources. Two stood out immediately:</div><div><ul><li>If you are lucky enough to have an image of the text, then <a href="http://www.whatthefont.com/" target="_blank">WhatTheFont</a> is without a doubt the place you want to be. Simply upload an image of the font and the site will provide you with a list of closest matches.</li><li>In my case, I had a business card on my desk and didn't feel like hassling with the scanner. So my choice was <a href="http://www.identifont.com/" target="_blank">Identifont</a>. The Identifont engine asks a number of questions about the look of the font and provides a list of matching fonts based on your response. A great feature is that it provides an updated list of fonts after each question.</li></ul></div></div>dayneohttp://www.blogger.com/profile/04380081381533944922noreply@blogger.com0tag:blogger.com,1999:blog-7826191065335473649.post-62895324766515057142012-01-12T08:33:00.000-08:002012-01-17T06:21:18.687-08:00Workaround for ORA-24347: Warning of a NULL column in an aggregate function<div style="text-align: right;">
Oracle 10g R2<br />
ORA-24347: Warning of a NULL column in anaggregate function</div>
<br />
I encountered this error while using DBMS_XMLGEN to generate someXML output. And if you're reading this, it's most likely because you encountering the same problem.<br />
<br />
Well, it turns out there is an Oracle bug (#5911073,consult metalink) that throws this error when an aggregate function (such as SUM, COUNT or AVG) receives a NULL input value and is used in conjunction with DBMS_XMLGEN.<br />
<br />
The following script demonstrates the problem:<br />
<a name='more'></a><br />
<pre class="CodeSnippet">dayneo@SANDBOX> create table test_tbl(
2 group_id number,
3 num_value number
4 )
5 /
Table created.
dayneo@SANDBOX> begin
2 insert into test_tbl values(0, 1);
3 insert into test_tbl values(0, 2);
4 insert into test_tbl values(1, null);
5 insert into test_tbl values(1, 3);
6 insert into test_tbl values(2, null);
7 insert into test_tbl values(2, null);
8 insert into test_tbl values(3, null);
9 commit;
10 end;
11 /
PL/SQL procedure successfully completed.
dayneo@SANDBOX> select group_id,
2 sum(num_value) sum_value,
3 count(num_value) cnt_value,
4 avg(num_value) avg_value
5 from test_tbl
6 group by group_id
7 order by group_id
8 /
GROUP_ID SUM_VALUE CNT_VALUE AVG_VALUE
---------- ---------- ---------- ----------
0 3 2 1.5
1 3 1 3
2 0
3 0
4 rows selected.
dayneo@SANDBOX> declare
2 l_ctx dbms_xmlgen.ctxhandle;
3 l_xml clob;
4 l_cur sys_refcursor;
5 l_cnt pls_integer;
6 begin
7
8 open l_cur for
9 select group_id,
10 sum(num_value) sum_value,
11 count(num_value) cnt_value,
12 avg(num_value) avg_value
13 from test_tbl
14 group by group_id
15 order by group_id;
16
17 l_ctx := dbms_xmlgen.newcontext(l_cur);
18 begin
19 dbms_xmlgen.setNullHandling(l_ctx, dbms_xmlgen.EMPTY_TAG);
20 l_xml := dbms_xmlgen.getxml(l_ctx);
21 l_cnt := dbms_xmlgen.getNumRowsProcessed(l_ctx);
22 dbms_xmlgen.closecontext(l_ctx);
23 close l_cur;
24 dbms_output.put_line(l_xml);
25 exception
26 when OTHERS then
27 dbms_xmlgen.closecontext(l_ctx);
28 close l_cur;
29 raise;
30 end;
31
32 end;
33 /
<span style="color: red;">declare
*
ERROR at line 1:
ORA-19202: Error occurred in XML processing
ORA-24347: Warning of a NULL column in an aggregate function
ORA-06512: at line 29</span></pre>
<br />
As can be seen by this script, the SQL statement executes just fine by itself, but when used with DBMS_XMLGEN, the error occurs.<br />
<br />
The workaround depends on the aggregatefunction being used. Fortunately, they are not wildly complex and follow the same pattern. The COUNT function is the easiest one to deal with. I willtackle the SUM, COUNT and AVG aggregate functions here.<br />
<br />
<b>COUNT</b><br />
So this one is the easiest to deal with since COUNT is simply the sum of the number of items that are NOT NULL. Using the <a href="http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions106.htm" target="_blank">NVL2</a> function, we can return 1's for NOT NULL values and 0's for NULL values. The sum of these 1's and 0's will be equal to the COUNT of the values. <br />
<br />
The CNT_VALUE column from the problem script above will now change to:<br />
<pre class="CodeSnippet">sum(nvl2(num_value, 1, 0)) cnt_value,</pre>
<br />
<b>SUM</b><br />
The SUM aggregate function is a little more tricky. For this function we need to check if all of the values in the group are NULL. If they are, then we must return NULL, otherwise we must run the sum and substitute NULLs with 0's using the NVL function.<br />
<br />
The SUM_VALUE column from the problem script above will now change to:<br />
<pre class="CodeSnippet">decode(
sum(nvl2(num_value, 1, 0)), -- count the NOT NULL values
0, null, -- if 0 NOT NULL values are found, return NULL
sum(nvl(num_value, 0)) -- else return SUM, replacing NULLS with 0's
) sum_value,</pre>
<br />
<b>AVG</b><br />
The AVG aggregate function seems like the most complex, but it is just a combination of the COUNT and SUM workarounds above. An average is simply the SUM/COUNT. <br />
<br />
The AVG_VALUE column from the problem script above will now change to:<br />
<pre class="CodeSnippet">decode(
sum(nvl2(num_value, 1, 0)), -- count the NOT NULL values
0, null, -- if 0 NOT NULL values are found, return NULL
sum(nvl(num_value, 0))/sum(nvl2(num_value, 1, 0)) -- else return SUM/COUNT
) avg_value</pre>
<br />
<b>PUTTING IT TOGETHER</b><br />
The followining script output demonstrates the final solution:<br />
<pre class="CodeSnippet">dayneo@SANDBOX> declare
2 l_ctx dbms_xmlgen.ctxhandle;
3 l_xml clob;
4 l_cur sys_refcursor;
5 l_cnt pls_integer;
6 begin
7
8 open l_cur for
9 select group_id,
10 decode(
11 sum(nvl2(num_value, 1, 0)),
12 0, null,
13 sum(nvl(num_value, 0))
14 ) sum_value,
15 sum(nvl2(num_value, 1, 0)) cnt_value,
16 decode(sum(nvl2(num_value, 1, 0)),
17 0, null,
18 sum(nvl(num_value, 0))/sum(nvl2(num_value, 1, 0))
19 ) avg_value
20 from test_tbl
21 group by group_id
22 order by group_id;
23
24 l_ctx := dbms_xmlgen.newcontext(l_cur);
25 begin
26 dbms_xmlgen.setNullHandling(l_ctx, dbms_xmlgen.EMPTY_TAG);
27 l_xml := dbms_xmlgen.getxml(l_ctx);
28 l_cnt := dbms_xmlgen.getNumRowsProcessed(l_ctx);
29 dbms_xmlgen.closecontext(l_ctx);
30 close l_cur;
31 dbms_output.put_line(l_xml);
32 exception
33 when OTHERS then
34 dbms_xmlgen.closecontext(l_ctx);
35 close l_cur;
36 raise;
37 end;
38
39 end;
40 /
<?xml version="1.0"?>
<ROWSET>
<ROW>
<GROUP_ID>0</GROUP_ID>
<SUM_VALUE>3</SUM_VALUE>
<CNT_VALUE>2</CNT_VALUE>
<AVG_VALUE>1.5</AVG_VALUE>
</ROW>
<ROW>
<GROUP_ID>1</GROUP_ID>
<SUM_VALUE>3</SUM_VALUE>
<CNT_VALUE>1</CNT_VALUE>
<AVG_VALUE>3</AVG_VALUE>
</ROW>
<ROW>
<GROUP_ID>2</GROUP_ID>
<SUM_VALUE/>
<CNT_VALUE>0</CNT_VALUE>
<AVG_VALUE/>
</ROW>
<ROW>
<GROUP_ID>3</GROUP_ID>
<SUM_VALUE/>
<CNT_VALUE>0</CNT_VALUE>
<AVG_VALUE/>
</ROW>
</ROWSET>
PL/SQL procedure successfully completed.</pre>dayneohttp://www.blogger.com/profile/04380081381533944922noreply@blogger.com3