Report showing column index

Question

Report showing column index

Navigation

#1 by (1 votes)
#2 by (1 votes)

9

I have a question for generating a report, I need it to get the data from an SQL table and instead 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 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;

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, it 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 puts the index of the column, there I made the gambiarra to put the "fixed "but I need it dynamically, I hope it has been able to explain.

I even managed to do a select one that searches for this data, but I could not relate it to my column if someone 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';

sql oracle procedure

asked by anonymous 06.05.2015 / 20:21

2 answers

Where does Git pull point? What is collation? [duplicate]

score 1 · Answer 1

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

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

The cat's leap is in the first query , which transforms table rows into XML like lines. The other queries are just for cleaning and formatting.

XMLSEQUENCE is marked as deprecated , but is still supported.

score 1 · Answer 2

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

Here's 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');

This response was based on on this topic .