1. Perl and Oracle.
I liked your statement:
<quote author="Tom Kyte">
I firmly believe the best java programs are those that have ZERO
"selects/inserts/updates/deletes" in them. Hence, using ref cursors is the way
to go. Lets you tune without bothering those java programmers.
</quote>
And I want to do alike...
There is no problem as far as Java is concerned either pure JDBC (at least in JDK 1.4) or JDBC with some Oracle specific stuff in the code. I'd like to follow that approach as well while programming in Perl. Thus I want to be able to do the following:
a) pass in/out an array of values (bulk store/fetch/update)
b) pass in/out object types (also with an array of such)

But the Perl DBI does not have any methods to do so. Do you know if this is at all possible even at the cost of using Perl-Oracle DBD directly? If so could you give an example or a link to a such?

2. Languages' API and performance.

The query:
select * from table_with_million_rows where rownum<=100

lives as straight select and also in a stored procedure returning refcursor. My client programs are written in Java (using both thin and OCI driver), Perl (only OCI is available) and PL/SQL. This query is (deliberately) softparsed and executed 100 times and all 100 rows are fetched and immediately discarded (I time only the SQL stuff). In both Perl DBI and Java JDBC client software the multiple row fetch is set to 10 rows and the network sniffer indeed shows that there were 10 client-server round-trips.

Each row is on average 100 bytes and at the time of tests I made sure to be the only user logged on DB.

Average timings on my system are:
-----------------------------------------------------------------------------------------
language driver straight query stored procedure returning cursor [msec]
-----------------------------------------------------------------------------------------
Java 1.3 thin Oracle9i 17.1 21.3
Java 1.3 OCI Oracle8i 14.8 17.2
Perl 5.8 OCI Oracle8i 7.7 85.4
PL/SQL internal 13.4 14.7

Client machine: AMD Athlon 1533 MHz (3060 Bogomips), 1 GB RAM
OS: Linux Debian 2.4.19
Oracle: Oracle 8i Client

Server machine: Pentium 3 400 MHz (800 Bogomips), 1 GB RAM, 9x SCSI 10 GB, 2x EIDE 30GB
OS: Linux Debian 2.4.18
Oracle: Oracle 9.2.0.0.0 Enterprise Edition

Network: 100 Mbit Ethernet
-----------------------------------------------------------------------------------------
This 85 msec in Perl results from a known performance bug in Perl-Oracle DBD and hopefully will be fixed. But anyway tests show that the straight query is always by 5-20% faster than exactly the same query hidden in stored procedure and returned as refcursor. I tried less or more complicated queries (seems like: the more complicated query in terms of parse effort and optimizer plan complexity/per fetched row number, the less the difference in performance, stored procedure call overhead is constant and tend to get neglectable when query complexity rises)

Assuming that there is this 5-20% performance hit between straight select and refcursor returned to the client would you still recommend to use stored procedures over straight queries? For testing and tuning it is great but in production it would better to use straight select, wouldn't it? Also how would you explain that Perl DBI/DBD (although its not "really pure" Perl but shared library written in "C" and scripted by Perl) running on remote machine is by 40% faster than PL/SQL running inside the DB server?

And here are the most important code excerpts:
-----------------------------------------------------------------------
JAVA

// run() methods are called from timer engine also 100 times

class Doit implements Timeable {
public void run(Object o) throws Exception {
Connection con=(Connection)o;
CallableStatement stmt=con.prepareCall("{ ? = call test.test_ref }");
stmt.registerOutParameter(1,OracleTypes.CURSOR);
stmt.execute();
ResultSet rs=(ResultSet)stmt.getObject(1);
while (rs.next());
// discard;
stmt.close();
}
}

class Doit2 implements Timeable {
public void run(Object o) throws Exception {
Connection con=(Connection)o;
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from table_with_million_rows where rownum<=100");
while (rs.next());
// discard;
stmt.close();
}
}

-----------------------------------------------------------------------
PERL

# These routines are called 100 times

sub doit {
my $sth=$dbh->prepare("begin ?:=test.test_ref; end;");
my $crs;
$sth->bind_param_inout(1,\$crs,0,{ora_type=>ORA_RSET});
$sth->execute();
while (my $row=$crs->fetchrow_arrayref()) {
# discard;
}
$sth->finish();
}

sub doit2 {
my $sth=$dbh->prepare("select * from table_with_million_rows where rownum<=100");
$sth->execute();
while (my $row=$sth->fetchrow_arrayref()) {
# discard;
}
$sth->finish();
}

-----------------------------------------------------------------------
PL/SQL

declare
t1 number default dbms_utils.get_time;
t2 number;
begin
for i in 1..100 loop
for x in (select * from table_with_million_rows where rownum<=100)
loop
-- discard;
null;
end loop;
end loop;
t2:=dbms_utils.get_time;
dbms_output.put_line('Time: '|| (t2-t1)*10/100 || ' msec');
end;

declare
t1 number default dbms_utility.get_time;
t2 number;
type rc is ref cursor;
cur rc;
x table_with_million_rows%rowtype;
begin
for i in 1..100 loop
cur:=test.test_ref;
loop
fetch cur into x;
exit when cur%notfound;
-- discard;
end loop;
close cur;
end loop;
t2:=dbms_utility.get_time;
dbms_output.put_line('Time: '|| (t2-t1)*10/100 || ' msec');
end;


-- packaged function returning refcursor

CREATE OR REPLACE PACKAGE test IS
type rctype is ref cursor;
FUNCTION test_ref RETURN rctype;
END test;
/
CREATE OR REPLACE PACKAGE BODY test AS

FUNCTION test_ref return rctype IS
rc rctype;
BEGIN
open rc for select * from table_with_million_rows where rownum<=100;
return rc;
END test_ref;


END test;
/

3. SQL*Loader vs. external tables
We have the CSV format using SOH (ASCII 0x01) and STX (ASCII 0x02) as field terminator and record delimiter respectively (in this example it is not necessary but we have data that may contain any printable characters including newline, tabs, spaces, form feeds, etc.). The SQL*Loader control file looks:

LOAD DATA
INFILE bds_2_pblk.csv "str X'02'"
INTO TABLE bds_2_pblk
REPLACE
FIELDS TERMINATED BY X'01'
TRAILING NULLCOLS
(BDS_ID,PBLK_ID)

And it all works fine. But when I want to use Oracle 9i's external tables:

create directory load_dir as '/var/tmp';

create table test_loader
(bds_id number, pblk_id number)
organization external
(
type oracle_loader
default directory load_dir
access parameters
( records delimited by X'02'
fields terminated by X'01'
)
location ('bds_2_pblk.csv')
)

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "identifier": expecting one of: "double-quoted-string, hexprefix, newline, single-quoted-string"
KUP-01008: the bad identifier was: X
KUP-01007: at line 1 column 22
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

I tried (almost) everything and it just doesn't work! I get syntax error either already at "create table..." or yet another at the "select * from test_loader" when I enclose X in double quotes moreover files like TEST_LOADER_XXXX.bad/TEST_LOADER_XXXX.log are created and the error says:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

I reckon the problem is 99.99% trivial and reduces itself to the magic word HEXPREFIX in error KUP-01005. Am I right? So what is the syntax of hexprefix in external table statement? I thought SQLLDR and external tables are technically the same thing. But are they really? Internally maybe but at least syntactically they are not.
Sure I could use the hint from your book (multiple character separators e.g. '||\n') but in first place we would like to use the existing format. And I searched through the Internet and found nothing. You seem to be my last hope :-) A derivative question: How can I incorporate control characters in PL/SQL string literals? Maybe I'm blind but there is no way to do this according to the PL/SQL manuals! Well chr(n) and concatenation would do the trick but I want that in literal strings.

4. When archive logs get full.
What happens when the database is running in archive mode and the disk(s) where archive logs are stored gets full? With online logs they simply get reused in a circular fashion. Will they (archive logs) be overwritten in a circular fashion as well. If not what does the Oracle do about that? Do archived logs go to a 'bit-bucket'?