U0100085: GaussDB does not support subqueries in TABLE

Description

In Oracle, the TABLE function can contain subqueries, for example, TABLE(SELECT F_SPLIT(a) from dual);

GaussDB does not support this syntax.

Database Type and Version

• Source database type and version: Oracle versions supported by UGO
• Target database type and version: GaussDB versions supported by UGO

Syntax Example

CREATE OR REPLACE PROCEDURE P_CFG_B777_LRUPARSER (p_RAW_ID number ,
                                          p_ATA_BLKIDENT varchar2) is
begin
 declare cursor iCur_ATABlk is
            SELECT replace(COLUMN_VALUE,CHR(03)) FROM TABLE(
            SELECT f_split(replace(replace(replace(ATA_BLKCONT,'TXT LRU ','TXT LRU-'),CHR(13),''),':'||CHR(10)||'TXT ',':'),'LRU ') FROM CFG_B777_ATABLK WHERE RAW_ID = p_RAW_ID AND ATA_BLKIDENT = p_ATA_BLKIDENT
            ) WHERE COLUMN_VALUE IS NOT NULL;
ii_LRUBlk varchar(4000); 
 begin
   open iCur_ATABlk;
      loop
        fetch iCur_ATABlk into ii_LRUBlk;
           exit when iCur_ATABlk%notfound;
      end loop;
   close iCur_ATABlk;
 end;
end P_CFG_B777_LRUParser;
/

Suggestion

Extract the entire SELECT statement declared by the cursor, extract the SELECT statement in the TABLE function, and save the query results into the table variables. Execute open cursor for select. Replace subqueries in SELECT with the table variables.

Syntax compatible with GaussDB:

CREATE OR REPLACE PROCEDURE P_CFG_B777_LRUPARSER (p_RAW_ID number ,
                                          p_ATA_BLKIDENT varchar2) is
begin
 declare 
 iCur_ATABlk sys_refcursor;
 V_f_split  T_SPLIT := T_SPLIT();
  ii_LRUBlk VARCHAR(4000); 
 begin
   SELECT f_split(replace(replace(replace(ATA_BLKCONT,'TXT LRU ','TXT LRU-'),CHR(13),''),':'||CHR(10)||'TXT ',':'),'LRU ') into V_f_split  FROM CFG_B777_ATABLK WHERE RAW_ID = p_RAW_ID AND ATA_BLKIDENT = p_ATA_BLKIDENT;
            
   open iCur_ATABlk for 
            SELECT replace(COLUMN_VALUE,CHR(03),'') FROM UNNEST_TABLE(V_f_split) AS COLUMN_VALUE WHERE COLUMN_VALUE IS NOT NULL;
      loop
        fetch iCur_ATABlk into ii_LRUBlk;
           exit when iCur_ATABlk%notfound;
      end loop;
   close iCur_ATABlk;
 end;
end ;
/