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.