Questions tagged [oracle]
Oracle Database is a Multi-Model Database Management System created by Oracle Corporation. Do NOT use this tag for other products owned by Oracle, such as Java and MySQL.
oracle
152,179
questions
0
votes
0
answers
2
views
Operation timed out when call JDBC driver from Oracle java stored procedure
I use oracle java stored procedure to read data from MS SQL Server and Advantage Database Server
via JDBC driver.
There are net.sourceforge.jtds.jdbc.Driver for MS SQL and com.extendedsystems.jdbc....
-1
votes
0
answers
9
views
How to use incremental data in stored procedure fetching in a table basis on serno. For eg: latest max serno is 100 at t=0, 103 at t=1, so on
I have to include this incremental data in stored procedure to generate file from t=0 and t= time take by procedure to complete.
select max(serno) from incremental_src ;
o/P: 100 at t=0
o/P: 105 at t=...
0
votes
0
answers
8
views
Hibernate: Generate non-id value from sequence
I have an entity with 2 fields which both use the same sequence, but when debugging I see that only the id field value is being generated:
@Table(name = "MY_TABLE")
public class MyEntity {
...
- 29
0
votes
0
answers
11
views
What is required for Oracle Data tools and VS 2022?
Cannot get oracle data tools to connect in VS 2022
Will not connect using odp.net managed driver
keep getting "ORA-12545 unable to resolve connect hostname"
will connect using legacy ,net ...
0
votes
1
answer
10
views
Update Onprem oracle table from aws glue python shell using oracledb
I have a usecase that needs to update an onprem table using the aws glue python shell.
Please note i want to use python-oracledb in thick mode
I have installed the oracledb
I dba has set up a jdbc ...
- 165
0
votes
0
answers
13
views
How to specify multiple search criteria with the emcli list command?
The below code returns the desired result set.
emcli list -resource="Targets" \
-bind="TargetType='oracle_database'" \
-columns="TARGET_NAME:20,TARGET_TYPE:20" \
-search=&...
- 119
0
votes
2
answers
45
views
Joining tables with different date formats (Literal does not match format string)
I'm trying to join two Oracle tables (basically to pull in a daily rate against the date in another table), but the table I'm pulling the date from has the timestamp in it. I'm seeing a lot of ...
- 1
0
votes
0
answers
15
views
How to write a MD5 SQL Code in Oracle similar to Informatica Expression Logic for Validation
My requirement is to write a sql code to replicate the MD5 Logic used in informatica expression transformation for Validation Purpose . The MD5 used in Informatica is MD5(TRIM(AGRMNT_NO)||TRIM(SRC_CD))...
- 239
0
votes
1
answer
42
views
Discrepancy Between SQL Server and Oracle months_between Function Outputs
I'm working on a project where I need to calculate the number of months between two dates in both SQL Server (using Synapse) and Oracle. I've created a user-defined function in SQL Server to replicate ...
- 23
0
votes
2
answers
49
views
Is there any method to convert a varchar with specific format into time in Oracle SQL?
I have a table with three columns, one of the columns ateststatus_date has the following format: e.g:
28-MAY-24 11.57.20.000000 PM
and it's defined as varchar(2). I used To_date() and ...
- 11
0
votes
0
answers
11
views
Oracle Forms fails to start up on OL9 - cannot find shared object file libopmnperf.so
I have freshly installed a 19c Database and an Oracle Forms and Reports 12.2.1.4 instance on an Oracle Linux 9 VM and everything starts properly except for the Forms application. When I try to start ...
- 11
1
vote
3
answers
49
views
Converting char to date and pulling past two years data
I need to pull data from tables with millions of rows and limit it to just the past two years. I am working on developing a SQL statement to enter into Power BI to limit what it pulls. Obviously I am ...
0
votes
0
answers
25
views
Oracle: how to add a decimal point to an integer [duplicate]
Oracle 19c.
I am reading a file and need to store the following values into a NUMBER column:
000036720
000008425
000754070
000086640
000001645
Desired output will be:
367.20
84.25
7540.70
866.40
16....
- 481
0
votes
0
answers
16
views
Oracle ORA-04068, Error when Making Changes to Package ODP.NET Project [duplicate]
I am encountering errors while interacting with an Oracle database from C# using pooling true connections. Specifically, when I make changes to a package in Oracle, I receive the following errors on ...
0
votes
0
answers
37
views
Call PL/SQL from JDBC has different behaviour with OracleConnection and Connection
I need to change package variable using JDBC and get new value back.
Oracle package:
create or replace package test_pkg
as
test_num number := 10;
end;
When I do it via java.sql....
- 363
-1
votes
2
answers
40
views
Materialized view log fast refresh memory management
I have a materialized view which is currently refreshing in complete mode (taking around 30 mins to refresh). I'm trying to optimize it by changing the refresh mode to fast. As the history data in the ...
0
votes
0
answers
42
views
Sporadic failures in FastAPI SQLAlchemy deployment due to database connection errors
Our FastAPI deployment uses SQLAlchemy to connect to our database and fetch/filter metadata, but it is proving very flakey. Load balancing tests using Locust show a very odd behavior where initial ...
- 23
0
votes
0
answers
20
views
I need to make the existing non partitioning table as partitioning table and non partitioning table has many linked child tables [migrated]
The main table is linked with many child tables using foreign key. And need to add the list partition in the main table but i want all child table will also be linked with this partitioned table. ...
1
vote
0
answers
24
views
Oracle 11g and EF Core 8 two similar servers one throws an error "OracleException: ORA-28040"
Looking for some help/advice.
I have two Oracle 11g servers call them "TEST" and "PROD". When I run my project it connects fine to "TEST" but when trying to run it with &...
- 78
1
vote
1
answer
15
views
VS Code Drag and Drop Database to .ipynb Not working?
Currently test driving the new Oracle SQL Developer extension for VS code and was referencing this Youtube Video: https://youtu.be/e-yqkI0t6dQ?t=2784
Noticed the presenter had a database open and ...
- 165
0
votes
3
answers
44
views
Oracle-14400 error but value entering partitioned column does map to partition
I have a problem with an insert I am attempting into an Oracle table called MACON. The table structure is below.
The issue I have is that when I attempt to insert a value into the MEASPART column, ...
- 103
0
votes
2
answers
28
views
Oralce JSON_TRANSFORM, set/insert/append not working
Why this:
with j(jval) AS
(
select JSON_SERIALIZE('{"contract" :{"contract-type" : "P",
"contract-id" : 123451,
"proposal-no" : "123456&...
- 652
0
votes
1
answer
28
views
How to Handle Indexes for @MapsId in JPA: Should FK and PK Share the Same Index?
I am using the @MapsId annotation in JPA to map a foreign key (FK) to a primary key (PK). According to the @MapsId annotation, the FK and PK share the same value. However, when I checked the database ...
- 121
0
votes
1
answer
23
views
View only access to Package Spec, Body and Procedures
In Production Environment, we need to provide View only access to Package Spec, Package Body and Procedures. Any time we need to change the queries, we need to reach out to the admin to get the latest ...
1
vote
0
answers
37
views
ArgumentOutOfRangeException error while assigning OracleClob value to .NET Core string
I want to read large clob data type (2 million rows in table) from a stored procedure in an Oracle database. After reading the data and trying to assign OracleClob value field to .NET Core string type,...
- 23
0
votes
1
answer
20
views
I need to do a control to verify that in each partition (BY ID) the sequence of the dates is correct
I need to do a add a column (CTRL) to verify that in each partition (ID) the sequence of the dates (REFERENCE_DATE) is correct keeping in consideration the column NUM_ROLL;
The db is ORACLE and I am ...
- 5
0
votes
1
answer
49
views
Oracle SQL gives ORA-01790 without UNION, and a workaround works strangely. Why?
We have an Oracle view "view1", which SQL is like this:
select ...,
nvl(iac.indirect_attri_cost, 0) + nvl(pc.premises_cost, 0) + nvl(cc.central_cost, 0) total_cost
from (select ..., ...
- 59
1
vote
1
answer
14
views
"PDML disabled because array binds are used"
I'm trying to delete a large number of rows from a table, but parallelism isn't fully working and the execution plan Note says "PDML disabled because array binds are used". How can I fully ...
- 36.5k
0
votes
2
answers
27
views
Why is this sliding query not producing the correct results?
What I am trying to do is
I have a nearly identical situation to this post, and this one (I'd rather not use 4 separate lag fields), and I even found this example that says that basically I need a ...
- 5,487
0
votes
0
answers
39
views
Error at line 9/0: ORA-00922: missing or invalid option [closed]
I get this error if anyone could guide me
Error at line 9/0: ORA-00922: missing or invalid option
7. );
8. -- Crear tabla Usuarios
9. CREATE TABLE Usuarios (
10. id_usuario NUMBER PRIMARY KEY,
...
0
votes
1
answer
38
views
Invalid characters in VARCHAR2 column - Oracle
I want to find out what characters are not supported in my VARCHAR2 column in Oracle. When I filter a VARCHAR2 field using asciistr(column) <> column (query provided below), I see that some ...
-1
votes
0
answers
20
views
SSRS reports are being generated even though the Oracle cursor doesnt have values
I have a Oracle SQL(11+) cursor that returns rows for a report. I also have a VB.NET batch that writes this file to a location by looping over all the Ids just for some context. Im trying to stop the ...
- 1
1
vote
1
answer
46
views
How do you create a function in Oracle with a lambda parameter?
I don't seem to be finding any documentation on this (and maybe I don't know the Oracle vernacular for this), but I would like to create a function that takes a function: Say, have it pass me a sort ...
- 5,487
1
vote
2
answers
44
views
SQL command works fine when run manually (SQL Developer) but gives ORA-00922 in Python's oracledb module
I am working with an Oracle SQL database, and I would like to run the command
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
It works fine when I run it from the SQL Developer app manually. ...
- 11
0
votes
0
answers
18
views
Last Oracle user UNlocked date/time
dba_users has the column lock_date .. perfect, but, is there a column LAST_UNLOCK_DATE (anywhere)?
We need to know when an account was unlocked last time.
Thank you!
- 330
0
votes
1
answer
41
views
The cursor is not printing any count even if there is record in table in Oracle Procedure
I have created a SP where I am running a logic to bring the V_SPANID_COUNT count but it is always showing 0 count even if there is some record say 10-20 records
Below is my SP..
create or replace ...
- 4,703
0
votes
1
answer
27
views
Oracle accumulate values with condition
below is an extract of an Oracle table with these columns:
ID
WeekStartDate
AbsentFrom
AbsentTo
1
01-JAN-24
null
null
1
08-JAN-24
09-JAN-24
17-JAN-24
1
15-JAN-24
09-JAN-24
17-JAN-24
1
22-JAN-24
...
0
votes
2
answers
66
views
EXPDP doesn't recognise user names
I'm trying run EXPDP on an Oracle 19 database but it doesn't recognize any of the schema names apart from sys.
select * from dba_users;
when run in Oracle SQL Developers returns all the users I'd ...
- 3
-2
votes
0
answers
62
views
Error Msg = ORA-06550: PLS-00306: wrong number or types of arguments in call to Stored procedure' ORA-06550: line 1, column 7:
I have below java code trying to call stored procedure. which is throwing
Caused by: Error : 6550, Position : 6, Sql = BEGIN JCPRMS.JCP_STORE_LIQUIDATION_PROCESS.JCP_STORE_LIQ_MW_TO_RMS(:1 , :2 ) ; ...
- 247
-2
votes
2
answers
33
views
oracle sql usage of CASE THEN [closed]
I have a set of complex queries that result in producing the result set that looks like :
TradeId | Currency | Value
1 USD 10
1 EUR 20
2 USD 30
2 ...
- 590
0
votes
1
answer
32
views
Querying Json_Object_t using dot notation in Oracle 19c
I have a case where Json document is passed as CLOB input to a stored procedure.
I am converting it into Json Object as below
l_js_obj := JSON_OBJECT_T(p_js_clob_i);
Json:
{"Key1":{"...
- 153
2
votes
1
answer
25
views
Oracle SQL pattern matching using wildcards
I'm trying to search though a large list of postal codes in various countries
Specifically, I want to identify all Canadian entries USING A CASE STATEMENT.
after all spaces have been stripped out, ...
- 690
-1
votes
1
answer
34
views
Oracle sql how to add long comment
I'm trying to execute
Comment on table TABLENAME is '';
But the content I want to use for the comment is too long, resulting in an ORA-01704: string literal too long error.
How can I store a very long ...
- 1
2
votes
2
answers
34
views
Why is my timestamp getting truncated when I select the maximum, non-null value from a series of columns?
So, I have a series of columns and I need the maximum, non-null value.
Here is a sample of the problem, and it works as I would expect:
select t.*, (select max(to_timestamp(column_value)) as END_TIME ...
- 5,487
0
votes
1
answer
30
views
No ODAC Driver found when running a macro in Excel
A user was trying to running a macro within an excel sheet, but when doing so, we run into the error of excel saying it's a -1004 runtime error. Upon further digging, we discover that the issue ...
- 1
-4
votes
1
answer
44
views
Compare records within the same table and find missing setup
I'm trying to write an SQL in Oracle where I compare only two items in the same table and find the records where item2 is missing the warehouse setup where the warehouses exist in comparison to setup ...
- 31
-1
votes
2
answers
33
views
ORA-00955 name is already in use
I got the error message that the name is already used by an existing object but then it still created the table.
CREATE TABLE InventoryItem (
InventoryID NUMBER PRIMARY KEY,
MovieID NUMBER,
...
- 1
0
votes
0
answers
31
views
cx_Oracle.DatabaseError: ORA-01804 on Windows Server
I am currently facing an issue while trying to connect to databases using cx_Oracle on windows server. I have set ORACLE_HOME and LD_LIBRARY_PATH correctly. However, I am encountering the following ...
- 1
1
vote
1
answer
42
views
Entity Framework 8 Query Slower Than Direct Oracle Command
I'm currently working on a C# project where I need to retrieve data from an Oracle 19 database. I'm using Entity Framework to handle the database queries. However, I've noticed a significant ...
- 23
0
votes
1
answer
45
views
Data Not Displaying Correctly with PostgreSQL odbc_fdw
After connecting to Oracle and DB2 via odbc_fdw and retrieving data, the record count of the tables is displayed correctly, but the data for each record's fields is not displayed correctly.
The value ...
- 1