Forum Stats

  • 3,770,167 Users
  • 2,253,079 Discussions
  • 7,875,357 Comments

Discussions

Need required output in sql or pl/sql

VSN Moor
VSN Moor Member Posts: 27 Green Ribbon
edited Oct 10, 2021 7:01PM in SQL & PL/SQL

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.

Tagged:
«1

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond

    Hi, @User_JRWZI

    I don't know if there's an easy way to do that using XML functions.

    Using string manipulation functions, you can do it like this:

    WITH	col_info (cell_index, data_type)  AS
    (
    	SELECT	1, 'Numner' FROM dual UNION ALL	-- or 'Number'
    	SELECT	2, 'String' FROM dual UNION ALL
    	SELECT	3, 'String' FROM dual UNION ALL
    	SELECT	4, 'String' FROM dual UNION ALL
    	SELECT	5, 'Numner' FROM dual UNION ALL
    	SELECT	6, 'Numner' FROM dual
    )
    ,  unpivoted_data  AS
    (
    	SELECT  c.*
    	,	DENSE_RANK () OVER (ORDER BY t.emp_id, t.ROWID)	AS rn
    	,	CASE c.cell_index
    		  WHEN 1 THEN TO_CHAR (emp_id)
    		  WHEN 2 THEN f_name
    		  WHEN 3 THEN l_name
    		  WHEN 4 THEN email
    		  WHEN 5 THEN TO_CHAR (salary)
    		  WHEN 6 THEN TO_CHAR (dept_id)
    		END						AS str_data
    	FROM	  col_info c
    	CROSS JOIN table_x  t
    )
    SELECT CASE
    	  WHEN ROW_NUMBER () OVER ( PARTITION BY rn
    	  	 	   	    ORDER BY	   cell_index
    	  	 	   	   ) = 1
    	  THEN '<ROW>' || CHR (10)
    	END			||
    	'Cell ss:Index="'	||
    	TO_CHAR (cell_index)	||
    	'"><Data ss:Type="'	||
    	data_type		||
    	'>'			||
    	str_data		||
    	'</Data></Cell>'	||
     	CASE
    	  WHEN ROW_NUMBER () OVER ( PARTITION BY rn
    	  	 	   	    ORDER BY	   cell_index DESC
    	  	 	   	   ) = 1
    	  THEN CHR (10) || '</ROW>'
    	END	AS output_str
    FROM	 unpivoted_data
    WHERE	 str_data IS NOT NULL
    ORDER BY rn, cell_index
    ;
    

    Do you need to do anything special when any of the strings contain '<' or '>'?

    Them query above assumes CHR (10) signifies a newline. On your system, you may need to use something else, such as CHR (10) || CHR (13).

  • VSN Moor
    VSN Moor Member Posts: 27 Green Ribbon

    @Frank Kulash , the problem here is the col_info is dynamic. today, I might have 6 columns today ... tomorrow colums might added or removed. I am expecting the code would be dynamic.

    Let me know your thoughts.

    Thanks,

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond

    Hi, @User_JRWZI

     the problem here is the col_info is dynamic. 

    You can make col_info dynamic, it you want to. You'll need to make the CASE expression in unpivoted_data dyanamic, too. As synamic SQL goes, that shouldn't be very hard. All the information you need is in all_tab_cols. If you get stuck, post your best attempt, along with a specific question.

  • VSN Moor
    VSN Moor Member Posts: 27 Green Ribbon

    @Frank Kulash , thank you for your help.

    I wrote a procedure regarding this and updated my earlier post with the procedure.

    I am getting an error in dynamic sql. Could you please take a look and help.

    That would solve my problem. I think.

    Thanks,

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    edited Oct 10, 2021 11:35PM

    Hi, @User_JRWZI

    I wrote a procedure regarding this and updated my earlier post with the procedure.

    Please don't ever do that: it makes the thread hard to read and you changes easy to miss. Post all additions and corrections in a new comment, at the end of the thread.

    You're using a considerable different approach. The code I posted didn't INSERT anything into any table. I'm not sure what you're trying to do. As always, post CREATE TABLE and INSERT statements for any tables used, and show the exact results you want from that sample data. In the case of a DML operation, such as INSERT, the CREATE TABLE and INSERT statements you post should reflect the tables as they are before the DML begins (at which point, one table may have 0 rows), and the results will be the contents of the changed table after the DML is finished. Explain, in general terms, how you get the desired results from the given data.

    Never write, let alone post, unformatted code. Indent the code to show the scope of blocks, loops, and mult-line statements.

  • VSN Moor
    VSN Moor Member Posts: 27 Green Ribbon

    @Frank Kulash , Ok, thanks for the advice. I am using this site 2nd time only. I will follow the steps you mentioned and update the comments.

  • User_H3J7U
    User_H3J7U Member Posts: 690 Silver Trophy
    with function gettypes(tbl dbms_tf.table_t) return varchar2 sql_macro as
      qry varchar2(32767 byte);
      function qryc(col dbms_tf.column_metadata_t) return varchar2 is
      begin
        return utl_lms.format_message(
          q'{select '%s' col, '%s' typ from dual}',
          replace(trim('"' from col.name),'''',''''''),
          replace(dbms_tf.column_type_name(col),'''',''''''));
      end qryc;
    begin
      qry := qryc(tbl.column(1).description);
      for i in 2..tbl.column.count loop
        qry := qry || '
    union all '||qryc(tbl.column(i).description);
      end loop;
      return qry;
    end gettypes;
    meta as (
    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
    ),
    data as (
    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
    )
    select xmltransform(xmltype(cursor(select * from data)), xmlquery('declare namespace ss="urn:schemas-microsoft-com:office:spreadsheet";
    declare namespace xsl="http://www.w3.org/1999/XSL/Transform";
    copy $x := $xsl modify
    (for $row in $x//Row
     return replace node $row with $cols)
    return $x'
      passing xmltype('
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"
     xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
      <xsl:output omit-xml-declaration="yes" indent="yes"/>
      <xsl:template match="/">
    <Table>
        <xsl:for-each select="ROWSET/ROW">
    <Row/>
        </xsl:for-each>
    </Table>
    </xsl:template>
    </xsl:stylesheet>  
    ') as "xsl",
      xcols as "cols"
      returning content)) xq
    from
    ( select
        xmlelement("Row",
          xmlattributes('urn:schemas-microsoft-com:office:spreadsheet' as "xmlns:ss",
                        'http://www.w3.org/1999/XSL/Transform' as "xmlns:xsl"),
          xmlagg(
            xmlelement("Cell", xmlattributes(meta.col_no as "ss:Index"),
              xmlelement("Data", xmlattributes(ty.typ as "ss:Type"),
                xmlelement("xsl:value-of", xmlattributes(upper(meta.col_name) as "select"))))
            order by meta.col_no)) xcols
    from meta join gettypes(data) ty on meta.col_name = ty.col collate binary_ci
    )
    /
    
    XQ                                                                              
    --------------------------------------------------------------------------------
    <Table xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
     <Row>
      <Cell ss:Index="1"><Data ss:Type="NUMBER">100</Data></Cell>
      <Cell ss:Index="2"><Data ss:Type="VARCHAR2">Steven</Data></Cell>
      <Cell ss:Index="3"><Data ss:Type="VARCHAR2">King</Data></Cell>
      <Cell ss:Index="5"><Data ss:Type="NUMBER">24000</Data></Cell>
      <Cell ss:Index="6"><Data ss:Type="NUMBER">90</Data></Cell>
     </Row>
     <Row>
      <Cell ss:Index="1"><Data ss:Type="NUMBER">101</Data></Cell>
      <Cell ss:Index="2"><Data ss:Type="VARCHAR2">Neena</Data></Cell>
      <Cell ss:Index="3"><Data ss:Type="VARCHAR2"></Data></Cell>
      <Cell ss:Index="5"><Data ss:Type="NUMBER">17000</Data></Cell>
      <Cell ss:Index="6"><Data ss:Type="NUMBER">90</Data></Cell>
     </Row>
     <Row>
      <Cell ss:Index="1"><Data ss:Type="NUMBER">102</Data></Cell>
      <Cell ss:Index="2"><Data ss:Type="VARCHAR2">Lex</Data></Cell>
      <Cell ss:Index="3"><Data ss:Type="VARCHAR2">De Haan</Data></Cell>
      <Cell ss:Index="5"><Data ss:Type="NUMBER">17000</Data></Cell>
      <Cell ss:Index="6"><Data ss:Type="NUMBER">90</Data></Cell>
     </Row>
     <Row>
      <Cell ss:Index="1"><Data ss:Type="NUMBER">103</Data></Cell>
      <Cell ss:Index="2"><Data ss:Type="VARCHAR2">Alexander</Data></Cell>
      <Cell ss:Index="3"><Data ss:Type="VARCHAR2">Hunold</Data></Cell>
      <Cell ss:Index="5"><Data ss:Type="NUMBER">9000</Data></Cell>
      <Cell ss:Index="6"><Data ss:Type="NUMBER">60</Data></Cell>
     </Row>
     <Row>
      <Cell ss:Index="1"><Data ss:Type="NUMBER">104</Data></Cell>
      <Cell ss:Index="2"><Data ss:Type="VARCHAR2">Bruce</Data></Cell>
      <Cell ss:Index="3"><Data ss:Type="VARCHAR2">Ernst</Data></Cell>
      <Cell ss:Index="5"><Data ss:Type="NUMBER">6000</Data></Cell>
      <Cell ss:Index="6"><Data ss:Type="NUMBER">60</Data></Cell>
     </Row>
    </Table> 
    


    cormaco
  • VSN Moor
    VSN Moor Member Posts: 27 Green Ribbon

    @User_H3J7U, Thanks for all your help. You spent lot of time for me.

    I will go though the code and get back to you. it will take time for me to understand.

    Amazing code.😁

  • VSN Moor
    VSN Moor Member Posts: 27 Green Ribbon

    @User_H3J7U , I am getting the below error...when I run in sql plus.

    When I run in sql workbench, it is completely hanging.

    with function gettypes(tbl dbms_tf.table_t) return varchar2 sql_macro as

    *

    ERROR at line 1:

    ORA-00600: internal error code, arguments: [kxes_sqlcomp_check_macro-1], [],

    [], [], [], [], [], [], [], [], [], []

    is it possible, to make this a simple pl/sql procedure instead?

    Thanks,

  • User_H3J7U
    User_H3J7U Member Posts: 690 Silver Trophy

    ORA-00600: internal error code, arguments: [kxes_sqlcomp_check_macro-1]

    SQL_MACRO backported to 19.7+ with a bugs. Create a function instead of declaration in the query or upgrade to 21c.