Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

Posts Tagged ‘Oracle’

“Refresh” a CSV File

Posted by Tyler Muth on February 5, 2013

In a previous post I discussed a PDF reader that allows you to refresh the PDF after changes are made. This is the same concept except it’s for CSV files. I’ve been using Perl to convert sqlplus spool output to CSV which is much easier to read into R. As I’m updating my code and regenerating sample CSV files, it’s really annoying that Excel gets an exclusive lock on the file. So, I would open it in Notepad++ or vim, but it’s much harder in those tools to pick out the columns and rows to find errors.

CSVed to the rescue!!! Here’s the website for CSVed. It’s a great, simple CSV viewer / editor and it does not lock the file. You simply press the “reload” button or ctrl+r and it reloads the file. Again, so simple but so useful for my use-case.

Posted in Oracle, R | Tagged: , , , | 1 Comment »

“Refresh” a PDF

Posted by Tyler Muth on February 5, 2013

I’ve been working in R (wikipedia entry) a lot lately to graph performance data about Oracle systems. In general I output PDF files. The problem with most PDF readers (on Windows) is that they either get an exclusive lock on the file when it’s open (yes you, Adobe Acrobat), or you have to close and re-open the PDF to see any changes. Enter Sumatra PDF (wikipedia entry, home page). I’ve used on and off for a few years. It’s free, very lightweight & fast to start.

The key feature for this post is that when you open a PDF, it doesn’t get an exclusive lock on the file. So, when I re-run my R code to regenerate the PDF file, Sumatra changes the title bar to [Changes detected; refreshing]… and reloads the PDF when it no longer detects changes. Brilliant! Absolutely brilliant!

Here’s a screenshot:

Sumatra-PDF-Refreshing

Here’s a follow-up post on how to “Refresh a CSV File

Posted in R | Tagged: , , | 6 Comments »

Convert sqlplus spool output to CSV

Posted by Tyler Muth on April 11, 2012

I’ve been doing some work lately with sizing / capacity planning where the source are a few of Karl Arao’s excellent AWR Mining scripts. Had I been reading his blog more I would have known he was going down a parallel path using Tableau as I was moving in the direction of R.  Anyway, I wanted to get the output of these sqlplus scripts which spool to a file into CSV format to import into R as data frames. Yes, R can deal with fixed-width data, but not nearly as well as CSV. If it were just a few files, wrangling the data into CSV isn’t that hard. However, I had ~40 files and I plan on doing this a lot more so it had to be easy and fast. I looked at a couple of existing solutions such as the sqlplus colsep directive combined with sed or XMLAGG. But each had it’s drawbacks.

For me, perl seemed like the best fit as it’s easy, cross platform, and made for text files. I only used one module and it’s one that’s installed by default on most platforms. Anyway, what started as about 10 lines grew to about a 100 lines as I had to handle edge cases. I could be a lot less, but it’s intentionally verbose as I’ll trade compactness for readability every time. By default it automatically uses the column headings as the headings in the CSV output.

OK, enough rambling. Here’s the script on dropbox. I’ll post the whole script at the end of this post for viewing and for people that can’t get to dropbox hosted files. Here are my test case files used in the following examples.

If you don’t supply parameters it returns the usage instructions:

$ perl sqlplus_spool_to_csv.pl
usage: program [--infiles "file.txt" | "*.out"] [--headers [AUTO] | FILE | NONE] [--header_file header.txt] [--help|-?]

This will generate a csv file for every file like “a-*.out”:

$ perl sqlplus_spool_to_csv.pl --infiles "a-*.out"
a-countries.out
a-departments.out
a-employees.out
a-employees_repeat_headers.out

$ ls *.csv
a-countries.out.csv  a-departments.out.csv  a-employees.out.csv  a-employees_repeat_headers.out.csv  a-locations.out.csv

$ head -7 a-countries.out
hr@orcl> select * from countries;

CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
AR Argentina                                         2
AU Australia                                         3
BE Belgium                                           1

$ head -4 a-countries.out.csv
"CO","COUNTRY_NAME","REGION_ID"
"AR","Argentina","2"
"AU","Australia","3"
"BE","Belgium","1"

This example will glue on the headers from a custom text file:

$ perl sqlplus_spool_to_csv.pl --infiles "b-*.out" --headers FILE --header_file employees_headers.txt
b-employees.out

On my laptop it only took 25 seconds to convert a 100M file to CSV (a directory of 40 smaller files took under 5 seconds). Here’s the 100M filecompressed to 17K in case you want it.:

$ ls -lh employees_big.out
-rw-r--r--+ 1 tmuth None 100M Apr 11 15:31 employees_big.out

$ wc -l employees_big.out
556200 employees_big.out

$ time perl sqlplus_spool_to_csv.pl --infiles employees_big.out
employees_big.out

real    0m25.047s
user    0m24.289s
sys     0m0.467s

OK, here’s the perl source in case you just want to take a peak or can’t download from dropbox:

#! /usr/bin/perl
# Author: Tyler D Muth - https://tylermuth.wordpress.com
# Version: 1.1

use warnings;
use strict;
use Getopt::Long;
my (@infiles, $headers, $header_file);
usage() if ( @ARGV < 1 or           ! GetOptions('infiles=s@' => \@infiles, 'headers=s' => \$headers, 'header_file=s' => \$header_file));

$headers = defined($headers) ? $headers : 'AUTO'; $headers=uc($headers);

file_loop();

sub usage
{
  print "Unknown option: @_\n" if ( @_ );
  print qq/usage: program [--infiles "file.txt" | "*.out"] [--headers [AUTO] | FILE | NONE] [--header_file header.txt] [--help|-?]\n/;
  exit;
}

sub file_loop{
	for my $in_file ( map {glob($_)} @infiles ) {
		print $in_file, "\n";
		my $unpack_layout =''; my $header_titles=''; my $headers_mapped='';my $dashes_line='';
		my $header_line_new='';
		open ( INFILE, "$in_file.csv" ) or die "$!";
		get_unpack_layout(*INFILE,*OUTFILE,$unpack_layout,$dashes_line,$header_line_new);
		chomp $header_line_new;
		get_header_titles($header_line_new,$header_titles);

		if($headers eq 'AUTO'){
			$headers_mapped=text_line_to_csv($header_titles,$unpack_layout);
		}else{
			$headers_mapped=$header_titles;
		}
		print OUTFILE "$headers_mapped\n" if $headers ne "NONE";
		while( my $line =  ){
			chomp $line;
			last if ($line =~ /^[[:digit:]]+[[:space:]]rows[[:space:]]selected.*/);	# this will be an issue for non-english sqlplus
			if($line ne $dashes_line && $line ne $header_line_new && length($line) > 2){
				print OUTFILE text_line_to_csv($line ,$unpack_layout) . "\n";
			}
		}
		close INFILE;
		close ( OUTFILE );
	};
}

sub get_unpack_layout{
	my $infile_handle=$_[0];my $outfile_handle=$_[1];my $unpack_layout_int='';
	my $header_line='';
	while( my $line =  ){
		if ($line =~ /-{3,}.*/) { #if the line starts with 3 or more dashes (may cause problems for < 3 character leading columns
			chomp $line;
			$_[3]=$line; #return the line of dashes so we can skip them if repeated
			my @columns = split(' ', $line);
			foreach my $col (@columns) {
				my $col_length = length($col)+1;
				$unpack_layout_int .= "A$col_length ";
			}
			$_[2]=$unpack_layout_int;
			$_[4]=$header_line;
			last;
		}
		$header_line=$line;
	}
}
#----------------------------------------------------------
sub get_header_titles{
	my $header_titles_in=$_[0]; my $header_titles_return='';my $dummy_line='';
	$header_titles_return=$header_titles_in if $headers eq 'AUTO';
	if($headers eq 'FILE'){
		open ( HEADERS_FILE, "		$header_titles_return = ;
		close ( HEADERS_FILE);
	}
	chomp $header_titles_return; $_[1]=$header_titles_return;
}
#----------------------------------------------------------
sub text_line_to_csv{
	my $in_line=$_[0]; my $in_unpack_layout=$_[1];

	my @f = unpack("$in_unpack_layout", $in_line);
	my $out_text = join ',', map {
		s/^\s+//;  		 # strip leading spaces
			  s/\s+$//;  # strip trailing spaces
			  s/#+/ /g;  # replace any pound signs (#) in the line
		qq/"$_"/ } @f;

	print "$out_text \n" if $out_text =~ m/\#+/g;
	return $out_text;
}

Posted in Oracle | Tagged: , , | 14 Comments »

Oracle Database 10.2 De-Supported

Posted by Tyler Muth on January 11, 2012

OK, that’s an alarmist title and is not really true. However, I feel this topic is important enough to warrant the title. I’ve talked to a lot of people lately that were not aware of the actual support status of Database 10.2 and below (it changed on July 2010 and the 1 year grace period ended July 2011). My goal of this post is certainly not to be critical of anyone, just to spread the word and allow people to make informed decisions.

The document “Oracle Lifetime Support Policy” (PDF, 474KB) discusses most of the details. There’s also a FAQ on MOS entitled “Oracle Database 10.2 End of Premier Support – Frequently Asked Questions [ID 1130327.1]” (HTML Version or Full Flash Version (for now…(details on that here)).

Here’s my quick summary of some key points:

  • 10.2 general availability was July 2005
  • “Premiere Support” is what you probably think of as “supported”.
    • It extends for 5 years from the date of general availability.
    • It ended for 10.2 in July of 2010.
    • Oracle offered a 1 year, no-cost grace period for 10.2 premiere support customers which ended in July of 2011. This was basically a year of free Extended Support.
  • “Extended Support” is a for-cost option that allows you to extend Premiere Support for an additional 3 years.
  • “Sustaining Support” is the level of support after Premiere Support ends, assuming you are not paying for Extended Support.
    • This is probably where a large majority of the 10.2 and earlier customers are today, but may not be aware of this.
    • This does NOT include a number of key services such as “New updates, fixes, security alerts, data fixes, and critical patch updates”.

A number of customers that I’ve talked to realized this situation when they went to download the latest Critical Patch Update and could not as they were on Sustaining Support.

The PDF also contains the following chart which is a nice overview of the dates for planning purposes:

Database Support Dates Chart

Posted in Oracle | Tagged: | 10 Comments »

AWR Formatter 1.6 Released

Posted by Tyler Muth on July 12, 2011

I made some minor updates to my previously released Google Chrome Extension called AWR Formatter. Enhancements include:

  • Performance increased by 3x in rendering large AWR reports.
  • Added new tabs to SQL Popup for Query Plan, Color SQL, and AWR SQL Report

The new options in the SQL Popup expose features of the AWR repository that I don’t think many people are aware of, but I think are really useful.

Installation

If you already have it installed, it should update automatically and the version will say 1.6. If you do not have it installed:

I hope to add support for RAC reports soon…

Posted in AWR, Oracle | Tagged: , , , | 52 Comments »

APEX is 57.4x Faster on Exadata

Posted by Tyler Muth on April 12, 2011

…is just about as useful (and accurate) as saying APEX is just as fast on my laptop as it is on Exadata. However, it makes for a great title. Now that I hopefully have your attention, lets talk about the reasons for this post. As many of you know, APEX used to be my primary specialty but I’ve changed roles a bit over the last year at Oracle and my primary focus is now Exadata. I’ve received an increasing number of questions about APEX on Exadata lately so I thought I’d put together some thoughts on them. Additionally, I’ve had a lot of Exadata questions recently that lead me to the conclusion that it’s not as well understood as I thought, at least within the groups of people I regularly interact with. Contrary to popular belief, it’s not a mythological beast powered by ferry dust and unicorn blood. It’s a very well engineered solution that solves a number of exceptionally challenging problems. In my opinion, the more you understand about it, the more you’ll appreciate it.

Does APEX Work on Exadata?

Yep, Exadata is just Oracle. Let me say that again: It’s just Oracle. Exadata runs an 11.2 Database on Linux x64. It’s the exact same binary install if you download those binaries for “generic” Linux x64 11.2 from OTN. So, if your code / app runs on 11.2, it runs on Exadata. There are a few exceptions to this rule for apps that don’t play well with ASM. So, more accurately, if it runs on 11.2 on Linux x64 with ASM and RAC (optional, but desirable), it runs on Exadata. The APEX Dev Team (my old friends and colleagues) did absolutely nothing to port their code to Exadata. I’ve run a number of customer benchmarks with customer’s data and queries and have yet to make a single change to their queries or structures to make them work on Exadata. The only thing I’ve done is to set indexes invisible in Data Warehouse benchmarks, but that was more for fun…more on that later.

What Problems Does Exadata Solve?

The most obvious problem is solves is the I/O bottleneck. While I’m not in the same league as many of the well known performance architects in the Oracle community, I spend a fair amount of time talking to customers about performance problems. In my estimation, the vast majority of the time the performance problems I encounter come down to I/O. Most DBAs (myself included) don’t know near enough about storage. Compounding that problem is the fact that there are often communication “issues” between storage groups and the DBAs.

Lets talk about how we get to data for second. I’ll try and use a Tom Kyte example. Lets take an example of reading a phone book and assume it has an index (paper style) in the back that tells you which names are on which pages. If you wanted to count all of the names in the phonebook, it would be much quicker to just scan through all of the names from beginning to end than it would to go to the index to find each and every name. This is an example of why full table and partition scans are often faster for data warehouse queries than index-based access paths. Flip that around and tell me the phone number for the “Muth’s”, and it would be much faster to look at the index, find out what page the “Mu”’s start on and go to that page. Think of this as an OLTP style query.

So, I/O throughput is particularly important in DSS and Data Warehousing environments because you’re scanning through whole tables and partitions. However, the vast majority of the customers I talk to are running a SAN attached via 2 or 4 Gbps (gigabit per second) Fibre Channel. That connection provides 200 or 400 MB/s (megabytes per second) respectively. If you want to run a full scan on a 200 GB table and you’re connected via 4 Gb FC, some simple math will tell you it will never run faster than 512 seconds or 8.5 minutes Now, compare that to the disk-only 25 GB/s (gigabytes per second) scan rate of an X2-2 full rack with High Performance disks and we can now scan that table in 8 seconds. Our scan rate jumps to 75 GB/s with Flash Cache, but I’ll stick to disk-only numbers for scan operations. It’s easy to see how a query in this example could run 64x faster on Exadata which supports my claim in the title of this post. The results I’ve obtained in POVs I’ve personally worked on have been incredible. On several occasions I’ve had prove to customers that the results were real, especially in the case of I/O intensive operations.

You don’t need to have a 10 TB database to have I/O problems either. The last POV I ran was on a 500 GB data set (including indexes). The database is a shared resource across many organizations and applications. As they’ve built more applications on this database, their indexing strategy hasn’t exactly kept pace. Their top wait events from statspack are mostly I/O related. Additionally, they copy this database nightly to a mirror server that they use for ad-hoc reporting, which as you might expect has even more I/O issues. It’s hard to index for ad-hoc…

What does this have to do with APEX? I’ve seen (and written) plenty of APEX apps that are front-ends to a data warehouse. If you are working with large data sets, doing a lot of aggregation, or allowing a lot of ad-hoc queries, traditional index strategies are often difficult to develop and of marginal value. In many cases like these, the best strategy for performance is to improve the I/O of your system.

So We Don’t Need Indexes Anymore?

I’ve heard this concept from far too many people not to address it. For Data Warehouse / DSS style queries, it is often the case with Exadata that the optimizer will choose a full table or partition scan access path, and consequently not use indexes. In several Data Warehouse POVs I’ve simply set all indexes invisible without negatively impacting performance since the optimizer wasn’t using them anyway. You still need indexes for primary keys, uniqueness, etc, but I think it’s fair to say that implementing a Data Warehouse on Exadata will allow you to eliminate many (dare I say “most) of your indexes. This allows you to reclaim a lot of disk space and certainly makes data loads faster.

However, this concept has nothing to do with OLTP style queries! If you want to retrieve a row based on it’s primary key, which do you think is faster 1) Traversing a few blocks in a b-tree index to find the rowid, then plucking the single-block that contains your row from disk (or RAM) or 2) scanning the whole multi-gigabyte table? We still need / want indexes for OLTP. Dropping all of the indexes on a busy Exadata-hosted OLTP application would be disastrous. This also applies to the APEX builder itself. Those tables are heavily indexed as other than some of the administrative reports, all of the queries are going after a few specific rows to render your page.

But I Thought Storage Indexes Replaced Traditional Indexes

Uh, no. Storage indexes are often mis-understood. They are unique to Exadata and implemented automatically by the storage cells. I actually like to think of them as anti-indexes. They store the high and low values of number and date columns for a storage unit (~1 MB). As we are scanning through the table or partition, and the query has a predicate that filters on one of these columns, we know we can skip all of the blocks in the storage unit if the value we are looking for falls outside of those high and low values. The key word there is “scanning”. They are used in data warehouse style queries, but not OLTP style queries. Clearly, they are no replacement for traditional indexes in OLTP applications.

Many of My APEX Apps are OLTP, Will They Run Faster on Exadata?

Potentially. Even though those apps shouldn’t be doing a lot of table scans, they will often still incur some physical I/O in the form of single-block reads. If the size of your “hot data and indexes” for all of your applications is larger than the RAM you have allocated to your SGA, you’re going to incur physical I/O. The time difference between reading a block from the buffer cache to reading it from disk dramatic. Buffer cache reads should be in the micro-second range whereas reads from disk can take 10’s of milliseconds. Your single block read times essentially fall off a cliff when you go to disk.

Exadata added Flash Cache in the second (V2) and third (X2-2, X2-8) versions. It’s essentially solid state storage (as cards, not disks) in the storage cells that is configured as a write-through (read) cache. So, if you ask for a block and we don’t find it in the SGA, we go to the storage cells. If it’s in Flash, we can return it from there. If not, we grab the block off disk, then optionally copy it to Flash so the next time we need to read that block from “disk” we can return it from Flash instead. The single-block read times from flash are often under a millisecond. It provides a nice step down in access time between RAM and disk. A full rack has 5.3 TB of Flash Cache, so we can cache A LOT of blocks there. I’ve skipped a lot of details here, but I can tell you from the results of several OLTP POVs that I’ve worked on that it plays a very significant role in OLTP performance. There’s a nice white paper on Flash Cache here (PDF).

APEX is Only for Departmental Applications, Why Would I Run it on Exadata?

Anybody connected to the APEX community know that this has a lot more to do with product positioning than it does actual capabilities. There are plenty of enterprise-class APEX apps there. One great example is the new Oracle Store. APEX is a great front-end to database centric applications. Exadata is a great platform for databases. Enough said.

Posted in APEX, Exadata | Tagged: , , | 5 Comments »

Logger, A PL/SQL Logging and Debugging Utility

Posted by Tyler Muth on November 3, 2009

I’ve been gradually building out a logging utility for PL/SQL over the last year or so. It’s been a huge help on some complicated projects, especially in APEX Applications with a lot of AJAX. I’m sure most people reading this have been stuck on a problem, created a logging table, and sprinkled a few inserts in their code to see what’s going on and when. It’s amazing how much faster you resolve these issues if the table is already there and the inserts are now just a short procedure call. Anyone that’s ever used Firebug probably can’t imagine it without console.log() or console.dir(). Yes, you can use dbms_output.put_line or htp.p, but those fall over pretty quick in a web environment with HTTP Posts or worse, a lot of AJAX Posts / Gets. Additionally, if you’re a Tom Kyte groupie, you’ve probably heard him talk about the value of instrumentation more than once.

Now that you know the “why”, lets talk about what Logger is.  It’s just a couple of tables and a PL/SQL package that make all of this easier for you.  I’ll give you a brief summary of features here, but for full details you should go to the project page at https://logger.samplecode.oracle.com.  So, the super short demo is:

logger@orcl> exec logger.log('hello world');

PL/SQL procedure successfully completed.

logger@orcl> select * from logger_logs_terse;

     ID LOGGER_LEVEL TIME_AGO             TEXT
------- ------------ -------------------- ---------------------
     48           16 35 seconds ago       hello world

Features Include:

  • Easily Enable / Disable all logging
  • Minimal Overhead.  There’s even a NO-OP version of the package that you can use for production if you’re really paranoid. It’s just a stub of the logger package that doesn’t write to or depend on any tables (or any other objects).
  • Automatically purges debug messages older than 7 days.  This is completely configurable
  • Ability to time blocks of code
  • Log sys_context(‘userenv’,”) variables
  • Log APEX Item Names and Values
  • Automatically captures key session information such as module, action, client_identifier including APEX session ID, timestamp, call stack.

I’d like to thank all of the community members that tested and contributed ideas for logger including:

I would love to get more feedback from the community as well.  You can either comment here, or use one of the 3 discussion forums I created for logger listed on the project page just above the download link.

The Fastest Way to Store / Retrieve a Cross Session Variable

Posted in APEX, Oracle, PLSQL | Tagged: , , | 44 Comments »

The Fastest Way to Store / Retrieve a Cross Session Variable

Posted by Tyler Muth on October 20, 2009

I’m working on a logging / debugging utility in my spare time that allows a developer to set the logging level. Currently I’m using Conditional Compilation to completely remove all logging code as this was the fastest way I could think of to do it. After talking it over with Tom Kyte, he brought up a very important point that since this technique relies on recompiling the logging package, this will invalidate any code that calls it which could be undesirable (potentially disastrous) in a production environment. His solution? Use Function Result Cache if the logger is installed in an 11g Database which should be plenty fast and not require recompilation to change the logging level.

If I’ve learned anything from Tom it’s the concept of “prove it”, so I set out to find the quickest way to store a cross session variable that will be heavily used in an application. In addition, was function result cache really fast enough given the huge benefit of the solution?

Update: John Scott posted a great suggestion in the comments of using a Global Application Context as there is no Database version / edition dependency issue and it does not require recompilation.  I added it to the test and it appears to be the new winner!  Thanks John!

I’ll post the results first since the test code is a bit lengthy. Each result is the time it took to check the variable 100,000 times.:

----------------------------------------------------------------
Simple Loop:                                     0.00247 seconds
No-op Procedure Call:                            0.06028 seconds
Procedure Call with simple IF-THEN:              0.05350 seconds
Procedure Call with Package Globals IF-THEN:     0.05019 seconds
Procedure with standard select:                  8.50581 seconds
Procedure with Query Result Cache:               9.50794 seconds
Function Result Cache:                           0.75690 seconds
Package Globals:                                 0.06700 seconds
Global Application Context:                      0.33737 seconds

As you can see, Function Result Cache is about 14 times slower than the current solution which results in a NO OP.  However, if we step back and look at this objectively, it’s still REALLY fast and as Tom said, “faster than fast enough”.  If we divide 0.7 by 100,000 we get the time per call to this function which is 0.000007.  Lets say we’re using this in an APEX environment and we call the logging package 100 times per page view which in my opinion is very generous.  Lets also assume our average page view time is 0.3 seconds.   (0.0007 / 0.3 ) * 100 = 0.23% overhead added to our application. In my opinion, 5% overhead would be a fair price to pay for an application that is instrumented with debug code.  This way when (not if) something goes wrong, the time to diagnose the problem should be substantially less. 0.23% isn’t even worth talking about.  The benefit of instrumentation FAR outweighs the overhead.

Here’s my test script (11g Database only):

set serveroutput on

-- grant create any context to user;

create or replace context test_global_ctx using set_global_ctx accessed globally;

create or replace function time_diff(
    p_start    in  timestamp,
    p_end      in  timestamp)
return varchar2
is
    l_return number;
begin
    l_return :=  extract(second from (p_end-p_start))+
           (extract(minute from (p_end-p_start))*60)+
           (extract(hour from (p_end-p_start))*60*60);
           
    return to_char(l_return,'9990D00000');
end time_diff;
/


drop table some_table;

create table some_table(
	object_name	varchar2(30),
	object_owner varchar2(30)
)
/

create unique index some_tab_index on some_table(object_name,object_owner)
/

insert into some_table
    select level object_name, level object_owner
      from dual
   connect by level <= 20;

begin     
    dbms_stats.gather_table_stats(
        ownname => user,
        tabname  => 'SOME_TABLE',
        estimate_percent => 100);
end;
/

create or replace package globals_test
as

	g_one	constant varchar2(255)   := 'One';
	g_two	constant varchar2(255)   := 'Two';
	g_three	constant varchar2(255)   := 'Three';
	
	
end globals_test;
/
show errors

create or replace procedure test1
is
begin
	null;
end;
/

create or replace procedure test1_2
is
begin
	if 1 = 1 then
		null;
	end if;
end;
/


create or replace procedure test1_3
is
begin
	if globals_test.g_one = 'One' then
		null;
	end if;
end;
/

create or replace procedure test2(p_name in varchar2)
is
	l_owner varchar2(30);
begin
	select object_owner into l_owner from some_table where object_name = p_name;
end;
/

create or replace procedure test3(p_name in varchar2)
is
	l_owner varchar2(30);
begin
	for c1 in (select /*+ result_cache */ object_owner from some_table where object_name = p_name)
    loop
        l_owner := c1.object_owner;
    end loop; --c1
end;
/

create or replace function test4(p_name in varchar2)
	return varchar2
	result_cache
	relies_on(some_table)
is
	l_owner varchar2(30);
begin
	for c1 in (select object_owner from some_table where object_name = p_name)
    loop
        l_owner := c1.object_owner;
    end loop; --c1
	
	return l_owner;
end;
/


create or replace procedure test5
is
	l_dummy varchar2(30);
begin
	l_dummy := globals_test.g_one;
end;
/


create or replace procedure set_global_ctx(
    p_var   in varchar2)
is
begin
    dbms_session.set_context(  
        namespace  => 'test_global_ctx', 
        attribute  => 'foo', 
        value      => p_var);
end;
/


accept DUMMY prompt "Ready to run tests which will take a while.  Press ENTER to continue..."
prompt  
prompt  

declare
    l_start     timestamp;
	l_object_name	varchar2(30);
	l_return varchar2(30);
	l_loop_size	pls_integer := 100000;
    
    function format_title(p_title in varchar2)
    return varchar2
    is
    begin
        return rpad(p_title,45);
    end format_title;
begin
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		null;
	end loop; --i
	dbms_output.put_line('----------------------------------------------------------------');
	dbms_output.put_line(format_title('Simple Loop: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');
						   
						   
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		test1;
	end loop; --i
	dbms_output.put_line(format_title('No-op Procedure Call: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');
						   
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		test1_2;
	end loop; --i
	dbms_output.put_line(format_title('Procedure Call with simple IF-THEN: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');
						   
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		test1_3;
	end loop; --i
	dbms_output.put_line(format_title('Procedure Call with Package Globals IF-THEN: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');
						   
	
	select object_name into l_object_name from some_table where rownum = 1; 
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		test2(l_object_name);
	end loop; --i
	dbms_output.put_line(format_title('Procedure with standard select: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');
						   
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		test3(l_object_name);
	end loop; --i
	dbms_output.put_line(format_title('Procedure with Query Result Cache: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');
						   
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		l_return := test4(l_object_name);
	end loop; --i
	dbms_output.put_line(format_title('Function Result Cache: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');
						   
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		test5;
	end loop; --i
	dbms_output.put_line(format_title('Package Globals: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');	
    
    set_global_ctx('bar');
    l_start := current_timestamp;
    for i in 1..l_loop_size 
	loop
		l_return :=  sys_context('test_global_ctx using','foo');
	end loop; --i
	dbms_output.put_line(format_title('Global Application Context: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');	
end;
/

Posted in Oracle, PLSQL | Tagged: , , | 28 Comments »

Octagonal Peg in a Hexagonal Hole (APEX Backward Compatibility)

Posted by Tyler Muth on June 4, 2009

I was going to title this post “Hacking away at an APEX 3.2 app until you can wedge it into a 3.1 instance”, but I shortened it to the current title.  This week I went to import an application I built in APEX 3.2 for an internal customer, only to find that their APEX instance was still on 3.1.  They support a ton of mission critical apps on that instance so upgrading wasn’t an option, no matter how hard I begged.  So, I began the tedious task of editing the export file until I was able to import it.

Just to be clear, this process is NOT SUPPORTED in any way, shape or form by Oracle or myself, so proceed with caution. DON’T EVEN THINK of calling support with issues introduced by this technique. If you start down this path, you are on your own! I’m posting this to try and help some of the more advanced developers in this community and I do not want to introduce a support nightmare for Oracle Support or the APEX Dev Team.

Import Hacking the Slow Way

I started by trying the import in SQL*Plus as it’s much faster and more informative than the web interface for repetitive imports.  The key things that change from version to version of APEX are the procedures and their parameters of WWV_FLOW_API.  Once I edited the SECURITY_GROUP_ID, SET_VERSION, and FLOW_ID (more on these later), I began the Import > Find Error > Edit File > Repeat sequence. One thing that really helps here is a text editor that supports split-screen, synchronized scrolling such as Notepad++ or UltraEdit.  Notepad++ screenshot here.  After a few minutes I came to the conclusion that there has to be a better way.

Import Hacking the Faster Way

I started by querying the ALL_ARGUMENTS view and saving the results in my own table:

create table apex_api_parameters as
	select '3.2' version, object_name,argument_name
	  from all_arguments
	 where package_name='WWV_FLOW_API'
	   and owner = 'APEX_030200'
/

I repeated this step in each database to get the WWV_FLOW_API procedures and parameters for APEX 3.2 and 3.1.  Now I can use SQL set operations to compare them:

select object_name
  from apex_api_parameters
 where version = '3.2'
 minus
select object_name
  from apex_api_parameters
 where version = '3.1'
with common_objects as (
			select object_name
			  from apex_api_parameters
			 where version = '3.2'
		 intersect
			select object_name
			  from apex_api_parameters
			 where version = '3.1')
 select p.object_name,p.argument_name
   from apex_api_parameters p,common_objects c
  where version = '3.2'
	and p.object_name = c.object_name
  minus
 select p.object_name, p.argument_name
   from apex_api_parameters p,common_objects c
  where version = '3.1'
    and p.object_name = c.object_name

Based on the results of the first query, I know the procedures in WWV_FLOW_API that exist in 3.2 but not in 3.1.  These almost always equate to new features.  If I were comparing 3.1 to 3.0 I would see the CREATE_WORKSHEET procedure which equates to interactive reports. The second query shows the parameters to procedures that exist in 3.2 but not in 3.1.

Step by Step Hacking

  1. Make a backup copy of the app you plan to edit.
  2. Using the APEX Application Builder in the newer (source) instance, edit the application to remove any major features that you know don’t exist in the older (target) instance, such as interactive reports.
  3. Export your application from the source instance.
  4. In the target instance, create a new, 1 page dummy application and export it.
  5. Use the SECURITY_GROUP_ID, SET_VERSION, and FLOW_ID from the dummy application in your actual application.
  6. Run my Parameter Diff application (below) and keep it open to use in the next two steps.
  7. Continue editing the application export file to remove any procedures that are not supported in the target instance.
  8. Now find all of the unsupported parameters for existing procedures and delete them.
  9. Test your import.  Repeat steps 7-9 if needed.

To make this process easier, I created an APEX app with all of the API parameters from 3.2, 3.1, 3.0, and 2.2.  You can see it in action here, or download it here.  Keep in mind the supporting objects to 8,000+ inserts, so it may take a minute or two to install.  Happy hacking!!!

Posted in APEX, Application Express, Oracle | Tagged: , , | 9 Comments »

PL/SQL LDAP over SSL – Please Test

Posted by Tyler Muth on January 30, 2009

I’ve been meaning to write this for quite a while now (measured in years), but never got around to it.  None of the DBMS_LDAP code samples support SSL, including the APEX LDAP Authentication Scheme.  Most LDAP directories require SSL for authentication, as you would otherwise send usernames and passwords in clear text.  Depending on your network topography, the risk of someone capturing that data might be very low, but just the same, it should really be SSL. 

One perceived barrier to making this work was my belief that this required an Oracle Wallet which are a bit of a hassle to setup.  There was also some confusion on my part that Oracle Wallets required a license for the Advanced Security Option (ASO), so this would limit the use of this code to only the subset of our customers that have purchased this option.  I’m still looking for the email on this but I believe we confirmed that the Oracle Wallet Manger by itself (such as for DBMS_LDAP or UTL_HTTP) does not require ASO.  Only when you are using the explicitly defined features of ASO such as encrypted column / tablespaces / backups, network encryption, and a few others I can’t remember.  If someone has more info on this, please feel free to comment as I’m too [ busy | lazy | (select adjective from adjectives where id = round(dbms_random.value(1, 100)))] to find it [today | tomorrow | (select sysdate + round(dbms_random.value(1,365)) from dual) ].  When I finally sat down to write this for my current project, I quickly discovered that for SSL Mode 1 (which is what 99.999% of people want), no wallet is required!  Can’t believe I put this off for so long based on a false assumption.

So what does this code do? It can:

  • Bind to an LDAP directory using SSL or non-SSL
  • Authenticate a user using their exact Distinguished Name (DN) such as: cn=tyler.muth, l=amer, dc=mycompany, dc=com
  • Given a starting point (search base) and a username (tyler.muth), search for that user, retrieve their DN, then authenticate.  This handles users in multiple sub-trees.

This is NOT production code! This is NOT production code! This is NOT production code!  Why am I posting it here then?  Because I want people to test it.  I’ve tested it on the project I’m working on, but that doesn’t mean it’s bug free.  I want people who are familiar with PL/SQL and LDAP to review and test my code.  As I receive suggested changes to the code, I’ll update it.  When I feel comfortable that enough people have tested, I’ll blog again and “release it” to the community to use.  My goal is make this as flexible and easy as possible so it just becomes a utility that people can use when they need LDAP.  I can’t make any promises, but if it makes it back into APEX, then you will have helped to improve a product.  If you are not all that familiar with PL/SQL and LDAP, or are looking for a production ready solution, this is probably not for you.  When it’s ready, this blog post will be mostly empty and point to the new location of the code.

I’ve only tested this against OID using an 11.something database, so I’d love to have people test this against Active Directory and any other LDAP directories they work with and at least 10gR2 and XE.  Mark Wilcox is testing it with Oracle Virtual Directory in the next few days as well.  I also ran Wireshark on the database server to capture the communitcation between the database and the LDAP directory.  As expected, in non-ssl mode, my username and password were in the clear.  With SSL enabled, everything was garbage.   Please use Wireshark with caution as it may violate network policies of your organization, get you fired, and accelerate global warming.  It’s a great tool when used ethicly.

I really appreciate the feedback I’ve recieved in the past from this community, especially in pointing out errors with my code.  Thanks in advance for your help.

Customize the ldap_globals package with data from your environment. For most environments, you’ll only need to change g_host and g_search_base.

create or replace package ldap_globals
authid definer
is

	g_host         	constant varchar2(255)   := 'ldap.mycompany.com';
    g_port          constant number          := 636;
    g_search_base   constant varchar2(255)   := 'dc=mycompany,dc=com';
    g_search_filter constant varchar2(255)   := 'cn=';
	g_exact_dn      constant boolean   		 := false;
	g_use_ssl       constant boolean   		 := true;

	-- if g_exact_dn is false, then we will start at the search base and find the user to get their DN.
	--   if true then we will just bind with the DN and you should pass the fully qualified user DN into p_username

	-- SSL is hard-coded to use mode 1, as modes 2 and 3 are highly unlikely.  See the DBMS_LDAP doc for more info.
	-- No wallet is needed for mode 1

end ldap_globals;
/
show errors

There’s no need (that I know of) to customize any code in this function.

create or replace function  ldap_authenticate(
	p_username        in    varchar2,
	p_password        in    varchar2)
return boolean
authid definer
is
	l_retval      pls_integer;
	l_session     dbms_ldap.session;
	l_attrs       dbms_ldap.string_collection;
	l_message     dbms_ldap.message;
	l_entry       dbms_ldap.message;
	l_dn          varchar2(1000);
	l_user_handle dbms_ldap_utl.handle;
	l_return      pls_integer;
	l_user_type   pls_integer;

	l_filter      varchar2(256);

	function do_connect
		return pls_integer
	as
		l_ldap_user   varchar2(256) := NULL;
		l_ldap_pwd    varchar2(256) := NULL;
	begin
		dbms_ldap.use_exception := true;
		l_session := dbms_ldap.init( ldap_globals.g_host , ldap_globals.g_port);

    if ldap_globals.g_use_ssl = true then
      l_retval := DBMS_LDAP.open_ssl(
					ld 				=> l_session,
                    sslwrl			=> NULL, -- wallet location, ie file:/etc/ORACLE/WALLETS/oracle
                    sslwalletpasswd	=> NULL, -- wallet password
                    sslauth			=> 1); -- NO_AUTH :1, ONE_WAY_AUTH: 2, TWO_WAY_AUTH: 3
    end if; 

		return dbms_ldap.simple_bind_s( l_session, l_ldap_user, l_ldap_pwd );
	end;

	procedure do_close
	as
	begin
		l_retval := dbms_ldap.unbind_s( l_session );
	end do_close;

	procedure handle_error(p_message in varchar2)
	as
	begin
		do_close;

		$IF $$logger $THEN
			logger.log_error(p_message);
		$END

		RAISE_APPLICATION_ERROR (-20001,p_message);
	end;

begin
	l_user_type := DBMS_LDAP_UTL.AUTH_SIMPLE;

	l_retval := do_connect;

	if ldap_globals.g_exact_dn = false then

		l_attrs(1)  := 'dn';
    l_filter := '('|| ldap_globals.g_search_filter|| p_username||')';
		l_retval := dbms_ldap.search_s( l_session, ldap_globals.g_search_base, dbms_ldap.scope_subtree, l_filter, l_attrs, 0, l_message );

		l_retval := DBMS_LDAP.count_entries(l_session, l_message);

		if l_retval > 1 then
			handle_error('More than one entry found when searching for users DN.');
		end if;

		if l_retval = 0 then
			handle_error('No entries were found when searching for user DN');
		end if;

		l_entry := dbms_ldap.first_entry( l_session, l_message );
		l_dn := dbms_ldap.get_dn( l_session, l_entry );
	else
		l_dn := p_username;
	end if; -- ldap_globals.g_exact_dn = false

	l_retval := null;
	l_retval := DBMS_LDAP_UTL.create_user_handle(
				user_hd     => l_user_handle,
				user_type   => DBMS_LDAP_UTL.TYPE_DN,
				user_id     => l_dn);

		begin
      dbms_ldap.use_exception := false;
      l_return := null;
			l_return := dbms_ldap.simple_bind_s(
				ld      => l_session,
				dn      => l_dn,
				passwd  => p_password
				);
      if l_return != 0 then
        do_close;
        return false;
      end if;

		exception when others then
			do_close;
			raise;
		end;

    dbms_ldap.use_exception := true;
	do_close;
	if l_return is null then
		handle_error('simple_bind_s did not return a value.');
		return false;
	end if;

	if l_return = 0 then
		return true;
	end if;

	exception when others then
		do_close;
		raise;
		return false;
end ldap_authenticate;
/
show errors

A simple test script. If using SQL Developer, make sure you enable output on the DBMS_OUTPUT tab.

set serveroutput on
declare
	l_return boolean;
begin
	l_return := ldap_authenticate(
					p_username	=> 'first.last',
					p_password	=> '');

	if l_return = true then
		dbms_output.put_line('Authenticated');
	else
		dbms_output.put_line('Fail!');
	end;
end;
/

Posted in Application Express, Oracle, PLSQL, Security | Tagged: , , , | 77 Comments »