Re: Powerbuilder problems?

david_at_us.oracle.com (David Natelson) writes:

>Binding: (examples)
 

>select name,address,sal from emp where deptno = :var1 and sal < :var2;
 

>insert into emp (name,address) values (:var1, :var2);
 

>The :var1 and :var2 are "bind variables" meaning the client will bind a
>value each time they are executed. Since Oracle7 stores the execution
>plan (parsing is done once and stored in memory on the server), a
>application can Open a Cursor(s), and execute or fetch from it without
>causing a reparse (even as the app. changes :var1's value each time).

Thanks for the explanation.

I am under the impression that cursors are read-only. That is, "select" statements are candidates for cursors, but INSERT statements are not. Am I mistaken?

>Powerbuilder (in many situations) does not declare a cursor with the above
>"bind variables", instead, each user has his/her SQL statement executed
>with the values "hard coded":
 

>select name, address,sal from emp where deptno = 10 and sal < 1212;
 

>This means each user for each statement has his/her SQL reparsed (even when
>doing a bunch of inserts).

Is this really a big deal? Simple SQL statements like these examples should take milliseconds to parse, shouldn't they?

>If you think about why you want to use a stored procedure you can list the
>following (some have have more or less but here is my list):
 

>1. Avoid reparsing -> save CPU cycles
>2. Avoid reoptimizing -> save CPU cycles
>3. Reduce network use -> save LAN traffic
 

>Item 3 has nothing to do with selecting data from Oracle because all users
>can take advantage of shared SQL in the server and Oracle sends back arrays
>of data (multiple rows/packet) to the client.

How does Oracle know that user A's SQL can be "shared" with user B's? Does one or both of the users tell the server? Does the server compare every new SQL statement to every one it has stored for sharing?

Are there advantages to shared SQL over stored procedures?

--MDC Received on Wed May 25 1994 - 00:18:19 CEST