RE: Riddle me this Oracle riddle...
Thanks for finding this I had searched high and low and somehow missed this reference. This doesn't quite jive with what Oracle has told me, so I'm following up for more detail from them. 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 4:47 PM To: Multiple recipients of list ORACLE-L Hi listers, continuing on varchar2 memory cost in PL/SQL, note this found in Oracle 8i/9i PL/SQL user guide : (Chapter 3 PL/SQL Datatypes - Character types - Varchar2) Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes. HTH Regards Gilles Parc carpe diem !! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gilles PARC 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). winmail.dat
RE: Riddle me this Oracle riddle...
Interesting, I wonder if this is 4000 bytes then for 9i and the manual was not updated? I will ask about this. So, this implies that my original answer would be b or c (depending on 9i behavior). This is what I understood Oracle to be telling me. 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 4:47 PM To: Multiple recipients of list ORACLE-L Hi listers, continuing on varchar2 memory cost in PL/SQL, note this found in Oracle 8i/9i PL/SQL user guide : (Chapter 3 PL/SQL Datatypes - Character types - Varchar2) Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes. HTH Regards Gilles Parc carpe diem !! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gilles PARC 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). winmail.dat
RE: Riddle me this Oracle riddle...
Interesting, I wonder if this is 4000 bytes then for 9i and the manual was not updated? I will ask about this. So, this implies that my original answer would be b or c (depending on 9i behavior). This is what I understood Oracle to be telling me. 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 4:47 PM To: Multiple recipients of list ORACLE-L Hi listers, continuing on varchar2 memory cost in PL/SQL, note this found in Oracle 8i/9i PL/SQL user guide : (Chapter 3 PL/SQL Datatypes - Character types - Varchar2) Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes. HTH Regards Gilles Parc carpe diem !! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gilles PARC 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).
RE: Riddle me this Oracle riddle...
Thanks for finding this I had searched high and low and somehow missed this reference. This doesn't quite jive with what Oracle has told me, so I'm following up for more detail from them. 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 4:47 PM To: Multiple recipients of list ORACLE-L Hi listers, continuing on varchar2 memory cost in PL/SQL, note this found in Oracle 8i/9i PL/SQL user guide : (Chapter 3 PL/SQL Datatypes - Character types - Varchar2) Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes. HTH Regards Gilles Parc carpe diem !! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gilles PARC 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).
Re: Riddle me this Oracle riddle...
Parameter that are defined as IN _are_ copied in. To avoid this and pass data by reference, use NOCOPY. NOCOPY is a compiler directive witch may or may not be followed by the compiler. regards Torben Holm http://www.miracleas.dk Khedr, Waleed writes: So the answer is none of the above. -Original Message- Sent: Tuesday, March 05, 2002 5:48 PM To: Multiple recipients of list ORACLE-L You did not specify in, out or in out, so the default is: IN. Parameters that are defined as IN don't get copied but are referenced (passed by reference). Regards, Waleed (I hope I'm right!) -Original Message- Sent: Tuesday, March 05, 2002 4:48 PM To: Multiple recipients of list ORACLE-L 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 The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed 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: Khedr, Waleed 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). Torben Holm Miracle A/S mobil: +45 2527 7104 mail : [EMAIL PROTECTED] http://www.miracleas.dk -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Re: Riddle me this Oracle riddle...
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: [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).
RE: Riddle me this Oracle riddle...
My guess is that it should allocate 200 bytes. If however PL/SQL variables Dynamic Memory Allocation then probably some minimal as set by the PL/SQL Engine designers/programmers. Aleem -Original Message- Sent: Wednesday, March 06, 2002 2:53 PM To: Multiple recipients of list ORACLE-L Subject:Re: Riddle me this Oracle riddle... 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: [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: Abdul Aleem 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).
RE: Riddle me this Oracle riddle...
Anybody wish to answer the following: Why does this matter? Or is this an OCP exam question? Personally, I fail to see the relevance. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, March 05, 2002 4:48 PM To: Multiple recipients of list ORACLE-L 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 The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F 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).
RE: Riddle me this Oracle riddle...
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! ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
Re: Riddle me this Oracle riddle...
I believe that varchar2 is a null-terminated string (like in C). Varchar is the string with the byte count at the beginning (like Pascal). Jamadagni, Rajendra wrote: 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! Name: ESPN_Disclaimer.txt ESPN_Disclaimer.txtType: Plain Text (text/plain) Encoding: 7BIT -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan 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).
RE: Riddle me this Oracle riddle...
I think it's an OCP - type question. They ask questions like this, when in reality people just buy servers, and if they need more space, they buy more disk... .. .providing there aren't obvious problems with database design. Does anyone really spend days figuring out the exact size of rows etc. before tables are created? It seems to me people would just try the new thing on a development machine, look at space usage, then declare how much disk and CPU they need for the production system. There is a point of diminishing returns, where more time is lost than the performance / storage gain would justify. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 06, 2002 9:09 AM To: Multiple recipients of list ORACLE-L Subject:RE: Riddle me this Oracle riddle... Anybody wish to answer the following: Why does this matter? Or is this an OCP exam question? Personally, I fail to see the relevance. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, March 05, 2002 4:48 PM To: Multiple recipients of list ORACLE-L 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 The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F 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: Boivin, Patrice J 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).
Re: Riddle me this Oracle riddle...
Varchar2 datatype is NOT null terminated in C or anywhere else that I can think of. It is a 2 member structure one being the contents the other is length of contents. Rick Scott Canaan srcdco@ritvaTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] x.rit.edu cc: Sent by: Subject: Re: Riddle me this Oracle riddle... root@fatcity. com 03/06/2002 08:58 AM Please respond to ORACLE-L I believe that varchar2 is a null-terminated string (like in C). Varchar is the string with the byte count at the beginning (like Pascal). Jamadagni, Rajendra wrote: 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! Name: ESPN_Disclaimer.txt ESPN_Disclaimer.txtType: Plain Text (text/plain) Encoding: 7BIT -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan 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: 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).
Re: Riddle me this Oracle riddle...
If it is an anomaly which is consuming unexpected amounts of memory it may be of interest to any site that is using a lot of PL/SQL and is running into ORA-04030 errors on a regular basis. 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 13:27 |Anybody wish to answer the following: | |Why does this matter? Or is this an OCP exam question? | |Personally, I fail to see the relevance. | |Tom Mercadante |Oracle Certified Professional | -- 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).
Re: Riddle me this Oracle riddle...
Bear in mind that you cannot declare a procedure like this: procedure blah( pinvarchar2(200) ); it has to be: procedure blah( pinvarchar2 ); 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 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 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 -- 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).
RE: Riddle me this Oracle riddle...
Just curious - it's not like I'm trying to be mean or anything. I was just wondering if this is an issue with certain types of applications. I remember I had to worry about memory usage a *long* time ago when mainframe systems only had limited core memory (anybody remember core memory?), and application design and memory reuse was a critical part of the whole process. But within Oracle, I have not seen the need to worry about these types of things. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, March 06, 2002 9:04 AM To: Multiple recipients of list ORACLE-L I think it's an OCP - type question. They ask questions like this, when in reality people just buy servers, and if they need more space, they buy more disk... .. .providing there aren't obvious problems with database design. Does anyone really spend days figuring out the exact size of rows etc. before tables are created? It seems to me people would just try the new thing on a development machine, look at space usage, then declare how much disk and CPU they need for the production system. There is a point of diminishing returns, where more time is lost than the performance / storage gain would justify. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 06, 2002 9:09 AM To: Multiple recipients of list ORACLE-L Subject:RE: Riddle me this Oracle riddle... Anybody wish to answer the following: Why does this matter? Or is this an OCP exam question? Personally, I fail to see the relevance. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, March 05, 2002 4:48 PM To: Multiple recipients of list ORACLE-L 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 The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F 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: Boivin, Patrice J 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego
RE: Riddle me this Oracle riddle...
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 actualparameter 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-From: Jonathan Lewis [mailto:[EMAIL PROTECTED]]Sent: Wednesday, March 06, 2002 9:58 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Riddle me this Oracle riddle...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%typeappears to tell Oracle that the parameteris limited to 200 bytes I believe there is nolimit, other than the inherent limit of varchar2(),viz: 4,000.Jonathan Lewishttp://www.jlcomp.demon.co.ukNext Seminar - UK, April 3rd - 5thhttp://www.jlcomp.demon.co.uk/seminar.htmlHost to The Co-Operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.htmlAuthor 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. Themaximum|that p_in_one can have is 200 bytes thought due to its anchoreddefinition.||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 theactual|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 ofESPN 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--Author: Jonathan Lewis INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: Riddle me this Oracle riddle...
Of course: you have to justify the memory that your application is using. There is a big difference between 200, 500, 2000 bytes. Just kidding! Regards, Waleed -Original Message- Sent: Wednesday, March 06, 2002 10:08 AM To: Multiple recipients of list ORACLE-L Just curious - it's not like I'm trying to be mean or anything. I was just wondering if this is an issue with certain types of applications. I remember I had to worry about memory usage a *long* time ago when mainframe systems only had limited core memory (anybody remember core memory?), and application design and memory reuse was a critical part of the whole process. But within Oracle, I have not seen the need to worry about these types of things. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, March 06, 2002 9:04 AM To: Multiple recipients of list ORACLE-L I think it's an OCP - type question. They ask questions like this, when in reality people just buy servers, and if they need more space, they buy more disk... .. .providing there aren't obvious problems with database design. Does anyone really spend days figuring out the exact size of rows etc. before tables are created? It seems to me people would just try the new thing on a development machine, look at space usage, then declare how much disk and CPU they need for the production system. There is a point of diminishing returns, where more time is lost than the performance / storage gain would justify. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 06, 2002 9:09 AM To: Multiple recipients of list ORACLE-L Subject:RE: Riddle me this Oracle riddle... Anybody wish to answer the following: Why does this matter? Or is this an OCP exam question? Personally, I fail to see the relevance. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, March 05, 2002 4:48 PM To: Multiple recipients of list ORACLE-L 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 The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F 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: Boivin, Patrice J 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
RE: Riddle me this Oracle riddle...
This is NOT an OCP question, and there is a HUGE bit of relevance, and it applies to something that we discovered in some legacy code here, that applies directly to this question. 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 9:04 AM To: Multiple recipients of list ORACLE-L I think it's an OCP - type question. They ask questions like this, when in reality people just buy servers, and if they need more space, they buy more disk... .. .providing there aren't obvious problems with database design. Does anyone really spend days figuring out the exact size of rows etc. before tables are created? It seems to me people would just try the new thing on a development machine, look at space usage, then declare how much disk and CPU they need for the production system. There is a point of diminishing returns, where more time is lost than the performance / storage gain would justify. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 06, 2002 9:09 AM To: Multiple recipients of list ORACLE-L Subject:RE: Riddle me this Oracle riddle... Anybody wish to answer the following: Why does this matter? Or is this an OCP exam question? Personally, I fail to see the relevance. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, March 05, 2002 4:48 PM To: Multiple recipients of list ORACLE-L 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 The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F 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: Boivin, Patrice J 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
RE: Riddle me this Oracle riddle...
Sorry and very good point. In this case, the question applies to OUT OR IN OUT parameters. 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 3:43 AM To: Multiple recipients of list ORACLE-L Parameter that are defined as IN _are_ copied in. To avoid this and pass data by reference, use NOCOPY. NOCOPY is a compiler directive witch may or may not be followed by the compiler. regards Torben Holm http://www.miracleas.dk Khedr, Waleed writes: So the answer is none of the above. -Original Message- Sent: Tuesday, March 05, 2002 5:48 PM To: Multiple recipients of list ORACLE-L You did not specify in, out or in out, so the default is: IN. Parameters that are defined as IN don't get copied but are referenced (passed by reference). Regards, Waleed (I hope I'm right!) -Original Message- Sent: Tuesday, March 05, 2002 4:48 PM To: Multiple recipients of list ORACLE-L 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 The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed 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: Khedr, Waleed 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). Torben Holm Miracle A/S mobil: +45 2527 7104 mail : [EMAIL PROTECTED] http://www.miracleas.dk -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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,
RE: Riddle me this Oracle riddle...
Patrice, Ahhh this is a think outside of the box type of question has nothing to do with disk space... might have something to do with having enough memory I suppose but given the answer to this question, there are some nasty possibilities that might well lurk that few have thought of. Mind you, much of my information is brand new (to me), but it is *direct* from Oracle development. I will reveal the answer in the next day, and what I see as some of the ramifications of this answer are... and see what everyone else thinks about those ramifications. Heck, maybe I'll find out that Oracle and I are just worrying to much about this thing :-)) 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. -- 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).
RE: Riddle me this Oracle riddle...
This issue has some application specific application and possible security implications (from a hacking point of view) I think. 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 10:08 AM To: Multiple recipients of list ORACLE-L Just curious - it's not like I'm trying to be mean or anything. I was just wondering if this is an issue with certain types of applications. I remember I had to worry about memory usage a *long* time ago when mainframe systems only had limited core memory (anybody remember core memory?), and application design and memory reuse was a critical part of the whole process. But within Oracle, I have not seen the need to worry about these types of things. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, March 06, 2002 9:04 AM To: Multiple recipients of list ORACLE-L I think it's an OCP - type question. They ask questions like this, when in reality people just buy servers, and if they need more space, they buy more disk... .. .providing there aren't obvious problems with database design. Does anyone really spend days figuring out the exact size of rows etc. before tables are created? It seems to me people would just try the new thing on a development machine, look at space usage, then declare how much disk and CPU they need for the production system. There is a point of diminishing returns, where more time is lost than the performance / storage gain would justify. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 06, 2002 9:09 AM To: Multiple recipients of list ORACLE-L Subject:RE: Riddle me this Oracle riddle... Anybody wish to answer the following: Why does this matter? Or is this an OCP exam question? Personally, I fail to see the relevance. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, March 05, 2002 4:48 PM To: Multiple recipients of list ORACLE-L 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 The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F 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: Boivin, Patrice J 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
RE: Riddle me this Oracle riddle...
Jonathan, I agree, but what I am implying (maybe a bit poorly) is that due to it's anchored declaration, it will NEVER be longer than 200 bytes plus 2 for the length. Initially it will still be 2 bytes plus a NULL string, unless the column (to which it is anchored to) has a default value. Also, that max 4000 is for DB columns only, for plsql it is 32767 (2^32-1). Tom, the way oracle allocates memory for plsql variables is if the varchar2 size (declared) is 1999 bytes or less, it is stack allocated, else it is allocated from heap. So, I'd say they would manage memory better, as majority of allocation would be from stack. In case of Robs declaration, if you assign more than 200 bytes to that variable (assuming it is a IN OUT type, otherwise you can't), I believe you will get a 6502 error. 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! ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
RE: Riddle me this Oracle riddle...
By core memory do you mean the 18x18 blocks of doughnuts with the wires in them that magnetized, read, or changed polarity of the magnetization for zero or ones? That was just a little before I was loading the boot strap loader with the toggle switches on a computer. And a long time before the disk drive with the 12 removable platter in a cartridge. ROR mª¿ªm [EMAIL PROTECTED] 03/06/02 10:08AM Just curious - it's not like I'm trying to be mean or anything. I was just wondering if this is an issue with certain types of applications. I remember I had to worry about memory usage a *long* time ago when mainframe systems only had limited core memory (anybody remember core memory?), and application design and memory reuse was a critical part of the whole process. But within Oracle, I have not seen the need to worry about these types of things. Tom Mercadante Oracle Certified Professional -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers 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).
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: [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).
Re: Riddle me this Oracle riddle...
One of my first exposure to computers was as repairman on a Univac computer which had a whopping 32K words of 30 bits in each word containing only ferrite core memory. The program was loaded from a 7-bit (2 octal digits, plus a parity bit) magnetic tape drive. In such a system, every bit mattered. Mercadante, Thomas F wrote: Just curious - it's not like I'm trying to be mean or anything. I was just wondering if this is an issue with certain types of applications. I remember I had to worry about memory usage a *long* time ago when mainframe systems only had limited core memory (anybody remember core memory?), and application design and memory reuse was a critical part of the whole process. But within Oracle, I have not seen the need to worry about these types of things. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, March 06, 2002 9:04 AM To: Multiple recipients of list ORACLE-L I think it's an OCP - type question. They ask questions like this, when in reality people just buy servers, and if they need more space, they buy more disk... .. .providing there aren't obvious problems with database design. Does anyone really spend days figuring out the exact size of rows etc. before tables are created? It seems to me people would just try the new thing on a development machine, look at space usage, then declare how much disk and CPU they need for the production system. There is a point of diminishing returns, where more time is lost than the performance / storage gain would justify. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 06, 2002 9:09 AM To: Multiple recipients of list ORACLE-L Subject:RE: Riddle me this Oracle riddle... Anybody wish to answer the following: Why does this matter? Or is this an OCP exam question? Personally, I fail to see the relevance. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, March 05, 2002 4:48 PM To: Multiple recipients of list ORACLE-L 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 The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F 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: Boivin, Patrice J 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
RE: Riddle me this Oracle riddle...
This is not true. the client does not communicate with the procedure. Oracle shadow process does. It's always a pointer. regards, Waleed -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( |pinvarchar2(200) |); |it has to be: |procedure blah( |pinvarchar2 |); | |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: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: Riddle me this Oracle riddle...
Batman is experiencing some technical difficulty with his 'OnStar' service ;) Looks like Robin forgot to mail the payment... :( Sorry, Jared... for the OT post.. -Original Message- Sent: Tuesday, March 05, 2002 4:48 PM To: Multiple recipients of list ORACLE-L Riddle me this Batman. Assume Oracle9i... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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).
Re: Riddle me this Oracle riddle...
What about the case where a client Pro*C program (for example) has an embedded anonymous pl/sql block which does: begin :local_target_variable := procedure xyz(:local_source_variable); end; You might also consider the warning that goes with the NOCOPY option - to the effect that it is not always possible for NOCOPY to be honoured because it is not always possible for a pointer to be used. 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 17:58 |This is not true. the client does not communicate with the procedure. Oracle |shadow process does. |It's always a pointer. | |regards, | |Waleed | -- 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).
RE: Riddle me this Oracle riddle...
The whole block gets transmitted to Oracle shadow process where it gets executed there. Sqlnet and the other layers take care of mapping host variables to Oracle shadow process and the PGA. There is no difference between your pro*C block and executing the same block from sqlplus on your PC. The warning is only for the IN OUT parameters not for IN parameters. You can not assign values to an IN parameter and this why referencing it makes sense. While it's not always for Oracle to assure that in case it's specified IN OUT. Regards, Waleed -Original Message- Sent: Wednesday, March 06, 2002 1:13 PM To: Multiple recipients of list ORACLE-L What about the case where a client Pro*C program (for example) has an embedded anonymous pl/sql block which does: begin :local_target_variable := procedure xyz(:local_source_variable); end; You might also consider the warning that goes with the NOCOPY option - to the effect that it is not always possible for NOCOPY to be honoured because it is not always possible for a pointer to be used. 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 17:58 |This is not true. the client does not communicate with the procedure. Oracle |shadow process does. |It's always a pointer. | |regards, | |Waleed | -- 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: Khedr, Waleed 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).
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( |pinvarchar2(200) |); |it has to be: |procedure blah( |pinvarchar2 |); | |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
Re: Riddle me this Oracle riddle...
Interesting, Thank you 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 18:52 |The whole block gets transmitted to Oracle shadow process where it gets |executed there. |Sqlnet and the other layers take care of mapping host variables to Oracle |shadow process and the PGA. | -- 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).
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 PMTo: Multiple recipients of list
ORACLE-LSubject: RE: Riddle me this Oracle riddle...The correct
answer is c, 4000 bytes, which is the defined max sizefor a varchar2 in
Oracle9i. This is what I was told directly lastweek in a very lively
discussion with two Oracle developers. So, Jonathan iscorrect (and does that
really surprise anyone?).I have discovered that our developers are
expecting that because the %typeis being used in the parameter of the
PL/SQL, that the parameter is beingconstrained to 200 bytes. In fact,
dbms_describe and the PRO*C describeprocedures 9.0.1.2 and 8.1.7.3
will report the size of the parameter as200 bytes.In fact, the
allocated size is 2000 bytes, and 2000 bytes is returnedaccording to Oracle
development (in Oracle8i, 4000 bytes in 9i).Thus, if in PRO*C I malloc a
variable array of 200 bytes for this supposed200 byte return OUT parameter,
I could potentially end up blowing the stack,and coring out. How?? Because
PL/SQL does not constrain that memoryvariable to 200 bytes... you could very
easily concat the OUT parameterwith some other text and have it end up
200 bytes. Return it to thecallingprogram 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 younever have a case where the size is exceeded. Thus,
if I am reading in datafrom the table into the parameter, then its likely
that you won't blast thevariable. But, bugs to occur and sometimes people
don't consider theimplications of what they are doing. and perhaps,
perhaps, someone couldfind a way to take advantage of this little know
issue. Oracle says that therisks 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 asize for the type'd procedures. This will change back to the way it
wasin 9.0.1.4, and then in 9.0.2 it is planned to revert BACK to not
reportingthe size.Of course, I don't know how many sites this really
impacts... also, withregards to the shared pool, there might be some
implications...RFRobert G. Freeman - Oracle8i OCPOracle DBA
Technical LeadCSX Midtier Database AdministrationThe Cigarette
Smoking Man: Anyone who can appease a man's conscience cantake his freedom
away from him.-Original Message-Sent: Wednesday,
March 06, 2002 11:58 AMTo: Multiple recipients of list
ORACLE-LI think we are talking at cross-purposes here, the point
I wastrying to make was that the declared parameter doesn't
havethe '200' associated with it that you might assume it to haveby
virtue of its apparent association with the table.Bear in mind, by the
way, that if a client machine calls a serverprocedure, the bit about
pointers and IN parameters is irrelevant.Jonathan Lewishttp://www.jlcomp.demon.co.ukNext
Seminar - UK, April 3rd - 5thhttp://www.jlcomp.demon.co.uk/seminar.htmlHost
to The Co-Operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.htmlAuthor
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, andan IN|OUT parameter. When you call the
subprogram, the IN parameter ispassed 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
valueof the|actual
parameter.Waleed-Original
Message-|mailto:[EMAIL PROTECTED]
]|Sent: Wednesday, March 06, 2002 9:58 AM|To: Multiple recipients of
list ORACLE-LBear 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
RE: Riddle me this Oracle riddle...
It works because PL/SQL will internally reallocate memory as required. This
has to do with
non Oracle code (e.g. Pro*C) interfacing with Oracle code.
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 2:37 PM
To: Multiple recipients of list ORACLE-L
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-
mailto:[EMAIL PROTECTED] ]
Sent: Wednesday, March 06, 2002 2:04 PM
To: Multiple recipients of list ORACLE-L
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 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 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] 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(
|pinvarchar2(200)
|);
|it has to be:
|procedure blah(
|pin
Re: Riddle me this Oracle riddle...
And that is because PL/SQL "varchar2" type (used to
describe PL/SQL variables)hasdifferent max length (32767)then
database "varchar2" type (used to describe table columns), which is
4000.
Igor Neyman, OCP DBA[EMAIL PROTECTED]
- Original Message -
From:
Khedr,
Waleed
To: Multiple recipients of list ORACLE-L
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 PMTo: Multiple recipients of list
ORACLE-LSubject: RE: Riddle me this Oracle riddle...The
correct answer is c, 4000 bytes, which is the defined max sizefor a
varchar2 in Oracle9i. This is what I was told directly lastweek in a very
lively discussion with two Oracle developers. So, Jonathan iscorrect (and
does that really surprise anyone?).I have discovered that our
developers are expecting that because the %typeis being used in the
parameter of the PL/SQL, that the parameter is beingconstrained to 200
bytes. In fact, dbms_describe and the PRO*C describeprocedures
9.0.1.2 and 8.1.7.3 will report the size of the parameter as200
bytes.In fact, the allocated size is 2000 bytes, and 2000 bytes is
returnedaccording to Oracle development (in Oracle8i, 4000 bytes in
9i).Thus, if in PRO*C I malloc a variable array of 200 bytes for this
supposed200 byte return OUT parameter, I could potentially end up blowing
the stack,and coring out. How?? Because PL/SQL does not constrain that
memoryvariable to 200 bytes... you could very easily concat the OUT
parameterwith some other text and have it end up 200 bytes. Return it
to thecallingprogram 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 younever have a case where the size is exceeded. Thus, if I
am reading in datafrom the table into the parameter, then its likely that
you won't blast thevariable. But, bugs to occur and sometimes people don't
consider theimplications of what they are doing. and perhaps, perhaps,
someone couldfind a way to take advantage of this little know issue.
Oracle says that therisks 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 asize for the type'd procedures. This will change
back to the way it wasin 9.0.1.4, and then in 9.0.2 it is planned to
revert BACK to not reportingthe size.Of course, I don't know how
many sites this really impacts... also, withregards to the shared pool,
there might be some implications...RFRobert G. Freeman -
Oracle8i OCPOracle DBA Technical LeadCSX Midtier Database
AdministrationThe Cigarette Smoking Man: Anyone who can appease a
man's conscience cantake his freedom away from
him.-Original Message-Sent: Wednesday, March 06,
2002 11:58 AMTo: Multiple recipients of list ORACLE-LI
think we are talking at cross-purposes here, the point I wastrying to
make was that the declared parameter doesn't havethe '200'
associated with it that you might assume it to haveby virtue of its
apparent association with the table.Bear in mind, by the way, that if
a client machine calls a serverprocedure, the bit about pointers and IN
parameters is irrelevant.Jonathan Lewishttp://www.jlcomp.demon.co.ukNext
Seminar - UK, April 3rd - 5thhttp://www.jlcomp.demon.co.uk/seminar.htmlHost
to The Co-Operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.htmlAuthor
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, andan IN|OUT parameter. When you call the
subprogram, the IN parameter ispassed 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 valueof the|actual
parameter.Waleed-Original
Message-|mailto:[EMAIL PROTECTED]
]|Sent: Wednesday, March 06, 2002 9:58 AM|To: Multiple recipients of
list ORACLE-LBear 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%
Re: Riddle me this Oracle riddle...
On 9.0.1.2 the output is
5000
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 19:44
|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
--
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).
Re: Riddle me this Oracle riddle...
As far as I remember, the size of the host variable is part of the
parameters when you define or bind your variables. In any case, it's
true with OCI. Which means that when Oracle copies the data into your
variable, it's bounded. You may have truncated values, perhaps, but then
it's just a matter of having properly sized your variables and in any
case it is in no way specific to how much memory Oracle allocates here
and there. If you have a cataclysmic crash, then, round up the usual
suspects: your developers.
Freeman, Robert wrote:
It works because PL/SQL will internally reallocate memory as required. This
has to do with
non Oracle code (e.g. Pro*C) interfacing with Oracle code.
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 2:37 PM
To: Multiple recipients of list ORACLE-L
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-
mailto:[EMAIL PROTECTED] ]
Sent: Wednesday, March 06, 2002 2:04 PM
To: Multiple recipients of list ORACLE-L
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 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 16:31
|From 9i DOC:
|
|Suppose a subprogram declares an IN parameter, an OUT parameter, and
an IN
|OUT
RE: Riddle me this Oracle riddle...
Ok here is my two cents:
First you asked what is the size of the parameter in PL/SQL procedure, and
we said it's not fixed size it's variable size depending on the returned
data.
Everybody knows that, so it's not 2000 or 4000.
Then you came and said you do not mean PL/SQL but you mean Pro*C.
When we declare a parameter as VARCHAR2 we don't specify a high limit on the
size. And it does not matter you're using %TYPE, it will be an open sized
varchar2.
Everybody knows this also.
It's the developer responsibility to take care of this when using Pro*C.
The size limitation you are talking about (2000 4000) is coming from the
host language that you're using which is natural.
Look at external data types.
Am I missing something?
It's very confusing to ask a question about pl/sql procedure and you are
taking about pro*C.
-Original Message-
Sent: Wednesday, March 06, 2002 2:49 PM
To: Multiple recipients of list ORACLE-L
It works because PL/SQL will internally reallocate memory as required. This
has to do with
non Oracle code (e.g. Pro*C) interfacing with Oracle code.
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 2:37 PM
To: Multiple recipients of list ORACLE-L
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-
mailto:[EMAIL PROTECTED] ]
Sent: Wednesday, March 06, 2002 2:04 PM
To: Multiple recipients of list ORACLE-L
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 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
RE: Riddle me this Oracle riddle...
You did not specify in, out or in out, so the default is: IN. Parameters that are defined as IN don't get copied but are referenced (passed by reference). Regards, Waleed (I hope I'm right!) -Original Message- Sent: Tuesday, March 05, 2002 4:48 PM To: Multiple recipients of list ORACLE-L 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 The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed 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).
RE: Riddle me this Oracle riddle...
So the answer is none of the above. -Original Message- Sent: Tuesday, March 05, 2002 5:48 PM To: Multiple recipients of list ORACLE-L You did not specify in, out or in out, so the default is: IN. Parameters that are defined as IN don't get copied but are referenced (passed by reference). Regards, Waleed (I hope I'm right!) -Original Message- Sent: Tuesday, March 05, 2002 4:48 PM To: Multiple recipients of list ORACLE-L 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 The Cigarette Smoking Man: Anyone who can appease a man's conscience can take his freedom away from him. -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed 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: Khedr, Waleed 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).
