----- Original Message -----
Sent: Wednesday, March 06, 2002 2:37
PM
Subject: RE: Riddle me this Oracle
riddle...
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).
