1,125 questions
Score of 3
0 answers
73 views
Using query block name hint in Merge statement
I'm trying to use qb_name hint in merge statement. The purpose of this is to be able to use hints like use_hash to stabilise execution plan of merge statement which at times goes wild by doing nested ...
- reputation score 73
Score of 3
4 answers
195 views
SQL Sum Aggregation
Data Set
animal
legs
parrot
2
chicken
2
dog
4
cat
4
c'thulu
999
missing'no
0
Goal
animal
legs
bird
4
mammal
8
I'm not sure what's wrong with my Oracle SQL query. It doesn't seem to be aggregating ...
- reputation score 33
Advice
0
votes
0
replies
104
views
Data type mapping strategy (Migrating Oracle 19c DB into SQL Server 2022 using SSMA)
Do anyone have any good strategy for data type mapping equivalent to be used, specifically for the NUMBER data type from the Oracle source. The default mapping that SSMA uses is FLOAT(53) but as much ...
- reputation score 1
Advice
2
votes
1
replies
78
views
Is it possible to set a default tablespace at session level in Oracle 19?
Here is the my problem. I have a ton of DDL scripts for creating tables for an app. Considering this script has always been run as the OWNER of the schema, I've always relied on the default tablespace ...
- reputation score 2562
Advice
1
vote
4
replies
96
views
I need to pass changes on certain tables to external system but am worried not to skip anything
I need to pass changes on a table to external system. Let's say I have table of messages. I have a column called change_id which is populated by trigger from a sequence on insert and update. ...
- reputation score 113
Score of 1
1 answer
99 views
How can apply timeout to hibernate query?
There are long running queries which consume memory of the application. I try to apply a timeout to interrupt execution before application is not responding, but hibernate and oracle does not respect ...
- reputation score 53
Score of 0
2 answers
163 views
How to return a JSON object with JSON_OBJECT or JSON_ARRAYAGG
I like to compose a JSON from some table data, I do it like this:
DECLARE
res JSON_OBJECT_T;
ja JSON_ARRAY_T;
ret CLOB;
CURSOR cur IS
SELECT JSON_OBJECT(
KEY 'name' VALUE ...
- reputation score 60835
Score of 4
1 answer
79 views
Oracle 19. date century. difference in behaviour between 2 updates
I update 2 different columns within the same update and with the same variable (a date with value "01.01.1900"), the first one get the right value, the second one gets "01.01.2000"....
- reputation score 41
Score of 3
0 answers
111 views
Why is this code leaking Oracle connections?
I've been using Npgsql, which uses the DbDataSource abstraction. It's really nice since commands created by it do not need explicit connections to be opened by them. However, the equivalent code using ...
- reputation score 1256
Score of 2
1 answer
135 views
Oracle: Character Set Mismatch
can you tell me why i get Character Set Mismatch in the first insert even if "in theory" they are equivalent?
Oracle version: 19
SD.ValueText is NVARCHAR2
SELECT value FROM ...
- reputation score 75
Score of 0
2 answers
92 views
Writing a JSON to Oracle CLOB column with Nifi
I have processor in Apache Nifi which prepares a big json (it varies between 4k to 500k), now I have the json which has nested objects and arrays in it. The flowfile has an attribute named "...
- reputation score 7352
Best practices
0
votes
8
replies
100
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 ...
- reputation score 1
Score of -3
1 answer
79 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 ...
- reputation score 2620
Score of 2
1 answer
109 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)...
- reputation score 1809
Score of 0
1 answer
117 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 ...
- reputation score 87
