For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
select max(decode(id,1,field_value)) cropx, max(decode(id,2,field_value)) cropy, max(decode(id,3,field_value)) height, max(decode(id,4,field_value)) width from RENDER_VALUES
VARIABLE dj REFCURSOR CREATE OR REPLACE FUNCTION dept_by_job RETURN SYS_REFCURSOR IS -- dept_by_job returns a SYS_REFCURSOR that has -- one row for every department in scott.emp and one -- column for every job, showing the number of employees -- in that department with that job. return_csr SYS_REFCURSOR; select_txt VARCHAR2 (5000); BEGIN -- 1st part of query is fixed select_txt := 'SELECT deptno '; -- 2nd part of query depends on what's in the table FOR r IN ( SELECT DISTINCT job FROM scott.emp ORDER BY job ) LOOP select_txt := select_txt || ', COUNT (CASE WHEN job = ''' || r.job || ''' THEN 1 END) AS ' || r.job || '_cnt '; END LOOP; -- Last part of query is fixed select_txt := select_txt || ' FROM scott.emp' || ' GROUP BY deptno' || ' ORDER BY deptno'; OPEN return_csr FOR select_txt; RETURN return_csr; END dept_by_job ; / SHOW ERRORS EXEC :dj := dept_by_job; PRINT :dj
CREATE OR REPLACE FUNCTION pivot_rv RETURN SYS_REFCURSOR IS return_csr SYS_REFCURSOR; select_txt VARCHAR2 (5000); BEGIN -- 1st part of query is fixed select_txt := 'SELECT '; -- 2nd part of query depends on what's in the table FOR r IN ( SELECT DISTINCT field_name , ROW_NUMBER () OVER (ORDER BY id) AS rnum FROM render_values ORDER BY rnum ) LOOP select_txt := select_txt || CASE WHEN r.rnum > 1 THEN ',' END || 'MAX (CASE WHEN field_name = ''' || r.field_name || ''' THEN field_value END) AS "' || r.field_name || '"'; END LOOP; -- Last part of query is fixed select_txt := select_txt || ' FROM render_values'; OPEN return_csr FOR select_txt; RETURN return_csr; END pivot_rv ;
col CropX for a10 col CropY for a10 col Height for a10 col Width for a10 select * from RENDER_VALUES pivot(max(field_value) for (ID,FIELD_NAME) in(('1','CropX') as CropX, ('2','CropY') as CropY, ('3','Height') as Height, ('4','Width') as Width)); CROPX CROPY HEIGHT WIDTH -------- -------- ------ ----- 10.31234 20.31234 100 200
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; / SHOW ERRORS CREATE OR REPLACE FUNCTION stragg ( input VARCHAR2 ) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING string_agg_type; / SHOW ERRORS
CREATE PUBLIC SYNONYM stragg FOR my_schema.stragg; GRANT EXECUTE ON string_agg_type TO PUBLIC; GRANT EXECUTE ON stragg TO PUBLIC;
MEMBER FUNCTION ODCIAggregateIterate ( self IN OUT string_agg_type, value IN VARCHAR2 ) RETURN NUMBER IS BEGIN -- self.total := self.total || ',' || value; *self.total := SUBSTR(self.total || value, 1, 4000);* RETURN ODCIConst.Success; END;
CREATE OR REPLACE PACKAGE PKG_TEST AS FUNCTION STRAGG(INPUT VARCHAR2) RETURN VARCHAR2; END PKG_TEST; / CREATE OR REPLACE PACKAGE BODY PKG_TEST AS FUNCTION STRAGG (INPUT VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING string_agg_type; END PKG_TEST; / SHOW ERRORS Package created. Warning: compiled but with compilation errors