RE: Riddle me this Oracle riddle...

Jonathan and all,

First, forgive me for not saying that I'd reveal the answer soon. (probably Thursday). So please bear with me on that issue.

This is not designed to be an OCP type question, I do not think you will find the answer documented anywhere as far as Oracle is concerned (at least I looked and could not). I'm not trying to be a smart ass or sell anything either.

I am asking this for these reasons:

1. I want to start a discussion that revolves around this discussion and some nasty stuff we discovered was present in some legacy code. I think it has some very damaging possibilities. If you use the OCI libraries describe function, or DBMS_DESCRIBE, after you have answered the question, go play with those functions and see if you can figure out what the possible impacts are. Also, you will find an interesting change in these functions between 9.0.1.1 and 9.0.1.2, which is what first clued us into this issue.
2. I want to compile the responses and chart what the current belief is out there about this question. Plan on discussing this topic at IOUG-A as a part of my Oracle Urban Legends presentation.
3. This has some direct relevance with some discussion we are having with Oracle here with regards to changes in 9.0.1.2, 9.0.1.4 AND 9.0.2.... It might well impact some of you.

So, please, bear with me, and provide me with your answers to the question. I've asked the same question on another list serv and the Revealnet DBA Pipeline. I'm finding the answers *facinating*.

All the best,

Robert G. Freeman - Oracle8i OCP
Oracle DBA Technical Lead
CSX Midtier Database Administration

The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him.

-----Original Message-----
Sent: Wednesday, March 06, 2002 4:53 AM
To: Multiple recipients of list ORACLE-L

Robert,

If this was a test, and you know the answer, please tell us what it was and how you got it. (trade secrets need not be revealed, of course). I thought I knew one way to find it, but the dump command I want to use doesn't work on 9 !!

Using rather crude methods, some tests I have just invented seem to indicate that the answer is 4,000 bytes.

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

|
||Riddle me this Batman.....
||
||Assume Oracle9i...
||
||assume you have a table thusly defined:
||
||Table Name: TEST
||col_1 number
||col_2 varchar2(200)
||
||And now a procedure defined thusly:
||
||create or replace procedure blah_blah
||( p_in_one test.col_2%type )
||
||Now, here is the question, how much memory will p_in_one have
|allocated to
||it within PL/SQL?? Is the answer:
||
||a. 200 bytes
||b. 2000 bytes
||c. 4000 bytes
||d. It will be defined based on the size of the data actually being
|passed
||into the parameter of the PL/SQL procedure
||e. None of the above.
||
||I'm currious what your answers will be... ;-)
||
||
||Robert G. Freeman - Oracle8i OCP
||Oracle DBA Technical Lead
||CSX Midtier Database Administration
||

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Freeman, Robert 
  INET: Robert_Freeman_at_csx.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).