the batch executes in jdbc would be just as fast.

do you have a tkprof of the optimal solution in java?

Oracle, Informix, you name it.


consider the jsp that asks you for username and password. It'll execute a query like:

"select count(*) from my_user_table where username = '" + UserName +
"' and password = '" + PassWord + "'"

if count > 0 then -- you are IN...

using concatenation instead of binds. great, I'll give you a username/password of

username = BOB
password = a' or 'a'='a


well, your query turns into

select count(*) from my_user_table where username = 'BOB'
and password = 'a' or 'a'='a'


hmm. I can change result sets, inject arbitrary SQL into your SQL. Not good.


Binds -- they are not just for performance....

do you do dynamic sql in your plsql?

if so, sure, if you do not use bind variables - your plsql is subject to it.


do they NOT use bind variables?

if so, if they concatenate strings, you are very (even in a worse way) prone to it.

Consider the routine in java or vb:

sqlStmt := "begin p( '" + some_inputs + "' ); end;"


looks blindingly simple, cannot go wrong, or can it.

I give you some inputs:

abc' ); delete from t; dbms_output.put_line( '


suppose ABC is valid, p will be happy with that. Now what block gets executed:

begin p( 'abc' ); delete from t; dbms_output.put_line( '' ); end;


now, replace delete from t with pretty much anything you want, execute immediate 'drop table important_table'; -- think about what might go there and you would just execute.


any application that accepts inputs from users and does not use bind variables to get that to the database -- any database -- is 100% prone to sql injection security bugs

huh?

this question needs an explanation.

seem to me you only need two types here?

numArray
dateArray

add a third for completeness

vcArray

and you would have them all. I don't know why you feel compelled to make all of those types?

Its been around for as long as people have been gluing in strings. I added it to the "bind variable repitoire" solely because people said "well, I don't need to do this in sqlserver, blah blah blah (they have no shared pool)"

So, I gave them yet another good reason that they need to do it (ESPECIALLY in sqlserver -- it is frightening what sql injection can do in their database!)

example of what?

if you are executing SQL in ORACLE, sql injection is limited to:


a) me changing the meaning of your query (the username/password example). This will give me access to stuff I otherwise would not have access to.

b) calling functions that take IN parameters and are callable from SQL. So, I might be able to run some of your code you did not intend me to run.


At the end of the day, it doesn't matter -- it is an 100% verifiable security hole and a performance sink.

In SQLServer - the security hole is (surprise) even bigger -- in sqlserver that statement you built would fly, it would execute.

you declared a type per variable.

I would declare three types and use them over and over and over.

...
create or replace type CS1_NUM_ARRAY as table of number;
create or replace type CS2_DATE_ARRAY as table of date;

create or replace type CS3_NUM_ARRAY as table of Number;
create or replace type CS4_NUM_ARRAY as table of Number;
create or replace type SS1_NUM_ARRAY as table of Number;
create or replace type SS2_DATE_ARRAY as table of date;...


why would you do that.

so, you only need 3 array types -- no one per input value.

YOU USE BIND VARIABLES -- YOU NEVER GLUE IN STUFF LIKE THAT.



your query would be


"select * from user_tab where user_id = ?"

period, end of problem.


Do this for me -- search google for:

"sql injection"


and tell me what happens when an end user inputs

abcd' or 'a' = 'a


into your "enter a username field". Hmmmmmmm -- that won't happen with binds (and you'll achieve slightly more scalability).


seek out and destroy ALL JAVA CODE that doesn't use bind variables on your system. If not for the performance, for security.

ouch, hurts my head to think about it.

guess I would concatenate all of the columns together (say the columns are varchar2(10)) and substr them back out:


ops$tkyte@ORA920LAP> select * from t;

C1         C2         C3         C4         C5         C6
---------- ---------- ---------- ---------- ---------- ----------
VIT        a          b          c          d          e
a          VIT        b          VIT        d          e
a          VIT        b          VIT        VIT        e
VIT        VIT        b          VIT        VIT        e

ops$tkyte@ORA920LAP> update (select t.*,
  2                 substr( data,  1, 10 ) new_c1,
  3                 substr( data, 11, 10 ) new_c2,
  4                 substr( data, 21, 10 ) new_c3,
  5                 substr( data, 31, 10 ) new_c4,
  6                 substr( data, 41, 10 ) new_c5,
  7                 substr( data, 51, 10 ) new_c6
  8            from (select t.*,
  9                         rpad( decode(c1,'VIT',null,c1), 10 ) ||
 10                         rpad( decode(c2,'VIT',null,c2), 10 ) ||
 11                         rpad( decode(c3,'VIT',null,c3), 10 ) ||
 12                         rpad( decode(c4,'VIT',null,c4), 10 ) ||
 13                         rpad( decode(c5,'VIT',null,c5), 10 ) ||
 14                         rpad( decode(c6,'VIT',null,c6), 10 ) data
 15                    from t
 16                   where c1 = 'VIT' or c2 = 'VIT' or c3 = 'VIT'
 17                      or c4 = 'VIT' or c5 = 'VIT' or c6 = 'VIT' ) t
 18          )
 19     set c1 = new_c1, c2 = new_c2, c3 = new_c3, c4 = new_c4, c5 = new_c5, c6 = new_c6
 20  /

4 rows updated.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select * from t;

C1         C2         C3         C4         C5         C6
---------- ---------- ---------- ---------- ---------- ----------
a          b          c          d          e
a          b          d          e
a          b          e
b          e

ops$tkyte@ORA920LAP> 

erh?

don't understand what you are saying.

(and i told you 'hash question is bigger then a breadbox, when I'm taking questions we can deal with it there')

huh????

where are you coming from?


<quote>
Tell you what -- code it right for Oracle, code it right for SQLServer and then
run 10, 100 conncurrent sessions (you know, like the "real world") and see what
happens. See which one scales. See which one flies then.
</quote>

where is that a "dis" -- thats a challenge.

dude, you are just so so sooooo far off base, you don't even know. you don't even know that using binds would make for better sqlserver applications. sigh.


lead horse to water
can you make them drink.
sometimes yes.
sometimes no.

you like what you know.
you know what you like.

wish i had a time machine so i could transport you back to your early days with sqlserver -- so you could remember how confusing that particular product was the first time you saw it as well.

sigh.

sqlserver is written in C. C is an old language. does that make it archiac? Have you even looked at transact sql? wanna talk archiac?

I know lots of people who do Oracle just dandy without advance degrees or consulting. Perhaps they

like what they know.
know what they like.

and they would find sqlserver excessively "hard" as well.

I was making a reference to their dig on Ada (which by the way was/is a really cool language. had tons of really nice features)

I was making the point that the age of the language, in reference to what I thought was a really silly comment:

It's 1970s technology - ADA, jeezuz !

has nothing to do with nothing. Compare something on it's merits, not age. Age has nothing to do with anything here.

I'm from the 70's -- geez, actually i predate the 70's -- geez, does that make me "old technology". I sure hope not.

Let them compare PL/SQL (which is Ada like - pascal like - modula like - pl/1 like) to T-SQL -- the barest of scripting languages ever (well, since the DOS batch/cmd scripting language that is)...



The age of something has nothing to do with anything.

1) shots in the dark, hmm, susposition, paranoia on your part is what I supose. Perhaps they just want to know who you are and keep track of what people use -- how they use it -- tie it all together. You know, like *lots* of sites do (i've accounts on MS sites -- can you spell "passport"? I've accounts on IBM sites -- to get at their documentation. I've accounts on Oracle sites as well -- and you know what -- they don't actually spam you)

so bzzzt. wrong answer.

2)

If Oracle > SQL Server( or Sybase) AND PL/SQL > TSQL then...WHERE is the
Oracle for SQL Server programmers documentation? (and not the cr*p that's
published in the Oracle migration workbench docs - that's a joke)

I don't know, why would you expect such a thing? there should be a "Language X for programmers of Language Y" book for every language?

tell me, whats the title of the "transact sql for plsql programmers" book?

Have you read the PL/SQL documentation?

Have you realized that perhaps there is not much mapping between the scripting language that is T-SQL and the language that is PL/SQL?

that perhaps you need to learn to program in PL/SQL -- not how to emulate sqlserver in Oracle (that -- that is where things fail. But to be fair, the opposite is true as well -- if you try to get SS to run an Oracle program -- well, good luck, they cannot -- way way too many differences in the concurrency and locking model. What works in Oracle failes in SQLServer and vice versa)

Have you bought any of the hundreds of PL/SQL books out there? (there are ALOT)

3) good question -- no wait, bad question.

Tell me -- where'dya get the idea that temp tables are a "good idea" in the first place? (sqlserver makes you use them at the drop of a hat. In SS it is "a good idea". In Oracle they are "not necessary, not used". We got along fine without them for many years before SS was even a database).

I've written lots of Oracle code.
I've used a temp table once (as an interface table).

I've taken dozens of multi-page SS stored procedures (wholly unreadable mess of procedural logic) and turned it into a single SQL statement which it should have been from the get go.


Here is an Oracle temp table done right:


select * from
( YOUR_QUERY you would have put into temp table 1) a,
( your_query you would have put into temp table 2) b,
.....
where


temp tables are a crutch needed by sqlserver -- temp tables are a tool you can use in Oracle if and when needed -- you just define them as part of the application itself.

Guess what -- this is in fact "Oracle", not "Sqlserver". If we did things just as they did -- what would be the point? And why pray tell should the rest of the world "do it just like MS"? You do understand that MS is the latest entry to the rdbms market right?

4) Ok,

create or replace function foo return sys_refcursor
as
l_cursor is sys_refcursor;
begin
open l_cursor for select * from emp;
return l_cursor;
end;


hmmm, you're right, its really hard??? or not? I don't know, as a coder, I sort of like having formal named parameters for outputs or a clearly defined return value from a function.

Tell me, in sqlserver:

exec foo

does that return

a) 0 result sets
b) 1 result set
c) N result sets

and if b or c is true (but you cannot tell without reading the code), what do those result sets mean? what order are they in?


Now, Oracle

create procedure foo( p_input in empno, p_emp_result_set out sys_refcursor )


Well, I can sort of see the inputs and outputs and it is a bit "self documenting"

In the client -- what extra code do you write? (i write no extra code, if you are -- it is self inflicted).

In the server what extra code? well, a declare -- that's it.

(in 8i, and before you must define once in your database a package like this:

create package types as type rc is ref cursor; end;

and then use the datatype types.rc instead of sys_refcursor)


again, I'll say this -- this is Oracle, it is different than SS. SS is different than Oracle. I've never understood this ref cursor/result set thingy here cause I've never had anyone show me line by line "what is so hard" about this -- never (there a challenge -- show me the SS code and show me the Oracle code and show me why the Oracle code is hard/obscure/ungainly/large)

5) Sybase does not default to a clustered index.

But anyway "why does a feeling" mean anything. do you have the numbers, the benchmarks, any facts that the overhead of their clustered index (which actually makes inserting slower in their database BUT spreads the inserts out so their extremely poor locking and concurrency model appears to sort of work in some cases......) is a "good thing"

no - it does not exist (in the positive sense). SS uses clustered indexes in an attempt to spread inserts out all over a table because of extreme contention they would otherwise experience.


So.... do you have bitmap indexes? hash clusters? IOTs (without the 110% overhead of emulating that in SS????)? b*tree clusters? partitioning? parallel operations? (list goes on and on and on and on and the answers are "no")


6) you provide no example, but the only time I see this is when you have code that goes on for pages and pages and pages with lots of if then else if yadda yadda yadda (calls out for subroutines). It is the parse tree -- not the size in bytes of the source code.

It all does really (truly) come back to "good code". you are using procedures -- not packages (why -- because, well, tsql does not have this functionality, its not a programming language, its a scripting language but I digress).

You have a single, monolithic, huge, page after page of code procedure.

It follows none of the "best practices" developers have been taught since the dawn of computers (modularize your code, use the language to help you develop modular, maintainable code)

tell you what -- how about you post the code that didn't compile, and let people comment on whether it falls into "a good idea" or "really bad practice, regardless of the language"? I post code all of the time, no one will mind more code....

7) It is not all about the developers, you have taken a far too ethnocentric view of YOUR world

I've said it before and I'll say it again. its all about the DATA. Your applications, they will be born, live and die many times before the data goes away. the data we work with today was in the most part first developed in the 60's. The data far outlives the applications.


So, if your applications screw up the data -- we've got a problem.
If you don't understand concurrency control, we've got a problem.

Tell me -- why do developers thing "tis OK to get magazines on my favorite language, get training on it, go to developer days to learn neat gui things, learn interesting algorithms" but -- even though their job is to develop DATA BASED applications -- they ignore the very tool they are using???? I'll bet you've had training in programming -- so, tell me database programmer -- how many hours of training in relational databases have you logged?

Don't get me wrong, I'm a developer -- I started as a developer. PL/1 (archiac language from mainframes, mainframes coming from the 1960's means they must not be used anymore). Using SQL/DS, DB2, then ingress, gupta, dbase even. Then informix, sybase, oracle, sqlserver. One thing I learned was a successful DATA BASED application depends on someone (gasp) understanding a database (i'm embarrassed by some of the bone headed mistakes and massive misunderstandings I had when first learning this stuff). I also learned that -- hey, these products are different.

Windows is an operating system
Unix is an operating system
VM/CMS is an operating system

guess what -- they are different. (they are all "posix" though, hmm, but if I try to code on unix what I code on windows or vice versa -- guess what happens??)

I can recommend a book -- you probably are not interested at this point, but I'll offer it up. It is called "expert one on one Oracle" -- accessible from my home page. I try to show you the water, and how and why to drink it. (i even cover many of the above topics believe it or not, even the clustered index thingy) anyway....

8) yes they are -- even in SS believe it or not.

9) you are kidding right? you've never had them do an anti-oracle presentation? man, you are missing a great thing (i've got the slides, it is funny -- i love to be in the room at the same time with them, just to help clear up some points of their confusion). give me a break.

and -- who is "slateing" them here. I'm saying "HEY- we are not sqlserver, they are not oracle -- we are different. do not expect to take code that works in SS and run it on oracle unchanged (and vice versa). Not more then you would expect to take windows code and run it on unix and vice versa. it just don't fly"

Wait -- i don't have to diss SS, they do it themselves:

</code> http://www.sql-server-performance.com/lock_contention_tamed_article.asp <code>

<quote>

Granted, if you've only got a few occasional users, you won't have much trouble with SQL Server's out-of-the-box behavior. You'll be hard pressed to see these problems with simple in-the-office tests or deployments involving just a few users. But throw a couple hundred concurrent users at your database and a constant stream of INSERTS and UPDATES with quite a few DELETEs sprinkled in, and you'll start reading Oracle literature and eyeing your war chest. However, I've got a solution for you that will only cost you a code review, a few minor tweaks, and a system test. You do have a system test procedure in place, right?
</quote>

and i love the "solutions" proposed in the following paragraphs (makes interesting reading).

solution 1 includes this caveat:

...
For financial code and denormalized aggregates (those little counters of related data that you stash away and try desperately to keep accurate), you should play it safe and not use this technique.
.....

solution 2 includes this:

....
However, be aware that if you are wrong and lots of rows are affected, either the database will take the initiative and escalate to page locks, or you'll have a whole army of row locks filling your server's memory and bogging down processing
.......

and closes up with this:

<quote>
Disclaimer
Use these techniques with caution and discretion. The way I approached it was to look at all my stored procedures and ad hoc queries, and based on my understanding of where and how they were used, I decided whether it would be acceptable for the caller or user to get possibly incorrect results for NOLOCK, and whether it was likely that more than a few dozen rows would be locked with ROWLOCK. In almost all cases it was fine, but maybe for your code you should be more careful. You might need to produce separate procedures based on whether or to lock, and how to lock. There are other incantations (PAGLOCK, TABLOCK) which you might want to use when you know the UPDATE or DELETE query will affect many rows.
</quote>

so tell me how a PROGRAMMER who doesn't understand the database is going to figure out this stuff?????? Remember -- this is from the "easy to use guys" right -- but wait, is it really or have you been lulled into a sense of "this is easy". Under the covers I'll betcha you there are nasty old bugs you are not even aware of in your data (i see it time after time after time -- not just in SS but in Oracle and every database - -why? cause the programming professionals did not bother to understand or learn the tool they are using)


The facts are in reality (i love it when people invoke "real world", as if we live in a fantasy planet somewhere where all is good and lovely. hmmmm).

Anyway, if you want to read it about it -- i wrote about it. people write about it. Heck, even the sqlserver pros write about it -- how to get around their concurrency model.


Here -- one more challenge. SHOW ME WHERE on this page I've "dissed" sqlserver?????

<quote>
Tell you what -- code it right for Oracle, code it right for SQLServer and then
run 10, 100 conncurrent sessions (you know, like the "real world") and see what
happens. See which one scales. See which one flies then.
</quote>

does that "diss them" or does it point out "real world reality" that

a) code it for sqlserver and run it on oracle isn't going to work (not anymore then code it for oracle and run it on sqlserver)

b) see which one scales -- please, benchmark it, test it -- the proof is in the pudding. SEE which one flies.

10) NOW, now I'm LOL, actually ROTFL

I'll play dictionary here:

auto commit: mode used by databases that have severe contention and locking issues. makes it impossible to perform transactional processing, leading to data integrity issues and corrupt data. DBA's have a love hate fear of this mode. They love it because it doesn't lock up their database. They fear it because they know it will lead to data integrity issues. It is not a feature, it is a failing.



Documentation? Have you

a) taken ANY training on relational databases (the tool you are using)
b) read the concepts guide (which does actually cover this stuff)
c) asked anyone with experience for help



The migration from SS to Oracle doc would be very short, here it is:

"forget everything you've 'learned'. These are different beasts, with different design spec's and different goals in mind. You need to read the concepts guide, the application developers guide and learn the tool you have at your disposal. It is not something you will learn and master in a day, or even a week. But the same is true of SS -- just as you probably learned something new each day with that tool -- you will with Oracle."



So, did SS make that investment for the other way? do you have the 100 most common PLSQL migration mistakes? I'd love to see that myself

this is software, this is software design, this is stuff based on scientifically proven fact.

this is not dogma. this is not a war.

sorry -- i thought I asked good questions too. no redress, oh well.


re-look at your first post and see who is coming in with dogma, sourgrapes, et.al. feel free to follow up some of my questions if you like. Like -- other then the fact you had to *learn* about ref cursors -- much like you *had to learn* about how sqlserver did it at sometime -- Like how ref cursors are this onerous, terrible, hard to code, many times cumbersome feature? i'd really really be interested -- really.

or maybe why "autocommit" is "a good thing" (it is the worst thing one can imagine really -- read a bit about databases and transaction processing and you'll sort of know "why")


or just point out where I've "dissed" the competition? that's how this sort of started....


or give us the example code that blows up -- just to see what it might look like?

no coronary here. fit and healthy. no worries with that.

Vietnam was before my time as well -- but this isn't a philosophical thing here either.

hah hah, lol.

thanks for the might fine laugh.

tell me, can you build something like say "a banking system" on that? or maybe a data warehouse to mine all of the stuff wal mart sold last week? (i can see it now, an object per sale, message all objects).

appropriate for somethings perhaps -- but in general? sorry. I don't agree.

client server was giving the users what they wanted. or at least what they thought they wanted. that -- well, we can see how "good" that idea was.

I see it all of the time "oh, well, we sort of modeled the objects from this perspective. sorry but your question cannot really be answered very efficiently (if at all) from our model. but that's ok, we'll just model it differently and duplicate the data over".

well, I guess time will tell at the end of the day.

i like the asbestos concept......


we did have b*tree clusters way back then -- they would (do) cluster data in a manner very similar to IOT's. Used quite extensively in the data dictionary to keep all of the data about a segment physically colocated together.

given that these real financial systems are governed by international law and real ones have constraints like -- you MUST ensure each transaction is commited in two geographicaly separate databases (eg: to prevent something like a world trade center attack from wiping out a million dollar transfer) -- I think we'd be interested to hear about:

o the availability and recoverability requirements here. Whats the disaster recovery scenario here? how did you accomplish that? or is the data deemed "losable" -- or is the time to recovery measured in days instead of minutes.

o how many users use this concurrently -- finance systems typically do hundreds of TPS for hours on end. How about this?

o how big is this (I can use one row to hold 30,000,000,000 worth of stuff)

come on, some facts here. is this a mission critical, gotta run application or something that is "nice to have, if it disappears, oh well".




giving a user, who is very "ethnocentric" if you will -- very focused on themselves -- not the corporation as a whole -- is not always the "right thing". might make you a hero with that person, but a real pain with the company. Say that data is important, relevant, should be professionally managed so we can in fact recover it, restore it, have it in the event of a real failure (this is not a slam at the object database -- rather a slam at the notion of "hey, we'll just wing this puppy -- no need for dba's or anything like that")....

did you know, lots of data out there predates our existence. data is forever (he who owns the data owns the keys to the city). applications are like mayflies. they are born, live, breed, and die in a day. they come, they go. In the last 10 years -- I've seens dozens of applications come and go. The data underneath them? well, i'll be darned if it has not "survived".


and you have read:

</code> http://www.slac.stanford.edu/BFROOT/www/Public/Computing/Databases/ <code>

right? that is not a single database, it is over 100 databases and took some 5 million lines of custom developed code to even fathom.

so, over 100 databases -- not one.
5,000,000 lines of custom code.

all to retrieve a blob at the end of the day.

interesting.




ahh, but the very very lack of what you deem evil (sql) is what makes these solutions "not very general purpose".


I deliver high speed, scalable, flexible as all get out old fashioned rdbms applications all of the time. all of the time. Hey - guess what -- you are using one right now (and look at amazon, yahoo, ebay, et. al. or does those guys just "not get it"?)

funny thing is, with regards to "grandpa"

1970 - year the System R paper from IBM was made. described a language 'SQL' and the RDBMS concept

1970 - the year smalltalk, "the" OO language was invented....

hmm -- maybe - just maybe - pot calling kettle black.

what is consider new and cutting edge may just well be "like really really old".

funny -- goes back to Ada doesn't it? RDBMS = 70's = Ada = PL/SQL = Object Oriented. they all be about the same age. funny isn't it?

OH OH -- we need a new paradigm. wait, that's not right, "paradigm" is a 1990's term as well. It is dated. guess we just need something new?

what do they have -- give me some info here.

what I heard from CW was "I want oracle to be exactly like ss. I don't want to have to learn anything about anything new. I want everything to be like ss"

so -- throw me a bone. give me a "for example", a list. something to work with.

We ain't going to mess up a really good concurrency model to be like them

We ain't going to dump plsql down to the level of t-sql.

We ain't going to remove our ability to scale really large.

But hey -- throw me a bone here.

should be "obvious"?

l_file_path := l_array( some_variable );


regardless of the CONTENTS of some_variable -- l_file_path can have at most one element -- there is but one string in there!


You want l_file_path = l_array( &DEFINE_VARIABLE ); to get some literal string replacement happening:



define filep="aa,ab"
column X new_val filepath
select '''' || replace('&filep', ',', ''',''' ) || '''' X from dual;

DECLARE
type l_array is table of varchar2(256);
l_file_path l_array;
BEGIN
l_file_path := l_array(&FILEPATH);
dbms_output.put_line(l_file_path(1));
END;
/

can you give me a small example to investigate with? against emp and dept. I don't have any small "sendBatch"

(but the trace file would tell you for sure, you can read it, it'll show you exactly what is being parsed and when)

Yes, it appears that every explicit "sendBatch" or "commit" will cause a subsequent soft parse.

If you set the batch size really low (send the executes way up) the parses stay low. If you add lots of commits, they go way up. if you add lots of sendBatches, they go way up.

Not sure "why", but using update batching -- you shouldn't necessarily need to use sendBatch. It is unfortunate (not sure of the implementation needs of this) but better then parse/execute for each row.

To minimize the parsing, do not invoke sendBatch. Also, consider having session_cached_cursors set as well.

you have to make the type visible.  right now there is exactly ONE block of code on the planet that can see it -- that anonymous block.  No one else knows about that type.

You would code like this:


ops$tkyte@ORA9IR2> create or replace type numArray as table of number
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure p( p_data in out numArray )
  2  as
  3  begin
  4          for i in 1 .. p_data.count
  5          loop
  6                  p_data(i) := p_data(i) * 2;
  7          end loop;
  8  end;
  9  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_data numArray := numArray(1,2,3);
  3  begin
  4          for i in 1 .. l_data.count
  5          loop
  6                  dbms_output.put_line( l_data(i) );
  7          end loop;
  8          p( l_data );
  9
 10          dbms_output.put_line( 'after....' );
 11          for i in 1 .. l_data.count
 12          loop
 13                  dbms_output.put_line( l_data(i) );
 14          end loop;
 15  end;
 16  /
1
2
3
after....
2
4
6
 
PL/SQL procedure successfully completed.



In this case, you can "hide" the type in a package as well if you like:

ops$tkyte@ORA9IR2> create or replace package demo_pkg
  2  as
  3          type numArray is table of number;
  4
  5          procedure p( p_data in out numArray );
  6  end;
  7  /
 
Package created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package body demo_pkg
  2  as
  3
  4  procedure p( p_data in out numArray )
  5  as
  6  begin
  7          for i in 1 .. p_data.count
  8          loop
  9                  p_data(i) := p_data(i) * 2;
 10          end loop;
 11  end;
 12
 13  end;
 14  /
 
Package body created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_data demo_pkg.numArray := demo_pkg.numArray(1,2,3);
  3  begin
  4          for i in 1 .. l_data.count
  5          loop
  6                  dbms_output.put_line( l_data(i) );
  7          end loop;
  8          demo_pkg.p( l_data );
  9
 10          dbms_output.put_line( 'after....' );
 11          for i in 1 .. l_data.count
 12          loop
 13                  dbms_output.put_line( l_data(i) );
 14          end loop;
 15  end;
 16  /
1
2
3
after....
2
4
6
 
PL/SQL procedure successfully completed.


<b>No java needed or desired here...</b>
 

"Absolutely", I will work "24/7" to "address the issue" "going forward". It was "awesome" to have that pointed out I'll work to recitify it. "Basically" you'll have to "bear with me" while I try to "blue sky" a method to avoid cliches. It "literally" "boggles the mind" how often it happens (sort of an "ongoing" problem) -- but I feel I'm "between a rock and a hard place" at times (I know, I know, "it's not rocket science") rushing to answer the questions in a conversational tone, thats the "bottom line". Guess I thought the cliches were "value added" text -- making it sound more like a discussion.

"The fact of the matter is" however -- that "I hear what you are saying", we are "singing from the same hymn sheet". I'll work to "prioritise" this, but "to be honest with you", I cannot come up with a "ballpark figure" as to how long the problem might persist. I'll try on a daily "basis" to cut down on their use, but it'll be "pushing the envelope" to think it'll happen overnight. "In terms of" a reasonable timeline -- I'm thinking "around" 2 or 3 weeks, but I'd give it "up to" a month or so. Unless someone "moves the goal-posts" on me of course -- that would change everything. I'm sure some "out of the box thinking" (maybe I can put a trigger on the table to catch them....) might apply. Guess it is a "glass half full/empty" problem really -- some people would just read over them as if they weren't there....

We'll "touch base" on this for sure.

what version?

Objects were a "for fee" option to 8.0. If you did not license it, they are not there.

In 8i -- every Oracle database would have them.

is that the "real query" ?

if you were trying to do a simple transpose (pivot), we might just opt for max(decode(....))

my preference is always (my four laws of database, like Asimov's three laws of robotics -- which got extended to 4):

a) if you can do it in a single (commented) SQL statement, do it.
b) if you cannot do it in a single sql statement, do it with as little plsql
as possible
c) if you cannot do it in plsql (rare), use the tiniest snippect of java in a
stored procedure you can
d) if you cannot do it in plsql or java (primarily for performance reasons), the
smallest C extproc you can get would be next


So, if that sql statement works for you -- put a nice comment block in front of it for furture generations (just like you would for the equally obscure procedural approach) and go for it.

ops$tkyte@ORA9IR2> create or replace type arrayType as table of number
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table emp;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'EMP' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table emp_tmp;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table emp_tmp as select * from scott.emp where 1=0;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_data arrayType := arrayType(7788,7566);
  3  begin
  4          insert into emp_tmp
  5          select *
  6            from emp
  7           where empno not in ( select column_value
  8                                  from TABLE( cast( l_data as arrayType ) )
  9                                                     where column_value is not null
 10                                                       and rownum >= 0 );
 11  end;
 12  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select count(*) from emp;
 
  COUNT(*)
----------
        14
 
ops$tkyte@ORA9IR2> select count(*) from emp_tmp;
 
  COUNT(*)
----------
        12
 

ops$tkyte@ORA9IR2> create or replace type myArrayType as table of number
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable x refcursor
ops$tkyte@ORA9IR2> declare
  2          l_data myArrayType := myArrayType(1,3,5);
  3  begin
  4          open :x for select * from table(cast(l_data as myArrayType));
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> print x
 
COLUMN_VALUE
------------
           1
           3
           5

 

ops$tkyte@ORA9IR2> create or replace type myArrayType as table of number
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table emp as select empno, ename, job from scott.emp where 1=0;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure doit( p_array in myArrayType, p_fname in varchar2, p_lname in varchar2 )
  2  as
  3      l_stmt long := 'insert into emp (empno, ename, job)
  4                      select empno, ename, job
  5                        from scott.emp
  6                       where 1=1 ';
  7  begin
  8      if ( p_fname is not null )
  9      then
 10          l_stmt := l_stmt || ' and ename = :p_fname ';
 11      else
 12          l_stmt := l_stmt || ' and :p_fname is null ';
 13      end if;
 14      if ( p_lname is not null )
 15      then
 16          l_stmt := l_stmt || ' and ename = :p_lname ';
 17      else
 18          l_stmt := l_stmt || ' and :p_lname is null ';
 19      end if;
 20
 21      if ( p_array is not null )
 22      then
 23          l_stmt := l_stmt || ' and empno not in ( select * from table(cast( :p_array as myArrayType ) ) )';
 24          execute immediate l_stmt using p_fname, p_lname, p_array;
 25      else
 26          execute immediate l_stmt using p_fname, p_lname;
 27      end if;
 28      dbms_output.put_line( sql%rowcount || ' rows inserted' );
 29  end;
 30  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec doit( null, 'KING', null );
1 rows inserted
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec doit( myArrayType( 7369, 7499, 7521, 7566, 7654, 7698, 7782, 7788 ), null, null );
6 rows inserted
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from emp;
 
     EMPNO ENAME      JOB
---------- ---------- ---------
      7839 KING       PRESIDENT
      7839 KING       PRESIDENT
      7844 TURNER     SALESMAN
      7876 ADAMS      CLERK
      7900 JAMES      CLERK
      7902 FORD       ANALYST
      7934 MILLER     CLERK
 
7 rows selected.
 
<b>is one approach to this...</b>

 

1) thank goodness data logic is in the database :)

You could:

o send plsql tables
o send collections of object types (arrays of records in effect)
o use a global temporary table, client batch inserts into this table and plsql bulk processes it.

2) you can always write a "wrapper" layer on top that recieves the array and then calls existing routine slow by slow - but it would make sense to look at some of the time consuming operations and see how much code you can erase.




ouch on that last paragraph.

actually, that is the most painful one!

plsql tables of RECORDS are not recognized outside of plsql, plsql tables of SCALARS are...

Menon's JDBC book does cover this yet -- definition.

If I had to rank them effort wise from easy to hard:

a) global temporary table
b) plsql tables of scalars
c) using jpublisher to create java mapping classes to support collections of objects

</code> http://asktom.oracle.com/pls/ask/search?p_string=registerIndexTableOutParameter <code>

there is no "end loop" to a forall

forall i in 1 .. p_table.count
insert ....;



but p_table in your example isn't a TABLE, it is string - not sure where you are going with that?

Yes, you would define the plsql table type in plsql and use that.

Yes, associative arrays (index by tables that are indexed by something other than a number) are PLSQL only types.



You should use packages, you define the plsql table type in the package specificiation.

yes, you need to use the thick drivers with the 9i drivers

but 10g added support for the thin drivers. I just tested.


create or replace package demo_pkg
as
type arry_type is table of varchar2(32000) index by binary_integer;
end;
/

CREATE OR REPLACE PROCEDURE SPMBTEST3 (
VAR1 IN VARCHAR2,
VAR2 IN demo_pkg.ARRY_TYPE,
VAR3 OUT demo_pkg.ARRY_TYPE)
IS
BEGIN
DECLARE
wk_Subject VARCHAR2(64);
BEGIN
VAR3(1) := 'in2';
var3(2) := 'in3';
var3(3) := 'in4';
var3(4) := 'in5';

return;
EXCEPTION
WHEN OTHERS THEN
RETURN ;
END;
END;
/



$ cat spmbtest.java
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;

class spmbtest
{
public static void main (String args []) throws SQLException, ClassNotFoundException
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@localhost:1521:ora10g","scott", "tiger");
run( conn );
conn.close();
}

static void run(Connection conn) throws SQLException
{
System.out.println ("SPMBTEST_TEST");
OracleCallableStatement
spmbtest=(OracleCallableStatement)conn.prepareCall("{ call SPMBTEST3( ?, ?, ? ) }");

String[] values = { "xy","xd","xe" };

int maxLen = values.length;
int currentLen = values.length;
int elemSqlType = OracleTypes.VARCHAR;
int elemMaxLen = 10;
int maxOutLen = 20;

spmbtest.setString( 1, "12345" );
spmbtest.setPlsqlIndexTable (2, values, maxLen, currentLen, elemSqlType, elemMaxLen);
spmbtest.registerIndexTableOutParameter (3, maxOutLen, elemSqlType, elemMaxLen);

System.out.println("going to run" );
spmbtest.executeQuery();
Datum[] outvalues = spmbtest.getOraclePlsqlIndexTable (3);
for (int i=0; i<outvalues.length; i++) System.out.println (outvalues[i].toString());
spmbtest.close();
}
}

create or replace package my_pkg
as
type plsql_table is table of varchar2(100) index by binary_integer;
procedure sp_array_test( p_table in plsql_table );
end;
/

create or replace package body my_pkg



USE PACKAGES.... (I see the sp_, that is a sqlserver'ism - forget your sqlserver ways of doing things, every database is different - in Oracle use packages for all real code)

you can switch out the thin drivers in a second?

the 10g thin drivers work with 9i and such. Why not use the 10g drivers with their "better functionality"?

Else you'll be using collections and that is slightly more awkward.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:712625135727 <code>


there are examples of java passing collections to plsql (watch your CASE, ARRAY, not Array, type names UPPER in the string - not lower)

Also, not sure what the result set .getArray is for?


(ps: i removed your code snippet to which I'm refering, just noticed it has a real IP address in it and a username/password)...

CREATE TYPE cmtm_collateral_type IS TABLE OF cmtm_collateral_owner_defn_ty


cmtm_collateral_type is a collection, not a "scalar record"

cmtm_collateral_owner_defn_ty is the scalar record type. Your function takes an ARRAY, not a single "record"

q1) you already know how to select from it? if you want to see what the ref points to, look up "deref"

q2) not sure what you are trying to do here


In short, my personal recommendation would be

use relational structures, learn relational, you'll never be sorry you did.

In your example:

a1         b1                  3 H       13.5       10.5
a1         b1                  4 G       18.5       15.5

both val1 and val2 for serialno = 3 are exactly 5 away from serialno 4.

Is that always the case - or could val1 be say 5 away and val2 be 10 away - and if so, what is the expected outcome.

Also, will val1/val2 always be an integer number of values away from the next one or could val1/val2 be 5.5 away for example - and if so, what is the expected outcome.

ops$tkyte%ORA11GR2> with data as
  2  (
  3  select pk1, pk2, serialno, ind, val1, val2,
  4         lead(val1) over (order by serialno) next_val1
  5   from c
  6  )
  7  select pk1, pk2, serialno, ind, val1+column_value, val2+column_value
  8    from data,
  9        table( cast( multiset( select level-1 l from dual connect by level <=
 10                                   case when ind = 'H' then next_val1-val1
 11                                                                else 1
 12                                                            end ) as sys.odciNumberList ) )
 13  /

PK1        PK2          SERIALNO I VAL1+COLUMN_VALUE VAL2+COLUMN_VALUE
---------- ---------- ---------- - ----------------- -----------------
a1         b1                  1 G              10.5              11.5
a1         b1                  2 G              12.5              12.5
a1         b1                  3 H              13.5              10.5
a1         b1                  3 H              14.5              11.5
a1         b1                  3 H              15.5              12.5
a1         b1                  3 H              16.5              13.5
a1         b1                  3 H              17.5              14.5
a1         b1                  4 G              18.5              15.5
a1         b1                  5 G              21.5              18.5

9 rows selected.

sys.odcinumberlist is just a builtin type - it is a table of number, there are types for varchar2 and dates as well.

So, it is just a collection type.

multiset takes a collection type and lets us treat it as a set - like rows.


So, when I use:

from data, TABLE( cast( multiset( query ) as sys.odicnumberlist ) )

I'm just unnesting a table (a predefined sort of join). Basically for each row from data, it will create a set of N rows in the TABLE() clause where N is either 1 (for ind <> 'H') or the number of rows that need to be made up - we'll turn that one row from data into N rows...

tell us about your function and explain what you mean a bit more. What should be the input to your function for each row in the output. would it just be as simple as:

ops$tkyte%ORA11GR2> with data as
  2  (
  3  select pk1, pk2, serialno, ind, val1, val2,
  4         lead(val1) over (order by serialno) next_val1
  5   from c
  6  )
  7  select pk1, pk2, serialno, ind, val1+ F(X), val2+ F(X)
  8    from data,
  9        table( cast( multiset( select level-1 l from dual connect by level <=
 10                                   case when ind = 'H' then next_val1-val1
 11                                                                else 1
 12                                                            end ) as sys.odciNumberList ) )
 13  /

all you have to do is tell us "what is X"

what if there are 50 rows between the two values - what then? since this function always returns 4.

ops$tkyte%ORA11GR2> with data as
  2  (
  3  select pk1, pk2, serialno, ind, val1, val2,
  4         lead(val1) over (order by serialno) next_val1
  5   from c
  6  )
  7  select pk1, pk2, serialno, ind, val1+ column_value, val2+ column_value
  8    from data,
  9        table( your_function_here( your, parameters, to, function, here ) )
 13  /

that is all - your function would return either

a) 1 element in your collection - because it is not an 'H' type
b) N elements to make up the 'gap' because it is an 'H'

suggest you give me your full reproducible test case so I can find your bug in your COL function.


tell us, does this:

table(col(val2,next_val2))

return ONE ROW when ind <> 'H'?

table(col(val2,next_val2)) will be called only when ind=H and should not be
called when ind<>h.

umm, no, it will be called each and every single time. That is why I said:

that is all - your function would return either

a) 1 element in your collection - because it is not an 'H' type
b) N elements to make up the 'gap' because it is an 'H'


Your table function is joined (has to be joined) to every single row in your real table. that is why when it is not 'H' - you need to return 1 row (so you a) see that row and b) see that row only once). And why when it is 'H' you need to return N rows.


I'm beginning to think you don't really know how this works - which is very very VERY dangerous. You have no way to maintain it, extend it, test it, prove that it does what you need. You might want to stop for a minute and get really familiar with the technique.


so, here is an example - this just does what my original query does because I return 0, 1, 2, 3, .... from my function when ind = "H" and I return just 0 when it isn't H

ops$tkyte%ORA11GR2> create or replace
  2  function your_function_here
  3  ( p_ind in varchar2,
  4    p_val1 in number,
  5    p_next_val1 in number
  6  ) return sys.odciNumberList
  7  PIPELINED
  8  as
  9  begin
 10          if ( p_ind = 'H' )
 11          then
 12                  for i in 1 .. p_next_val1 - p_val1
 13                  loop
 14                          pipe row( i-1 );
 15                  end loop;
 16          else
 17                  pipe row( 0 );
 18          end if;
 19          return;
 20  end;
 21  /

Function created.

ops$tkyte%ORA11GR2> show errors
No errors.
ops$tkyte%ORA11GR2> pause

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> with data as
  2  (
  3  select pk1, pk2, serialno, ind, val1, val2,
  4         lead(val1) over (order by serialno) next_val1
  5   from c
  6  )
  7  select pk1, pk2, serialno, ind, val1+ column_value, val2+ column_value
  8    from data,
  9        table( your_function_here( ind, val1, next_val1 ) )
 10  /

PK1        PK2          SERIALNO I VAL1+COLUMN_VALUE VAL2+COLUMN_VALUE
---------- ---------- ---------- - ----------------- -----------------
a1         b1                  1 G              10.5              11.5
a1         b1                  2 G              12.5              12.5
a1         b1                  3 H              13.5              10.5
a1         b1                  3 H              14.5              11.5
a1         b1                  3 H              15.5              12.5
a1         b1                  3 H              16.5              13.5
a1         b1                  3 H              17.5              14.5
a1         b1                  4 G              18.5              15.5
a1         b1                  5 G              21.5              18.5

9 rows selected.

it used to be a requirement:

ops$tkyte%ORA9IR2> create or replace
  2  function your_function_here
  3  ( p_ind in varchar2,
  4    p_val1 in number,
  5    p_next_val1 in number
  6  ) return sys.odciNumberList
  7  PIPELINED
  8  as
  9  begin
 10          if ( p_ind = 'H' )
 11          then
 12                  for i in 1 .. p_next_val1 - p_val1
 13                  loop
 14                          pipe row( i-1 );
 15                  end loop;
 16          else
 17                  pipe row( 0 );
 18          end if;
 19  end;
 20  /

Function created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select * from table( your_function_here( 'H', 1, 5 ) );
ERROR:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "OPS$TKYTE.YOUR_FUNCTION_HERE", line 16



no rows selected

guess I still just do it out of habit.

It doesn't hurt.