| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Riddle me this Oracle riddle...
And that is because PL/SQL "varchar2" type (used to describe PL/SQL variables) has different max length (32767) then database "varchar2" type (used to describe table columns), which is 4000.
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
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:Robert_Freeman_at_csx.com]
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:
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 <ORACLE-L_at_fatcity.com>
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:jonathan_at_jlcomp.demon.co.uk> ]
|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 <ORACLE-L_at_fatcity.com>
|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: 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: 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). --
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: Igor Neyman INET: ineyman_at_perceptron.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).Received on Wed Mar 06 2002 - 14:14:29 CST
 |
 |