1,123 questions
Best practices
0
votes
7
replies
44
views
Create a trigger with clause to update only one specific row
Due to a BUG, an application that persists data in an Oracle 19c database is updating a incorrect value in a specific row of a table.
This incorrect value prevents users from accessing that table ...
- 1
-3
votes
0
answers
80
views
New to Oracle to SQL Server migration through SSMA [closed]
I am trying to migrate from Oracle 19c to SQL Server 2019, however I need a comparison report for the migration of scripts so that I can see the changes and maybe modify in my SQL Server schema also. ...
- 1
-3
votes
1
answer
58
views
Oracle Log and handling exception of function [closed]
budling Oracle database function to move data between two schema that may has differences in structure as below and I trying to handling and log exception in log error table so function keep going ...
- 2,620
2
votes
1
answer
93
views
ORA-00904 when querying a sys.anydata column
Why am I getting a ORA-00904: invalid identifier error? How can I select the value of a sys.anydata column?
CREATE TABLE temp (
id NUMBER PRIMARY KEY,
val sys.anydata
);
INSERT INTO temp (ID, val)...
- 1,784
0
votes
0
answers
38
views
Oracle 19c FOR UPDATE SKIP LOCKED still allows same row to be processed by two app instances
I have a JOBS table in Oracle 19c and a method that fetches jobs from the table, sets their status as RUNNING, and then releases the lock by committing the transaction. Basically, I have something ...
0
votes
1
answer
119
views
Spring boot 3.4 app not respecting hikari max conn pool limit
We have configured Hikari with a maximum of 24 connections. However, in our Oracle DB we are observing more than 100 active connections per pod. Our setup is Spring Boot with Kafka (Confluent) using a ...
- 1,719
0
votes
1
answer
76
views
When using SET SQLFORMAT INSERT; is there a way to wrap each insert into its own block?
I'm using Oracle 19c.
I'm trying to develop scripts to generate scripts to copy table data from one environment to another (Dev -> Test -> PPRD -> Prod).
Is there a way when using SET ...
- 183
2
votes
3
answers
94
views
Remove empty params from URL
URL like this
https://test.com/test?username=john&x1=1234&x2=5677
I want to remove params if any of them looks like param=.
So for example:
https://test.com/test?username=&x1=1234&x2=...
- 702
1
vote
4
answers
151
views
Expand a table by filling the holes between bounds - regular SQL (i.e. not PLSQL)
I have a table with 1 line by stay in a room with arrival date and departure date, like this:
INPUT_TABLE:
STAY_ID;CUSTOMER_NAME;ROOM_NO;STAY_START_DATE;STAY_END_DATE
00031;ROGER;08;20180316;20180324
...
- 119
0
votes
0
answers
94
views
Tibco bwce with connection Oracle19client with Wallett
I'm using Tibco Business Work Container Edition. For design purposes, I need to connect to an Oracle database, specifically Oracle 19 Client, which uses a wallet for security. After editing the sqlnet....
- 1
0
votes
0
answers
41
views
Oracle 19c Text Option: Behaviour of CONTAINS with NEAR and MAX_SPAN
We found a strange behaviour of Oracle full text search when using contains(text, 'NEAR((A,B,C,D), 2, true)') where 2 is the max_span parameter. The documentation says:
max_span Optionally specify ...
- 1,804
0
votes
3
answers
91
views
Filter table based on substrings taken from column in another table
In my data (Table 1) I have IDs and a list of codes that are stored as one large string Example:
ID
CD_LIST
1
A;B;C;D
2
A;E;F;G
3
X;Z;W;F;H
4
C;D;E
5
P;Q;R;S;T;U
In another table (Table 2) I have a ...
- 716
2
votes
1
answer
77
views
Create Function Based Oracle Spatial Index without Data
Summary
I need to create a function based spatial index on Oracle 19c.
The table contains no data, when the index should be created.
This fails with ORA-13199.
MRE
My code is:
-- create table
create ...
- 1,070
0
votes
1
answer
52
views
REST working in SQLPlus but not workin in PLSQLDeveloper [closed]
I have a procedure run_rest. Executing this procedure in sqlplus works perferctly - i get response from external system. But executing this in plsql developer throws 400: Bad request.
Does someone has ...
- 702
0
votes
0
answers
475
views
How to fix ORA-29024: Certificate validation failure when using UTL_SMTP with STARTTLS and Wallet in Oracle 19c?
I'm migrating an old Oracle send_mail procedure to use authenticated SMTP with STARTTLS over port 587, in Oracle Database 19c (version 19.0.0).
✅ My Goal:
Send emails using UTL_SMTP through an SMTP ...
- 441
1
vote
0
answers
49
views
Oracle GoldenGate multiple handler ABEND
Issue with gg.handlerlist in Oracle GoldenGate: Replicat does not ABEND on all handler failures
Context
A GoldenGate Replicat process was tested with two handlers configured in sequence:
gg....
0
votes
1
answer
180
views
How to delete large number of records from a table faster in plsql?
Table #1:
buck number(10),
sname varchar(20),
...
total of 20 columns
Table #2:
sname varchar(20),
sdiv varchar(10),
...
total of 15 columns
Table1 has 30M rows
Table2 has 10M rows
I want to ...
- 3
0
votes
1
answer
54
views
Using Map in where clause in @Select query, giving error parameter not found
@Select("""
SELECT CURRENCY_CD,COUNTRY_CD,FEE_RATE_VAL FROM FEE
WHERE (COUNTRY_CD, CURRENCY_CD) IN
<foreach item="currencyCd" ...
- 649
0
votes
0
answers
66
views
JavaMail running on Oracle 19c database and TLS1.2
Running Oracle 19c with Java 1.8.0_451 and JavaMail 1.6.7. I'm attempting to send emails via SendGrid from a stored procedure on the database referring the the following Java class:
CREATE OR REPLACE ...
-1
votes
1
answer
129
views
Why we use select * from dual at the end of inserting many values in oracle
I want to insert many values in oracle database (I working with oracle 19c)
and I found that there is a query is INSERT ALL but I try to insert multiple lines.
but I found that at the end of this ...
1
vote
2
answers
70
views
Function to support apportionment of values
I have a structure composed of an INVOICE table, with basic data; ITEMS with the invoice goods; and PAYMENTS with the invoice payments. ITEMS and PAYMENTS are not linked to each other, but both are ...
- 564
2
votes
2
answers
289
views
ORA-20000: missing column list for dbms_cloud external table in Parquet format
I'm currently trying to create an external table for a Parquet file in a S3 bucket. This works:
begin
dbms_cloud.create_external_table(
table_name =>'test_parquet'
,credential_name =>'&...
- 30.5k
1
vote
0
answers
61
views
Oracle create type as table of varchar char-length
I encountered strange behavior of Oracle object types:
create or replace type test_type force as table of varchar2(1 char);
select * from table(test_type('abcdefghi'));
This query returns 'abcd'. ...
- 196
1
vote
1
answer
29
views
MyBatis seelct query with where condition list of objects
Employee Class
Long id:
String Name;
I have list of Employee object and i need to get the details from Employee table filtered with both id and name.
I tried something like this
<resultMap type=&...
- 649
1
vote
2
answers
169
views
DBMS_PARALLEL_EXECUTE insert multiple views in same table parallel
i want to insert the data from 7 views (defintely multiple views, less or more, depends...) into one table with DBMS_PARALLEL_EXECUTE, table structure and view structures are the same.
i am quite new ...
- 91
0
votes
2
answers
65
views
conditional where with subquery
I am using conditional where as shown below to make query more dynamic when there are no condition are meet but I am getting below error while when I omit the (SELECT CP.COLUMN1 FROM TABLE2 CP WHERE ...
- 2,620
0
votes
0
answers
57
views
Liquibase changeset is getting stuck with tables with million records
We have 110 million records in Database. There were 11 tables in which we have added a new column. To populate the values in the column, we are using FK to identify the data from event table. Now, ...
- 31
3
votes
0
answers
103
views
Recover from PLS-00123: program too large (Diana nodes) without dropping package
This question is not about the error, but about compiling the PACKAGE BODY after if the specification is fixed.
Using a query to generate the package source:
SELECT 'CREATE OR REPLACE PACKAGE test_me ...
- 55.7k
0
votes
2
answers
70
views
Function procedure with Oracle standard execution
I am working with multiple projects, one using Oracle 11g and the other with Oracle 19c.
Both of them have multiple databases which I need to share data between them.
Any procedure or function need to ...
- 2,620
0
votes
1
answer
99
views
Calender weeks with Oracle to_char function
in the calendar I see both 30th and 31st of Dec in years 2025 and 2026 fall in the same week but using the to_char function to create year-week field I will get different weeks
select
to_char(...
- 333
2
votes
2
answers
118
views
Group orders and sum the rate
I have couple of order for each partno.
sometime they cover each other sometime there could be one order pro partno at a time.
each order has a rate and could be 0 to 100.
valid output is when at any ...
- 333
3
votes
1
answer
114
views
Allow Commit/Rollback in PRAGMA AUTONOMOUS_TRANSACTION
At the application level, we enforce "ALTER SESSION DISABLE COMMIT IN PROCEDURE" to restrict commits/rollbacks inside procedures, but this also blocks Pragma Autonomous Transactions as well.
...
1
vote
0
answers
72
views
PHP OCI: How to pass above 33k string via bind variable to PL/SQL - is producing ORA-01460: unimplemented or unreasonable conversion requested;
I would like to record API calls using an autonomous transaction so that it cannot be rolled back. There is a call made once a day that has a huge return value. When I try to record that, I get a &...
- 171
0
votes
1
answer
27
views
Summing up the result based on historical data
I need to do a summation of a result based on
account and transaction date.
I have the following result:
ACCOUNT_ID TRANSACTION_DATE AMOUNT
581 05-SEP-23 309.32
581 ...
- 85
0
votes
2
answers
64
views
How to get the updated count record wise using FORALL using PLSQL
Need help on getting the updated count while using the FORALL
Steps to replicate
Create table:
create table TEST_UPDATE (name_id number(9), name_name varchar2(40));
Insert data
insert into ...
1
vote
2
answers
59
views
Pass BLOB to stored procedure from SQL*Plus
How to call a stored procedure from SQL*Plus with blob input parameters?
Below a sample of the SP to be called:
procedure p_dummy_proc (sessionId varchar2, accountId integer, lobData blob, oId out ...
1
vote
1
answer
47
views
How to send a warning in a PL/SQL successful response?
I have a rest call in PL/SQL that create a record in the database and that returns a response with the whole record back. This works fine.
But now I want to return a warning message when a certain ...
- 2,139
0
votes
0
answers
32
views
Inserting List<HashMap<String, String>> into Flyway Oracle 19c
I have a @MappedEntity object in Micronaut that connects to an ADB-S 19c instance of Oracle Transaction Processing.
Searching the internet suggests using the CLOB data type in Oracle for larger JSON ...
- 5,630
0
votes
0
answers
39
views
ORACLE how to find the transactions from the DBA tables
From the DBA_AUDIT_TRAIL table we found that some process is running every second, connecting to DB and does some transactions. What i see in DBA_AUDIT_TRAIL table, ACTION_NAME column as "LOGON ...
- 649
0
votes
0
answers
47
views
Oracle Error Converting Base64 Clob to Blob [duplicate]
I am trying to attach a pdf file to an invoice. The pdf comes to me as a base64 value that I store in a table as a clob. I then try to convert the base64 clob to a blob.
DECLARE
l_base64_clob CLOB;
...
- 1,461
0
votes
3
answers
94
views
Need to include a condition in my SQL lead function to check the previous year data when only one line of data entry is present for current year
I have an SQL query where I want to compare the data in a column with another line from the same column. So I included a LEAD function into my query.
However, when I run this query for the condition ...
0
votes
1
answer
86
views
How to capture the errored column name in the oracle error table
Inserting the rows from source_table to the target_table using stored proc and the inserting query,
insert into target_table (column_1, column_2)
select (column_1, column_2)
from source_table
log ...
- 2,116
0
votes
3
answers
70
views
How to Remove a Large Element From a JSON Object in Oracle
I have an xxmf_json_feed table with the following JSON clob in the json_data column:
{
"P_INVOICE_MASTER_TBL_ITEM": [
{
"P_INVOICE_NUM": "INV20250224-1",
...
- 1,461
0
votes
1
answer
36
views
Oracle copy command is changing the target table layout
I am trying to create a generic script which can replicate the oracle source table structure along with data to target db. I am using the below copy command but it is somehow changing the table ...
- 171
1
vote
1
answer
107
views
ORA-00904 Error When Using JSON_OBJECT(*) with Variables in PL/SQL Procedure (Oracle 19c)
I’m encountering an issue when using the JSON_OBJECT function inside a PL/SQL block. Below is an example of a PL/SQL procedure that triggers an unexpected error:
create table JSON_TABLE (
ID ...
0
votes
2
answers
90
views
Does creating an Index on a table, invalidates objects depending on the table?
I know that altering an index does not invalidate the objects depending on the table. Dropping an index invalidates objects, but what about creating an index on a table ?
- 6,114
0
votes
1
answer
89
views
Oracle JSON_TABLE Returning Empty Array Instead of Expected Data
I'm working with an Oracle PL/SQL stored procedure that processes JSON input and extracts data using JSON_TABLE. However, I'm encountering an issue where a specific JSON field (PRODUCTS) always ...
- 108
0
votes
2
answers
84
views
Get List of Months Between Two Dates
I am attempting to write a query that transforms the following table, where Accrual Length is the number of months between and inclusive of each Activation Date and Accrual End Date.
Institution
...
- 7
0
votes
0
answers
83
views
Oracle 19c and Pro*C: array fetching from a cursor behaving weirdly when using aggregate functions
TOOLS: I'm using Pro*C under Oracle 19c and the pc files have been parsed using parse=partial code=c++ char_map=string as command line options.
Take the following example, with some fictional main ...
- 12.5k
-1
votes
1
answer
94
views
What is the correct LISTAGG command for the equivalent STRING_AGG [duplicate]
We recently migrated the data from SAP HANA to Oracle. The database is Oracle 19c.
I need to know the corresponding Oracle 19c command for the below query. The below query is utilizing STRING_AGG. ...
- 67