Oracle temporary tables solve ORA-22992 Problems

Source: Internet
Author: User
Tags sql using

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)
V_num number;
V_ SQL varchar2 (2000 );
V_sfzh string (18 );
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;
Then null;
Then raise;
END proc_photo;
End pkg_photo;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.