CREATE TABLE SUBPRJ
(
  PROJECT_ID VARCHAR2 (12) NOT NULL,
  PROJECT_TITLE VARCHAR2 (255),
  STRUCTURE VARCHAR2 (10);
  PROJECT_PROFILE CHAR (1),
)
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE VALEA_DATA
STORAGE
(
  INITIAL 10 M
  NEXT 10 M
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  PCTINCREASE 0
  BUFFER_POOL DEFAULT
)
NOCACHE
NOPARALLEL
NOLOGGING
/

ALTER TABLE SUBPRJ ADD
(
  CONSTRAINT SUBPRJ_PK
  PRIMARY KEY
  (
PROJECT_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  TABLESPACE VALEA_INDEX
  STORAGE
  (
    INITIAL 10 M
    NEXT 10 M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    PCTINCREASE 0
  )
  ENABLE
)
/

INSERT INTO SUBPRJ
(
  PROJECT_ID,
  PROJECT_TITLE,
  STRUCTURE,
  PROJECT_PROFILE
)
VALUES
('1',
 'project one',
 'single',
 'P'
);
/
INSERT INTO SUBPRJ
(
  PROJECT_ID,
  PROJECT_TITLE,
  STRUCTURE,
  PROJECT_PROFILE
)
VALUES
('2',
 'project two',
 'single',
 'C'
);
/
INSERT INTO SUBPRJ
(
  PROJECT_ID,
  PROJECT_TITLE,
  STRUCTURE,
  PROJECT_PROFILE
)
VALUES
('3',
 'project three',
 'multi',
 'D'
);

CREATE TABLE SPRJCATEGORY
(
  SPRJCAT_ID VARCHAR2 (12) NOT NULL,
  PROJECT_ID VARCHAR2 (12) NOT NULL,
  CATEGORY VARCHAR2 (255),
)
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE VALEA_DATA
STORAGE
(
  INITIAL 10 M
  NEXT 10 M
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  PCTINCREASE 0
  BUFFER_POOL DEFAULT
)
NOCACHE
NOPARALLEL
NOLOGGING
/

ALTER TABLE SUBPRJ ADD
(
  CONSTRAINT SUBPRJ_PK
  PRIMARY KEY
  (
    SPRJCAT_ID
  )
  NOT DEFERRABLE INITIALLY IMMEDIATE
  USING INDEX 
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  TABLESPACE VALEA_INDEX
  STORAGE
  (
    INITIAL 10 M
    NEXT 10 M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    PCTINCREASE 0
  )
  ENABLE
)
/

ALTER TABLE SPRJCATEGORY ADD
(
  CONSTRAINT SPRJCATEGORY_SUBPRJ_FK
  FOREIGN KEY
  (
    PROJECT_ID
  )
  REFERENCES SUBPRJ
  (
    PROJECT_ID
  )
  ON DELETE CASCADE
  NOT DEFERRABLE INITIALLY IMMEDIATE
  ENABLE
)
/

INSERT INTO SPRJCATEGORY
(
  SPRJCAT_ID, 
  PROJECT_ID,
  CATEGORY
)
VALUES
('1',
 '1',
 'Application Development',
);
/

INSERT INTO SPRJCATEGORY
(
  SPRJCAT_ID, 
  PROJECT_ID,
  CATEGORY
)
VALUES
('2',
 '1',
 'Exploratory Research',
);
/

INSERT INTO SPRJCATEGORY
(
  SPRJCAT_ID, 
  PROJECT_ID,
  CATEGORY
)
VALUES
('3',
 '2',
 'Custom Advisory',
);
/

INSERT INTO SPRJCATEGORY
(
  SPRJCAT_ID, 
  PROJECT_ID,
  CATEGORY
)
VALUES
('4',
 '3',
 'Exploratory Research',
);
/


Oracle-Version = 9.2.0.4.0

1. SQL Statement (query)

select subprj.project_id,
       subprj.project_title
       sprjcategory.category
       from subprj, sprjcategory
            where subprj.project_id = '1' and subprj.project_id = sprjcategory.project_id;


1. SQL result =

project_id project_title category
---------- ------------- --------
1          project one   Application Development
1          project one   Exploratory Research



2. SQL statement (Query with concat_all)

select subprj.project_id,
       subprj.project_title,
       concat_all (CONCAT_EXPR(sprjcategory.category, ','))
       from
       (select subprj.project_id,
               subprj.project_title,
               sprjcategory.category
               from subprj, sprjcategory
               where subprj.spid = '1' and subprj.project_id = sprjcategory.project_id);

2. SQL result =
ORA-06575: Package or function CONCAT_ALL is in an invalid state


On creating the concat_all_ot type and body:
PLS-00306: wrong number or types of arguments in call to 'concat_all_ot'