Oracle 9i to_clob issues

Greetings,
I have a procedure that I needed to build for an Oracle 9i DB (sorry had no choice)
and this was the best I could come up with for creating a dynamic "update part":


PROCEDURE create_update_part
   IS
   v_update CLOB;
   BEGIN
     <<update_loop>>
      FOR i IN (    SELECT table_name
                          ,to_clob(ltrim( max( sys_connect_by_path( to_clob('tgt.' || column_name || '=src.' || column_name), ' and ')), ' and ')) update_part
                      FROM (SELECT b.table_name
                                  ,a.column_name
                                  ,row_number() OVER( PARTITION BY b.table_name ORDER BY a.column_name) rn
                              FROM dba_tab_cols a
                                   JOIN dba_ind_columns b
                                      ON a.table_name = b.table_name
                                     AND a.owner IN ('HHO'
                                                    ,'SKR'
                                                    ,'TWE'
                                                    ,'ROOT')
                                     AND b.index_owner IN ('HHO'
                                                          ,'SKR'
                                                          ,'TWE'
                                                          ,'ROOT'))
                  GROUP BY table_name
                CONNECT BY rn = PRIOR rn + 1
                       AND table_name = PRIOR table_name
                START WITH rn = 1)
      LOOP
         BEGIN
            v_update := i.update_part;
            UPDATE gcu.frprs_table_merge
               SET update_part  = v_update
             WHERE table_name = i.table_name;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line( i.table_name || chr( 10) || 'UPDATE_PART : ' || i.update_part);
               DBMS_OUTPUT.put_line( DBMS_UTILITY.format_error_stack);
               DBMS_OUTPUT.put_line( DBMS_UTILITY.format_call_stack);
               ROLLBACK;
         END;
      END LOOP update_loop;

      COMMIT;
   END;

Open in new window



I am consistently getting the ORA-01489 error no matter which way I twist the to_clob() part (to include placing the character concats in individual to_clob() parts). Incidnetally, the column being updated is a clob column...

Can anyone help?
g_currierAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sventhanCommented:
Please check this out ...

Error:  ORA 1489
Text:   result of string concatenation is too long
-------------------------------------------------------------------------------
Cause:  The result of a string concatenation was larger than the maximum
        length of a string (4000 characters).
Action: Reduce the size of one or both of the strings to be concatenated.
        Make certain the total length of the concatenation result is less than
        4000 characters.
0
sventhanCommented:
Here is the work around

Some queries that attempted to concatenate columns in a recursive WITH clause
mey return an ORA-1489 error even though the result of the concatenation
do not exceed the maximum length allowed.
 
Sample Workaround

Cast one of the inputs to the concatenation as a varchar2(4000).  For example,
the testcase query could be modified to use cast in the following way:
  WITH colonnes (table_name, col_list, lvl) AS
  (SELECT table_name, cast(column_name as varchar2(4000)), 1 lvl
   FROM user_tab_columns
   WHERE column_id = 1
   UNION ALL
   SELECT c.table_name, c.col_list || ',' || u.column_name, 1 + lvl
   FROM colonnes c,
        user_tab_columns u
   WHERE u.TABLE_NAME = c.table_name AND
         u.column_id = lvl + 1)
  SELECT table_name, col_list
  FROM (select c.*, count(*) over (partition by table_name) cnt
        from colonnes c)
  WHERE cnt = lvl;
0
sdstuberCommented:
recursive WITH clauses weren't supported until 11gR2

Try creating your own aggregate.  It'll be much more efficient than the sys_connect_by trick
and easier to read, and, more importantly, it'll allow you to work with clob values where the connect by fails at the varchar2 limit

Modifying concat_agg and corresponding type in this article should do it

http://www.experts-exchange.com/Database/Oracle/A_9391-How-to-Create-User-Defined-Aggregates-in-Oracle.html

that might looks something like this...


CREATE OR REPLACE TYPE sds.concat_clob_agg_type
    AS OBJECT
(
    v_temp VARCHAR2(32767),
    v_result CLOB,
    STATIC FUNCTION odciaggregateinitialize(ctx IN OUT concat_clob_agg_type)
        RETURN NUMBER,
    MEMBER FUNCTION odciaggregateiterate(self IN OUT concat_clob_agg_type, p_string IN VARCHAR2)
        RETURN NUMBER,
    MEMBER FUNCTION odciaggregatemerge(
        self   IN OUT concat_clob_agg_type,
        ctx2   IN     concat_clob_agg_type
    )
        RETURN NUMBER,
    MEMBER FUNCTION odciaggregateterminate(
        self          IN     concat_clob_agg_type,
        returnvalue      OUT CLOB,
        flags         IN     NUMBER
    )
        RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY sds.concat_clob_agg_type
IS
    STATIC FUNCTION odciaggregateinitialize(ctx IN OUT concat_clob_agg_type)
        RETURN NUMBER
    IS
    BEGIN
        ctx := concat_clob_agg_type('', ''); -- initialize the concatenation to NULL   
        RETURN odciconst.success;
    END odciaggregateinitialize;

    MEMBER FUNCTION odciaggregateiterate(self IN OUT concat_clob_agg_type, p_string IN VARCHAR2)
        RETURN NUMBER
    IS
    BEGIN
        -- Appending to clobs is slower than appending to varchar2
        -- so use varchar2 until you can't anymore then append one big chunk
        self.v_temp := self.v_temp || ',' || p_string; -- Append a delimiter and new value

        IF LENGTH(self.v_temp) > 28700
        THEN
            self.v_result := self.v_result || self.v_temp;
            self.v_temp := NULL;
        END IF;

        RETURN odciconst.success;
    END odciaggregateiterate;

    MEMBER FUNCTION odciaggregatemerge(
        self   IN OUT concat_clob_agg_type,
        ctx2   IN     concat_clob_agg_type
    )
        RETURN NUMBER
    IS
    BEGIN
        -- If merging, simply concatenate them together
        -- Since each string will either be NULL or delimiter prefixed, no need to re-delimit
        self.v_result := self.v_result || self.v_temp || ctx2.v_result || ctx2.v_temp;
        self.v_temp := NULL;
        RETURN odciconst.success;
    END odciaggregatemerge;

    MEMBER FUNCTION odciaggregateterminate(
        self          IN     concat_clob_agg_type,
        returnvalue      OUT CLOB,
        flags         IN     NUMBER
    )
        RETURN NUMBER
    IS
    BEGIN
        -- Since we prefix the string initially with a comma, remove the extra here before returning
        returnvalue := LTRIM(self.v_result || self.v_temp, ',');
        RETURN odciconst.success;
    END odciaggregateterminate;
END;
/

CREATE OR REPLACE FUNCTION sds.concatclobagg(p_string VARCHAR2)
    RETURN CLOB
    PARALLEL_ENABLE
    AGGREGATE USING concat_clob_agg_type;
/

SELECT concatclobagg(object_name) FROM dba_objects

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
g_currierAuthor Commented:
Thanks for the examples and the references...
I resolved the problem by using this:
CREATE OR REPLACE TYPE STRAGG_TYPE AS OBJECT
(
  STRING VARCHAR2(4000),

  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT STRAGG_TYPE)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF  IN OUT STRAGG_TYPE,
                                       VALUE IN VARCHAR2) RETURN NUMBER,

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN STRAGG_TYPE,
                                         RETURNVALUE OUT VARCHAR2,
                                         FLAGS       IN NUMBER)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT STRAGG_TYPE,
                                     CTX2 IN STRAGG_TYPE) RETURN NUMBER
)
;
/

CREATE OR REPLACE TYPE BODY STRAGG_TYPE IS

STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT STRAGG_TYPE) RETURN NUMBER IS BEGIN

SCTX := STRAGG_TYPE(NULL);

RETURN ODCICONST.SUCCESS;

END;

MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT STRAGG_TYPE, VALUE IN VARCHAR2) RETURN NUMBER IS BEGIN

SELF.STRING := SELF.STRING || ',' || VALUE;

RETURN ODCICONST.SUCCESS;

END;

MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN STRAGG_TYPE, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS BEGIN

RETURNVALUE := LTRIM(SELF.STRING, ',');

RETURN ODCICONST.SUCCESS;

END;

MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT STRAGG_TYPE, CTX2 IN STRAGG_TYPE) RETURN NUMBER IS BEGIN

SELF.STRING := SELF.STRING || CTX2.STRING;

RETURN ODCICONST.SUCCESS;

END;

END;
/

CREATE OR REPLACE FUNCTION STRAGG(INPUT VARCHAR2) RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE AGGREGATE USING STRAGG_TYPE;
/

Open in new window


and then I was able to change my query to this:

SELECT TABLE_NAME
      ,STRAGG(DISTINCT 'tgt.' || COLUMN_NAME || '=src.' || COLUMN_NAME)
  FROM (SELECT B.TABLE_NAME
              ,A.COLUMN_NAME
          FROM DBA_TAB_COLS A
          JOIN DBA_IND_COLUMNS B
            ON A.TABLE_NAME = B.TABLE_NAME
           AND A.OWNER IN ('HHO', 'SKR', 'TWE', 'ROOT')
           AND B.INDEX_OWNER IN ('HHO', 'SKR', 'TWE', 'ROOT'))
 WHERE COLUMN_NAME IS NOT NULL
 GROUP BY TABLE_NAME;

Open in new window


which gives me very decent output (and what I needed):

   	TABLE_NAME	UPDATE_PART
1	ACC_LISTBOX$	tgt.APPNAME=src.APPNAME,tgt.COLCOUNT=src.COLCOUNT,tgt.COLWIDTH=src.COLWIDTH,tgt.DOACTION=src.DOACTION,tgt.FORMNAME=src.FORMNAME,tgt.ID0=src.ID0,tgt.ID1=src.ID1,tgt.ID2=src.ID2,tgt.ID3=src.ID3,tgt.ID4=src.ID4,tgt.ID5=src.ID5,tgt.ID6=src.ID6,tgt.ID7=src.ID7,tgt.ID8=src.ID8,tgt.ID9=src.ID9,tgt.SF1=src.SF1,tgt.SF2=src.SF2,tgt.SF3=src.SF3,tgt.SKEY=src.SKEY,tgt.SQLFIELDS=src.SQLFIELDS,tgt.SQLFROM=src.SQLFROM,tgt.SQLOR=src.SQLOR,tgt.SQLORDER=src.SQLORDER,tgt.SQLWHERE=src.SQLWHERE
2	ACM_CUSTOMER_ADDRESS$	tgt.AM_CODE=src.AM_CODE,tgt.BUILDING=src.BUILDING,tgt.CITY=src.CITY,tgt.COUNTRY=src.COUNTRY,tgt.COUNTY=src.COUNTY,tgt.CUST_NO=src.CUST_NO,tgt.FLOOR=src.FLOOR,tgt.NAME1=src.NAME1,tgt.NAME2=src.NAME2,tgt.PO=src.PO,tgt.REMARK=src.REMARK,tgt.ROOM=src.ROOM,tgt.STATE=src.STATE,tgt.STREET1=src.STREET1,tgt.STREET2=src.STREET2,tgt.STREET3=src.STREET3,tgt.STREET4=src.STREET4,tgt.UNIT=src.UNIT,tgt.ZIPCODE=src.ZIPCODE
3	ACM_CUSTOMER_CONTACT$	tgt.BIRTHDATE=src.BIRTHDATE,tgt.CUST_NO=src.CUST_NO,tgt.DEPARTMENT=src.DEPARTMENT,tgt.EMAIL=src.EMAIL,tgt.FAX=src.FAX,tgt.FIRST_NAME=src.FIRST_NAME,tgt.GENDER=src.GENDER,tgt.HOBBIES=src.HOBBIES,tgt.LFD=src.LFD,tgt.MESSENGER=src.MESSENGER,tgt.MOBILE=src.MOBILE,tgt.NAME1=src.NAME1,tgt.NAME2=src.NAME2,tgt.NAME3=src.NAME3,tgt.NAME=src.NAME,tgt.NICKNAME=src.NICKNAME,tgt.PHONE=src.PHONE,tgt.POSITION=src.POSITION,tgt.REMARK=src.REMARK,tgt.SEGMENT=src.SEGMENT,tgt.SKYPE=src.SKYPE,tgt.SUBCONTRACTORS=src.SUBCONTRACTORS,tgt.TITLE=src.TITLE,tgt.VOIP=src.VOIP,tgt.WEBSITE=src.WEBSITE

Open in new window


By way of explanation, this is a method (for me) of building a merge statement (insert,update,join parts).  The reson for this is to migrate data from 9i to 11gR2 without doing an upgrade (just moving the data that will be kept to another DB).  I'm sure that other methods would probably work, but I thought that one could never have enough practice using pl/sql...

I appreciate the help,

Thank you.
0
sdstuberCommented:
your version of stragg and my original concat_agg in the article don't help because they have the same 4000 character limit you're already hitting.

you'll need the clob version in order to reliably aggregate all of your values
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.