Excel aggregating function

Question

I've got an excel file with columns,

C_IP
SESSION_ID
CS_USER_AGENT
CS_URI_STEM
CS_URI_QUERY
WEB_LINK

I'm not able to aggregate the above attributes due to the limitations of the string size allowed in Oracle (11g). I tried to use a user-defined aggregating function. I want to aggregate the "WEB_LINK" column, and group by C_IP. Is it possible to do this in Excel?

The SQL query I tried to use was,

CREATE TABLE WEBLOG_AGG AS
SELECT C_IP,
tab_to_string(CAST(COLLECT(WEB_LINK) AS T_VARCHAR2_TAB)) AS WEBLINKS
FROM WEBLOG_SESSION
GROUP BY C_IP;

Answer 1

I guess it would be easier to perform concatenation into clob than to write VBA code.

15:34:36 SYSTEM@dwal> create table t1 ( group_col number, value varchar2(1 byte) );

Table created.

Elapsed: 00:00:00.10
15:34:38 SYSTEM@dwal> insert into t1
15:35:34   2  select 1, decode(mod(rownum,5), 0,0,1) from dual connect by rownum <= 4001
15:36:20   3  union all
15:36:22   4  select 2, decode(mod(rownum,5), 0,0,1) from dual connect by rownum <= 4001
15:36:27   5  ;

8002 rows created.

Elapsed: 00:00:00.05
15:36:28 SYSTEM@dwal> commit;

Commit complete.

Elapsed: 00:00:00.02
15:36:31 SYSTEM@dwal> create type t_varchar2_tab is table of varchar2(1);
15:37:11   2  /

Type created.

Elapsed: 00:00:00.50
15:38:15 SYSTEM@dwal> ed
Wrote file S:\tools\buffer.sql

  1  create function tab_to_str(tab in t_varchar2_tab) return clob
  2  as
  3    result clob;
  4  begin
  5    for i in tab.first .. tab.last loop
  6      result := result || tab(i);
  7    end loop;
  8    return result;
  9* end;
15:38:46 SYSTEM@dwal> /

Function created.

Elapsed: 00:00:00.19
15:46:01 SYSTEM@dwal> select group_col
15:46:04   2  ,length(tab_to_str(cast(collect(value) as t_varchar2_tab))) len
15:46:10   3  ,substr(tab_to_str(cast(collect(value) as t_varchar2_tab)), 1, 20) val
15:46:12   4  from t1 group by group_col;

 GROUP_COL    LEN VAL
---------- ------ --------------------
         1   4001 11011110111101111011
         2   4001 11011110111101111011

Elapsed: 00:00:01.13