sql – Report showing the column index

Question:

I have a question to generate a report, I need it to take the data from an SQL table and instead of the table name I need its ID, below the data that the query generates

     COD_CLIENTE NOME        ENDERECO             CPF                
-----------------------------------------------------------------------

      2 Fulano               Av. Rio Branco       2837462890           
      3 Ciclano              Rua Zero             4625427282           
      4 Beltrano             Rua Doze             2634623637           

I created this procedure

create or replace PROCEDURE COLUNAS_TESTE AS 
Cursor linha is
Select cod_cliente, nome, endereco, cpf from clientes where rownum < 4;

rLin linha%rowtype;

BEGIN
Open linha;
Loop

Fetch linha into rLin;

Exit when linha%notFound;

        dbms_output.put_line('Linha: '||rLin.cod_cliente||' Coluna: 2'||' Valor: '||rLin.Nome);
        dbms_output.put_line('Linha: '||rLin.cod_cliente||' Coluna: 3'||' Valor: '||rLin.Endereco);
        dbms_output.put_line('Linha: '||rLin.cod_cliente||' Coluna: 4'||' Valor: '||rLin.CPF);
End loop;
Close linha;
END;

What generates this result?

Linha: 2 Coluna: 2 Valor: Fulano
Linha: 2 Coluna: 3 Valor: Av. Rio Branco
Linha: 2 Coluna: 4 Valor: 2837462890
Linha: 3 Coluna: 2 Valor: Ciclano
Linha: 3 Coluna: 3 Valor: Rua Zero
Linha: 3 Coluna: 4 Valor: 4625427282
Linha: 4 Coluna: 2 Valor: Beltrano
Linha: 4 Coluna: 3 Valor: Rua Doze
Linha: 4 Coluna: 4 Valor: 2634623637

Line and value OK, it takes the code, but I need 2 reports, one that instead of the column it puts the name of the field, and another that puts the column index, there I made the workaround of putting the "fixed", but I need it dynamically, I hope you were able to explain.

I even managed to do a select that seeks this data, but I didn't know how to relate it to my column, if anyone can help and I've managed to explain the problem.

select COLUNAS.COLUMN_ID AS COLUNAS_ID ,COLUNAS.COLUMN_NAME AS COLUNAS_NOME
   from USER_TAB_COLUMNS COLUNAS
   where COLUNAS.TABLE_NAME = 'CLIENTES';

Answer:

It was work, but it was fun 🙂

WITH 
raw_xml AS (
  -- Obtem um flaten XML da tabela departments
  SELECT TRIM(COLUMN_VALUE) AS vals
  FROM TABLE(XMLSEQUENCE(CURSOR(SELECT * FROM departments)))
),  
extract_row_tag AS (
  -- Substitui a tag <ROW> por vazio
  SELECT REGEXP_REPLACE(vals, '<ROW>', '') AS vals
  FROM raw_xml
),
extract_end_tags AS (
  -- Substitui todas as end tags por vazio
  SELECT REGEXP_REPLACE(vals, '</[[:print:]]+>', '') AS vals
  FROM extract_row_tag
), 
replace_start_sign AS (
  -- Substitui os sinais de maior por vazio
  SELECT REGEXP_REPLACE(vals, '<', '') AS vals
  FROM extract_end_tags
),
replace_end_sign AS (
  -- Substitui os sinais de menor por dois pontos
  SELECT REGEXP_REPLACE(vals, '>', ':') AS vals
  FROM replace_start_sign
)
SELECT * FROM replace_end_sign;

In this example, I'm building the report from the departments table, but this query works with any table and any number of fields.

I separated in successive queries just for understanding. In a giant report, I believe that WITH could generate some unnecessary overhead .

The catch is in the first query , which transforms the table rows into XML like rows. The other queries are just for cleaning and formatting.

The XMLSEQUENCE function is marked as deprecated , but it is still supported.

Scroll to Top