Works -- but why?
Vladimir Andreev, May 07, 2002 - 8:44 am UTC
This is definitely a way around the LONG columns. But that does NOT alter the fact that Oracle keeps LONG columns in the dictionary even while strongly encouraging developers to migrate to LOBs.
I could understand keeping the datatype unchanged for the old columns (like USER_VIEWS.Text), but why defining NEW dictionary columns (like USER_IND_EXPRESSIONS.COLUMN_EXPRESSION) of the LONG datatype beats me. That converts otherwise trivial tasks into nightmare -- try to get the equivalent of user_ind_columns with real column names when some of them are descending, for example. Or try to automate range partition maintenance operations based on the high values and not names, and you'll se what I mean.
USER_VIEWS column TEXT is of datatype LONG --> VARCHAR2
Alex Hendriks, November 14, 2002 - 8:23 am UTC
Thanks! This really helped me a lot. I implemmented it in a meta-table system we use here.
I wonder if it is also possible to use these longs to varchar2 conversion with execute_emmediate instead of dbms_sql?
November 14, 2002 - 7:22 pm UTC
nope
A reader, November 15, 2002 - 9:37 am UTC
Hi Tom,
Since LONG data type is depricated in oracle why oracle has long datatype is user_views views(In oracle 9.2)???
SQL> desc user_views;
Name Null? Type
----------------------------------------- -------- --------------------
VIEW_NAME NOT NULL VARCHAR2(30)
TEXT_LENGTH NUMBER
TEXT LONG <<------
TYPE_TEXT_LENGTH NUMBER
TYPE_TEXT VARCHAR2(4000)
OID_TEXT_LENGTH NUMBER
OID_TEXT VARCHAR2(4000)
VIEW_TYPE_OWNER VARCHAR2(30)
VIEW_TYPE VARCHAR2(30)
SUPERVIEW_NAME VARCHAR2(30)
November 15, 2002 - 7:23 pm UTC
backwards compatibility for now.
A reader, November 15, 2002 - 9:42 am UTC
In oracle 9i R2
SQL> select count(*) from all_tab_columns where DATA_TYPE = 'LONG';
COUNT(*)
----------
99
Still oracle is using LONG. Strange !!!!!
Substr on Long?
Maverick, May 17, 2005 - 12:48 pm UTC
Tom, I was trying to extract values from user_tab_partitions in Oracle version 9.2.0.1 .
I need something like this
select table_name,partition_name,substr(high_value,11,19) date from user_tab_partitions
where table_name ='MY_TABLE';
but since substr cannot be used on long datatypes, how should i get this info. We are putting date values for this partitions in this column
eg: high value has a value
TO_DATE(' 2005-04-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
Any help is appreciated.
Thanks,
May 17, 2005 - 2:17 pm UTC
see orginal answer "longsubstr" above.......
Extracting LONGS from DBLINK to new DB
Russell, August 30, 2005 - 4:18 am UTC
Tom,
There is an Oracle DB that we need to extract data out of. For some reason, one of the fields in the remote data is a LONG datatype. Ultimately, I only need about 7 characters from the LONG, and from what I can see, the field has less than 32K characters.
I created a table on my database (with a Long , varchar2 and a CLOB field to try and store the data in different attempts).
I have tried Insert Into (into the LONG), and the error ORA-00997: Illegal use of LONG datatype) is returned
I created the longsubstr function listed on this page, and when I tried to insert that data ( or select it even)
select MSGKEY, DESTINATION, DATECREATED, MSGTYPE, MSGTEXT, longsubstr(M.rowid,1,100)
FROM W6ADMIN.W6MESSAGES@CNRP M
WHERE M.MSGTYPE = 'No Work'
AND m.DATECREATED BETWEEN TRUNC(SYSDATE) -1 AND TRUNC(SYSDATE);
I get the following:
ORA-02069 global_names parameter must be set to TRUE for this operation
On setting global_names to true, I get
ORA-02070 database CNRP does not support ROWIDs in this context.
I do not have access to change or modify anything on the remote end. I have to import this database into mine every day. Do you have any ideas, tips or suggestions to help me out?
thanks
Russell
August 30, 2005 - 7:09 am UTC
That remote site is going to have to help you. if they won't, you'll have to copy the entire long over (using something like sqlplus copy command).
IF THEY have the longsubstr and create a view on their site:
select MSGKEY, DESTINATION, DATECREATED, MSGTYPE, MSGTEXT,
longsubstr(M.rowid,1,100) text
FROM W6ADMIN.W6MESSAGES;
You can just select from that.
Conversion/Extraction of Longs
Sucheta Babar, December 23, 2005 - 2:19 am UTC
Actually, this answer helped me few years back. Recently some body asked me same question and I came back to this site. asktom.oracle.com is always guieded me in my career.
Sorry I have not responded earlier questions even though I have used it many times.
Thanks TOM
Select LONG from REMOTE view
Mark Bowl, April 14, 2006 - 9:41 am UTC
I have the following problem: I can't select a LONG column from remote database, when there are Join operation in the query. Example:
SELECT long_column FROM remote_table@DB
-> that's OK, but:
SELECT long_column FROM remote_table@DB INNER JOIN remote_table2@DB ON ...
-> returns: "ORA-00997: Illegal use of LONG datatype"
I cannot even perform (because there a LONG column in the joined table):
SELECT nonlong_column FROM remote_table@DB INNER JOIN remote_table2@DB ON ...
That's very strange to me because:
SELECT long_column FROM local_table INNER JOIN local_table2 ON ...
-> is OK!
Can you help me to SELECT a long column from remote DB in query with JOIN clause (the LONG column is only in the SELECT list and not in the join clause)!
So how can I get the value of this column?
Actually I'm trying to retrieve data from Oracle's views so the advice to change the LONG columns into LOBs is not useful here. Any help? Thanks in advance.
p.s. Here is the exact query that I'm trying to execute (to extract only the partitions by DATE range):
SELECT apc.NAME||'.'||apc.COLUMN_NAME cname, atp.PARTITION_NAME, atp.HIGH_VALUE
FROM all_tab_partitions atp INNER JOIN
all_part_key_columns apc ON (atp.TABLE_OWNER=apc.OWNER AND atp.TABLE_NAME=apc.NAME
AND apc.OBJECT_TYPE='TABLE' AND apc.COLUMN_POSITION=1) INNER JOIN
all_tab_cols atc ON (apc.OWNER=atc.OWNER AND apc.NAME=atc.TABLE_NAME
AND apc.COLUMN_NAME=atc.COLUMN_NAME AND atc.DATA_TYPE='DATE')
WHERE atp.TABLE_OWNER='MDR'
How to get the length of a LONG over HS?
Mathew Butler, June 14, 2006 - 8:33 am UTC
Oracle 9.2.0.5
I am attempting to process some data stored in MS access which is being retrieved into Oracle using a Heterogeneous Service (HS). The remote data is being interpretted as a LONG.
I want to check the length of the data in the remote Access DB - but over the HS connection ( ie: fro mteh Oracle side) - I can then compare the source and target data lengths for validation.
Is there a simple way to do this?
I thought that SHOW_LONG might help - I searched for SHOW_LONG on this site but got no hits.
Can you please publish the code for SHOW_LONG?
June 14, 2006 - 12:28 pm UTC
search for showlong
ORA-01010 invalid OCI operation for showlong, dblink and HS
Mathew Butler, June 15, 2006 - 5:16 am UTC
* showlong ran successfully with a local query
* showlong ran successfully with a dblink ( a link back to the same DB ) query
* Ran showlong over dblink to remote acccess database over HS and got ORA-01010
* ORA-01010 received when executing "dbms_sql.column_value_long" - see below:
<b>- Is this expected behaviour?</b>
<b>- Can you think on any (Oracle based) workaround?
I'm pulling data into Oracle from access and carrying out some validation on the data retrieved ( checking the length of remote columns that are translated as longs ) </b>
Here is an example of the failure:
SQL> create or replace procedure showlong( p_query in varchar2 )
2 as
3 l_cursor integer default dbms_sql.open_cursor;
4 l_n number;
5 l_long_val varchar2(250);
6 l_long_len number;
7 l_buflen number := 250;
8 l_curpos number := 0;
9 begin
10 dbms_output.put_line( 'Parsing...' );
11 dbms_sql.parse( l_cursor, p_query, dbms_sql.native );
12
13 dbms_output.put_line( 'Define...' );
14 dbms_sql.define_column_long(l_cursor, 1);
15 dbms_output.put_line( 'Execute...' );
16 l_n := dbms_sql.execute(l_cursor);
17
18 dbms_output.put_line( 'Fetch...' );
19 if (dbms_sql.fetch_rows(l_cursor)>0)
20 then
21 loop
22 dbms_output.put_line( 'Process fetch...' );
23 dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,
24 l_long_val, l_long_len );
25 l_curpos := l_curpos + l_long_len;
26 dbms_output.put_line( l_long_val );
27 exit when l_long_len = 0;
28 end loop;
29 end if;
30 dbms_output.put_line( '====================' );
31 dbms_output.put_line( 'Long was ' || l_curpos || ' bytes in length' );
32 dbms_sql.close_cursor(l_cursor);
33 exception
34 when others then
35 if dbms_sql.is_open(l_cursor) then
36 dbms_sql.close_cursor(l_cursor);
37 end if;
38 raise;
39 end showlong;
40 /
Procedure created.
SQL>
SQL> begin
2 showlong( 'select "Description" from part1@segordb where "ReferenceSeq" = 254' );
3 end;
4 /
Parsing...
Define...
Execute...
Fetch...
Process fetch...
begin
*
ERROR at line 1:
ORA-01010: invalid OCI operation
ORA-02063: preceding line from SEGORDB
ORA-06512: at "NPADMIN.SHOWLONG", line 38
ORA-06512: at line 2
ORA-01010 invalid OCI operation
Cause: One of the following:
* You attempted an invalid OCI operation.
* You are using an Oracle client application linked with release 7.1 (or higher) libraries, the environment variable ORA_ENCRYPT_LOGIN is set to TRUE, and you attempted to connect to a release 7.0 (or lower) Oracle database server.
* You are connected to a release 7.1 (or higher) Oracle database server, the DBLINK_ENCRYPT_LOGIN initialization parameter is set to TRUE, and you attempted to use a database link pointing to a release 7.0 (or lower) Oracle database server.
* You are connected to a release 9.0.2(or higher) Oracle database server and you attempted to use a database link pointing to a release 9.0.1 (or lower) Oracle database server for distributed autonomous transactions.
Action: For the above causes:
* Do not use the invalid OCI operation.
* If you do not wish to use encrypted connect passwords in your distributed database, then set ORA_ENCRYPT_LOGIN to FALSE. If you wish to use encrypted connect passwords, then you must upgrade all Oracle database servers to release 7.1 (or higher).
* If you do not wish to use encrypted database links in your distributed database, then set DBLINK_ENCRYPT_LOGIN to FALSE. If you wish to use encrypted database links, then you must upgrade all Oracle database servers to release 7.1 (or higher).
* Do not attempt distributed autonomous transactions on release 9.0.1 (or lower) Oracle database servers.
As always, many thanks in advance.
Best Regards.
June 15, 2006 - 8:44 am UTC
seems like it could be expected (I never tried it), to confirm, you would need to utilize support.
Thanks - already have an SR open for a related LONG issue.
Mathew Butler, June 16, 2006 - 4:52 am UTC
Best Regards,
Appearing some junk characters
ravs, July 06, 2006 - 6:18 am UTC
Hi Tom,
I am using your function given above to read data from long over a dblink and trying to insert it into a clob.
some junk charcters are appearing.
select longsubstr1('select case_history from table_case@mylnk1 where objid=:bv',268719164,0,4000) as desc from dual;
desc
--------------------------------------------------------
Tony Holder welcomed everyone to the combined CAMSS and SPACE User Group called <B>¿The National Repair User Group¿</B>. Introductions were made, and the minutes of the previous meeting were agreed.
The original string
select case_history from table_case@mylnk1 where objid=268719164 ;
case_history
----------------------------------------------------------
Tony Holder welcomed everyone to the combined CAMSS and SPACE User Group called <B>The National Repair User Group</B>. Introductions were made, and the minutes of the previous meeting were agreed.
¿ appearing in place of
I debugged your function and could not find why this character change?
my source db version is : 8.1.7.2
my target version is : 9.2.0.6
Source character set : WE8ISO8859P1
Target Charcterset : WE8ISO8859P1
source
SQL> select parameter,value from v$nls_parameters@mylnk1 where parameter='NLS_CHARACTERSET' ;
PARAMETER VALUE
---------------------------------------------------------------- -------------------------------
NLS_CHARACTERSET WE8ISO8859P1
target
SQL> select parameter,value from v$nls_parameters where parameter='NLS_CHARACTERSET' ;
PARAMETER VALUE
---------------------------------------------------------------- ----------------------
NLS_CHARACTERSET WE8ISO8859P1
i don't have any clue why this is happening?
can you please help on this Tom?
- Thanks in advance
ravi
July 08, 2006 - 9:46 am UTC
stupid windows "smart quotes"
You have characters in there that are not valid in your character set - they are "windows only" characters.
When we pull the long and convert to a clob, character set conversion is kicking in and they are getting replaced with their "equivalent" which is basically "not there".
The client that inserted this said "I'm using the we8iso8859p1 characterset", but it lied to us. It was using the windows WE8MSWIN1252 (probably) one...
those smart quotes - not part of we8iso8859p1.
Characters missing
ravs, July 10, 2006 - 1:57 am UTC
Hi Tom,
Thanks alot for your response.
I tried by running the above test on unix box also.
But it is also giving the same problem. My oracle is on sun solaris 5.8. I think somewhetere in the function it is not able to understand those characters? Is there any way that i can keep them intact?
- Thanks in advance,
ravi
July 10, 2006 - 7:45 am UTC
characters are not missing, your charactersets are NOT windows charactersets, hence when character set translation takes place (convert LONG into LOB), the invalid characters are mapped - it is all about these "windows smart quotes" which exist in the MSWIN character sets, but not in the standard characterset you are using.
character conversion
ravs, July 11, 2006 - 5:38 pm UTC
Hi Tom,
I checked my code again. The smart quotes are getting converted to some junk character at the following piece of code itself
if (dbms_sql.fetch_rows(l_cursor)>0)
then
dbms_sql.column_value_long
( c => l_cursor,
position => 1,
length => p_for_bytes,
offset => p_from_byte,
value => o_output, -- varchar2 datatype
value_length=> l_buflen );
end if;
dbms_output.put_line(substr(o_output,1,250)) ;
when tried to print them, here itself its showing some junk characters.
so i think "dbms_sql.column_value_long" is removing those smart quotes.
Is there any way to include windows smart quotes also
- Thanks in advance
ravi
July 12, 2006 - 3:27 pm UTC
THEY ARE NOT VALID CHARACTERS IN YOUR CHARACTER SET.
I don't know what else to say, any time character set conversion kicks in - bamm, garbage in, garbage OUT. The data is bad to begin with.
How to go about text compare
Kshitij Peter, July 24, 2006 - 12:05 pm UTC
I need to compare the text in the views (as pointed out in one of the post on this page).
I need to check whether a particular string (say xyz) exists in the TEXT column in DBA/USER _VIEWS.
Is there a simpler way to just compare the text in it.
What I am trying to do is:
select * from DBA_VIEWS
where TEXT like '%xyz%';
--But here the TEXT being long can't be compared directly. Tried to use to_char and to_lob, but it gives a 00932-inconsistent data type error.
Please help.
Thanks in advance
July 24, 2006 - 2:47 pm UTC
if your views are 32k and less - you can use plsql
begin
for x in ( select * from dba_views )
loop
if x.view_text like whatever
then
dbms_output.put_line(something)
.....
Use INSTR function with long datatype
Sara, September 18, 2006 - 1:18 pm UTC
I have the same problem with INSTR function.
Can you help me?
Thanks for all
Sara
September 18, 2006 - 1:45 pm UTC
well, just use the same technique, you have to piecewise fetch it and do the instr against the bits of the string as you fetch them.
how to use forms with hetrogeneous dblink
esraa, May 29, 2008 - 6:04 am UTC
I am using Oracle 10G , Access Db 2007, Windows XP
I finished hetrogeneous connectivity from Oracle 10G to Access Db 2007.
It is running well on the Oracle database .
I could display, update data on Access form my SQL>....
You can see the following for view creation and description:
connect sys/sys as sysdba
SQL> select view_name ,owner from all_views where owner = 'EMPLOYMENT'
2 /
VIEW_NAME OWNER
------------------------------ ------------------------------
ORA_ATTENDANCE EMPLOYMENT
VACANCY EMPLOYMENT
INDEX_BY_TYPES EMPLOYMENT
SQL> select text from all_views where view_name = 'ORA_ATTENDANCE';
TEXT
--------------------------------------------------------------------------------
select ID,USERID,CHECKTIME from my_attendance@access_db
-----------------------------------------------------------------
After connect to oracle schema 'employment':
SQL> desc ora_attendance;
Name
---------------------------------------
ID
USERID
CHECKTIME
---------------------------------------------------------
My probelm now is how to create Oracle Form data block on this view .
When I create the form aand run it :
no data display on query and error FRM-40505 hapens.
What is wrong???
Thanks for your help in addvance.
May 29, 2008 - 8:14 am UTC
why do people do this all of the time. post the same exact text here, there and everywhere.
How to find the the length of data of long data type
vivek, June 09, 2008 - 4:48 am UTC
How to find the the length of data of long data type
June 09, 2008 - 12:01 pm UTC