oracle – ORA-01722 Comparing Character Literal with VARCHAR2 Column

The SELECT statement returns ORA-01722 even though the expression is a string enclosed in single quotes and compared to a column of VARCHAR2(400 CHAR)

the column contains rows with non-numeric data, including special characters; this is as intended. The question is why does the WHERE clause generate ORA-01722 when the value is enclosed in single quotes, i.e. ‘1031404102’.

select * from mxyzab_ss_xm.xx_gzips_even1 e where e.KEYCODE = ‘1232332233’

There are records on mxyzab_ss_xm.xx_gzips_even1 with KEYCODE = ‘1232332233’. As you can see, the error isn’t reproduced consistently.

 1  select * from mxyzab_ss_xm.xx_gzips_even1 e
  2* where e.KEYCODE = '1232332233'
08:57:01 SYS@xyzab965> /
where e.KEYCODE = '1232332233'
                             *
ERROR at line 2:
ORA-01722: invalid number

  1  select count(1) from mxyzab_ss_xm.xx_gzips_even1 e
  2* where e.KEYCODE = '1232332233'
10:17:54 SYS@xyzab965> /

  COUNT(1)
----------
    32192 

oracle – Get sequence value date?

I have an OOB system that has hundreds of tables. Each table has a ROWSTAMP column (integer) that gets populated by a trigger each time a record is created/updated.

All the tables & triggers in the db use the same sequence. Each time a record is updated anywhere in the db, that one sequence is used to get the next available integer. (I suppose I could consider the sequence to be a sort of global sequence).

IBM: Usage of ROWSTAMP column in Maximo tables

It’s unfortunate that those ROWSTAMP columns are integers, and not dates. What I really need in the tables are CHANGEDATE columns. Dates would be useful for data entry troubleshooting, reporting, and analysis in general. But I can’t really justify customizing hundreds of tables with date columns and adding hundreds of triggers in the OOB system. The customized tables wouldn’t be supported by IBM and it wouldn’t be worth the effort/added complexity.


I feel like those ROWSTAMP columns are so close to being useful to me. I can’t help but wonder:

Is there a way to retrieve the date that those ROWSTAMP sequence values were generated?

For example, and I doubt this is possible, could I add a DATE column to the sequence, and then join from my table to the sequence to get the date? Or could I use some sort of logging mechanism on the sequence to put rows (with dates) in a table whenever the next sequence value is used?

converting from oracle to mysql

please i want to convert this codes from oracle to mysql (phpmyadmin) using only ( clauses like select, where, group by, having, order by , union, joint )

  1. SELECT LEVEL, EMPNO, ENAME, JOB, MGR FROM EMP
    CONNECT BY MGR = PRIOR EMPNO
    START WITH MGR IS NULL
    ORDER BY LEVEL;
  2. SELECT LEVEL, ENAME FROM EMP
    WHERE ENAME <> ‘JONES’
    CONNECT BY MGR = PRIOR EMPNO
    START WITH ENAME = ‘JONES’
    ORDER BY LEVEL;
  3. SELECT LEVEL, AVG(SAL) FROM EMP
    CONNECT BY MGR = PRIOR EMPNO
    START WITH MGR IS NULL
    GROUP BY LEVEL;
  4. SELECT ENAME FROM EMP
    WHERE ENAME <> ‘JONES’ AND ENAME <> ‘SCOTT’
    CONNECT BY MGR = PRIOR EMPNO
    START WITH ENAME = ‘JONES’;
  5. SELECT ENAME FROM EMP
    WHERE ENAME <> ‘JONES’
    CONNECT BY MGR = PRIOR EMPNO AND ENAME <> ‘SCOTT’
    START WITH ENAME = ‘JONES’

Oracle SQL grant privilege to fetch xml from a website

I need to fetch data from a website in XML format, I added the website’s certificate to the wallet.
the XML for example can be found here:
https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esummary.fcgi?db=taxonomy&id=273

When I try to get the data with SYSDBA user, I’m able to do it with:

select utl_http.request(
    'https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esummary.fcgi?db=taxonomy' || '&' || 'id=' || '273',
    '',
    '/my/path/to/wallet', 
    'my_wallet_password'
    )
from DUAL;

But when I try the same select from USER1 I get an error:

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1530
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1470
ORA-06512: at line 1
29273. 00000 -  "HTTP request failed"
*Cause:    The UTL_HTTP package failed to execute the HTTP request.
*Action:   Use get_detailed_sqlerrm to check the detailed error message.
           Fix the error and retry the HTTP request.

I know for sure that the problem is with privileges because after giving all privileges to another user (TEST_USER) it worked for this user, but I couldn’t find which privileges I need.
Privileges I tried to add:

grant execute on utl_http to USER1;
grant connect to SPU_RUN;

BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
    (
        HOST => '*.ncbi.nlm.nih.gov*',
        LOWER_PORT => NULL,
        UPPER_PORT => NULL,
        ACE => XS$ACE_TYPE(PRIVILEGE_LIST => xs$name_list('http'),
                             PRINCIPAL_NAME => 'USER1',
                         PRINCIPAL_TYPE => xs_acl.ptype_db
                   )
    );
END;

BEGIN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl => '/sys/acls/utl_tcp.xml',
    description => '',
    principal => 'USER1',
    is_grant => TRUE,
    privilege => 'connect',
    start_date => SYSTIMESTAMP,
    end_date => NULL);
END;

BEGIN
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl => '/sys/acls/utl_tcp.xml',
    principal => 'USER1',
    is_grant => true,
    privilege => 'connect');
    COMMIT;
END;

BEGIN
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl => '/sys/acls/utl_tcp.xml',
    principal => 'USER1',
    is_grant => true,
    privilege => 'resolve');
    COMMIT;
END;    

I’m not a DBA so I’m sorry if i got it all wrong, but I still need to know which privilege is missing.
Thanks.

windows – Oracle 19c listener stops on connection TNS-12541 error

I have just installed Oracle 19c Standard Edition 2 on Windows 10 and created a local database.

I can connect to it with sqlplus / sysdba, but if I try to connect with sqlplus user/password@host:port, the listener automatically stops and results in ORA-12537 error. Then lsnrctl status shows TNS 12541 error. I can restart the listener with lsnrctl startmanually, but it stops again if I try to connect.

I have tried

  1. Changing HOST to 127.0.0.1, localhost or name of PC
  2. Deleting and recreating the listener with Net Configuration Assistant
  3. Re-installing oracle
  4. Deleting log file, disable DIAG_ADR ins ora files
  5. Re-starting the services
  6. Editing HOSTS file, add oracle path to Windows system variables

But same error still appears.

The error log (It wasn’t fully in English, so I used machine translation)

PS C:Windowssystem32> sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 9 09:30:45 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. all rights reserved.


Connect to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> exit
From Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.3.0.0.0 Disconnect
PS C:Windowssystem32> lsnrctl status

LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 09-Jun-2021 09:33:04

Copyright (c) 1991, 2019, Oracle. all rights reserved.

is connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=LAPTOP)(PORT=1521)))
LISTENER STATUS
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Start date 09-Jun-2021 09:14:58
Uptime 0 days 0 hours 18 minutes 9 seconds
Tracking level off
Security ON: Local OS Authentication
SNMP OFF
Listener parameter file D:ProgramOraclenetworkadminlistener.ora
Listener log file D:ProgramOraclenetworkloglistener.log
Listening endpoint summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=LAPTOP)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=. pipeEXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=LAPTOP)(PORT=5500))(SECURITY=(my_wallet_directory=D:PROGRAMadminorclxdb_ wallet))(Presentation=HTTP)(Session=RAW))
Service Summary...
The service "CLRExtProc" contains 1 instance.
  Instance "CLRExtProc", status UNKNOWN, contains 1 handler for this service...
Service "orcl" contains 1 instance.
  Instance "orcl", status READY, contains 1 handler for this service...
Service "orclXDB" contains 1 instance.
  Instance "orcl", status READY, contains 1 handler for this service...
Command executed successfully
PS C:Windowssystem32> sqlplus sys/123456@localhost:1521/orcl

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 9 09:33:45 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.

ERROR:
ORA-12537: TNS: Connection closed


Please enter the username: sys as sysdba
Enter the password:

Connect to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> exit
From Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.3.0.0.0 Disconnect
PS C:Windowssystem32> lsnrctl status

LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 09-Jun-2021 09:36:15

Copyright (c) 1991, 2019, Oracle. all rights reserved.

is connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=LAPTOP)(PORT=1521)))
TNS-12541: TNS: No listener
 TNS-12560: TNS: Protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 61: Unknown error
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS: No listener
 TNS-12560: TNS: Protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 2: No such file or directory

listener.ora:

# listener.ora Network Configuration File: D:ProgramOracleNETWORKADMINlistener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:ProgramOracle)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:ProgramOraclebinoraclr19.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = LAPTOP)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

DIAG_ADR_ENABLED_LISTENER = OFF

tnsnames.ora:

# tnsnames.ora Network Configuration File: D:ProgramOracleNETWORKADMINtnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = LAPTOP)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = LAPTOP)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )


sqlnet.ora:

# sqlnet.ora Network Configuration File: D:ProgramOracleNETWORKADMINsqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

DIAG_ADR_ENABLED = OFF

HOSTS:

# Copyright (c) 1993-2009 Microsoft Corp.
#
# This is a sample HOSTS file used by Microsoft TCP/IP for Windows.
#
# This file contains the mappings of IP addresses to host names. Each
# entry should be kept on an individual line. The IP address should
# be placed in the first column followed by the corresponding host name.
# The IP address and the host name should be separated by at least one
# space.
#
# Additionally, comments (such as these) may be inserted on individual
# lines or following the machine name denoted by a "https://dba.stackexchange.com/#" symbol.
#
# For example:
#
#      102.54.94.97     rhino.acme.com          # source server
#       38.25.63.10     x.acme.com              # x client host

# localhost name resolution is handled within DNS itself.
#   127.0.0.1       localhost
#   ::1             localhost
127.0.0.1   localhost   LAPTOP
::1             localhost

sql – Identificador no valido ORACLE

estoy modificando una query para darle performance. Esta devuelve millones de registros.
Lo que hice fue lo siguiente:

  1. Transformar el producto cartesiano en JOIN’s
  2. Transformar EXISTS en JOIN
  3. Transformar NOT EXISTS en LEFT JOIN
  4. Colocar las consultas en el FROM

Me quedo la siguiente query pero me arroja un error de identificador no valido.
Es decir ¿no se puede usar un campo de la tabla obligaciones@dbl_cldd para MATCHEAR con otro campo de otra tabla obligaciones_genericas@dbl_cldd que se encuentra en una subconsulta de una subconsulta?

En el código donde dice “–IDENTIFICADOR NO VALIDO!!!!!!!!!!!!!!” es donde arroja el error

  SELECT     'A' caso_tipo, OBN_ID, OBN_SPO_ID, OBN_TIPO_OBJETO_ID, OBN_OBJETO_ID, OBN_PERIODO, OBN_CUOTA, OBN_FECHA_VTO_ORIGINAL, OBN_SALDO, 
             OBN_PEO_ID_FORMAL, OBN_PEO_ID_MATERIAL, OBN_FAO_ID, OBN_FAO_ID_CUOTA, OBN_ICP_CCO_ID, OBN_ICP_IPO_ID, OBN_VTO_PRORROGADO/*count(*)*/
  FROM       obligaciones@dbl_cldd o 
  JOIN
             (SELECT      oga.oga_id "oga_id"
              FROM        obligaciones_genericas@dbl_cldd oga
              WHERE      oga.oga_fecha_baja IS NULL
              AND         oga.oga_icp_ipo_id IN (5,600,58)
              AND         oga.oga_periodo >= '2015'
              AND         oga.OGA_ICP_CCO_ID IN ('101','201','601','701','581','582')) b
  ON        (o.obn_oga_id = b."oga_id")
  LEFT JOIN
            (SELECT mcc.mcc_obn_id "mcc_obn_id"
            FROM movimientos_cuenta_corriente@dbl_cldd mcc 
            WHERE mcc.mcc_fecha_baja IS NULL 
            AND mcc.mcc_tipo_origen IN ('PAO','FAO','CRE','PSV')) c
  ON        (c."mcc_obn_id" = o.obn_id )
  JOIN
            (SELECT      oo.obn_objeto_id "obn_objeto_id", oo.obn_tipo_objeto_id "obn_tipo_objeto_id", oo.obn_periodo "obn_periodo"
             FROM        obligaciones@dbl_cldd oo
             JOIN
                        (SELECT      og.oga_id "oga_id"
                         FROM        obligaciones_genericas@dbl_cldd og
                         WHERE       og.oga_fecha_baja IS NULL
                         AND         og.oga_icp_ipo_id = o.OBN_ICP_IPO_ID --IDENTIFICADOR NO VALIDO!!!!!!!!!!!!!!
                         AND         og.oga_periodo = o.obn_periodo --IDENTIFICADOR NO VALIDO!!!!!!!!!!
                         AND         og.oga_periodo >= '2015'
                         AND         og.OGA_ICP_CCO_ID IN ('100','200','600','700','580')) aa
             ON           (oo.obn_oga_id = aa."oga_id")
             JOIN
                         (SELECT mcc.mcc_obn_id "mcc_obn_id"
                          FROM movimientos_cuenta_corriente@dbl_cldd mcc 
                          WHERE mcc.mcc_fecha_baja IS NULL 
                          AND mcc.mcc_tipo_origen IN ('PAO','FAO','CRE','PSV')) bb
             ON            (oo.obn_id  = bb."mcc_obn_id")
             WHERE       oo.obn_fecha_baja IS NULL
             AND         oo.obn_peo_id_material IN ('PTET','PTFT','VPPET','VPPFT','VPTET','VPTFT','PPET','PPFT','AFPPG')) d
  ON         (o.obn_objeto_id = d."obn_objeto_id" and o.obn_tipo_objeto_id = d."obn_tipo_objeto_id" and o.obn_periodo = d."obn_periodo")
  WHERE     c."mcc_obn_id" is null
  AND       o.obn_fecha_baja IS NULL
  AND       o.OBN_CUOTA NOT IN (50,80,81,90,91,45); ```

oracle – host name resolution in dba_network_acls

When you create an ACL with:

BEGIN
 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
 (
 host => 'subdomain.acme.com',
 lower_port => 10000,
 upper_port => 65535,
 ace => xs$ace_type(privilege_list => xs$name_list('jdwp'),
 principal_name => 'myusername',
 principal_type => xs_acl.ptype_db)
 );
END;
/

and then a client tells Oracle to connect to a numeric ip address will Oracle use name resolution to match the host name with the ip address and if so, in which direction? Will Oracle try to make a reverse lookup for the ip address and compare the host names or will it make a domain name lookup and compare the ip addresses?

Couldn’t connect to my oracle database with Tableau Desktop


Your privacy


By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.




oracle – Add a foreign key to a composite primary key on a UNF database

Working on an over 30 y.o. UNF Oracle 8i database (which has lately been migrated to 19c).

Here a summarized description of the 3 weirdly linked tables: Item, Category, SubCategory.

create table Item (
  identity     VARCHAR2(20) not null,
  description  VARCHAR2(60),
  category     VARCHAR2(8),
  subcategory  VARCHAR2(8)
);

create table Category (
  identity     VARCHAR2(8) not null,
  description  VARCHAR2(30)
);

create table SubCategory (
  identity     VARCHAR2(8) not null,
  description  VARCHAR2(30),
  category     VARCHAR2(8)
);

It seems appropriate to me to specify that the tables cannot be modified since they are used, in their current form, by a variety of applications (>40) that should not be modified.

The primary keys (although not defined in the above example) are, logically, identity for the Item and Category tables, and Identity + Category for the SubCategory table.

I’m wondering how to create foreign keys between Item -> SubCategory and SubCategory -> Category.

The purpose being to build a working reference (for our team) to develop new applications using this database without reengineering it.

oracle 11g r2 – ORA-01858: a non-numeric character was found where a numeric was expected

I am beginner in oracle and need help regarding following query:

select admin_id, to_date(date_created, 'DD-MM-YYYY'), name, mobile, email, (select status from status where status.status_id = admin.status_id) from admin;

Here are my tables:

SQL> desc admin
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADMIN_ID                  NOT NULL NUMBER(5)
 STATUS_ID                 NOT NULL NUMBER(5)
 DATE_CREATED                       TIMESTAMP(6)
 STATUS_DATE                        DATE
 NAME                           VARCHAR2(45)
 MOBILE                         VARCHAR2(20)
 EMAIL                          VARCHAR2(110)

SQL> desc status
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATUS_ID                 NOT NULL NUMBER(5)
 STATUS                         VARCHAR2(36)