Array em Select

[Motta 645]

Em uma procedure pl-sql gostaria de fazer o seguinte :

 

Carregar um array e poder acessar este array dentro de um Sql.

 

Fiz um exemplo com a ALL_TAB_COLUMNS para pode ser executado de qualquer ambiente.

 

DECLARE
   TYPE T_INDICES IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
   VT_INDICES     T_INDICES;
BEGIN
   -- CARREGA INDICES MÉDIOS
   FOR I IN 1..999
   LOOP
      VT_INDICES(I) := I;-- POR EXEMPLO
   END LOOP;
   --
   -- RELATÓRIO EM SI
   FOR R IN (SELECT COLUMN_NAME,COLUMN_ID, VT_INDICES(CAST(COLUMN_ID AS INTEGER)) AS XXX
             FROM   ALL_TAB_COLUMNS
             WHERE  TABLE_NAME = 'ALL_TABLES')
   LOOP
     DBMS_OUTPUT.PUT_LINE(R.COLUMN_NAME||' '||R.COLUMN_ID||' '||R.XXX);
   END LOOP;
END;

[alphamek 2]

Olá Motta,

 

Fiz uns testes com o modelo que passou, foi baseado na view dba_objects, veja se com a utilização das funções TABLE e CAST podem fazer a atividade que deseja.

 

SQL> create type t_obj is array (999) of number;
 2 /

Tipo criado.

SQL> declare
 2 -- type t_obj is array(999) of number;
 3 v_obj t_obj := t_obj(1234,4343,4534);
 4 begin
 5 for dd in (select object_id, object_name
 6 from dba_objects
 7 where object_id in (select *
 8 from TABLE(CAST(v_obj as t_obj)))) loop
 9 dbms_output.put_line ('Valor:' || dd.object_id);
 10 end loop;
 11 end;
 12 /
Valor:1234
Valor:4343

Procedimento PL/SQL concluÝdo com sucesso.

SQL>

Não sei se é exatamente isso o que precisa.

 

Abraços,

[Motta 645]

Na realidade o que eu preciso é acessar o array de dentro do SQL.

 

Uma coisa que chama a atenção é que se passa o array indexado por um número não dá erro, o que parece realmente ocorrer é que o compilador não consegue resolver um campo do result set como indice.

 

Resumo do problema.

 

Tenho dois relatórios (sql´s) prontos :

 

Um me gera um indicador por Especialidade Médica

Outro me gera os mesmos indicadores por Médico (um médico tem uma especialidade, a principal dele no caso)

 

Neste da Espec. por Médico preciso ter para efeitos de comparação o indicador médio da especialidade.

 

Tenho poucas Especialidades (umas 30), mas é meio pesado de carregar.

 

Pensei :

 

1) carregar os indicadores médios das especialidades e joga-las num array

2) no sql dos indicadores por médicos acesso este array usando o código da especialidade (que é um numérico de 3) e "pego" o

indicador médio da especialidade de cada médico.

 

Vou tentar mudar o código passado.

 

Grato!

[alphamek 2]

Motta,

 

Compreendi o problema. Bom, Array é sim uma solução para sair desse bico de sinuca, porém, existem outros que facilmente poderia lhe oferecer mais performance para os seus relatórios, sem ter que alterar tanto a sua aplicação, por exemplo:

 

1) Como a tabela de especialidades é pequena (30 registros) porque não pede ao DBA colocar a tabela em KEEP CACHE (db_keep_cache_size), deste modo os dados da tabela não fazem leitura física e ficam em memória para uso mais rápido das PL/SQLs.

 

2) Verifica se o que está pegando é MERGE JOIN, HASH JOIN ou outro tipo de junção que faz gastar tempo e CPU. Veja pelo plano de execução.

 

3) É possivel também, você passar esses dados para um GTT durante a sessão dos relatórios, e usar os dados presos em memória durante o tempo de execução.

 

Abraços,

[Motta 645]

1) Na realidade não é uma tabela e sim uma fucntion que calcula isto

 

2) As tabelas aqui são de um Sistema Microsiga, que seguem um modelo que fazem os SQL´s serem tarefas herculias ... :)

 

3) Tem um exemplo ?

 

 

Acho que vou criar uma tabela que carregue estes indicadores médios.

 

Vou tentar fazer uma tabela virtual.

 

Grato !

[alphamek 2]

Motta,

 

Acho que tu acabo com a brincadeira, MICROSIGA! heheheheheh... Até alguns anos atrás, quando eu trabalhei com eles, a fábrica só sabia que existia 2 tipos de dados, NUMBER e VARCHAR. O resto era firula das empresas! Complicado demais... :o

 

Mas vamos lá:

 

1) Descartada.

 

2) Troque o ERP! heheheheh...

 

3) Bom se é díficil tirar um plano de execução, imagino então customizar esses relatórios e usar as GTTs. Mas abaixo segue um exemplo:

 

SQL> create global temporary table GTT_1 (coluna 1 number, coluna2 varchar2(1000));

 

SQL> insert into GT_1 values (select sua_function(parametros) from dual);

 

begin

 

... trabalhar com os dados da GTT, com SQLs simples.

 

end;

 

Como a GTT trabalha em memória e está sem nenhuma claúsula de COMMIT, após encerrar a sua sessão, a tabela GTT_1 terá os seus dados apagados automaticamente.

 

Abraços,

[Motta 645]

Acho que esta GTT resolve a questão, vou testar !!

[alphamek 2]

Veja aí, senão pensamos em outra coisa. B)

[Motta 645]

A GTT funcionou no Oracle, porém no maldito Crystal Reports não, deve ser devido ao drive de ODBC que o Crystal usa (só conseguimos fazer funcionar nele), creio que ele fecha a sesão ou coisa assim pois não localizei os dados depois.

 

Resolvi criando uma tabela comum, do tipo sanfona engorda e deleta, agora vou validar o rel. com o usuário.

 

A ideia da GTT porém deverá funcionar para outras remelas aqui.

 

Valeu pela ajuda !

[alphamek 2]

Motta,

 

O crystal não deve estar enxergando os dados pq a GTT não foi configurada com a opção ON COMMIT PRESERVE ROWS, assim, a sua sessão do Oracle preenche a tabela, porém, outras sessões não enxergam nada!

 

Colocando a opção na GTT de ON COMMIT PRESERVE ROWS irá funcionar o Crystal. O Server do Crystal deve abrir uma nova sessão na base e por isso que não enxerga.

 

Abraços,

[Motta 645]

Funcionou !

 

Estou fazendo um DELETE ao final da SP, é necessário ou não o COMMIT ?

[alphamek 2]

Motta,

 

Então, se na sua sessão que está por aplicação ou diretamente no Oracle desejar compartilhar os dados com outras sessões, por exemplo, no caso do Crystal reports, será necessário usar o atributo acima e sim, realizar um COMMIT, para notificar a GTT que os dados ficaram armazenados e será compartilhada com outra sessão.

 

Mas lembre-se, precisa da propriedade ON COMMIT PRESERVE ROWS na GTT e após realizar a manipulação dos dados, executar o COMMIT.

 

Agora, curiosidade, ficou mais performática?

 

Abraços,

[Motta 645]

Com a tabela TEMP ficou rápido e funcionou perfeitamente, como é para gerar um relatório não preciso manter os dados eles podem ser carregados na próxima execução, estou fazendo :

 

Um delete de segurança antes da carga dos dados na TEMp

Carrego a TEMP

Executo a query que usa a TEMP e as tabelas "permanentes"

Deleto a TEMP

Sem COMMIT

 

Falando nisto você pode indicar alguma boa documentação sobre aquelas funções do Oracle

que fazem relatórios gerenciais (agregadoras) ?

 

Abraço.