Oracle temporary tables solve ORA-22992 Problems

When you use Database Link to integrate a remote Database, the system returns the ORA-22992 error when querying Blob fields from the remote Database. Some people use materialized views to solve this problem, but the synchronization mechanism of materialized views will be troublesome. If the synchronization time is too short, a large amount of system resources will be occupied, which puts a great deal of pressure on the server; if the setting takes too long, the foreground user is unacceptable.

Referring to the online materials, it is said that the global temporary table is used from AskTom to achieve a self-deemed elegant solution.

The Code is as follows:
Create or replace package pkg_photo
Type sys_refcursor is ref cursor;
Procedure proc_photo (p_sfzh in string,
P_photo OUT pkg_rkzp.sys_refcursor );
End pkg_photo;

Create or replace package body pkg_photo
Procedure proc_photo (p_sfzh in string, p_photo OUT pkg_rkzp.sys_refcursor)
AS
V_num number;
V_ SQL varchar2 (2000 );
V_sfzh string (18 );
BEGIN
Select count (*) into v_num from user_tables where table_name = upper ('tmp _ jn_czrk_zp ');
Dbms_output.PUT_LINE (v_num );
If v_num <1 then
V_ SQL: = 'Create global temporary table tmp_ryzp (sfzh char (18), photo blob) on commit preserve rows ';
Execute immediate v_ SQL;
End if;

V_ SQL: = 'insert into tmp_photo select sfzh, photo from v_remote_table @ mydblink where sfzh =: 1 ';
Execute immediate v_ SQL using p_sfzh;

OPEN p_photo FOR v_ SQL;
EXCEPTION
WHEN NO_DATA_FOUND
Then null;
WHEN OTHERS
Then raise;
END proc_photo;
End pkg_photo;