Baby USE COPY COMMAND
BUT BE VERY CAREFUL
BE COMPLETELY AWARE OF
COPY -Insert
REPLACE--DROPS AND RECREATES THE TABLE
TRUNCATE--TRUNCATES DATA.

In the url
</code> http://asktom.oracle.com/~tkyte/Misc/MoveLongs.html <code>
for copying long datatypes

How can I find the sine of my longest long
set long N -- size of your longest long

Thank you

We are not storing the length of the long datatypes. Is there a Oracle package that can give this kind of information.

Thank you

We have about 300000 + records in the table.

Thank you

Sir,

I am getting ORA-00997 on selecting a view.

Scenario:
========

1. A view(v1) is created on a table using dblink (the table contains long column)

2. Another view(v2) is created on this view(v1).

3. Select on this view(v2) throws ORA-997

4. Source and destination database version is 9.2.0.5


The query is not falling on any restriction of LONG.

Views:
=====

V1:
==
CREATE OR REPLACE VIEW PS_SQS_NOTICE AS
SELECT a.effdt_from , a.seqnum , a.descr , a.effdt_to , a.yes_delete ,
a.sqs_display_to_all , a.lastupdoprid , a.last_dttm_update , a.htmlArea
FROM PS_SQS_NOTICE@S76LNK a;

v2:
==
CREATE OR REPLACE VIEW PS_SQS_NOTICE_VW AS
SELECT EFFDT_FROM ,SEQNUM ,SQS_DISPLAY_TO_ALL ,HTMLAREA
FROM PS_SQS_NOTICE
WHERE TO_DATE(TO_CHAR(EFFDT_FROM,'DD-MON-YYYY'),'DD-MON-YYYY')
<= TO_DATE(TO_CHAR(SYSDATE,'DD-MON-YYYY'),'DD-MON-YYYY')
AND TO_DATE(TO_CHAR(EFFDT_TO,'DD-MON-YYYY'),'DD-MON-YYYY')
>= TO_DATE(TO_CHAR(SYSDATE,'DD-MON-YYYY'),'DD-MON-YYYY')
ORDER BY EFFDT_FROM DESC, SEQNUM DESC;



In the above views, only the column HTMLAREA is of type LONG.


Please, clarify the reason.


Thanks in advance
Muthu

How can I check on the length of a long field?

I forgot to tell you that I am using oracle 8i. Please tell me how to check on the length of a long field in a table

Hi Tom,

I create a table with a column of long datatype. I insert a string more than 3000 characters. When I select this column, it returns the string in double byte format. Please help why it is happening?

Thanks,
- Cathy

The data is feteched back as "I N S T ..." instead of "INST...". Is there any set up on Server side? I saw this problem on oracle8i, oracle9i, but not on oracle 10g.

Tom,

You mentioned LONG is deprecated in 8i, is it a good idea to use it in 10G R2? We have a situation where the column datatype is blob but the actual data is charecter with maximum size being 1MB (average being 10KB). This column gets lot's of update/inserts so performance with blob is really slow (Especially updates). Which one do you suggest for this column datatype conversion LONG or CLOB?

Thanks

Tom,

I found in documentation that LONG is only for backward compatibility. http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_sqltypes.htm#sthref347

It seems like my only chioce is CLOB and reading about optimizing them.

Thanks

Hi Tom,

I need to get the name of the partition (column partition_name) that holding
the value 'MAXVALUE' at column high_value in dba_tab_partitions.
Since the high_value column is LONG datatype , i used the following
function in pl/sql block to get the value.

Its work , but im not feeling that this the way its should be done.
Could You please suggest alternative way ?

create Function GetPartHighValue (i_table_name varchar2,i_partition_name varchar2)
return varchar2
is
temp varchar2(32000);
begin
select high_value
into temp
from dba_tab_partitions
where table_name = i_table_name
and partition_name = i_partition_name ;
return temp;
exception
when no_data_found then
return(null);
end;

set serveroutput on

declare
v_max_partition_name dba_tab_partitions.partition_name%type;
begin
select partition_name
into v_max_partition_name
from dba_tab_partitions
where TABLE_NAME = 'BB_CALLS'
and GetPartHighValue(table_name,partition_name) = 'MAXVALUE';

dbms_output.put_line('v_max_partition_name='||v_max_partition_name);
exception
when no_data_found then
dbms_output.put_line('NOTHING FOUND');
end;

v_max_partition_name=BB_CALLS_MAX_VALUE

PL/SQL procedure successfully completed.

Thanks