Questions tagged [oracle19c]
This tag is for questions which deal specifically with Oracle RDBMS version 19c. General question regarding the Oracle RDBMS product should use the `oracle` tag.
568
questions
15votes
1answer
29kviews
Hibernate Dialect for Oracle 19
One of our database was upgraded to Oracle 19c and now my application is unable to connect to database
I am getting the following error.
HHH000400: Using dialect: org.hibernate.dialect....
- 3,597
6votes
1answer
235views
Initial extent size when converting to partitioned table
Working in an Oracle 19c database on Linux x86/64 trying to convert non-partitioned table to partitioned table.
Since Oracle12, alter table modify partition has been available to convert non-...
- 63
6votes
1answer
937views
SQL Developer doesn't display XML
Oracle's SQL Developer doesn't display the content of XML columns when the datatype XMLType is used. The first lines are displayed ok (if Preferences > Database > Advanced > Display XML Value in Grid ...
- 7,835
5votes
2answers
5kviews
how to install oracle database express on mac m1
I want to Install Oracle Database Express on macbook m1 but it only supports linux and windows, I downloaded Sql developer but I m unable to download Oracle database. I tried to install using docker ...
- 71
4votes
1answer
3kviews
Using JSON_ARRAYAGG in a Sub-Select with ORDER BY gives error
Using Oracle 19c:
I have the following query in which a Sub-Select (linked to the main Select via plans1_.ID) uses the JSON_ARRAYAGG function.
select
... , /* Other columns... */
(SELECT
...
- 8,326
4votes
1answer
7kviews
How to Register Pluggable Database(PDB) with new created LISTENER
I am working with Oracle 19c in centos 7. After Oracle installation, i created new Container Database(CDB) with Plugguble Database(PDB) using DBCA.
I am working with port number=1522
[oracle@ol8-19 ~]$...
- 111
4votes
2answers
855views
Can we use System.Data.OracleClient in .Net against Oracle 19c?
so we migrated Oracle 12.2 to 19c and the Asp.Net app that connects to oracle service is now failing with the following error:
Oracle 12560: TNS Protocol Adapter Error
before anything, I'd like to ...
- 41
4votes
1answer
2kviews
Couldn't determine Dialect for "oracle"
I am using Spring boot(2.3.5), Oracle19c DB, and Hibernate(5.4).
I tried to make multi-datasource connection, but I keep getting a dialect error Couldn't determine Dialect for "oracle".
...
- 895
4votes
2answers
219views
Avoid full table scan with SELECT using PL/SQL table
Test data
CREATE TABLE parent AS ( SELECT ROWNUM AS id, 'XXX' AS dummy FROM dual CONNECT BY ROWNUM <= 1000 );
CREATE UNIQUE INDEX idx_parent ON parent(id);
CREATE TABLE child AS ( SELECT CEIL(...
- 52.2k
4votes
0answers
66views
How to forbid using the same table alias twice in a query?
I am running Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.8.0.0.0.
Consider the following example:
create table tab_a as
select 1 as id from dual
union ...
- 53
4votes
1answer
128views
Simple Query with SQL injection protection takes magnitudes longer than without
I'm very inexperienced on Oracle. What's going on here?
Query A:
SELECT COUNT(*)
FROM MUHSCHEMA.MUH_TABLE
WHERE MUH_DATE = TO_DATE(
TRIM(
'''' ...
- 2,136
3votes
6answers
7kviews
\[INS-30014] Unable to check whether the location specified is on CFS
On migrating my local Oracle DB on Windows, from 11g to 19c, while running the setup.exe, I got a:
[INS-30014] Unable to check whether the location specified is on CFS
- 124
3votes
1answer
16kviews
Connecting to Oracle19C Server using Oracle11g client
all
I'm trying to connect to an Oracle 19C database. I have installed two Oracle Clients (11g and 12c) because we need to support legacy programs. I can connect with no issue with any user using ...
- 43
3votes
1answer
88views
Use inline function in subquery (in a WHERE clause)
In a COTS system, I am able to enter a SQL WHERE clause.
Example of WHERE clauses that work:
worktype = 'Corrective Maintenance'
or
1 = (
with
cte as (select 1 as calc from dual)
...
- 74
3votes
2answers
1kviews
Oracle DB - Connecting 11gR2 DB from 19c Client - ORA-12650 : No Common Encryption or data integrity algorithm
We have Oracle 19c Client installed in our application Unix server.
When we connect to a 19c Database server from our application Unix server, it is working as expected.
But when we try Connecting ...
- 97
3votes
1answer
295views
How to Change Static and Dynamic parameters Oracle 19c
I am working with Oracle 19c in Centos 7. After the installation, I want to Change Static and dynamic parameters of the newly created database.
After that, I need to read the alert log to witness the ...
- 193
3votes
2answers
853views
Any impact on existing SQL, PL/SQL scripts and data model due to Oracle DB upgrade from 12cto 19c?
I wanted to discuss my understanding for impact on existing SQL, PL/SQL scripts and data model due to DB upgrade from 12cto 19c:
As per my own research and understanding-
There is no identified ...
- 49
3votes
2answers
539views
Oracle Apex 19.2: Cannot resolve the "schema is reserved or restricted" issue
I attempted to create a workspace in APEX using an existing schema called PEOPLE and it gave the error message "The schema is reserved or restricted". I tried with other existing schemas that I ...
- 49
3votes
1answer
270views
How to auto-increment oracle table from Entity Framework code-first approach?
I have the following table where I just added the decorator DatabaseGenerated like so:
public class Reference
{
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Key]
...
- 1,122
3votes
1answer
99views
Spring JPA slow query with Oracle using like and upper
I encounter an optimisation problem and I can't figure out why my query is so slow.
In production when searching for document.name the application takes about 4 minutes when there are no results and ...
- 31
3votes
1answer
386views
Serializing/marshalling package level user define record types to JSON
I'm trying to convert a record type, defined in a PL/SQL package, to JSON.
I found that in SQL, I can use select json_object(*) from SomeTable, to return objects that have a property for each column ...
- 111k
2votes
2answers
294views
How to get lowest and highest date from a table column?
Am trying to get the lowest date and highest date from a table column. Am using below SQL query for that.
select MIN(trunc(TO_DATE(MOD_BEGIN, 'YYYYMMDDHH24MISS'))) AS MIN_DATUM
, MAX(trunc(...
- 55
2votes
3answers
649views
I am creating a PL SQL function in oracle 19c to find largest among two numbers
I have created the function named findLargest as show below :
And I am trying to call it ,but i am getting errors as show below :
SQL> CREATE FUNCTION findLargest (x IN NUMBER, y IN NUMBER)
2 ...
2votes
3answers
6kviews
Oracle Database Setup.exe file not executing after extracting into folder on windows
I am trying to install oracle 19c DB but after extraction the setup.exe file isn't executing. I have tried it with admin privileges, still doesn't work.
- 162
2votes
2answers
419views
Syntax for creating foreign key index inline in create table statement
I want to create a table bar with a named foreign key constraint backed by a named index. I would like to do that with an inline definition in the create table DDL statement. When looking at the ...
- 4,202
2votes
1answer
84views
Group BY query utilizes indexes, but window function query doesn't
I use a COTS system by IBM called Maximo Asset Management. The system has a WORKORDER table with 350,000 rows.
Maximo has a concept called relationships that can be used to pull in data from related ...
- 74
2votes
2answers
79views
What limit should I set for Oracle character types, when there is no business constraint?
I'm used to designing for PostgreSQL, whose character types don't have performance differences, and where the community's advice is that an explicit limit only exists to enforce a business rule.
...
- 587
2votes
1answer
61views
Oracle - LAST_DAY and TRUNC - order of execution
I am getting unexpected result when using combination of LAST_DAY and TRUNC:
select LAST_DAY(TRUNC(sysdate, 'DAY')) from dual;
--31-JUL-2021 00:00:00
select TRUNC(LAST_DAY(sysdate), 'DAY')from dual;
-...
- 510
2votes
1answer
343views
Can two transactions modify the same row at the same time in Oracle?
What happens if two transactions try to modify the same row simultaneously? Normally, once the row is modified, the other transaction waits until the first one performs commit or rollback. But what if ...
- 689
2votes
1answer
158views
LPX-00209: PI names starting with XML are reserved when parsing soap response
I parsed a SOAP response. Next I want to check the value of <HasError> like so:
DECLARE
l_clob CLOB;
l_error VARCHAR2(20);
BEGIN
--SOAP RESPONSE
l_clob :=
'
<?xml version="1.0" ...
2votes
1answer
388views
Oracle SQL - set values for multiple parameters in the same alter_session_set_clause
It's said in documentation that
You can set values for multiple parameters in the same alter_session_set_clause
but there are no examples how to do it.
I'm trying to make it work with a query:
alter ...
2votes
1answer
343views
Update a json column after use JSON_MERGEPATCH
Having this
create table departments_json (
department_id
integer
NOT NULL
CONSTRAINT departments_json__id__pk PRIMARY KEY,
department_data
CLOB
NOT NULL
CONSTRAINT ...
- 421
2votes
1answer
6kviews
Oracle 19c installation error : [INS-35179] Current available memory is less than the required available memory (1,597MB) for creating DB
I am trying to install the oracle 19c and during installation met with a memory related issue " [INS-35179] Current available memory is less than the required available memory (6,537MB) for ...
- 109
2votes
1answer
3kviews
Oracle Database 19c Not available tnsnames.ora file
I am working with Oracle 19c in Centos. After Oracle installation, not available tnsnames.ora file.but the listener.ora file is available
- 193
2votes
2answers
340views
External table read issue on 19c
we are migrating DB from Oracle 11g -> 19 and facing issue with external table. Old and new db have exactly same table definition and pointing to the same file (db running on different hosts but ...
- 25
2votes
1answer
75views
What's the equivalent to Postgres's fsync setting in OracleDB?
I have an OracleDB database running in a CI pipeline. It gets setup and torn-down on each test run; and it's bloody slow.
Since I don't care about integrity, is it possible to run OracleDB with no ...
- 312
2votes
1answer
929views
Materialized view fast refresh - insert and delete when updating base table
Hello fellow Stackoverflowers,
TLDR: Are MVIEWs using UPDATE or DELETE + INSERT during refresh?
some time ago I ran into an obscure thing when I was fiddling whit materialized views in Oracle. Here is ...
- 83
2votes
2answers
1kviews
Oracle 19c connecting to ColdFusion 2018
ColdFusion 2018 on Windows 2016 Server, currently using Oracle 12c.
My institution is planning an Oracle upgrade to 19c, I have found nothing in the CF documentation about connections to a 19c ...
- 85
2votes
2answers
3kviews
Unlocking SCOTT user in oracle 19c
Trying to unlock SCOTT in oracle 19c.
alter user scott account unlock identified by tiger;
ERROR Message:
ORA-01918: user 'SCOTT' does not exist
- 21
2votes
1answer
667views
Oracle 19C database issue
I have a package working fine in 11g version.
But when I deploy the same package in 19c version, the behavior is different.
PFB the description.
Package specification has an cursor and created a ...
- 21
2votes
5answers
112views
I want to change a number to a hours:minutes
I want to do a request that change a number up to 4 digits('X', 'XX', 'XXX'or 'XXXX') to a format HH:MM
For instance, I expect the output to be like this:
7 => 00:07
17 => 00:17
317 =&...
- 60
2votes
2answers
500views
wk buffer error when running .get_WKT Oracle Spatial function on SDO_GEOMETRY objects
My company runs Oracle 19 and we recently (October 25) installed the October Oracle quarterly patch. Since the patch was applied, I have been unable to run the .Get_WKT function without frequently ...
- 33
2votes
1answer
92views
PL/SQL/Oracle DB: Procedure: ORA-29013: SSL MAC verification failure (Database 19c)
Procedure that was developed on database Oracle 12c returns the following error, when ran on database Oracle 19c.
ORA-29273: HTTP request failed
ORA-29013: SSL MAC verification failure
ORA-06512: at &...
- 395
2votes
1answer
72views
Oracle doesn't inherit number scale and precision for columns in a view consumed by Kafka Connect
I want to force a column from my view to have an exact scale and precision. My current preset is as follows:
create or replace view v1
as
select cast(1 as number(1,0)) as some_val,
'X' ...
- 325
2votes
1answer
148views
How to use a WHERE clause with Oracle JSON_OBJECT?
I am trying to produce some oracle records snapshots using JSON_OBJECT but, surprisingly it seems that I cannot use a variable in the clause statement?
CREATE TABLE SAMPLE(
person_id NUMBER,
...
- 2,046
2votes
1answer
665views
PL/SQL Use table variable as query table
In PL/SQL is it possible to use a variable as a query a table?
Tried:
declare
TYPE t_name IS TABLE OF varchar(50) INDEX BY PLS_INTEGER;
v_names t_name;
begin
select name bulk collect into ...
- 1,049
2votes
1answer
1kviews
Oracle 19c Open_cursor exceeded issue
We have same stored procedure exist in Oracle 10g and 19c with same set of data and setup.
The procedure does so many data fetching and manipulation.
When we execute with same set of data(let say ...
- 21
2votes
1answer
119views
Bulk Collect on collection Type 'object' in Oracle
I have an collection Type object
create or replace
TYPE "COLLECTION_OBJECT" AS OBJECT
(
attribute1 integer,
attribute2 date,
attribute2 integer,
) ;
Then inside my pl/...
- 149
2votes
1answer
183views
Oracle Autonomous Database - Over-counting storage used?
I was trying out the oracle's autonomous DB. Wanted to understand what is the fuss about oracle APEX services - which enables one to directly build apps on a db.
The DB comes with a bunch of system ...
- 91
2votes
3answers
5kviews
Error while installing Oracle 19c on Redhat 8.1
I was trying to install Oracle 19c on my Redhat 8.1 server, but i get the following error when i launch the installation
[INS-08101] Unexpected error while executing the action at state: '...
