A reader, March 27, 2001 - 4:16 am UTC
What if the LONG datatype is a view?
A Reader, February 21, 2003 - 7:06 pm UTC
Tom,
What if we cannot convert the datatype and still want to use function like substr? Example user_views:
select view_name, substr(text,1, 10)
from user_views;
We want to get the first 10 characters in the long field. We may copy the contents of the base table into another table and convert that table's datatype. Is there any esasier ways?
Thank you very much.
February 22, 2003 - 9:34 am UTC
Why Oracle don't use CLOB but use LONG in it's dictionary tables?
A Reader, February 22, 2003 - 3:18 pm UTC
Just want to know the reason. Thanks.
February 22, 2003 - 3:36 pm UTC
legacy reasons.
when you make a change, you affect yourself.
when we make a change, we affect -- well, more then just ourselves. Changing the datatypes in the dictionary will be a rather large change for us. Backwards compatability is a hard thing to achieve. The smallest changes can have large ripples.
How to use a Clob datatype as a variable
Balaji, November 15, 2003 - 3:59 pm UTC
Hi
I am forming a dynamic sql in a proc.
the length of this sql goes more than 32 k.
so how am i to declare a clob variable and assign the query to it??
i also need to return the clob as output.
November 16, 2003 - 10:35 am UTC
well, you would use dbms_lob.createtemporary and dbms_lob.writeappend to write to it, BUT you will not be using native dynamic sql on it, you'll have to use dbms_sql which allows you to call parse with a plsql table of strings -- each string can be 255 characters but the strings are glued together to create an entire sql statement.
Long into Varchar2
Dan Clamage, November 17, 2003 - 2:27 pm UTC
If you're doing this one row at a time, AND the Long value is under 32K, you can fetch the Long into a PL/SQL VARCHAR2 and then use the standard string functions.
November 21, 2003 - 7:25 am UTC
(that was part of the original answer!)
Why Oracle don't use CLOB but use LONG in it's dictionary tables?
Vladimir Andreev, February 03, 2004 - 11:41 am UTC
That's a very good question.
Tom, your first answer to it ("Legacy reasons") doesn't tell us why the *new* data dictionary columns are of type LONG. I mean, there cannot be any backward compatibility concerns for "user_ind_expressions.column_expression", for example -- LOBs were present and recommended at the time when function-based indexes came around.
I have a grudge with that particular view, since it prevents me from using a simple read-only script to tell me if a column is indexed -- and it is a rare occasion when I am allowed to create a stored PL/SQL function in a production database, where every change is strictly controlled (as it should be).
Regards,
Flado