Perguntas[oracle](coding)

Estou usando o DBeaver como meu gerenciador de banco de dados, estou tentando fazer uma conexão com o OracleDB com vários esquemas e cada esquema com seu próprio nome de usuário/senha.

Gostaria de saber se existe uma maneira de configurar esses vários nomes de usuário/senhas para esses esquemas em uma única conexão de banco de dados?

por exemplo: exemplo de conexão com o banco de dados

exemplo:
 Esquema
   sc1 - conecte-se a isto  
   sc2 - conecte-se a isso também
    .
    .
    .
   scn - e isso

Criei uma página de detalhes mestre. Em uma página mestre, criei um Processo de Página que chama um procedimento armazenado ao clicar no botão e salva um registro mestre em uma tabela, o que funciona bem como esperado. Em uma página de detalhes, criei um Processo de Página semelhante que chama um procedimento armazenado ao clicar no botão e esperava salvar os registros de detalhes em uma tabela, mas quando o botão é clicado, ele exibe a mensagem Linhas Atualizadas com Sucesso, mas na verdade nenhuma linha foi inserida na tabela. Não consegui descobrir por que o registro não foi inserido na tabela. O procedimento funciona bem quando chamado diretamente do banco de dados.

Aqui está meu Processo de Página - Opção Inserir

Aqui está a página onde estou adicionando o registro de detalhes.

quando cliquei no botão Criar o registro não foi inserido na tabela.

Espero que o registro seja inserido na tabela de detalhes.

Por outro lado, quanto às colunas LOB, não consigo descobrir como dar o nome que desejo ao segmento de armazenamento criado para uma coluna XMLTYPE dentro do script CREATE TABLE. Isso é possível no Oracle 19c e alguém pode eventualmente postar um exemplo?

CREATE TABLE tab (
  doc   XMLTYPE
)
XMLTYPE COLUMN (doc) STORAGE CLOB { name the storage segment tab#doc_lob } 

Recebi um script que continha isto:

alter table TABLE_1 add constraint pk_1 primary key(ID_1)
         using index
         tablespace schema1_ind
   exceptions into junk;

O problema é que quando executo, ele gera este erro: ORA-02445: exceptions table not found

Não estou muito familiarizado com tabelas de exceções, e há muito pouca informação que eu possa encontrar online. Encontrei este pedaço de documentação da Oracle, https://docs.oracle.com/cd/A58617_01/server.804/a58312/newch2h5.htm que diz isto:

ORA-02445: tabela de exceções não encontrada

Causa: A tabela de exceções declarada explícita ou implicitamente não existe.

Ação: Se o nome correto da tabela de exceções foi usado, crie a tabela e tente novamente o comando enable.

e também encontrei isso https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/EXCEPTIONS.html :

EXCEPTIONS contém informações sobre violações de restrições de integridade. Esta tabela é criada pelo script utlexcpt.sql.

Mas, estranhamente, quando executo um script diferente que também usa "lixo", ele é executado sem erro:

alter table TABLE_2 add constraint PK_2
    primary key(ID_2)
        using index tablespace schmea1_ind2
    storage (initial 8M NEXT 2M)
    exceptions into junk;

Todos esses scripts são executados em uma determinada ordem, e o de baixo é executado primeiro, sem problemas, vários outros scripts são executados e, então, o de cima tenta ser executado e falha.

O problema é que não sei como encontrar essa tabela de exceções 'junk'. Tentei executar o primeiro script sozinho e funcionou, mas depois, quando olhei em DBA_OBJECTS, não havia nada chamado 'junk'. Nada mesmo. Em nenhum lugar nada cria ou remove uma tabela chamada 'junk'.

Alguém pode explicar por que estou recebendo esse erro 'exceptions table not found'? Onde estão as exceptions tables, se não em dba_objects? Por que a primeira instrução falha, mas a segunda é bem-sucedida?

Qualquer conhecimento sobre tabelas de exceções seria útil, pois encontrei muito pouca documentação.

Baixei o SQLcl, descompactei-o, configurei a variável de sistema PATH para apontar para seu \bin, mudei para o prompt de comando (Windows 10), mas nunca consegui me conectar ao meu banco de dados (11g) com ele. sql /nolog foi o máximo que consegui.

Primeiro, ele disse:

“Mensagem de erro = nenhum ocijdbc21 em java.library.path: …”,

Pesquisei e surpreendentemente descobri que ele precisa do "instant client" da Oracle (a Oracle diz algo como "basta descompactar e conectar"). Eu baixo e instalo o IC, executo e então outra mensagem de erro:

“Mensagem de erro = Versão incompatível de libocijdbc[Jdbc:211200230906, Jdbc-OCI:2115000”

Eu tentei outra ver. do IC (ver. 19), mas recebi “cannot find ..” ou algo assim, então voltei para a “versão incompatível” (21_15). Estou usando o SQLcl release 24.2.0.180.1721.

Você pode me ajudar com isso?

Tenho uma tabela onde desejo obter registros que MODIFIED_DATEsejam iguais à contagem de2

Por exemplo: se para job_id= 1234 houver 2 registros com data MODIFIED_DATE= 04-10-2024então ele deve filtrar esse registro.

Name                   Null?    Type           
---------------------- -------- -------------- 
JOB_PROGRESS_ID        NOT NULL NUMBER         
JOB_ID                          NUMBER         
STATUS_ID                       NUMBER         
HOTO_OFFERED_DATE               DATE           
HOTO_ACCEPTENCE_DATE            DATE           
LIT_ACCEPTENCE_DATE             DATE           
APPROVED_BY                     NVARCHAR2(200) 
APPROVED_DATE                   DATE           
REJECTED_BY                     NVARCHAR2(200) 
REJECTED_DATE                   DATE           
APPROV_REJECT_REMARK            NVARCHAR2(255)     
ABD_COMPLETED_LENGTH            NUMBER         
NE_SPAN_LENGTH                  NUMBER(10,4)   
CREATED_BY                      NVARCHAR2(200) 
CREATED_DATE           NOT NULL DATE           
MODIFIED_BY                     NVARCHAR2(200) 
MODIFIED_DATE                   DATE 

Tenho um script que copia registros que atendem a uma condição específica para uma tabela temporária. A tabela original é então truncada e os registros são copiados de volta.

A instrução usada para criar a tabela temporária é;

create table ARCHIVE_TMP as select * from ORIGINAL_TABLE where TIMESTAMP_UPDATED < '2017-10-04'

E a declaração para copiar os registros novamente é;

insert /*+ APPEND */ into ORIGINAL_TABLE select * from ARCHIVE_TMP

Quando a segunda linha é executada no script, ela gera a exceção ORA-01007: variable not in select list, mas se eu executar manualmente a segunda instrução, ela funciona como esperado.

Verifiquei se o usuário tem privilégios CREATE e INSERT, pois houve um problema anteriormente em que os privilégios foram atribuídos a uma função em vez do usuário.

Também tentei especificar os nomes das colunas na selectinstrução em vez de usar select *, mas isso não ajudou.

A seguir está o roteiro completo;

create or replace procedure TRUNCATE_EXPIRED_ARCHIVE_DATA
(
    p_retention_period      in number   := 84, 
)
as
begin
    declare
        cursor table_cursor is
            select      owner, table_name, column_name, data_type
            from        all_tab_columns
            where       table_name like '%!_A' escape '!'
                        and column_name like 'TIMESTAMP!_%' escape '!'
            group by    owner, table_name, column_name, data_type           
            order by    table_name, column_name;
        RetentionDt     date := null;
        ExpiredCount    number := 0;
        InsertedCount   number := 0;
        TableExists     number := 0;
        ExpiredSql      varchar2(500);
        SelectSql       varchar2(800);
        SqlStmt         varchar2(1300);
    begin
        RetentionDt := add_months(sysdate, p_retention_period * -1);
        for table_rec in table_cursor loop
            begin
                -- Check if there are any expired records...
                SelectSql := 'select count(*) from ' || table_rec.owner || '.' || table_rec.table_name;
                ExpiredSql := ' where ' || table_rec.column_name || ' < ''' || RetentionDt || '''';
                SqlStmt := SelectSql || ExpiredSql;
                execute immediate SqlStmt into ExpiredCount;
                if (ExpiredCount > 0) then
                    -- Drop the temporary table if it already exists...
                    SelectSql := 'select count(*) from tab where tname = ''ARCHIVE_TMP''';
                    execute immediate SelectSql into TableExists;
                    if (TableExists > 0) then
                        SelectSql := 'drop table REODT_PROD.ARCHIVE_TMP';
                        execute immediate SelectSql;
                    end if;
                    -- Transfer the records to be retained to the temporary table...
                    SelectSql := 'create table REODT_PROD.ARCHIVE_TMP as select * from ' || table_rec.owner || '.' || table_rec.table_name || ' where ' || table_rec.column_name || ' >= ''' || to_char(RetentionDt, 'YYYY-MM-DD') || '''';
                    execute immediate SelectSql;
                    InsertedCount := sql%rowcount;
                    commit;
                    if (InsertedCount > 0) then
                        SelectSql := 'truncate table ' || table_rec.owner || '.' || table_rec.table_name;
                        dbms_output.put_line('Truncate Table    : ' || SelectSql);
                        execute immediate SelectSql;
                        SelectSql := 'insert /*+ APPEND */ into ' || table_rec.owner || '.' || table_rec.table_name || ' select * from REODT_PROD.ARCHIVE_TMP';
                        execute immediate SelectSql into InsertedCount;
                        commit;
                    end if;
                end if;
            end;
        end loop;
    end;
end;
/

 SELECT
     *
     FROM
     (
     SELECT
    ordr.program,
    ordr.order_id,
    ordr.part_no,
    ordr.order_no,
    ordr.actual_start_date,
    ser.serial_no,
    oper.asgnd_machine_id,
    oper.time_stamp,
    oper.updt_userid,
    oper.oper_status,
    oper.oper_no ,
      
    CASE
      WHEN oper.oper_no IN ('1234') THEN 'paint_bike'  
 
    END AS oper_type
  
    FROM
    sfmfg.sfwid_order_desc ordr
    LEFT JOIN sfmfg.sfwid_serial_desc ser
    ON ordr.order_id = ser.order_id
    LEFT JOIN sfmfg.sfwid_oper_desc oper
     ON ordr.order_id = oper.order_id
     AND oper.step_key = -1
     AND ordr.program IN ('bike')
     AND oper.oper_no IN ('1234')
    WHERE
    ordr.actual_start_date > TO_DATE('08/01/2023', 'MM/DD/YYYY')
     AND ordr.part_no LIKE '123'
     AND ser.serial_no LIKE '123')

e quero adicionar esse pivô no final dele.

    PIVOT (
    MAX(asgnd_machine_id) AS asgnd_machine_id,
    MAX(time_stamp) AS time_stamp,
    MAX(updt_userid) AS updt_userid,
    MAX(oper_status) AS oper_status FOR oper_type IN ('bike_Cutting' AS bike_Cutting, 'bike_Prep' AS         bike_Prep,
       )

Estou tentando fazer o pivô funcionar, mas estou recebendo erros de definição ambígua de coluna para minha primeira instrução select quando adiciono um alias como este para a primeira instrução select

 SELECT 
    a1.program,
    a1.order_id,
    a1.customer_description,
    a1.stiffener_type,
    a1.mold_tool_no,
    a1.part_no,
    a1.order_no,
    a1.actual_start_date,
    a1.serial_no,
    a1.asgnd_machine_id,
    a1.time_stamp,
    a1.updt_userid,
    a1.oper_status,
    a1.plan_title,
    a1.oper_type  

me dá erros de identificador inválido

Preciso acumular dados em uma consulta hierárquica como esta:

select 
id, 
prior id as parent_id,
count * prior count  --need to be accumulated through whole hierarchy like sys_connect_by_path 
from table
start with id = 1
connect by prior id = parent_id

Exceto que eu só posso acessar a linha anterior, então não vai funcionar corretamente. E eu não posso usar o alias definido na SELECTcláusula atual para a linha anterior:

select 
id, 
prior id as parent_id,
count,
(count * prior whole_count) as count_product --not allowed to do this :(
from table
start with id = 1
connect by prior id = parent_id

O que estou esquecendo? SYS_CONNECT_BY_PATHfazer exatamente o que preciso (exceto concatenação em vez de multiplicação), então deve ser possível. Posso fazer isso usando WITHcláusula, mas por algum motivo é extremamente lento, tipo meio segundo para CONNECT_BYvs ~20 para WITH.

Saída esperada:

Fiddle usando cláusula WITH (de MT0)

Boa tarde comunidade,

Tenho um script PLSQL executado no ORACLE 19 que está exibindo um erro que não entendo.

O script descarta qualquer índice existente antes de recriá-los. Os objetos são definidos em uma lista de tipo-off para evitar repetição de instruções

• remover um índice inexistente está OK (basta capturar a exceção e continuar)
• criar um índice que está falhando deve continuar a criar os índices restantes até o fim

Meu script PLSQL é

set serveroutput on;
WHENEVER SQLERROR EXIT SQL.SQLCODE;

DECLARE
    index_not_exists EXCEPTION;
    table_not_exists EXCEPTION;
    PRAGMA EXCEPTION_INIT (index_not_exists, -1418);
    PRAGMA EXCEPTION_INIT (table_not_exists, -942);

    TYPE ind_list IS TABLE OF VARCHAR(100) NOT NULL;
    ind_to_drop ind_list := ind_list('sst_ind1', 'sst_ind2');
    /* first index is wrong because col0 does not exist */
    ind_to_create ind_list := ind_list('create index sst_ind1 on sst_table(col0)', 'create index sst_ind2 on sst_table(col2)');

    sql_to_exec varchar(200);
    is_all_index_created boolean := TRUE;

BEGIN
    BEGIN
        EXECUTE IMMEDIATE 'drop table sst_table cascade constraints purge';
    EXCEPTION
        WHEN table_not_exists THEN
        NULL;
    END;

    EXECUTE IMMEDIATE 'create table sst_table (col1 number, col2 number)';
    EXECUTE IMMEDIATE 'create index sst_ind1 on sst_table (col1)';
    EXECUTE IMMEDIATE 'create index sst_ind2 on sst_table (col2)';

    FOR l_index IN ind_to_drop.FIRST..ind_to_drop.LAST LOOP
        BEGIN
            sql_to_exec := 'drop index ' || ind_to_drop(l_index);
            dbms_output.put_line(sql_to_exec);
            EXECUTE IMMEDIATE sql_to_exec;
        EXCEPTION
            WHEN index_not_exists THEN
                dbms_output.put_line('no index ' || ind_to_drop(l_index));
        END;
    END LOOP;

    FOR l_index IN ind_to_create.FIRST..ind_to_create.LAST LOOP
        BEGIN
            dbms_output.put_line(ind_to_create(l_index));
            EXECUTE IMMEDIATE ind_to_create(l_index);
        EXCEPTION
            WHEN OTHERS THEN
                dbms_output.put_line('ERROR WITH ' || ind_to_create(l_index));
                is_all_index_created := FALSE;
        END;
    END LOOP;

    IF NOT is_all_index_created THEN
        RAISE_APPLICATION_ERROR(-20001, 'PROBLEM CREATING INDEX');
    END IF;

END;
/

exit

Quando executo meu script pelo sqplus tenho o ERRO abaixo não entendi

sqlplus xxxxx/yyyyy@zzzzzz @ubscls_6711.sql

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 25 17:04:11 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

drop index sst_ind1
drop index sst_ind2
create index sst_ind1 on sst_table(col0)
ERROR WITH create index sst_ind1 on sst_table(col0)
create index sst_ind2 on sst_table(col2)
DECLARE
*
ERROR at line 1:
ORA-20001: PROBLEM CREATING INDEX
ORA-06512: at line 49


Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Alguém pode me ajudar a consertar meu problema? Provavelmente é simples, mas não consigo encontrar o motivo.

Obrigado