Report showing the Column Index

Question

I have a question to generate a report, I need it to take the data of an SQL table and in the place in the name of the precise table of 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 trial

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;

That 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, he takes the code, but I need 2 reports, one that in the place of the column he put the name of the field, and another that put the column Dice, there I did the gambiarra to put the "fixed", but I need it dynamically, I hope you have been able to explain.

I even managed to make a select that searches for this data, but did not know how to relate it to my column, if anyone can help and I have been able 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 1

It was hard 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 am assembling the report from the table departments, but this query works with any table and any number of fields.

I separated in successive queries only for understanding. In a giant report, I believe that WITH could generate a certain overhead unnecessarily.

The cat jump is in the first query, that turns table rows into rows XML like. The other darlings are only for cleaning and formatting.

To Function XMLSEQUENCE is marked as deprecated, but is still supported.

Answer 2

To do this you will need two cursors, one for the rows and the other for the columns.

Below is the solution:

DECLARE
  nome_tabela VARCHAR(32) := 'CLIENTES';
  nome_coluna VARCHAR2(32);
  id_coluna   NUMBER(10);
  query_str   VARCHAR2(100);
  mycount     NUMBER;
  valor       VARCHAR2(4000);

  CURSOR linha
  IS
    SELECT rownum AS num_linha,
      cod_cliente,
      nome,
      endereco,
      cpf
    FROM clientes
    WHERE rownum < 4;

  rLin linha%rowtype;

  CURSOR colunas (tabela VARCHAR2)
  IS
    SELECT column_name,
      column_id
    FROM user_tab_columns
    WHERE table_name = tabela
    ORDER BY column_id;

BEGIN
  OPEN linha;

  LOOP
    FETCH linha INTO rLin;
    EXIT
  WHEN linha%notFound;
    OPEN colunas (nome_tabela);

    LOOP
      FETCH colunas INTO nome_coluna,id_coluna;
      EXIT
    WHEN colunas%NOTFOUND;
      query_str := 'Select CAST('|| nome_coluna ||' AS VARCHAR2(4000)) from clientes where cod_cliente = '||rLin.cod_cliente;
      EXECUTE immediate query_str INTO valor;
      dbms_output.put_line('Linha: '||rLin.num_linha||' Coluna: '||id_coluna||' Valor: '||valor);

    END LOOP;
    CLOSE colunas;

  END LOOP;
  CLOSE linha;
END;

Code used to create the test table:

create table clientes (cod_cliente number, nome varchar2(200), endereco varchar(200), cpf varchar2(11));

insert into clientes values (2,'Fulano','Av. Rio Branco','2837462890');
insert into clientes values  (3,'Ciclano','Rua Zero','4625427282');
insert into clientes values  (4,'Beltrano', 'Rua Doze','2634623637');

That response was based on on that topic.