RE: Separate cursor for bind value assignment, is this normal

Nods, due to you and your team as well as Bryn, et. al, Oracle has made huge advances in the availability of program interfaces and environments.  

As a practical matter if someone has a list of bind variables to be used in the execution of a procedure serially, the trivial thing to do is stuff them all into a table and have the procedure iterate through the list from the table with nary a context shift. (Having many users each using a procedure one variable at a time because that is all they want to process is another matter where no matter what you do there will be some cost to setting the value from wherever the user is sitting and getting it available to the processing routine. Doing it by one of the methods you’ve mentioned that probably can skip doing a sql parse just to set the variable is probably a smart choice.)  

My comment was intended purely as an historical note agreeing with the notion that what has grown into SQLPLUS was not originally intended to be more than a generic user interface to launch queries, let alone an efficient process control engine. Client-side only PL/SQL was an answer to user demand for more programming features than were available natively to SQLPLUS and of course then someone realized that putting that in the database enhanced volume processing. To the extent SQLPLUS can be used pretty efficiently for volume processing now is simply amazing.  

But it remains best to do actual programming for heavy loads. The shift it what it means TO BE a heavy load sets the bar for when it is worthwhile to do actual coding rather than trick a mere very capable user interface into doing some pretty heavy lifting.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jeff Smith Sent: Monday, October 08, 2018 6:50 PM
To: mwf_at_rsiz.com; orahawk_at_gmail.com; contact_at_soocs.de Cc: krishsingh.111_at_gmail.com; Oracle - L Subject: RE: Separate cursor for bind value assignment, is this normal  

If you want to write some code and do some oracle, SQLcl has access to javascript, jython, groovy, etc. I’m sure there’s a way to init a bind there  

From: Mark W. Farnham <mwf_at_rsiz.com> Sent: Monday, October 8, 2018 5:26 PM
To: orahawk_at_gmail.com; contact_at_soocs.de Cc: krishsingh.111_at_gmail.com; Oracle - L <oracle-l_at_freelists.org> Subject: RE: Separate cursor for bind value assignment, is this normal  

hehe, which started as the ufi (user friendly interface), which some customers called the uvi (user viscious interface) for things like its lack of a history buffer and so forth.  

You are correct that it was never intended as a programming tool. Oracle had Pro*C for amateurs and the C call libraries for professionals. And at 300 baud, you had better figure out a way to avoid telecom line turn-arounds in the middle of a transaction, too, and with 25 MHz cpus and table locking you better avoid parses in the middle of transactions, too.

UFI was competitive with its competition as a quick way to ask the database quick questions.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dragutin Jastrebic Sent: Monday, October 08, 2018 2:18 PM
To: contact_at_soocs.de
Cc: krishsingh.111_at_gmail.com; Oracle - L Subject: Re: Separate cursor for bind value assignment, is this normal  

Hello

In this old thread=>

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:113012348062

 Tom Kyte explained

==>

.... in sqlplus the exec :x := &x will be parsed each time,

those will be unavoidable (sqlplus is not a programming environment, it is just a simple command line tool)

Yes, I wish sqlplus had a way to initialize a bind variable -- it would be nice.

<===

So possibly that it was event a design choice of Oracle development team, since sqlplus was more seen a command line tool, not a programming tool.

Dragutin                     

--
http://www.freelists.org/webpage/oracle-l