I have a query like below.
I need output as I mentioned below.
Note: if the data in query is NULL, then that field to be excluded from the output.
In the output of 1st record, the index is eliminated as email is NULL.
The numner of colums would be dynamic. The columns might increase or decrease.
-----sql query:
select emp_id,f_name,l_name,email,salary,dept_id from
(
select 100 emp_id,'Steven' f_name,'King' l_name,null email,24000 salary, 90 dept_id from dual union
select 101 emp_id,'Neena' f_name,null l_name,'NKOCHAAR' email,17000 salary,90 dept_id from dual union
select 102 emp_id,'Lex' f_name,'De Haan' l_name,'LDEHAAN',17000 salary,90 dept_id from dual union
select 103 emp_id,'Alexander' f_name,'Hunold' l_name,'AHUNOLD' email,9000 salary,60 dept_id from dual union
select 104 emp_id,'Bruce' f_name,'Ernst','BERNST' email,6000 salary,60 dept_id from dual
);
OUTPUT Needed as below:
<Row>
<Cell ss:Index="1"><Data ss:Type="Numner">100</Data></Cell>
<Cell ss:Index="2"><Data ss:Type="String">Steven</Data></Cell>
<Cell ss:Index="3"><Data ss:Type="String">King</Data></Cell>
<Cell ss:Index="5"><Data ss:Type="Numner">24000</Data></Cell>
<Cell ss:Index="6"><Data ss:Type="Numner">90</Data></Cell>
</Row>
<Row>
<Cell ss:Index="1"><Data ss:Type="Numner">101</Data></Cell>
<Cell ss:Index="2"><Data ss:Type="String">Neena</Data></Cell>
<Cell ss:Index="4"><Data ss:Type="String">NKOCHAAR</Data></Cell>
<Cell ss:Index="5"><Data ss:Type="Numner">17000</Data></Cell>
<Cell ss:Index="6"><Data ss:Type="Numner">90</Data></Cell>
</Row>
--
--
---
--
etc
create or replace procedure test_xml is
l_col_count number :=6;
l_count number :=0;
l_str varchar2(10000) := null;
cursor data_cur is
select emp_id,f_name,l_name,email,salary,dept_id from
(
select 100 emp_id,'Steven' f_name,'King' l_name,null email,24000 salary, 90 dept_id from dual union
--select 101 emp_id,'Neena' f_name,null l_name,'NKOCHAAR' email,17000 salary,90 dept_id from dual union
--select 102 emp_id,'Lex' f_name,'De Haan' l_name,'LDEHAAN',17000 salary,90 dept_id from dual union
--select 103 emp_id,'Alexander' f_name,'Hunold' l_name,'AHUNOLD' email,9000 salary,60 dept_id from dual union
select 104 emp_id,'Bruce' f_name,'Ernst','BERNST' email,6000 salary,60 dept_id from dual
);
cursor col_cur is
select col_no,col_name from
(
select 1 col_no,'emp_id' col_name from dual union
select 2 col_no,'f_name' col_name from dual union
select 3 col_no,'l_name' col_name from dual union
select 4 col_no,'email' col_name from dual union
select 5 col_no,'salary' col_name from dual union
select 6 col_no,'dept_id' col_name from dual
)
order by col_no;
BEGIN
dbms_output.put_line('Started');
for data_rec in data_cur
loop
l_count := 0;
for col_rec in col_cur
loop
l_count := l_count + 1;
l_str := 'insert into table_name(xml_data) values ('||'data_rec.'||col_rec.col_name||')';
--dbms_output.put_line(l_str);
execute immediate l_str;
--dbms_output.put_line('rec:'||data_rec.emp_id||'-'||data_rec.f_name||'-'||'col:'||col_rec.col_name);
--commit;
end loop;
dbms_output.put_line('count:'||l_count);
end loop;
dbms_output.put_line('Ended');
END;
/
sho error;
I wrote a pl/sql procedure...but, I am getting an error as below:
ERROR at line 1:
ORA-00984: column not allowed here
Need help.