• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 27475
  • Last Modified:

ORACLE PL/SQL BULK INSERT

Hi experts,
I have PL/SQL procedure, which fetchs row and concatenate  rows into a column with a delimiter  and insert on to table. This process is done row by row.
For example,
source table
COLUMN1           COLUMN2
1                           A
1                           B
1                           C
2                            D
2                             E
3                            F
3                            A
output  table
 COLUMN1           COLUMN2
1                           A,B,C
2                            D,E
3                            F,A

Could any one provide/suggest how to   process and insert the records in bulk.
Thanks
sree
0
sreej
Asked:
sreej
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
there is a "easy" way to do it, shown/explained here:
http://halisway.blogspot.com/2006/08/oracle-groupconcat-updated-again.html

if you need help implementing it, please ask
0
 
sdstuberCommented:
CREATE OR REPLACE TYPE "STRING_AGG_TYPE" AS OBJECT(
    total   VARCHAR2(4000),
    STATIC FUNCTION odciaggregateinitialize(sctx IN OUT string_agg_type)
        RETURN NUMBER,
    MEMBER FUNCTION odciaggregateiterate(
        SELF    IN OUT   string_agg_type,
        VALUE   IN       VARCHAR2
    )
        RETURN NUMBER,
    MEMBER FUNCTION odciaggregateterminate(
        SELF          IN       string_agg_type,
        returnvalue   OUT      VARCHAR2,
        flags         IN       NUMBER
    )
        RETURN NUMBER,
    MEMBER FUNCTION odciaggregatemerge(
        SELF   IN OUT   string_agg_type,
        ctx2   IN       string_agg_type
    )
        RETURN NUMBER
);
/


CREATE OR REPLACE TYPE BODY STRING_AGG_TYPE
IS
    STATIC FUNCTION odciaggregateinitialize(sctx IN OUT string_agg_type)
        RETURN NUMBER
    IS
    BEGIN
        sctx := string_agg_type(NULL);
        RETURN odciconst.success;
    END;
    MEMBER FUNCTION odciaggregateiterate(
        SELF    IN OUT   string_agg_type,
        VALUE   IN       VARCHAR2
    )
        RETURN NUMBER
    IS
    BEGIN
        SELF.total := SELF.total || ',' || VALUE;
        RETURN odciconst.success;
    END;
    MEMBER FUNCTION odciaggregateterminate(
        SELF          IN       string_agg_type,
        returnvalue   OUT      VARCHAR2,
        flags         IN       NUMBER
    )
        RETURN NUMBER
    IS
    BEGIN
        returnvalue := LTRIM(SELF.total, ',');
        RETURN odciconst.success;
    END;
    MEMBER FUNCTION odciaggregatemerge(
        SELF   IN OUT   string_agg_type,
        ctx2   IN       string_agg_type
    )
        RETURN NUMBER
    IS
    BEGIN
        SELF.total := SELF.total || ctx2.total;
        RETURN odciconst.success;
    END;
END;
/

CREATE OR REPLACE FUNCTION stragg(input VARCHAR2)
    RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE
    USING string_agg_type;

stragg is also from Tom Kyte.  His new method of using hierarchical queries is interesting but more expensive than using his original method of stragg.

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
just browse thru the below links to understand the different methods availabe to get the result output you wanted.

http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php 

Thanks
0
 
sreejAuthor Commented:
Thanks Experts. All answers are excellent. However i have assigned maxmimun points for the first expert comment.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now