CLOB BLOB Oracle PL/SQL
Oracle 11gR2. APEX , . - DB, APEX, , Oracle DBLinks.
INTRO: OP , , , " " " ", , OP Oracle.
- LOB dblink. , : ORA-22992: cannot use LOB locators selected from remote tables.
, , . , 2010 , - , . , .
: , , ( ) / , "remote" "local" ... , , , .
REMOTE SERVER
REMOTE DATABASE INSTANCE.
CREATE TYPE object_row_type AS OBJECT (
MY_ORDER NUMBER,
MY_ID NUMBER,
MY_CLOB_AS_VARCHAR VARCHAR2(4000));
CREATE TYPE object_table_type AS TABLE OF object_row_type;
DDL
CREATE TABLE REMOTE_CLOB_TABLE (
ID NUMBER NOT NULL,
MY_CLOB CLOB
);
INSERT INTO REMOTE_CLOB_TABLE(id, my_clob)
VALUES(1001, 'When can the expenditure enter behind a shock recovery? The strategy fishes underneath the sugar. An after wrap masters a slim moron. The twenty dish hunts an aunt opposite the credible zone. The phoenix copes. The diagonal flours a bag against the positive fan.');
INSERT INTO REMOTE_CLOB_TABLE(id, my_clob)
VALUES(2001, 'A traveled concept hides a removed skin. A liquid steers whatever understandable heart. A curve strips away an assembly. A wartime freezes the outcome.');
INSERT INTO REMOTE_CLOB_TABLE(id, my_clob)
VALUES(3001, 'A lonely genre bolts beside the obliging prisoner. The freedom stamps! Its game fluid dictates. How will her power imagine the quantum?');
INSERT INTO REMOTE_CLOB_TABLE(id, my_clob)
VALUES(4001, 'The young office caps the travelled temper. A forum husbands the family. The detail peers. Her jammed agenda experiments against the regarding obstruction.');
INSERT INTO REMOTE_CLOB_TABLE(id, my_clob)
VALUES(5001, 'The incredible drivel suspects. A vehicle reads. A cardboard jacket shares the insult above the baking constitutional. Outside this effort composes the invited jest.');
INSERT INTO REMOTE_CLOB_TABLE(id, my_clob)
VALUES(6001, 'The vegetarian strength marches underneath an opposing intellectual. The ringed lifestyle bends the archaic thirst. A saga escapes with the ego. The acorn escapes against the old lark.');
INSERT INTO REMOTE_CLOB_TABLE(id, my_clob)
VALUES(7001, 'Whatever bear furthers a mania. The norm contours a ruin. How can the reasoned composite cough? With a unimportant timetable reasons the sorry frog. Can the key jacket toss an author?');
COMMIT;
Script
CREATE or REPLACE FUNCTION CONVERT_CLOB_TO_VARCHAR RETURN object_table_type PIPELINED
IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_clob_length number;
v_loops number;
v_varchar_size number := 100;
-- purposely set to a small value to see the looping step work
-- normally set to max varchar2 size (4000 BYTE).
BEGIN
FOR cur IN (SELECT id, my_clob from remote_clob_table)
LOOP
v_clob_length := dbms_lob.getlength (cur.my_clob);
v_loops := trunc(v_clob_length/v_varchar_size) +
sign (mod(v_clob_length, v_varchar_size )) - 1;
FOR i IN 0..v_loops
LOOP
-- This chunks the CLOB/LOB file from beginning to end in chunks
-- of pre-designated size.
PIPE ROW(object_row_type(i+1, cur.id, dbms_lob.substr(cur.my_clob,
v_varchar_size, v_varchar_size * i + 1 )));
END LOOP;
END LOOP;
COMMIT;
RETURN;
END;
VIEW CLOB:
CREATE or REPLACE VIEW myremotedata AS
SELECT * FROM TABLE(CONVERT_CLOB_TO_VARCHAR) a;
LOCAL DATABASE INSTANCE.
DB :
CREATE PUBLIC DATABASE LINK MY_REMOTE_CONNECTION
CONNECT TO REMOTE_USER
IDENTIFIED BY <PWD>
USING <TNS or DIRECT CONNECTION STRING>
:
CREATE OR REPLACE TYPE MY_TABLE_TYPE AS TABLE OF VARCHAR2(4000);
PL/SQL VARCHAR Pieces CLOB
CREATE OR REPLACE FUNCTION F_VARCHAR_TO_CLOB (input_table_of_varchar my_table_type)
RETURN CLOB IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_clob clob;
BEGIN
FOR i in 1..input_table_of_varchar.COUNT
LOOP
v_clob := v_clob || input_table_of_varchar(i);
END LOOP;
RETURN v_clob;
END;
CREATE OR REPLACE VIEW MY_REMOTE_DATA AS
SELECT a.id,
f_varchar_to_clob(
CAST( MULTISET( SELECT b.MY_CLOB_AS_VARCHAR
FROM remote_user.myremotedata@my_remote_connection b
WHERE a.id = b.my_id
ORDER BY b.my_id ASC, b.my_order ASC )
as my_table_type)
) MY_CLOB
FROM REMOTE_CLOB_TABLE@my_remote_connection a;
, , . , CLOB . . , 4 000 varchar2, CLOB, .
: MYREMOTEDATA
CONVERT_CLOB_TO_VARCHAR PL/SQL.
: ( CLOB)
select id, my_clob from my_remote_data
where id between 1000 and 5000
ID MY_CLOB
1001
When can the expenditure enter behind a shock recovery? The strategy fishes unde
rneath the sugar. An after wrap masters a slim moron. The twenty dish hunts an a
unt opposite the credible zone. The phoenix copes. The diagonal flours a bag aga
inst the positive fan.
2001
A traveled concept hides a removed skin. A liquid steers whatever understandable
heart. A curve strips away an assembly. A wartime freezes the outcome.
3001
A lonely genre bolts beside the obliging prisoner. The freedom stamps! Its game
fluid dictates. How will her power imagine the quantum?
4001
The young office caps the travelled temper. A forum husbands the family. The det
ail peers. Her jammed agenda experiments against the regarding obstruction.
4 rows selected.
VARCHAR , CLOB-.
:
, , Oracle RDBMS , , CLOBs BLOB- .
, , , (.. ).