RE: Riddle me this Oracle riddle...

[Khedr, Waleed]

I hope Oracle developers know what they are talking about!

How does this work:

create or replace procedure blah_blah
  ( p_in_one in out   test.col_2%type ) as
  begin
  null;
  p_in_one := rpad('0',5000,'0');

  end;


  declare
  rr varchar2(5000);
  begin
  blah_blah(rr);
  dbms_output.put_line(length(rr));
 
  end;

Regards,

Waleed

-----Original Message-----
From: Freeman, Robert [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 06, 2002 2:04 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Riddle me this Oracle riddle...


The correct answer is c, 4000 bytes, which is the defined max size
for a varchar2 in Oracle9i. This is what I was told directly last
week in a very lively discussion with two Oracle developers. So, Jonathan is
correct (and does that really surprise anyone?).

I have discovered that our developers are expecting that because the %type
is being used in the parameter of the PL/SQL, that the parameter is being
constrained to 200 bytes. In fact, dbms_describe and the PRO*C describe
procedures < 9.0.1.2 and < 8.1.7.3 will report the size of the parameter as
200 bytes.

In fact, the allocated size is 2000 bytes, and 2000 bytes is returned
according to Oracle development (in Oracle8i, 4000 bytes in 9i).

Thus, if in PRO*C I malloc a variable array of 200 bytes for this supposed
200 byte return OUT parameter, I could potentially end up blowing the stack,
and coring out. How?? Because PL/SQL does not constrain that memory
variable to 200 bytes... you could very easily concat the OUT parameter
with some other text and have it end up > 200 bytes. Return it to the
calling
program with a variable malloc'd to 200 bytes and boomo.

Now, all is well and good if you describe the type, get the size, and if you
never have a case where the size is exceeded. Thus, if I am reading in data
from the table into the parameter, then its likely that you won't blast the
variable. But, bugs to occur and sometimes people don't consider the
implications of what they are doing..... and perhaps, perhaps, someone could
find a way to take advantage of this little know issue. Oracle says that the
risks are:

1. Coring the program.
2. Crashing the database.
3. Corruption of the database.
4. Certain security issues.

Now, in 8.1.7.3 and 9.0.1.2, the dbms_describe procedures do NOT return a
size for the type'd procedures. This will change back to the way it was
in 9.0.1.4, and then in 9.0.2 it is planned to revert BACK to not reporting
the size.

Of course, I don't know how many sites this really impacts... also, with
regards to the shared pool, there might be some implications...

RF

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 11:58 AM
To: Multiple recipients of list ORACLE-L



I think we are talking at cross-purposes here, the point I was
trying to make  was that the declared parameter doesn't have
the '200' associated with it that you might assume it to have
by virtue of its apparent association with the table.

Bear in mind, by the way, that if a client machine calls a server
procedure, the bit about pointers and IN parameters is irrelevant.



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

Next Seminar - UK, April 3rd - 5th
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


-----Original Message-----
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 06 March 2002 16:31


|From 9i DOC:
|
|Suppose a subprogram declares an IN parameter, an OUT parameter, and
an IN
|OUT parameter. When you call the subprogram, the IN parameter is
passed by
|reference. That is, a pointer to the IN actual
|parameter is passed to the corresponding formal parameter. So, both
|parameters reference the same memory location, which holds the value
of the
|actual parameter.
|
|
|
|Waleed
|
|
|
|-----Original Message-----
|<mailto:[EMAIL PROTECTED]> ]
|Sent: Wednesday, March 06, 2002 9:58 AM
|To: Multiple recipients of list ORACLE-L
|
|
|
|Bear in mind that you cannot declare a procedure like this:
|    procedure blah(
|        p    in    varchar2(200)
|    );
|it has to be:
|    procedure blah(
|        p    in    varchar2
|    );
|
|So even though a declaration like Rob's
|    test.col_01%type
|appears to tell Oracle that the parameter
|is limited to 200 bytes I believe there is no
|limit, other than the inherent limit of varchar2(),
|viz: 4,000.
|
|
|Jonathan Lewis
|http://www.jlcomp.demon.co.uk <http://www.jlcomp.demon.co.uk>
|
|Next Seminar - UK, April 3rd - 5th
|http://www.jlcomp.demon.co.uk/seminar.html
|<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
|<http://www.jlcomp.demon.co.uk/faq/ind_faq.html>
|
|Author of:
|Practical Oracle 8i: Building Efficient Databases
|
|
|-----Original Message-----
|To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
|Date: 06 March 2002 13:49
|
|
||Initially is will be nothing unless test.col2 has a default value as
||varchar2 structures are data_length followrd by actual string. The
|maximum
||that p_in_one can have is 200 bytes thought due to its anchored
|definition.
||
||The answer, none initially as it will be initializes to NULl value,
|then
||whatever you assign ti it plus 2 bytes to store the length of the
|actual
||string, max size is 200+2 bytes (assuming standard characterset).
||
||This has no baring on max size of varchar2 because by definition of
||p_in-one, its max length is limited to 200.
||
||Raj
||______________________________________________________
||Rajendra Jamadagni MIS, ESPN Inc.
||Rajendra dot Jamadagni at ESPN dot com
||Any opinion expressed here is personal and doesn't reflect that of
|ESPN Inc.
||
||QOTD: Any clod can have facts, but having an opinion is an art!
||
|
|--
|Please see the official ORACLE-L FAQ: http://www.orafaq.com
|<http://www.orafaq.com>
|--
|Author: Jonathan Lewis
|  INET: [EMAIL PROTECTED]
|
|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: [EMAIL PROTECTED] (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: Jonathan Lewis
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).