transactions and pl/sql developer

i'm just learning my way around pl/sql developer, which generally seems to be a more productive environment for day-to-day development work than, oh, say, oracle enterprise manager. haven't used toad enough to do a fair comparison.

something which confused me because i'm new to oracle dev, and confused simon because he's used to toad rather than pl/sql developer, is that dev windows in pl/sql developer tend to reuse existing transactions, so that if you run DML in window a without committing, then read from that table in window b, you'll see uncommitted changes. if you read from that table in a completely separate sqlplus window, you won't see the uncommitted changes.

run a script against a db from the commandline

 

sqlplus /@ @

e.g.:

 

C:\andrew.chalmers>sqlplus system/passwordhere@aboded12_local @x.sql
SQL*Plus: Release 9.2.0.5.0 - Production on Thu Nov 25 09:12:03 2004
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Table created.
Package created.
Synonym created.
Package body created.
SQL> quit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

submitting multiple chunks of data to a(n Oracle) stored procedure in a single hop

there are a few different approaches.

• comma delimited varchar2 params - supported by ODP.NET and MS provider, ugly as hello kitty, need to write an encoder at the middle tier and a decoder in the database. Supports jagged input vector sizes (see below).
• ArrayBinding over a standard stored procedure - ODP.NET only. Stored procedure is standard, middle tier effort is slightly lower than the encoder necessary for comma delimiting. Does not support jagged input vectors (see below).
• Associative Arrays - Stored procedure accepts one associative array for each input parameter. Middle tier effort is about the same as that for ArrayBinding. Supports jagged input vectors.

I haven't done any performance testing as yet. I don't know enough off the top of my head to identify a massive perf advantage or disadvantage in any of the approaches.

"Jagged input vectors" can best be explained with an example. Consider a simple "InsertParentRow" stored proc which accepts three parameters, one for each field in the target table. If fifty rows are to be inserted, each of the three parameters will need to take fifty different values in turn. Consider now a "InsertChildRow" stored proc which accepts two parameters, one for each field in its target table; one hundred rows are going into this table, so each of the parameters to this proc will need to take on a hundred values in succession. Imagine now that we aggregate the two sprocs into a "InsertParentAndChildRows" sproc. 50 rows are going into the parent table, and 100 into the child. The first three params (destined for the parent table) must take 50 different values, and the last two must take 100 - this is an example of jagged input vectors. Because ArrayBinding requires you to provide a fixed number of values for each parameter, you'd be in a position where you have no data to provide for the parent table fields in the last 50 parameter value sets. Because comma-delimiting and Associative Arrays support jagged input vectors, this isn't a problem you need to deal with in those approaches.

We're using Associative Arrays to aggregate tens of row inserts into a Grandparent, parent and child table complex into a single stored procedure call.