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
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
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
Criticism accepted, perhaps I didn't phrase my question very well (but then,
in that case, it *would* make a good OCP question).
>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.
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.
Whe
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
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 Ef
--
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_bla
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 ap
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
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 Datab
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 developer
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
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 a
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
>
>
> Rid
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,
| 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...
>
&g
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
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 lo
By "core" memory do you mean the 18"x18" 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,
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, t
ED]]
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
O
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
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.
-
[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
n, 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
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
x27;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
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 l
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.
by: Subject: Re: Riddle me this Oracle
riddle...
root@fatcity.
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 followin
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
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
what
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
Riddl
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
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 in
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.
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
Clearly the best guess is (e) - none of the above.
It is unlikely to be based on incoming data for
reasons of bounds checking. Which means that
you have given us 3 static values from a range of
32767 which may be possible. ;)
I haven't checked it - but if I had to guess, I
would guess, based on
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 ORA
43 matches
Mail list logo
