Converting a Set of rows into a Single XML Object

In this blog, I have given an example which will generate a single XML object from a set of employee records. The query is written using XMLELEMENT and XMLAGG functions below. 

1. SELECT xmlelement("employeeDetails"  
2.                     ,  xmlagg( xmlelement("employeeDetail"  
3.                                 , xmlelement("employeeID",employee_id)  
4.                                 , xmlelement("firstName",first_name)  
5.                                 , xmlelement("lastName",last_name)  
6.                                 , xmlelement("email",email)  
7.                                 , xmlelement("phoneNumber",phone_number)  
8.                                 , xmlelement("hireDate",hire_date)  
9.                                 , xmlelement("salary",salary)  
10.                                 , xmlelement("departmentID",e.department_id)  
11.                                 , xmlelement("departmentName",department_name)  
12.                         )  
13.                       )  
14.                   ) AS employee_xml FROM employees e, departments d  
15.                     WHERE e.department_id=d.department_id  
16.                   ;  
17.   
18. Output  
19. -------  
20.   
21. <?xml version="1.0" encoding="UTF-8"?>  
22. <employeeDetails>  
23.    <employeeDetail>  
24.       <employeeID>200</employeeID>  
25.       <firstName>Jennifer</firstName>  
26.       <lastName>Whalen</lastName>  
27.       <email>JWHALEN</email>  
28.       <phoneNumber>515.123.4444</phoneNumber>  
29.       <hireDate>2003-09-17</hireDate>  
30.       <salary>4400</salary>  
31.       <departmentID>10</departmentID>  
32.       <departmentName>Administration</departmentName>  
33.    </employeeDetail>  
34.    <employeeDetail>  
35.       <employeeID>201</employeeID>  
36.       <firstName>Michael</firstName>  
37.       <lastName>Hartstein</lastName>  
38.       <email>MHARTSTE</email>  
39.       <phoneNumber>515.123.5555</phoneNumber>  
40.       <hireDate>2004-02-17</hireDate>  
41.       <salary>13000</salary>  
42.       <departmentID>20</departmentID>  
43.       <departmentName>Marketing</departmentName>  
44.    </employeeDetail>  
45.    <employeeDetail>  
46.       <employeeID>202</employeeID>  
47.       <firstName>Pat</firstName>  
48.       <lastName>Fay</lastName>  
49.       <email>PFAY</email>  
50.       <phoneNumber>603.123.6666</phoneNumber>  
51.       <hireDate>2005-08-17</hireDate>  
52.       <salary>6000</salary>  
53.       <departmentID>20</departmentID>  
54.       <departmentName>Marketing</departmentName>  
55.    </employeeDetail>  
56. </employeeDetails>  

The same code can be replaced with XMLELEMNT, XMLFOREST AND XMLAGG and the code is much reduced. 

1. SELECT xmlelement("employeeDetails"  
2.                     ,  xmlagg( xmlelement("employeeDetail"  
3.                                 , xmlforest(employee_id AS "employeeID"  
4.                                 , first_name AS "firstName"  
5.                                 , last_name AS "lastName"  
6.                                 , email AS"email"  
7.                                 , phone_number AS "phoneNumber"  
8.                                 , hire_date AS "hireDate"  
9.                                 , salary AS "salary"  
10.                                 , e.department_id AS "departmentID"  
11.                                 , department_name AS "departmentName"  
12.                         ))  
13.                       )  
14.                   ) AS employee_xml FROM employees e, departments d  
15.                     WHERE e.department_id=d.department_id  
16.                   ;   
17.   
18. Output  
19. -------  
20.   
21. <?xml version="1.0" encoding="UTF-8"?>  
22. <employeeDetails>  
23.    <employeeDetail>  
24.       <employeeID>200</employeeID>  
25.       <firstName>Jennifer</firstName>  
26.       <lastName>Whalen</lastName>  
27.       <email>JWHALEN</email>  
28.       <phoneNumber>515.123.4444</phoneNumber>  
29.       <hireDate>2003-09-17</hireDate>  
30.       <salary>4400</salary>  
31.       <departmentID>10</departmentID>  
32.       <departmentName>Administration</departmentName>  
33.    </employeeDetail>  
34.    <employeeDetail>  
35.       <employeeID>201</employeeID>  
36.       <firstName>Michael</firstName>  
37.       <lastName>Hartstein</lastName>  
38.       <email>MHARTSTE</email>  
39.       <phoneNumber>515.123.5555</phoneNumber>  
40.       <hireDate>2004-02-17</hireDate>  
41.       <salary>13000</salary>  
42.       <departmentID>20</departmentID>  
43.       <departmentName>Marketing</departmentName>  
44.    </employeeDetail>  
45.    <employeeDetail>  
46.       <employeeID>202</employeeID>  
47.       <firstName>Pat</firstName>  
48.       <lastName>Fay</lastName>  
49.       <email>PFAY</email>  
50.       <phoneNumber>603.123.6666</phoneNumber>  
51.       <hireDate>2005-08-17</hireDate>  
52.       <salary>6000</salary>  
53.       <departmentID>20</departmentID>  
54.       <departmentName>Marketing</departmentName>  
55.    </employeeDetail>  
56. </employeeDetails>
57. 
    

Converting N Rows into 1 Rown N Columns

The below example gives you an idea on how to Convert N rows of data into 1 row N Column. 
The busniess requirement says, "Pull out the list of application users and the corresponding groups they belongs".

Assume each users belongs to 4 to 5 different groups.  The output contains only one record for each row. The output format is something linke this. 

1. username| user_group1| user_group2| user_group3 |user_group4 |user_group5  
2. --------|------------|------------|-------------|------------|------------  
3.         |            |            |             |            |   

user details are stored in adm_users and the group/role details are stored in adm_user_groups. These two tables need to be joined. The query is given below. 

1. SELECT  usr.usr_username ,  
2. MAX(CASE WHEN RN=1 THEN gr_role_name ELSE NULL END)    group1,  
3. MAX(CASE WHEN RN=2 THEN gr_role_name ELSE NULL END)    group2,  
4. MAX(CASE WHEN RN=3 THEN gr_role_name ELSE NULL END)    group3,  
5. MAX(CASE WHEN RN=4 THEN gr_role_name ELSE NULL END)    group4,  
6. MAX(CASE WHEN RN=5 THEN gr_role_name ELSE NULL END)    group5,  
7. FROM (  
8. SELECT  usr.usr_username,grp.gr_role_name,  
9. ROW_NUMBER() OVER (PARTITION BY gr_role_name ORDER BY gr_role_name) AS rn FROM adm_users usr, adm_user_groups grp   
10. WHERE  user.usr_username=grp.grp_usr_username  
11. )  
12. WHERE RN<=5  
13. GROUP BY usr_username;  
14.   
15. Output  
16. --------  
17.   
18. username| user_group1| user_group2| user_group3 |user_group4 |user_group5  
19. --------|------------|------------|-------------|------------|------------  
20. scott   |admin       |developer   |billing      |approver    |  
21. tom     |admin       |developer   |billing      |accounting  | Others

Converting Multiple Rows into a Single Column using XMLAGG

The following SQL code will convert the multiple rows to a single column using XMLAGG function. The output is same as LISTAGG function. 

1. SELECT rtrim(XMLAGG(XMLELEMENT(Department,DEPARTMENT_NAME||',' ).EXTRACT('//text()')),',') DEPT_NAME FROM DEPARTMENTS;  
2.   
3. output  
4. -------  
5.   
6. DEPARTMENT_NAME  
7. -----------------------   
8. Administration,Marketing,Purchasing,Human Resources,Shipping,IT,Public Relations,Sales,Executive,Finance,Accounting,Treasury,Corporate Tax,Control And Credit,Shareholder Services,Benefits,Manufacturing,Construction,Contracting,Operations,IT Support,NOC,IT Helpdesk,Government Sales,Retail Sales,Recruiting,Payroll 

How to Reset the Oracle Apex Username Password

What if we forget the apex admin password? Is there any mechanism to reset the password? The answer is "Yes". This can be achieved easily by following the steps mentioned below. 

1. Identify the User Id/Username from the the table "wwv_flow_fnd_user"; This is usually available in the Schema "APEX_050100" (if you are using Apex 5.1).  

view plaincopy to clipboardprint?

1. select * from  APEX_050100.wwv_flow_fnd_user;  

2. Execute the following script and pass the argument such as userId, username, email and the new password.

view plaincopy to clipboardprint?

1. declare  
2.       c_user_id  constant number         := to_number( '19380353220414198' );  
3.       c_username constant varchar2(4000) := upper( 'ADMIN' );  
4.       c_email    constant varchar2(4000) := 'admin@yourdomain.com';  
5.       c_password constant varchar2(4000) := 'Abc#123';  
6.   
7.       c_old_sgid constant number := wwv_flow_security.g_security_group_id;  
8.       c_old_user constant varchar2(255) := wwv_flow_security.g_user;  
9.   
10.       procedure cleanup  
11.       is  
12.       begin  
13.            wwv_flow_security.g_security_group_id := c_old_sgid;  
14.            wwv_flow_security.g_user              := c_old_user;  
15.       end cleanup;  
16.   begin  
17.        wwv_flow_security.g_security_group_id := 10;  
18.        wwv_flow_security.g_user              := c_username;  
19.   
20.        wwv_flow_fnd_user_int.create_or_update_user( p_user_id  => c_user_id,  
21.                                                    p_username => c_username,  
22.                                                    p_email    => c_email,  
23.                                                    p_password => c_password );  
24.   
25.       commit;  
26.       cleanup();  
27.   exception  
28.       when others then  
29.           cleanup();  
30.           raise;  
31.   end;  
32.   /  

Once the PL/SQL block is executed successfully, Password will be updated in the table APEX_050100.wwv_flow_fnd_user 

WITH Clause Tips in Postgresql

Postgres provides "WITH" clause which helps to write auxiliary statements for use in larger query.It helps in breaking down complicated and large queries into simpler forms, which are easily readable. These statements, which are often referred to as Common Table Expressions. 
WITH clause has two forms; Simple & Recursive. 

Simple WITH Clause

Example is mentioned below. 

1. With data_set AS  
2. (Select  
3. ID  
4. , NAME  
5. , AGE  
6. , ADDRESS  
7. , SALARY  
8. FROM COMPANY )  
9.   
10. Select * from data_set; 

Recursive WITH Clause

Recursive WITH or Hierarchical queries, is a form of Common Table Expressions(CTE) where a CTE can reference to itself, i.e., a WITH query can refer to its own output, hence the name recursive. 

Example to generate 1000 Rows.

view plaincopy to clipboardprint?

1. WITH RECURSIVE ROWS100 AS (  
2. SELECT 1  AS ID  
3. UNION ALL  
4. SELECT ID+1 FROM ROWS100  
5. WHERE ID<100  
6. ) SELECT * FROM ROWS100  

To populate the date dimension.

1. WITH RECURSIVE time_dim(id) AS (  
2.                 select 1 AS ID, 0.000694444444444444444444444444444444444444 as interval_period, date_trunc('Day',current_timestamp) + interval '0.000694444444444444444444444444444444444444 days'as date  
3.                 UNION ALL   
4.                 SELECT id+1 , (id+1)*interval_period, date_trunc('Day',current_timestamp) + interval '0.000694444444444444444444444444444444444444 days'*(id+1) as date  
5. from time_dim  
6.                 WHERE id < 1440  
7. )  
8. select id, date, lpad(extract('hour' from date)::varchar(2),2,'0')  as hour , lpad(extract('minute' from date)::varchar(2),2,'0') as minute,to_char(date,'HH') TWLVE,  
9. TO_CHAR(date,'AM') am_pm  
10. from time_dim  

Another example specified below will give the sum of the salaries < 20000

view plaincopy to clipboardprint?

1. WITH RECURSIVE t(n) AS (  
2.     VALUES (0)  
3.     UNION ALL  
4.     SELECT SALARY FROM COMPANY WHERE SALARY < 20000  
5. )  
6. SELECT sum(n) FROM t;  

Example for will delete the data from a set the deleted records are inserted into another table using WITH Clause

view plaincopy to clipboardprint?

1. CREATE TABLE COMPANY1(  
2.    ID INT PRIMARY KEY     NOT NULL,  
3.    NAME           TEXT    NOT NULL,  
4.    AGE            INT     NOT NULL,  
5.    ADDRESS        CHAR(50),  
6.    SALARY         REAL  
7. );  
8.   
9. WITH moved_rows AS (  
10.     DELETE FROM COMPANY  
11.     WHERE  
12.         SALARY >= 30000  
13.     RETURNING *  
14. )  
15. INSERT INTO COMPANY1 (SELECT * FROM moved_rows);