Long datatype fields
David, April 03, 2002 - 11:52 am UTC
Tom, how can I insert into a table the values returned by l_curpos for each row. When I include an insert INTO table statement it only does it once.
I would like to pass as the query 'select text from all_tables' but then insert the LONG VALUE SIZE into a separate table.
Regards
April 03, 2002 - 4:06 pm UTC
does not compute -- I don't know what you are asking to do. l_curpos is just a number, an offset.
If you want to insert the "long value size" - it sounds like you want a function that returns the size in bytes of a given long. turn this into a function that given a query that identifies a row -- returns the long size in bytes. In 8i -- if you make that an autonomous transaction, you can call it from sql in an insert statement.
longsubstr() should be a function, not a procedure
Sudhakar Atmakuru, August 18, 2004 - 9:35 am UTC
Hi tom,
I see a typo in your response. The example you gave for longsubstr() should define a function but not a procedure, since a procedure can not return a value. Dont you think so?
Thanks,
Sudhakar.
August 18, 2004 - 9:52 am UTC
yes
Dealing with long datatype fields
Bopanna Kallichanda, August 18, 2004 - 5:15 pm UTC
I created this function
create or replace function long_substr
( p_table_owner in varchar2,
p_table_name in varchar2,
p_partition_name in varchar2)
return varchar2
as
l_tmp long;
begin
select high_value
into l_tmp
from all_tab_partitions
where table_owner = p_table_owner
and table_name = p_table_name
and partition_name = p_partition_name ;
return l_tmp;
end;
and then I ran the following query
select table_owner, table_name, partition_name, HIGH_VALUE, long_substr2(table_owner, table_name, partition_name) HIGH_VALUE2
from all_tab_partitions
where table_name = 'ORDER_ITEM_FACT'
KBDSS ORDER_ITEM_FACT OIF_FY1999M10 2451511 <null>
KBDSS ORDER_ITEM_FACT OIF_FY1999M11 2451546 <null>
KBDSS ORDER_ITEM_FACT OIF_FY1999M12 2451574 <null>
Function returns null value. Any ideas on why it is null?
Thanks
August 18, 2004 - 11:40 pm UTC
you named it long_substr but are calling long_substr2?
when I changed both to be long_substr, i get:
ops$tkyte@ORA9IR2> select user, table_name, partition_name, HIGH_VALUE,
2 long_substr2(user, table_name, partition_name) HIGH_VALUE2
3 from user_tab_partitions
4 where table_name = 'T'
5 /
USER TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME
------------------------------
HIGH_VALUE
-------------------------------------------------------------------------------
HIGH_VALUE2
-------------------------------------------------------------------------------
OPS$TKYTE T
JUNK
MAXVALUE
MAXVALUE
OPS$TKYTE T
PART1
TO_DATE(' 2003-03-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORI
AN')
TO_DATE(' 2003-03-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORI
AN')
OPS$TKYTE T
PART2
TO_DATE(' 2003-03-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORI
AN')
TO_DATE(' 2003-03-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORI
AN')
Dealing with long datatype fields
Bopanna Kallichanda, August 19, 2004 - 12:48 pm UTC
The problem seems to be table and the function need to be owned by the same user. When I created the function as the user who owns the table, it works fine. That is what I wanted. Thank you.
August 19, 2004 - 5:27 pm UTC
or use dba_tab_partitions...
Dealing with long datatype fields
Bopanna Kallichanda, August 19, 2004 - 7:43 pm UTC
dba_tab_partitions worked. Thank you once again.
Need to accomplish the same thing in a Before Insert Trigger.
Ben Sauer, September 02, 2004 - 5:29 pm UTC
Tom,
I used this to create a function that takes a field and populates another field with the top line of the first field. Worked great for all my historical data. Problem is that I now need to have the database do the same thing for new records on a Before Insert Trigger. My function calls dbms_sql.column_value_long(l_cursor, 1, 40, 0 , val_out, len_ref); which produces a this command must follow a fetch. (The previous line.) Any ideas on modifying this approach or an alternate approach for it? Thanks,
September 02, 2004 - 9:45 pm UTC
need to accomplish "what" exactly?
Long Values cont...
Ben L Sauer, September 07, 2004 - 2:42 pm UTC
Sorry, should have been more clear. I need to get part of the data from a long datatype field. I know I can't use substr or instr to do that, but what can I use. The approach above won't work because I need to do it before it's written to the database.
September 07, 2004 - 2:51 pm UTC
nothing much is going to work in the trigger as longs are not accessible to the row triggers.
why do you need it "before" (since longs in general are not necessarily all there during the insert -- they normally do piecewise inserts after the row has been inserted already for big longs)
what is the goal -- what is the end result needed? (i hope you don't have data encoded in the long, that would just be "wrong")
That's what I was afraid of...
Ben Sauer, September 07, 2004 - 4:03 pm UTC
I'm trying to get around a poorly designed front end by clipping out the first line of the long and putting it into a title field and having it go in at the same time as the insert so the front end will update the main screen with it. I realize it's bad practice, but given the circumstances about optimal recovery from bad code; at least if it were possible.
September 07, 2004 - 4:21 pm UTC
You might be able to get away with this, if the long is piecewise inserted, all bets are off. if long exceeds 32k, you have to use dbms_sql to piecewise fetch the first bit:
ops$tkyte@ORA9IR2> create table t ( pk int primary key, l long, x varchar2(20) );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package state_pkg
2 as
3 type array is table of number index by binary_integer;
4
5 g_data array;
6 end;
7 /
Package created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace trigger t_bi
2 before insert on t
3 begin
4 state_pkg.g_data.delete;
5 end;
6 /
Trigger created.
ops$tkyte@ORA9IR2> create or replace trigger t_aifer
2 after insert on t for each row
3 begin
4 state_pkg.g_data(state_pkg.g_data.count+1) := :new.pk;
5 end;
6 /
Trigger created.
ops$tkyte@ORA9IR2> create or replace trigger t_ai
2 after insert on t
3 declare
4 l_rec t%rowtype;
5 begin
6 for i in 1 .. state_pkg.g_data.count
7 loop
8 select * into l_rec from t where pk = state_pkg.g_data(i);
9 update t set x = substr( l_rec.l, 1, 20 );
10 end loop;
11 end;
12 /
Trigger created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1, 'hello world blah blah blah blah blah blah blah blah blah', null );
1 row created.
ops$tkyte@ORA9IR2> select pk, x from t;
PK X
---------- --------------------
1 hello world blah bla
serach long datatype
Charanjiv, September 21, 2004 - 6:32 am UTC
Can i do something like ..
select view_name from dba_views
where TEXT like 'MY STRING'
Dealing with long datatype fields
sohwe, December 06, 2004 - 12:55 am UTC
RE: search long datatype in xxx_VIEWS (see September 21, 2004)
A reader, September 19, 2007 - 5:49 pm UTC
September 24, 2007 - 7:00 am UTC
where did I suggest above that you would create an index on the data dictionary?
if in fact that is what you mean by xxx_views
TEXT from DBA_VIEWS
Duke Ganote, November 05, 2007 - 3:11 pm UTC
I think they were referencing this:
Q: "Can i do something like ..
select view_name from dba_views where TEXT like 'MY STRING'"
A: "index it, create index t_idx on t(longcolumns) indextype is ctxsys.context;
and search it where contains( longcolumns, '{my string}' ) > 0;"
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:665224430110#24342648974885 But you're really suggesting something like this example, where I'm trying to find views that embed a NO_MERGE hint (version 10gR2):
BEGIN
FOR rec in
( SELECT view_Name, text, text_length
FROM USER_VIEWS
WHERE text_length < 32000 ) LOOP
IF UPPER(rec.text) LIKE '%NO_MERGE%' THEN
DBMS_OUTPUT.PUT_LINE(rec.view_Name);
END IF;
END LOOP;
END;
/
Oscar Rivera, November 13, 2012 - 10:38 am UTC
Thanks and Function to get LONG to CLOB
kpanchan, May 22, 2013 - 7:51 pm UTC
Tom
I enhanced your SHOWLONG procedure as Function to return CLOB. This helped me to overcome copying data from legacy database with LONG datatype to CLOB dataype in Oracle 11g. Your solution rock across the dblink.
Code:
create or replace FUNCTION F_long_to_clob( p_query in varchar2,
p_name in varchar2,
p_value in varchar2 )
RETURN CLOB
as
l_cursor integer default dbms_sql.open_cursor;
l_n number;
l_long_val varchar2(250);
l_long_len number;
l_buflen number := 250;
l_curpos number := 0;
v_big_str VARCHAR2(32767):='';
vTempClob CLOB;
vClob CLOB;
begin
dbms_sql.parse( l_cursor, p_query, dbms_sql.native );
dbms_sql.bind_variable( l_cursor, p_name, p_value );
dbms_sql.define_column_long(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);
if (dbms_sql.fetch_rows(l_cursor)>0)
then
loop
dbms_sql.column_value_long(l_cursor, 1, l_buflen,
l_curpos , l_long_val,
l_long_len );
l_curpos := l_curpos + l_long_len;
--dbms_output.put_line( l_long_val );
vTempClob:= l_long_val;
vClob:= vClob || vTempClob;
exit when l_long_len = 0;
end loop;
end if;
--dbms_output.put_line( '====================' );
--dbms_output.put_line( 'Long was ' || l_curpos ||
-- ' bytes in length' );
dbms_sql.close_cursor(l_cursor);
return (vClob);
exception
when others then
if dbms_sql.is_open(l_cursor) then
dbms_sql.close_cursor(l_cursor);
end if;
raise;
end f_long_to_clob;
/
How to execute this code:
select
F_LONG_TO_CLOB('SELECT LONG_COL FROM schemaname.table_name@dblink WHERE 1 = :x ',':x',1)
from dual;
I want to share because your solution works across dblink.
Thanks once again. Your website oracle knowledgebase !!
May 22, 2013 - 8:57 pm UTC
thanks!