All Questions
97 questions
1
vote
2
answers
248
views
Oracle SQL query slow even with indexes
I have an Oracle 19c database (19.0.0.0.0), and two tables with lots of columns, I'll mention only the relevant ones:
table1:
createtime date
contact_key char(96 byte)
table2:
contact_key char(96 ...
- 163
1
vote
2
answers
1k
views
How to search text in oracle view
I need to create a filter where I can locate specific text in my views.
I tried using the following queries:
SELECT OWNER,
VIEW_NAME
FROM DBA_VIEWS
WHERE UPPER(TEXT) LIKE UPPER('%ABC%');
But ...
0
votes
0
answers
19
views
Querying arrays in JSON fields
The DB is Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.16.0.0.0.
Suppose we have a table CASE_ISSUES with a JSON field ISSUES_JSON:
CASE_ID
ISSUES_JSON
105
[{&...
- 115
0
votes
1
answer
47
views
Validate "insert", "update", "delete" in the body of a trigger
I need to create a query where I can remove triggers that have "insert", "update" or "delete" in their body from the result.
I'm using the following query for this
SELECT ...
2
votes
1
answer
194
views
How to locate a procedure in the body of a trigger
I'd like to perform a validation by capturing all my triggers that call procedures.To do the search, I'm looking for the words "Call" and "Execute" in the trigger body
-- I'm using ...
0
votes
1
answer
58
views
Oracle MAX gives different result than ORDER BY desc
When trying to sort some hexadecimal values I've got a different results for MAX() aggregation and ORDER BY desc sorting:
with some_data as
(
select '123A55' as hex
from dual
union all
...
- 110
1
vote
4
answers
130
views
Loop through 30 columns in Oracle DB to check if a value exists
I have a table which contains an ID in column one and that ID can have up to 30 different codes and dates associated with it.
ID | CODE_1 | CODE_1_DATE | CODE_2 | CODE_2_DATE | CODE_3 | CODE_3_DATE
...
- 13
1
vote
0
answers
36
views
Possible explanation for why "SELECT" query executed from JDBC returning just the first row in the table
I installed OracleDB(XE - free version) and I tried to use it in Java. I have this code:
static final String DB_URL = "jdbc:oracle:thin:@localhost:1521/XE";
static final String ...
- 111
0
votes
1
answer
51
views
Break down integer interval into rows
I'm a developer that uses Oracle database with .Net application. I successfully created a query to return some data that I want, and it returns something like this:
cod
charac1
charac2
items
I001
...
1
vote
1
answer
248
views
Oracle SQL: Create a new field based on multiplying 2 columns
I think I might be overlooking something obvious here. I'm not asking someone to write the whole code for me, I just need a hint or a link to a similar case, please. I'm posting it without a test case....
- 125
0
votes
1
answer
1k
views
How To turn a string with "pipe-separated" values into individual rows in Oracle PL/SQL
I have a table with below structure :
create table student_info
(
item_number number,
st_firstname varchar2(50),
st_lastname varchar2(50),
st_score varchar2(50)
)
Here is a ...
- 1,486
2
votes
2
answers
324
views
Multiple records same id retrieval
I have a table with the following structure
USERID
CONTACT
VALUE
1
LANDLINE
+12312322334
1
MOBILE
+11234567890
1
EMAIL
[email protected]
2
LANDLINE
+12312322335
2
MOBILE
+11234567899
3
LANDLINE
+...
- 39
1
vote
1
answer
2k
views
Receiving error "can not access rows from a non-nested table item" while working with associative arrays
Below , I'm trying to use the data of the array I've declared,
in the where clause of the query but I receive the following error :
declare
v_input varchar2(400) := '0,1,2,3,4';
type ...
- 1,486
0
votes
1
answer
670
views
Receiving error "Local collection types not allowed" while working with associative arrays
I have an input parameter with this format '0,1,2,3,4' , what I want to do is to declare an array with a variable size (by this I mean the input parameter might be like '0,1' , so the size is not fix),...
- 1,486
0
votes
2
answers
58
views
Converting string with DIN 1421 pattern to human-readable
In the Oracle DB there is a table that stores "DIN"s attribute (VARCHAR2(1200 BYTE)), see a table/image below.
Each string follows the DIN 1421 pattern.
+------------------+
| DIN ...
- 177
0
votes
2
answers
422
views
Filtering Data on VARCHAR data type
I have a column. The data type is VARCHAR2. What I am trying to do is write a query to catch only the records that contain a string either in the beginning or end or in the middle. Below is a sample.
...
- 3
0
votes
0
answers
568
views
Procedure updating table with CLOB data type column does not work properly
I have a table with below structure:
create table bb_analysisitems_batch
( batch_id number,
batch_name varchar2(100),
batch_result clob )
There are other ...
- 1,486
0
votes
1
answer
1k
views
Add new column based on conditions and the value difference of other column
I think I might be overlooking something obvious here. I'm not asking someone to write the whole code for me, I just need a hint or a link to a similar case.
Thanks in advance...
My query:
select ...
- 125
1
vote
2
answers
568
views
Applying conditional statement in Oracle DB
In the Oracle DB there are three tables, see below.
Table: 'People'
Table: 'Dogs'
Table: 'Cats'
Here is a Fiddle with data to test : http://sqlfiddle.com/#!4/d7cb4
Depending on the result of a ...
- 177
0
votes
1
answer
178
views
Query to update one column based on the amount of another column
I have a table with below structure :
create table Z_TEST_ONE
(
col_id NUMBER,
bed_amnt NUMBER,
bes_amnt NUMBER,
bop NUMBER
)
Sample data is like this :
col_id bed_amnt ...
- 1,486
0
votes
1
answer
114
views
Query to show whether a record is appeared in the previous date or not
I have a table with below structure and sample data:
create table TEST_ONE
(
purchase_date DATE,
customer_id NUMBER
)
| purchase_date | customer_id
----------------------------------
|...
- 1,486
0
votes
3
answers
1k
views
Does using "qb_name()" hint (query block name) in a query have significant effect on performance?
I've just become familiar with the hint "qb_name" (query block name) in Oracle and below you can see an example of using this hint in a query:
select
/*+
qb_name(main)...
- 1,486
0
votes
2
answers
3k
views
Inserting data into the VIEW , result in data being inserted into the original table, why is this happening?
I have a table as you can see below:
create table table_test
(col_id number(1))
The table has below data:
col_id
------
1
2
3
I create a view based on this table:
create view v_table_test as
...
- 1,486
0
votes
1
answer
3k
views
Executing a query with a variable in a loop
I have two select, how can I do the same using a loop and a variable in PL SQL Developer.
I have:
select 1 as nm from dual;
select 2 as nm from dual;
I need something similar:
DECLARE x NUMBER;
BEGIN
...
- 1
0
votes
0
answers
1k
views
How to use PIVOT or LISTAGG to solve this?
I have a table like below.
USERID
USERNAME
ITEM
12345
JESSE
ITEM1
12345
JESSE
ITEM3
12345
DON
ITEM2
12346
DANNY
ITEM4
12346
ELISA
ITEM1
12347
ANDY
ITEM2
12347
ANDY
ITEM4
What im trying to ...
- 1
0
votes
1
answer
4k
views
oracle pl/sql block throwing ORA-00922: missing or invalid option
I have the following segment of oracle pl/sql block
DECLARE
rec all_tab_columns%ROWTYPE;
v_tableName VARCHAR2(100);
v_columnName VARCHAR2(100);
v_schemaName VARCHAR2(100);
BEGIN
...
- 13
0
votes
0
answers
109
views
What establish a session?
As an analyst I'm running multiple queries on our Oracle ERP via Power Query (Power BI). Now, I hit some form of session limit on my default profile's session limit. How do you evaluate how many ...
0
votes
0
answers
899
views
Oracle 19c Select Query with Database Link
How can I use the following query with database link:
set pages 999
col "Size MB" format 999,999,999
col "Objects" format 999,999,999
col OWNER for a25
select obj.owner "Owner&...
- 29
0
votes
2
answers
2k
views
Is it possible to nest CASE-statement into WHERE clause?
I am developing an application with FastAPI. It contains a function that brings two variables into a SQL query using the sqlalchemy.sql.text as shown here.
SELECT ATTR3
FROM TABLE
WHERE ATTR1 = ...
- 177
0
votes
1
answer
4k
views
Facing error "cannot fetch plan for SQL_ID" while trying to capture the execution plan
I'm trying to capture the execution plan of my query on PL/SQL developer as you can see below :
select *
from vmi_dimcustomer t1
inner join vmi_factcustomer t2
on t1.customer_num = t2....
- 1,486
0
votes
2
answers
1k
views
Insert query in Orcale database - not showing the row in a table
I am inserting a row in the Oracle database table. The query looks like as below -
INSERT INTO <schema_name>.<Table_name> (SID, LABEL, DESCR, GTI_EXPTYP, GTI_MODUS, GTI_NAME) VALUES (22, '...
- 11
1
vote
1
answer
2k
views
The correct way for generating "Execution plan" for a PL-SQL statement in Oracle
I'm studying about Explain plan and Execution plan in Oracle and according to what I've read so far , The explained plan can differ from the actual plan used during statement execution. So I think ...
- 1,486
2
votes
2
answers
432
views
weird behaviour of NVL(<Column_name>,Sequence.nextval) when the colummn has data?
I've created a sequence like this :
CREATE SEQUENCE seq_test2
MINVALUE 0
MAXVALUE 999999999999999999999999999
START WITH 0
INCREMENT BY 1;
Both of these two queries show me the number 0:
...
- 1,486
2
votes
3
answers
140
views
Ordering streets by their names and polysyllabic numbers in SQL query
In Oracle DB there is a table with some data:
LAG
HNR
STREET
00020
44
Aachener Straße
00020
44/2
Aachener Straße
00020
44/1
Aachener Straße
00020
46
Aachener Straße
00020
46/6
Aachener Straße
...
- 177
0
votes
1
answer
550
views
lock and get next n records from table in oracle
I have a oracle table PHONE_NUMBER which has 2 columns PHONE and STATUS
PHONE_NUMBER
PHONE STATUS
1 U
2 O
3 U
U stands for used , O is open/UnUSED .
in my input I will get n as input ...
- 101
3
votes
3
answers
17k
views
How to solve error "ORA:01-006 :bind variable does not exist" when working with bind variables?
I have a table with below structure and data :
create table TEST_TABLE
(
item_number NUMBER,
item_name VARCHAR2(50),
item_col VARCHAR2(50),
item_pol VARCHAR2(50)
)
Sample data:
...
- 1,486
0
votes
1
answer
520
views
Fetch all rows and leave column value empty if no equality match
I have a query where I want to fetch all rows where the value in column x is between 400-600.
Including in this I want a matching column value from antoher table.
But I want all 200 rows between 400 ...
- 103
0
votes
0
answers
127
views
Oracle and stopkey of an index range scan?
I am struggling to resolve a performance issue with an Oracle query that is opting to perform a full table scan of a table with several million records. The table is partitioned by month of a ...
- 273
0
votes
1
answer
486
views
Better way of handling incorrect date format in a column with "char(10)" data type / TRY_CONVERT equivalent in PLSQL
I have a source table with below structure:
create table customer_info
(customer_num number,
birth_date char(10))
Unfortunately the birth_date column data type is char(10) instead of date. ...
- 1,486
1
vote
2
answers
6k
views
Query to find all dates between two specified dates using plsql
I have a table with below structure
create table test_table
(
customer_num NUMBER,
card_number char(2),
card_issue_date DATE,
card_expire_date DATE
)
Example data is:
...
- 1,486
0
votes
1
answer
2k
views
how to get the difference between 2 tables in 2 different schema's in Oracle? [duplicate]
I am using Oracle 11G.
I have a table called Tickets in the schema called 'tickets1"
I have the same table structure in another schema called 'tickets2'
I want to find out
What data is the same ...
- 176
0
votes
1
answer
295
views
Procedure with "bind varibale" does not return the desired result set
I have a table with below structure:
create table CUST_MODERN_CHANNEL_BRANCH
(
customer_Num NUMBER,
channel_Code NUMBER(5)
)
Some example data :
Customer_Num Channel_Code
========...
- 1,486
0
votes
2
answers
939
views
How to retrieve missing values of an id from a list?
have a data structure that is value/tag inside a table on Oracle sql
Lets say I have this structore
exTable
ID
TAG
VALUE
1
1
x
1
4
y
2
2
x
2
3
y
2
4
z
I want to retrieve the missing tags for a ...
0
votes
1
answer
173
views
PL-SQL "LAST_VALUE" function does not give the desired result
I have a table with below structure:
create table DOC_AMOUNT_DETAIL
(
col_id NUMBER,
amount NUMBER,
operation_date DATE
)
and some example data for this table is :
col_id ...
- 1,486
0
votes
2
answers
3k
views
PL-SQL query to find the most recent record the "Operation_date" of which is lower than a specified date
I have a table with below structure:
create table DOC_AMOUNT_DETAIL
(
col_id NUMBER,
amount NUMBER,
operation_date DATE
)
some example data for this table is :
col_id | ...
- 1,486
0
votes
1
answer
174
views
SQL Query - Pivot with Ranges
This is my sample table
BSEG_ID DST_ID BILL_SQ AUDIT_CALC_AMT BILL_SQ, MSR_PEAK_QTY_SW, DESCR_ON_BILL, VERSION, AUDIT_CALC_AMT
12334 REV-WSC 62 6.40667 For 0 - 60 (Next 62.000 cubic ...
- 1
-1
votes
3
answers
2k
views
Oracle 11g, show running queries [duplicate]
I've a web application which submits some queries to Oracle 11g. I want to know what are these queries, exactly.
I there a way I do this in Oracle 11g? Couldn't do this in the web application itself.
0
votes
1
answer
924
views
Query returns NULL - Oracle
I've an Oracle query where I need to get some data on the basis of some records (strings). I am using IN clause and query is working fine in TOAD with hardcoded data. But when I run that query through ...
- 175
0
votes
1
answer
195
views
Seperate value of a specific column with "," delimiter in Oracle using "Regular Expression"
I have a table with below structure:
create table CUSTOMER_INFO
(
cust_id NUMBER,
cust_name VARCHAR2(50),
cust_address VARCHAR2(50)
)
Example Data :
cust_id | cust_name | ...
- 1,486
3
votes
2
answers
170
views
Get if field has max value and display value
I need to display Y or N when displaying rows in a report based on the max value for them on their version. If they have multiple versions all of them will display 'N' but the max (last version) will ...
