3,983 questions
0
votes
2
answers
43
views
Oracle sql. how to check file existence on remote server
I'm trying to check if a file exists on a remote server, in a Forms 12c program unit.
I tried to use
utl_file.file_exists('\\server\d$\folder\filename.txt')
and also
utl_file.fgetattr('DBFOLDER','...
- 23
0
votes
0
answers
31
views
Different results when invoking function with raw sql and plsql [duplicate]
Well I have 2 distinct Oracle 12c databases, lets name them db1 and db2
I have a link from db1 to db2 (DBL_DB2), also grants for execution of pkg2 which contains procedure 2, which we call proc2 (in ...
0
votes
0
answers
24
views
Tab Back from required fields in Oracle Forms 12c
I´m migrating an application developped in Oracle Forms 6i to Oracle Forms 12c and i have found the following problem. In forms6i you could tab backward from required field but in forms12c you can't.
...
1
vote
0
answers
168
views
Creating Kerberos credentials cache - Oracle VS Code
I've been working on getting the Oracle VS Code extension set up. I have an Oracle client that uses Kerberos authentication (note that this is a company-managed laptop, I do not have admin access)
I'm ...
- 21
0
votes
2
answers
62
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,622
0
votes
0
answers
51
views
Tracing configuration in Oracle Enterprise Manager
I'm optimizing a form in Oracle Forms 12c, and to identify the points where it takes the longest, I've enabled traces for events:
65 (Procedure (Start & End). - duration
66 (Trigger (Start & ...
-1
votes
1
answer
74
views
Replicate data to another DB server in Oracle [closed]
We have an Oracle DB running for a production environment. To not effect online processes we keep the data limited in this. Like one year. There is a job running everyday to delete older entries.
We ...
- 29
0
votes
0
answers
53
views
How to load data which is dmp file to oracle db
I have an Oracle database running on a remote server, but I do not have access to the server itself. However, I have a user account having dba privilage that allows me to connect to the database using ...
- 11
0
votes
2
answers
88
views
Issue with INSERT ALL SQL statement Oracle
I got an issue with INSERT ALL statement for the below table and this table has a Before Insert trigger which automatically populates the date when the ID_NO column is populated.
CREATE TABLE TestA
(
...
0
votes
0
answers
98
views
StoredProcedureQuery does not end
We are trying to call a PL/SQL Procedure that runs in a Oracle Database. The complete PL/SQL procedure runs for 10-12 minutes.
With SpringBoot 3.x with Java 21, we use Hibernate to call this Stored ...
0
votes
0
answers
51
views
ODI studio command line interface to import ODI xml scenario file and execute. How to do it?
I have ODI Studio installed and the ODI agent configured. We have a test environment where an ODI XML scenario has been exported. Now, we need to import and execute the same exported XML scenario in ...
- 11
1
vote
1
answer
75
views
Why is not the PL/SQL package state not reset?
I have encountered a weird behavior with initialization state of a package when ORA is thrown. Here is my example that I run on Oracle 12c:
create or replace package pkg is
v_num number := pkg_two....
- 33
2
votes
1
answer
207
views
How to prevent temporary table operations from generating archive logs in Oracle Database?
In my application, we dynamically create tables to store session-specific information. These tables are created at the beginning of an active session and dropped once the session is closed. Recently, ...
- 397
0
votes
1
answer
83
views
How to change Oracle's Enterprise manager 12c runtime properties from a webservice call?
I work with Oracle SOA Suite (Oracle fusion middleware 12c and 11g), combined with weblogic 12c, Azure pipelines, git repository, and SOAtest from parasoft as testtool.
I want to be able to change ...
- 195
0
votes
1
answer
154
views
Grant rights to create tables in a particular Oracle schema
What is the correct procedure for allowing team members to develop applications and create tables in only a specific schema?
I am running Oracle 12C. If I create a schema - say MYAPP1 - how should I ...
- 698
1
vote
3
answers
80
views
SQL query not fetching correct number of records
I have this SQL query. When I specify the date and time explicitly, it shows the correct number of rows:
select *
from annual_report_order
where report_order_date >= '29-OCT-2024 00:00:00.000'
...
- 39
-3
votes
2
answers
53
views
Adjust the date as working date | Oracle |
I was reviewing several articles on Stack Overflow and came across a query that worked as expected. However, I need to make some modifications to tailor it to my specific requirements.
I need to ...
2
votes
2
answers
171
views
oci_connect(): OCIEnvNlsCreate() failed in RHEL 8 with HTTPD
I need your help to connect to Oracle in PHP8 on my Red Hat 8.9 server.
OCI8 is enabled and the HTTPD web server is installed. This is my code:
<?php
// Enable error reporting in PHP
...
- 868
0
votes
1
answer
39
views
Get 12 month running total for subgroups in Oracle
I'm trying to get a running 12 month total for data, but changing when values in my fields change. I've made a simpler sql fiddle, for a 3 month running total, for one value in the table. I'm stuck ...
- 7,996
2
votes
1
answer
60
views
When the same alias is used twice, which one will subsequent references point to?
There is a query that joins the same table twice with the same alias (but under different conditions). To which of the two data sets will subsequent references to this alias point?
SELECT ...
FROM ...
- 145
2
votes
1
answer
52
views
Oracle hierarchical query joining 2 tables
I have 2 tables: projects and tasks (that's a simplification, but will do for now). PROJECTS has a hierarchy of projects where there can be a detached (standalone) project or a projects hierarchy. ...
- 63
0
votes
1
answer
113
views
Converting timestamp to date format in Oracle 12c
I have a below date value stored in a column in a table. I wanted to get the result in dd/mm/yyyy format only.
15-JUL-18 22.04.11.587000000
I am using this format but seems not rendering the correct ...
- 39
0
votes
0
answers
42
views
Retrieving Images from Oracle 12C into BIP Report
I want to create report in BIP to show me customer images form SIGTEXT column in Oracle 12c, Like Displaying in Oracle front app but when I want to insert image in BIP I face to some error.
SELECT sg....
1
vote
0
answers
440
views
ODI 12c Work Repository: Reusable Mapping Lineage
My team is trying to create a Data Lineage SQL query using the metadata tables from the ODI-12c work repository. The idea is to input a specific column into our query and return all the stages/...
- 11
1
vote
0
answers
34
views
Visibility of external tables in CROSS APPLY
It would seem that the value from a row in an external dataset should be visible internally. Working example:
WITH table_1 AS (
SELECT 1 col_id FROM dual UNION ALL
SELECT 2 col_id FROM dual UNION ...
- 145
0
votes
2
answers
54
views
Oracle SQL find max number before repeating values start
I am trying to use a query to find the maximum value in a column before repeating values start. I have three columns - year, jobnumber, hourly_rate
After a certain year, which can vary, the rates ...
- 37
0
votes
1
answer
76
views
Generate Nested JSON oracle19c using JSON functions
Hi I have below sample query.In real case scenario there are many more columns in each view.
The A view has unique ids and is present in other views. I tried to join all the views and generate JSON ...
- 943
1
vote
2
answers
102
views
how to query a different view depending on oracle version
i have 2 queries.The 1st one runs on 11g:
select owner,directory_name,directory_path from dba_directories;
The 2nd one runs on 12c an 19c oracle versions only:
select owner,directory_name,...
- 47
-1
votes
1
answer
77
views
Oracle 12c: How can the LENGTH() of a VARCHAR2 value be more than its size?
Today I SELECTed the LENGTH() of a value in a VARCHAR2(1) column and got "5" even though it sure looks like 1 character to me when I SELECT the column directly in PLSQL Developer (it has the ...
- 9,600
1
vote
1
answer
54
views
Yii2 - Oracle performance issue when getting one row by a unique field
I'm using Yii2, version 2.0.35, apaoww/yii2-oci8 version **1.0.**0 to connect to Oracle DB
I have a table that stores users, it's about 8 millions rows. This table has an unique index by "...
- 469
0
votes
1
answer
123
views
Pivot statement randomly produces NULL valued columns (Oracle 12c)
I have a query that uses a CTE for pivoting. I run the query without pivoting and the results are always the same.
HOWEVER when I pivot this data from rows to columns (about 15 columns MAX), I keep ...
- 23
0
votes
1
answer
276
views
No data source definition was found for data source name: 'StorePrimary'. Oracle Retail Xstore POS Dev Setup
So i have been trying to install Oracle Retail Xstore POS dev Setup on my desktop and couldn't find any solution to this error.
Here is what i get on screen when running it
the stack trace states that
...
1
vote
2
answers
80
views
Oracle catch exception code given at runtime
I want to create an Oracle PLSQL procedure that receives a statement that will be executed and a number representing a possible exception code that can arise and should be handled. I tried something ...
- 1,595
0
votes
3
answers
65
views
Oracle's SDO_NET.FIND_CONNECTED_COMPONENTS fails with 'wrong number or types of arguments in call'
I am attempting to create an Oracle Network Model to analyze a fibre optic communications network using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production.
All the steps are ...
- 3
1
vote
1
answer
377
views
Clone schema within same database
I'm using Oracle 12.1. The database I'm talking about is used for build purpose and we need to create db schema which is compatible with the version of code in use.
For example, we have say schema1 ...
- 63
1
vote
1
answer
285
views
Oracle PL_SQL set execution timeout
I am using Oracle 12c Enterprise (12.1.0.2.0) and have a package which creates queries using dynamic SQL, according to some rules. The dynamic SQL query is then executed by opening a cursor on it, ...
- 93
0
votes
1
answer
423
views
Error while exchanging partition in Oracle ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
I am trying to swap partition PART_201901 from the MAIN_TABLE to the TABLE_HISTORY.
I have created the TABLE_HISTORY using the same script as MAIN_TABLE, literally used the same script, indexes, ...
- 945
1
vote
0
answers
75
views
Oracle xQuery merge two xml matching nodes
I have Oracle12 and two XML and want to merge them into one preferably using xQuery.
If 2nd XML have new nodes then they must be added.
In addition, if possible, if the node matches then change its ...
- 41
0
votes
2
answers
4k
views
Oracle 12c impdp ORA-12154: TNS:could not resolve the connect identifier specified
I'm new with Oracle and I'm just trying to import a dump file. I'm able to create my container database, however when I go to run impdp gy/gy@mygy file=C:\app\impdir\EL1.DMP I am getting ORA-12154.
...
- 57
1
vote
1
answer
45
views
Fetch/Update the amount value from previous record based on date
I have this table named Pricing. Below is the ddl.
CREATE TABLE PRICING (ITEM VARCHAR2(80) NOT NULL,
LOCATION NUMBER(10) NOT NULL,
...
- 65
0
votes
1
answer
67
views
Oracle SQL - search for entries inside the same month but with different start/end dates [duplicate]
I want to query for all entries in the table 'unique_delivery_locations' where 'transaction_identifier' is the same, and we are within the same month. The only difference should be in '...
- 77
0
votes
1
answer
37
views
hierarchical table connect left element with right
I have a problem with creating Hierarchical table,
I have the following data
With data(left_code, left_category, right_code, right_category) as (
SELECT '21BEMVXP040150FIS4', 'A', '...
- 143
0
votes
0
answers
161
views
Materialized view with REFRESH ON COMMIT is not working in ORACLE 19
I am trying to create materialized views and materialized view log but the materialized views are not getting refreshed even after the use of REFRESH ON COMMIT in ORACLE 19. The same code for the ...
- 1
0
votes
1
answer
42
views
How do I create data (not a temp table) using a loop and Oracle 12c?
I'm using a PaaS (Asset Works) to create a report that needs looping. I have a bus table and need to create a spreadsheet that lists the bus replacements. Some buses need to be replaced every 10 ...
0
votes
1
answer
248
views
sqlplus 19c varchar2 formatting different fromk 12c
We are updating our database from 12c to 19c and ovviously the Oracle client.
We do have some jobs which are reporting from sqlplus. We noticed that the varchar2 fields are formatted differently in ...
- 340
0
votes
1
answer
50
views
Update current row of Date Column based on the Previous row of different Date column
Please help to achieve this in Oracle SQL.
I have table structure like below ,
create table temp_t
(
rule_id number,
start_datetime timestamp,
end_datetime timestamp,
last_update ...
- 41
0
votes
0
answers
524
views
Unable to find listener.ora in oracle 12c
There is a default oracle 12c installation done in RHEL 7.5.
This is a single instance database.
When i issue lsnrctl status command i am able to see below output.
LSNRCTL for Linux: Version 12.2.0.1....
- 81
0
votes
1
answer
472
views
Unable to connect to database oracle sql 12c
Yesterday i discovered that i have a problem with oracle sql database 12c running on windows 10 pro for workstations. When i try to connect to a local database with the sql developer tool i received ...
- 11
0
votes
0
answers
66
views
Import data from old Jira version with HSQL DB to higher Jira version Oracle 12c
I am a beginner and in my company currently using Jira version 7.11.0 configured with oracle 12.1.0.1 database, Windows and it have some new data with it.
My task to import the old data from ...
0
votes
0
answers
106
views
Export 1.4 billion rows from single oracle table
I have a table with about 2.3 billion rows where I need to export about 1.4 billion of these rows to CSV.
Info:
I have the max pk to retreive.
PK is indexed.
What would be the best way of doing this?...
- 121