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
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
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
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
(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
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
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
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
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
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
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
:
Sent by: Subject: Re: Riddle me this Oracle
riddle...
root@fatcity.
com
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
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
| 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
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
-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
: 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
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
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
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
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,
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
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
| 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 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
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
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
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
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
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
;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
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
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
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
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
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.
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
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
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
40 matches
Mail list logo
