Menu de navegação

[mommParticipante]

Pessoal,

Estava acostumado a utilizar o SQL Server como banco de dados aqui na empresa, mas surgiu um novo cliente e estamos utilizando o Oracle agora.

Enfim, a minha dúvida é a seguinte, tem como retornar uma table dinâmica através de uma procedure/function/view da vida? Digamos assim, preciso receber como retorno uma tabela que tenha N colunas criadas dinamicamente, ou seja, não poderia ser feita criando um tipo object, pois nele todas as colunas tem que ser especificadas previamente, certo?

Exemplo 1:
ID | Nome | Descrição | Coluna 1 | Coluna 2

Exemplo 2:
ID | Nome | Descrição | Coluna 1 | Coluna 2 | Coluna 3

Sendo que “as Coluna” poderiam existir em quantidades variaveis de acordo com os parametros que eu passar e a lógica que eu programar.

Um outro exemplo seria algo do tipo:
“CALL funcaoTeste(2)”: Faria um looping gerando 2 colunas… se eu passasse 3 como parametro utilizaria o mesmo looping, mas retornaria 3 colunas.

Isso é possível de ser feito? Como fazer? alguém tem algum exemplo? No SQL Server era possível utilizando meio que um EVAL passando uma string com código T-SQL concatenado embutido.

Obrigado.

[Leonardo LitzParticipante]

Segue um exemplo abaixo, onde dado um select ira retornar um array bidimensional, ai so vc extrair o array…..

Só tem um problema, é limitado em 45 colunas…

Primeiro crie os types

TYPE typ_table_array IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

TYPE mult_table IS TABLE OF typ_table_array INDEX BY BINARY_INTEGER;

Depois a funcao

FUNCTION fnc_select(p_sql IN VARCHAR2)
RETURN mult_table PIPELINED AS tabela table_array;
—Leonardo Litz em 16/05/2007, versão inicial

CUR INTEGER;

TEXTO VARCHAR2(32767);

TEMP NUMBER;

E1 EXCEPTION;

PRAGMA EXCEPTION_INIT(E1,-1007);

DADOS DBMS_SQL.Varchar2_Table ;

NC NUMBER;
BEGIN

CUR := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(CUR, P_SQL, DBMS_SQL.V7);

BEGIN

TEMP := DBMS_SQL.EXECUTE(CUR);

FOR DD IN 1..45 LOOP

DBMS_SQL.DEFINE_COLUMN(CUR, DD, TEXTO, 32767);

END LOOP;

EXCEPTION WHEN E1 THEN

NULL;

END;
TEMP := DBMS_SQL.EXECUTE(CUR);

LOOP
IF DBMS_SQL.FETCH_ROWS(CUR) = 0 THEN

EXIT;
ELSE

BEGIN

FOR DD IN 1..45 LOOP

DBMS_SQL.COLUMN_VALUE(CUR, DD, TEXTO);
DADOS(DD) := TEXTO;
NC := DD;

END LOOP;

EXCEPTION WHEN E1 THEN

—Limitei em 45 colunas… pois não consegui fazer maior que isso…
IF NC = 1 THEN
TABELA := TABLE_ARRAY(DADOS(1));
ELSIF NC = 2 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2));
ELSIF NC = 3 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3));
ELSIF NC = 4 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4));
ELSIF NC = 5 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5));
ELSIF NC = 6 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6));
ELSIF NC = 7 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7));
ELSIF NC = 8 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8));
ELSIF NC = 9 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9));
ELSIF NC = 10 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10));
ELSIF NC = 11 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11));
ELSIF NC = 12 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12));
ELSIF NC = 13 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13));
ELSIF NC = 14 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14));
ELSIF NC = 15 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15));
ELSIF NC = 16 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16));
ELSIF NC = 17 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17));
ELSIF NC = 18 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18));
ELSIF NC = 19 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19));
ELSIF NC = 20 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20));
ELSIF NC = 21 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21));
ELSIF NC = 22 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22));
ELSIF NC = 23 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23));
ELSIF NC = 24 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24));
ELSIF NC = 25 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24),DADOS(25));
ELSIF NC = 26 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24),DADOS(25),DADOS(26));
ELSIF NC = 27 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24),DADOS(25),DADOS(26),DADOS(27));
ELSIF NC = 28 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24),DADOS(25),DADOS(26),DADOS(27),DADOS(28));
ELSIF NC = 29 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24),DADOS(25),DADOS(26),DADOS(27),DADOS(28),DADOS(29));
ELSIF NC = 30 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24),DADOS(25),DADOS(26),DADOS(27),DADOS(28),DADOS(29),DADOS(30));
ELSIF NC = 31 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24),DADOS(25),DADOS(26),DADOS(27),DADOS(28),DADOS(29),DADOS(30),DADOS(31));
ELSIF NC = 32 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24),DADOS(25),DADOS(26),DADOS(27),DADOS(28),DADOS(29),DADOS(30),DADOS(31),DADOS(32));
ELSIF NC = 33 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24),DADOS(25),DADOS(26),DADOS(27),DADOS(28),DADOS(29),DADOS(30),DADOS(31),DADOS(32),DADOS(33));
ELSIF NC = 34 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24),DADOS(25),DADOS(26),DADOS(27),DADOS(28),DADOS(29),DADOS(30),DADOS(31),DADOS(32),DADOS(33),DADOS(34));
ELSIF NC = 35 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24),DADOS(25),DADOS(26),DADOS(27),DADOS(28),DADOS(29),DADOS(30),DADOS(31),DADOS(32),DADOS(33),DADOS(34),DADOS(35));
ELSIF NC = 36 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24),DADOS(25),DADOS(26),DADOS(27),DADOS(28),DADOS(29),DADOS(30),DADOS(31),DADOS(32),DADOS(33),DADOS(34),DADOS(35),DADOS(36));
ELSIF NC = 37 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24),DADOS(25),DADOS(26),DADOS(27),DADOS(28),DADOS(29),DADOS(30),DADOS(31),DADOS(32),DADOS(33),DADOS(34),DADOS(35),DADOS(36),DADOS(37));
ELSIF NC = 38 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24),DADOS(25),DADOS(26),DADOS(27),DADOS(28),DADOS(29),DADOS(30),DADOS(31),DADOS(32),DADOS(33),DADOS(34),DADOS(35),DADOS(36),DADOS(37),DADOS(38));
ELSIF NC = 39 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24),DADOS(25),DADOS(26),DADOS(27),DADOS(28),DADOS(29),DADOS(30),DADOS(31),DADOS(32),DADOS(33),DADOS(34),DADOS(35),DADOS(36),DADOS(37),DADOS(38),DADOS(39));
ELSIF NC = 40 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24),DADOS(25),DADOS(26),DADOS(27),DADOS(28),DADOS(29),DADOS(30),DADOS(31),DADOS(32),DADOS(33),DADOS(34),DADOS(35),DADOS(36),DADOS(37),DADOS(38),DADOS(39),DADOS(40));
ELSIF NC = 41 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24),DADOS(25),DADOS(26),DADOS(27),DADOS(28),DADOS(29),DADOS(30),DADOS(31),DADOS(32),DADOS(33),DADOS(34),DADOS(35),DADOS(36),DADOS(37),DADOS(38),DADOS(39),DADOS(40),DADOS(41));
ELSIF NC = 42 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24),DADOS(25),DADOS(26),DADOS(27),DADOS(28),DADOS(29),DADOS(30),DADOS(31),DADOS(32),DADOS(33),DADOS(34),DADOS(35),DADOS(36),DADOS(37),DADOS(38),DADOS(39),DADOS(40),DADOS(41),DADOS(42));
ELSIF NC = 43 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24),DADOS(25),DADOS(26),DADOS(27),DADOS(28),DADOS(29),DADOS(30),DADOS(31),DADOS(32),DADOS(33),DADOS(34),DADOS(35),DADOS(36),DADOS(37),DADOS(38),DADOS(39),DADOS(40),DADOS(41),DADOS(42),DADOS(43));
ELSIF NC = 44 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24),DADOS(25),DADOS(26),DADOS(27),DADOS(28),DADOS(29),DADOS(30),DADOS(31),DADOS(32),DADOS(33),DADOS(34),DADOS(35),DADOS(36),DADOS(37),DADOS(38),DADOS(39),DADOS(40),DADOS(41),DADOS(42),DADOS(43),DADOS(44));
ELSIF NC = 45 THEN
TABELA := TABLE_ARRAY(DADOS(1),DADOS(2),DADOS(3),DADOS(4),DADOS(5),DADOS(6),DADOS(7),DADOS(8),DADOS(9),DADOS(10),DADOS(11),DADOS(12),DADOS(13),DADOS(14),DADOS(15),DADOS(16),DADOS(17),DADOS(18),DADOS(19),DADOS(20),DADOS(21),DADOS(22),DADOS(23),DADOS(24),DADOS(25),DADOS(26),DADOS(27),DADOS(28),DADOS(29),DADOS(30),DADOS(31),DADOS(32),DADOS(33),DADOS(34),DADOS(35),DADOS(36),DADOS(37),DADOS(38),DADOS(39),DADOS(40),DADOS(41),DADOS(42),DADOS(43),DADOS(44),DADOS(45));
END IF;

PIPE ROW(TABELA);
END;
END IF;

END LOOP;

DBMS_SQL.CLOSE_CURSOR(CUR);

RETURN;
END;

Valeu Leonardo Litz

[Autor]

Posts