Long字段的处理

对于long databyte和LONG RAW类型字段的诸多操作，许多人都存在迷惑
1、如何insert ,update long databyte的问题？
2、以及如何使用Long RAW的问题？
3、如何在long字段中匹配查询？
4、如何得到long字段长度

在这里，提供3个方法insert long databyte

方法一：
declare
str_start integer := 15;
str_len integer := 20;
crs integer;
prsd integer;
outval varchar2(2000);
outval_length integer;

begin
dbms_output.enable(20000);
crs := dbms_sql.open_cursor;
dbms_sql.parse(crs
,'select text from dba_views where view_name=''DBA_USERS'''
,dbms_sql.v7);
dbms_sql.define_column_long(crs,1);
prsd := dbms_sql.execute_and_fetch(crs);
dbms_sql.column_value_long(crs
,1
,str_len
,str_start -1
,outval
,outval_length);
dbms_sql.close_cursor(crs);
outval := translate(outval,CHR(10),' ');
dbms_output.put_line(outval);
end;

方法二
declare
/*
Modify the SQL in cursor v_cur and take out the test ROWNUM code.
*/
cursor v_cur is
select rowidtochar(rowid) c_rowid, DCONTENT l_longraw
from tst03
where rownum < 100;

v_max_length number :=0;
v_length number :=0;
v_rowid varchar(18) := ' ';

begin
for v_row in v_cur
loop
v_length := lengthb(v_row.l_longraw);
if v_length > v_max_length then
v_max_length := v_length;
v_rowid := v_row.c_rowid;
end if;
end loop;
dbms_output.put('RowID: '||v_rowid||' Max Length: '||v_max_length);
dbms_output.new_line;
end;

方法三，用copy命令
set long 30000
copy to <username>/<password>@<connect string> -
insert <destination table> -
(columns in destination table),-
from <source table>;

例子：
SET LONG 30000
SET COPYCOMMIT 1
SET ARRAYSIZE 1000

COPY TO TOlogin/TOpassword@TOconnectname -
APPEND TOowner.tablename -
USING -
SELECT * FROM FROMowner.FROMtablename;

3、如何在long lolumn中进行search
其实，没有什么特别直接的方法在long 字段中进行select，那样会报错。
通常，写一个PL/SQL script，把长内容的数据阶段成一些片段，存在你定义的string中，这里可以用 dbms_sql.define_column_long
dbms_sql.column_value_long
然后用INSTR( )在没一个片段总搜索定义的string。

4、如何得到long字段的长度
This is a sample of what we do;

CREATE OR REPLACE PROCEDURE cesp_get_long
(
i_table IN VARCHAR2, -- table name
i_row_id_fld IN VARCHAR2, -- name of column to id row
i_row_id IN VARCHAR2, -- data in row id oolumn
i_col_pos IN NUMBER, -- long column position
within row
io_nextseg IN INTEGER, -- start of long chuck (start
with byte zero)
o_data OUT VARCHAR2,-- 2000 byte long chunk to send back to
calling program
io_seglengh OUT INTEGER -- actual length of chunk
returned

) IS

/*
In order to get any long field out of any table you must send this proc the
following information
in this order from either another calling PL/SQL proc or from something like
VB;

- Table name of the table that has the long data type in it.
- Column name of the column by which you want to access the row with the
long data.
NOTE; this is not the LONG column
- What in the column name you are looking for (used in a where clause
- Position in the table of the LONG column
- The starting segment byte with which to start getting chunks of the long
column. 0 starts at the beginning
- The (up too) 2000 byte chunk is put sent back in this parameter
- The actual lengh of the column chunk being sent back. If less than 2000
than no more data exists in the
long column. This should be used to determine if the calling program needs
to come back if more data exists.


- SAMPLE calling program to access this program..

DECLARE
h_data VARCHAR2(2000) := NULL;
seg_length INTEGER := 0;
seg_out INTEGER := 0;
next_byte INTEGER := 0;
begin
LOOP
cesp_get_long('sw_basic_script', 'swname', 'CE Site Ratings I', 6,
next_byte, h_data, seg_length);
insert into md_dummy values (h_data);
DBMS_OUTPUT.PUT_LINE(seg_length);
IF seg_length = 0 OR
seg_length < 2000 THEN
EXIT;
END IF;
next_byte := next_byte + seg_length;
END LOOP;
commit;
end;

*/


v_CursorID Integer;
v_ActualOut Integer;
v_Iter Integer := 2000;
v_SelectStmt VARCHAR2(100);
v_Dummy Integer;

BEGIN

v_CursorID := DBMS_SQL.OPEN_CURSOR;
v_SelectStmt := 'SELECT * FROM ' || i_table || ' WHERE ' || i_row_id_fld
|| ' = ' || '''' || i_row_id || '''';
DBMS_SQL.PARSE(v_CursorID, v_SelectStmt, DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN_LONG(v_CursorID, i_col_pos);
v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
IF DBMS_SQL.FETCH_ROWS(v_CursorID) > 0 THEN
DBMS_SQL.COLUMN_VALUE_LONG(v_CursorID, i_col_pos, v_Iter, io_nextseg,
o_data, io_seglengh);
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
io_seglengh := -1;
o_data := NULL;
END;

转载于:https://www.cnblogs.com/dynasty/archive/2005/10/13/253996.html