SQL & PL/SQL

Announcement

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!

Generate XML from tables

muttleychessNov 7 2018 — edited Nov 8 2018

I have tables like schem HR (clone)

I used only 3 in example the names table are changed

TBL_LOCATION,

TBL_DEPT

TBL_EMPLOYEES

CREATE TABLE tbl_location
    ( location_id    NUMBER(4)
    , street_address VARCHAR2(40)
    , postal_code    VARCHAR2(12)
    ) ;
 /   
CREATE UNIQUE INDEX tbl_loc_id_pk
ON tbl_location (location_id) ;
/
ALTER TABLE tbl_location
ADD ( CONSTRAINT tbl_loc_id_pk
            PRIMARY KEY (location_id)
  ) ;
/
CREATE TABLE tbl_dept
    ( department_id    NUMBER(4)
    , department_name  VARCHAR2(30),
     location_id      NUMBER(4)
    ) ;
/    
CREATE UNIQUE INDEX tbl_dept_id_pk
ON tbl_dept (department_id) ;    
/
ALTER TABLE tbl_dept
ADD ( CONSTRAINT tbl_dept_id_pk
                PRIMARY KEY (department_id)
    , CONSTRAINT tbl_dept_loc_fk
                FOREIGN KEY (location_id)
              REFERENCES tbl_location (location_id)
  ) ;
 / 
  
CREATE TABLE tbl_employees
    ( employee_id    NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25)
    , manager_id     NUMBER(6)
    , department_id  NUMBER(4)
   ) ;
/  
CREATE UNIQUE INDEX tbl_emp_emp_id_pk
ON tbl_employees (employee_id) ;
/
ALTER TABLE tbl_employees
ADD ( CONSTRAINT     tbl_emp_emp_id_pk
                     PRIMARY KEY (employee_id)
    , CONSTRAINT     tbl_emp_dept_fk
                     FOREIGN KEY (department_id)
                      REFERENCES tbl_dept
    , CONSTRAINT     tbl_emp_manager_fk
                     FOREIGN KEY (manager_id)
                      REFERENCES tbl_employees
                      DEFERRABLE INITIALLY DEFERRED
    ) ;
/

Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (1000, '1297 Via Cola di Rie', '00989');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (1100, '93091 Calle della Testa', '10934');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (1200, '2017 Shinjuku-ku', '1689');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (1300, '9450 Kamiya-cho', '6823');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (1400, '2014 Jabberwocky Rd', '26192');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (1500, '2011 Interiors Blvd', '99236');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (1600, '2007 Zagora St', '50090');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (1700, '2004 Charade Rd', '98199');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (1800, '147 Spadina Ave', 'M5V 2L7');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (1900, '6092 Boxwood St', 'YSW 9T2');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (2000, '40-5-12 Laogianggen', '190518');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (2100, '1298 Vileparle (E)', '490231');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (2200, '12-98 Victoria Street', '2901');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (2300, '198 Clementi North', '540198');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS) Values  (2400, '8204 Arthur St');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (2600, '9702 Chester Road', '09629850293');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (2700, 'Schwanthalerstr. 7031', '80925');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (2800, 'Rua Frei Caneca 1360 ', '01307-002');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (2900, '20 Rue des Corps-Saints', '1730');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (3000, 'Murtenstrasse 921', '3095');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (3100, 'Pieter Breughelstraat 837', '3029SK');
Insert into TBL_LOCATION  (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE) Values  (3200, 'Mariano Escobedo 9991', '11932');
COMMIT;
Insert into TBL_DEPT  (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)  Values (20, 'Marketing', 1800);
Insert into TBL_DEPT  (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID) Values (30, 'Purchasing', 1700);
Insert into TBL_DEPT  (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID) Values (60, 'IT', 1400);
Insert into TBL_DEPT  (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID) Values (90, 'Executive', 1700);
COMMIT;
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID) Values  (100, 'Steven', 'King', 90);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (201, 'Michael', 'Hartstein', 100, 20);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (114, 'Den', 'Raphaely', 100, 30);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (101, 'Neena', 'Kochhar', 100, 90);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (102, 'Lex', 'De Haan', 100, 90);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (103, 'Alexander', 'Hunold', 102, 60);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (105, 'David', 'Austin', 103, 60);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (104, 'Bruce', 'Ernst', 103, 60);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (106, 'Valli', 'Pataballa', 103, 60);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (107, 'Diana', 'Lorentz', 103, 60);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (119, 'Karen', 'Colmenares', 114, 30);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (117, 'Sigal', 'Tobias', 114, 30);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (115, 'Alexander', 'Khoo', 114, 30);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (116, 'Shelli', 'Baida', 114, 30);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (118, 'Guy', 'Himuro', 114, 30);
Insert into TBL_EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID, DEPARTMENT_ID) Values  (202, 'Pat', 'Fay', 201, 20);
COMMIT;

I would like to create a XML like below

 <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  <EMPLOY CAPTA="TRUE">
      <FUNC CMD="VOID">
       <LOCAL>
       <location_id>1700</<location_id>
          <DEPTO>
           <department_id>30</department_id>
              <EMPLOY>
               <EMPLOYEE_ID>114 </EMPLOYEE_ID>
               <FIRST_NAME>Den</FIRST_NAME>
               <LAST_NAME>Raphaely</LAST_NAME>
             </EMPLOY>  
              <EMPLOY>
               <EMPLOYEE_ID>115</EMPLOYEE_ID>
               <FIRST_NAME>Alexander</FIRST_NAME>
               <LAST_NAME>Khoo</LAST_NAME>
             </EMPLOY>  
              <EMPLOY>
              <EMPLOYEE_ID>116 </EMPLOYEE_ID>
              <FIRST_NAME>Shelli</FIRST_NAME>
              <LAST_NAME>Baida</LAST_NAME>
             </EMPLOY>  
              <EMPLOY>
             <EMPLOYEE_ID>117 </EMPLOYEE_ID>
             <FIRST_NAME>Sigal</FIRST_NAME>
             <LAST_NAME>Tobias</LAST_NAME>
             </EMPLOY>  
              <EMPLOY>
             <EMPLOYEE_ID>118</EMPLOYEE_ID>
             <FIRST_NAME>Guy</FIRST_NAME>
             <LAST_NAME>Himuro</LAST_NAME>
             </EMPLOY>  
              <EMPLOY>
             <EMPLOYEE_ID>119</EMPLOYEE_ID>
             <FIRST_NAME>Karen</FIRST_NAME>
             <LAST_NAME>Colmenares</LAST_NAME>
             </EMPLOY>  
          </DEPTO>  
          <DEPTO>
       <department_id>90</department_id>
         <EMPLOY>
              <EMPLOYEE_ID>100</EMPLOYEE_ID>
              <FIRST_NAME>Steven</FIRST_NAME>
              <LAST_NAME>King</LAST_NAME>
         </EMPLOY>  
         <EMPLOY>
               <EMPLOYEE_ID>101</EMPLOYEE_ID>
               <FIRST_NAME>Neena</FIRST_NAME>
               <LAST_NAME>Kochhar</LAST_NAME>
         </EMPLOY>  
         <EMPLOY>
              <EMPLOYEE_ID>102 </EMPLOYEE_ID>
              <FIRST_NAME>Lex</FIRST_NAME>
          <LAST_NAME>De Haan</LAST_NAME>
            </EMPLOY> 
          </DEPTO>   
        </LOCAL>  
     </FUNC>
   </EMPLOY>

I tried the query below, but I do know how can to do It

SELECT E.EMPLOYEE_ID,
        E.FIRST_NAME ,
        E.LAST_NAME ,
        J.DEPARTMENT_ID
FROM  TBL_EMPLOYEES E
INNER JOIN TBL_DEPT J
ON E.DEPARTMENT_ID = J.DEPARTMENT_ID
INNER JOIN TBL_LOCATION L
ON J.LOCATION_ID = L.LOCATION_ID
WHERE L.LOCATION_ID = 1700
START WITH E.MANAGER_ID IS NULL 
CONNECT BY PRIOR E.EMPLOYEE_ID = E.MANAGER_ID

Using

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

Thank you in advance

This post has been answered by Paulzip on Nov 7 2018

Jump to Answer

John Thorton

580988 wrote:

Hi All,

I have a stored procedure which is running forever. The particular stored procedure reads a file and performs validation and inserts into some transaction table.

There are many stored procedures that are called within the main procedure. There might be locks or blocks or some query is taking long time to execute

How do i find out which query in which procedure is taking long time to execute? But sometimes the same procedures completes successfully within few seconds inserting

all the records (10000 recs).

Thanks

Gautam S

Consider to issue SQL below just prior to starting the rogue procedure.

ALTER SESSION SET SQL_TRACE=TRUE; -- ensure the USER has necessary privilege to successfully run this SQL statement

process resultant trace file using TKPROF

1 person found this helpful

I just ran the main procedure now and it ran successfully within few seconds. I don't know why it was not running earlier 1 hour ago. Can someone throw some light on this?

Thanks

Gautam

John Thorton

580988 wrote:

I just ran the main procedure now and it ran successfully within few seconds. I don't know why it was not running earlier 1 hour ago. Can someone throw some light on this?

Thanks

Gautam

Something changed, but we don't know what you have.

Does any of the SQL in your mystery procedure use bind variables?

1 person found this helpful

No, none of the SQL use bind variables. My procedure calls the innermost procedure which is a PRAGMA AUTONOMOUS procedure.

FYI : The innermost procedure has a FOR UPDATE in a select clause. I have issued proper COMMIT/ROLLBACK also in that procedure. Does it have something to do with that? Also, the total record count i am processing is 10000.

Thanks

Gautam

@John Thorton

However the records are processed successfully and inserted into relevant tables. Only the session keeps hanging in SQL Developer.

Let me know if you need anything else. If the session keeps hanging probably the application would also keep hanging in Production.

Can you someone kindly help on this.

Thanks

Gautam

Hi all,

It's solved. The hanging was due to the excessive dbms_output messages being populated for each of the 10000 records.

Cookiemonster76

580988 wrote:

No, none of the SQL use bind variables.

Are you using dynamic SQL? Because unless you are, all your SQL will definitely be using bind variables. PL/SQL sorts that out automatically.

BEDE

The most simple thing is to use a log table and log there when the code gets to some place using a store procedure with pragma autonomous_transaction.

Or, you may see what shows in the sys.gv_$open_cursor and sys.gv_$sql for that session of yours, like below:

select sq.*

from sys.gv_$open_cursor oc

join sys.gv_$sql sq on oc.sql_id=sq.sql_id and oc.inst_id=sq.inst_id

where oc.sid=&sid and oc.inst_id=&inst_id

and sq.users_executing>0

;

If it's quite difficult to know which your session is, you may mark that session using the facilities of package dbms_application_info.

BEDE

If you suspect there may be some session blocking:

Select * from sys.gv_$session_blockers

;

And see what you get.

1 - 9

Added on Nov 7 2018

#xml

8 comments

333 views

SQL & PL/SQL

Generate XML from tables

Comments

Post Details