Oracle: Function to concatenate column output

 Type:

create or replace
TYPE        "STRINGAGGTYPE"                                          as object
    (
       theString varchar2(4000),
   
       static function
            ODCIAggregateInitialize(sctx IN OUT StringAggType )
            return number,
   
       member function
           ODCIAggregateIterate(self IN OUT StringAggType ,
                                value IN varchar2 )
           return number,
  
      member function
           ODCIAggregateTerminate(self IN StringAggType,
                                  returnValue OUT  varchar2,
                                  flags IN number)
           return number,
  
      member function
           ODCIAggregateMerge(self IN OUT StringAggType,
                              ctx2 IN StringAggType)
           return number
   );

Function:

create or replace
FUNCTION stringAgg(input varchar2 )
    RETURN varchar2
    PARALLEL_ENABLE AGGREGATE USING StringAggType;

Now, you can run a query like this:

select deptno, stringAgg(ename) enames from emp group by deptno;

Result:

DEPTNO  ENAMES
----------  ------------------------------
        10  CLARK,KING,MILLER
        20  SMITH,FORD,ADAMS,SCOTT,JONES
        30  ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

Oracle: functions to join and split strings in sql

Join:

create or replace
function join
(
    p_cursor sys_refcursor,
    p_del varchar2 := ','
) return varchar2
is
    l_value   varchar2(32767);
    l_result  varchar2(32767);
begin
    loop
        fetch p_cursor into l_value;
        exit when p_cursor%notfound;
        if l_result is not null then
            l_result := l_result || p_del;
        end if;
        l_result := l_result || l_value;
    end loop;
    return l_result;
end join;
 /

Now, you can run a query like this:

select join(cursor(select ename from emp))from dual;

Split:

create or replace TYPE "SPLIT_TBL" as table of varchar2(32767);
/

create or replace
FUNCTION split(
      p_list VARCHAR2,
      p_del  VARCHAR2 := ',' )
    RETURN split_tbl pipelined
  IS
    l_idx pls_integer;
    l_list VARCHAR2(32767):= p_list;
  BEGIN
    LOOP
      L_IDX   :=INSTR(L_LIST,P_DEL);
      IF l_idx > 0 THEN
        pipe row(SUBSTR(l_list,1,l_idx-1));
        l_list:= SUBSTR(l_list,l_idx  +LENGTH(p_del));
      ELSE
        pipe row(l_list);
        EXIT;
      END IF;
    END LOOP;
    RETURN;
  END split;
 /

Now, you can run a query like this:  

SQL> select * fromtable(split('one,two,three'));


one
two
three

Ref: create functions to join and split strings in sql

Problem with Oracle 11.2.0.1

Following works on oracle 10g not on oracle11g.

SELECT column1,
 column2
FROM
 (SELECT 1 column1,2 column2,3 column3 FROM dual
 )
GROUP BY column1;

I found the same issue on Oracle Technology Forum (OTN). It seems there is a bug in Oracle 11.2.0.1.

this would be fixed by suggested workaround in the oracle forum.

Following should be set by Database administrator.
    set "_optimizer_group_by_placement"=FALSE

Reference: http://forums.oracle.com/forums/thread.jspa?threadID=1128695
 

Creating Database link

CREATE DATABASE LINK "<linkdemo>" CONNECT TO <user> IDENTIFIED BY <password> '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = <host name> )(PORT = < Port Number>)) (CONNECT_DATA = (SID = <SID>)))'


linkdemo = Name of the link.
host name= where the database is installed
Port Number=TNS listener port of the database
SID=database name
user=Database user
password=Database password

QUERY TO VIEW THE PROCEDURE

USER_SOURCE describes the text source of the stored objects owned by the current user.

SELECT * FROM USER_SOURCE;

NAME: Name of the object
TYPE: Type of object: FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY
LINE: Line number of this line of source
TEXT: Text source of the stored object

More:-
ALL_SOURCE: describes the text source of the stored objects accessible to the current user.
DBA_SOURCE: describes the text source of all stored objects in the database.

View oracle table structure

SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='MY_TABLE';

In SQLPLUS, DESCRIBE command can be used.

DESCRIBE 'MY_TABLE';

Search for column names in Oracle

SELECT * FROM USER_TAB_COLUMNS WHERE COLUMN_NAME='COLUMN_NAME' ;
SELECT * FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME='COLUMN_NAME' ;

Try the following also:

• DBA_TAB_COLUMNS
  
• all_constraints (user_constraints, dba_constraints)
• all_indexes
• all_tables
• all_tab_columns