whew, been getting some pretty looonnngg questions. Ok, here we go


X. Here is an explicit cursor:

declare
cursor c is select * from emp;
l_rec c%rowtype;
begin
open c;
loop
fetch c into l_rec;
exit when c%notfound;
....
end loop;
close c;
end;

Here is the same thing with an implicit cursor:

begin
for l_rec in ( select * from emp )
loop
...
end loop;
end;


If you don't see the word "cursor" anywhere, you have an implicit one. It is true that you can also:

declare
cursor c is select * from emp;
begin
for l_rec in c
loop
...
end loop;
end;

and I would call that an implicit cursor as well. If you (you personally) do not do the cursor managment (open, close, fetch, %isopen, %notfound, etc) it is implicit.

I. difference between number and integer.

This is like asking a C programmer whats the difference between a float and an int.

An integer is alot like a number(38,0) -- in fact, it is. It has 38 digits of precision but NONE of those digits appear to the right of the decimal point.

A number has 38 digits of precision, and might have a fractional component.

Here is the difference:

ops$tkyte@ORA8I.WORLD> create table t ( x int, y number );

Table created.

ops$tkyte@ORA8I.WORLD> insert into t values ( 1.555, 1.555 );

1 row created.

ops$tkyte@ORA8I.WORLD> select * from t;

X Y
---------- ----------
2 1.555

ops$tkyte@ORA8I.WORLD>

X will round to the nearest integer, Y will preserve every digit (upto 38 of them)


II. Set timing on -- say you use it with autotrace (many try). It times the autotrace stuff as well as your query -- autotrace can add tons of overhead (v$ table queries can be very slow)

Say you use set timing on with: select * from really_big_table. that timing will include the time it takes sqlplus to paint the screen and scroll, or write to a spool file.

I'm interested in how long the query takes in the server -- thats all tkprof reports.

As I recall -- the times where in thousands of seconds so 3244 would be 3.244. A quick and dirty way to confirm (or deny) this would be:

SQL> set timing on
SQL> exec dbms_lock.sleep(1)

if that puts out 1000, then it was thousands, if it puts out 100 then it was hundredths (div by 100, not 1000 to get seconds)

III

If that procedure is called by another procedure -- yes. See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:286816015990

for why

IV 

I have a good chapter on this in my book -- in the section on dbms_application_info in the necessary supplied packages guide.  See also:

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:927351834270

it shows how you can use this table -- if you use dbms_application_info you too can populate it

V

False, we dynamically partition objects for parallel query.  Parallel updates/deletes (parallel dml) works against partitioned objects only.

To test with this, you can set parallel_min|max_servers and use some hints to see how it works.  You can also set parallel_automatic_tuning and set the default degree of parallelism on the tables themselve (but the hint would be better for just testing purposes as it won't affect anyone NOT doing the hints)

As for the "how does parallel query work" -- i'll refer you to the docs.  Suffice it to say that they never get the same rows as they all work on non-overlapping rowid ranges.  see

http://docs.oracle.com/cd/A81042_01/DOC/server.816/a76965/c22paral.htm#22669 <code>

VI

No, you cannot change the way SQPlus is coded - think about how you would write sqlplus -- you would have a cursor and as the end user input a statement you would parse and execute it. thats what plus is doing.

PLSQL is not a "front end" tool IMO - its a back end tool, its in the server. It is optimized to do one thing -- execute tons of SQL really efficiently. I would say most front end tools are not optimized this way (forms is however)

VII

DDL invalidates objects in your database. Drop an index/Add an index -- views go invalid. Alter a table -- procedures/views/triggers go invalid, etc. Doing DDL changes things and invalidates lots of things. This is bad in a production (stable) system. You don't want things going invalid

DDL is serialized. Data dictionary changes are expensive.

DML does not do the above.

VIII (you know what, roman numerals are a pain ;)

You can minimize it by using BULK operations in PLSQL (less switching from PLSQL to SQL. 10 Array inserts of 100 rows -- much faster then 1000 inserts of a row. 1 fetch of 100 rows -- much faster then 100 single row fetches.

If a function is called in SQL, there is a switch from sql to plsql for each call. Function based indexes can virtually eliminate this. Use of application contexts are another alternative (if you are just trying to parameterize a view or something)

IX

A function in PLSQL (as with most languages) is simply a PROCEDURE that can return a value. It is nothing special.

Some people feel functions should have only IN parameter and a single output. I disagree.

A function is nothing more then a procedure with an extra un-named OUT parameter.