Works -- but why?
May 7, 2002 - 8am Central time zone
Reviewer: Vladimir Andreev from Germany
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
November 14, 2002 - 8am Central time zone
Reviewer: Alex Hendriks from Netherlands
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?
Followup November 14, 2002 - 7pm Central time zone:
nope
November 15, 2002 - 9am Central time zone
Reviewer: A reader
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)
Followup November 15, 2002 - 7pm Central time zone:
backwards compatibility for now.
November 15, 2002 - 9am Central time zone
Reviewer: A reader
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?
May 17, 2005 - 12pm Central time zone
Reviewer: Maverick from USA
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,
Followup May 17, 2005 - 2pm Central time zone:
see orginal answer "longsubstr" above.......
Extracting LONGS from DBLINK to new DB
August 30, 2005 - 4am Central time zone
Reviewer: Russell from Melbourne, VIC Australia
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
Followup August 30, 2005 - 7am Central time zone:
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
December 23, 2005 - 2am Central time zone
Reviewer: Sucheta Babar from Mumbai INDIA
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
April 14, 2006 - 9am Central time zone
Reviewer: Mark Bowl from Columbus, OH USA
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?
June 14, 2006 - 8am Central time zone
Reviewer: Mathew Butler from UK
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?
Followup June 14, 2006 - 12pm Central time zone:
search for showlong
ORA-01010 invalid OCI operation for showlong, dblink and HS
June 15, 2006 - 5am Central time zone
Reviewer: Mathew Butler from UK
* 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:
- Is this expected behaviour?
- 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 )
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.
Followup June 15, 2006 - 8am Central time zone:
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.
June 16, 2006 - 4am Central time zone
Reviewer: Mathew Butler from UK
Best Regards,
Appearing some junk characters
July 6, 2006 - 6am Central time zone
Reviewer: ravs from india
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
Followup July 8, 2006 - 9am Central time zone:
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
July 10, 2006 - 1am Central time zone
Reviewer: ravs from india
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
Followup July 10, 2006 - 7am Central time zone:
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
July 11, 2006 - 5pm Central time zone
Reviewer: ravs from india
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
Followup July 12, 2006 - 3pm Central time zone:
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
July 24, 2006 - 12pm Central time zone
Reviewer: Kshitij Peter from Hyderbad, India.
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
Followup July 24, 2006 - 2pm Central time zone:
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
September 18, 2006 - 1pm Central time zone
Reviewer: Sara from Spain
I have the same problem with INSTR function.
Can you help me?
Thanks for all
Sara
Followup September 18, 2006 - 1pm Central time zone:
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
May 29, 2008 - 6am Central time zone
Reviewer: esraa from Egypt
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.
Followup May 29, 2008 - 8am Central time zone:
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
June 9, 2008 - 4am Central time zone
Reviewer: vivek
How to find the the length of data of long data type
|