oracle新增longtext字段,Long字段的处理

-

(columns in destination table),-

from ;

例子：

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;