Questions tagged [plsql]
Procedural Language/Structured Query Language is Oracle Corporation's procedural language extension for SQL. IBM supports PL/SQL for DB2 since version 9.7. Questions about PL/SQL should probably be tagged "oracle" or "db2" as well.
586
questions
0
votes
0
answers
15
views
Why is it that when filtering by two collections, the execution time increases significantly and the query takes a very long time?
Given: A table with 6 million records. We need to collect some statistics for the quarter. 650 thousand lines fall into the quarter.
The data filtering parameters come into the procedure. Some of ...
- 101
-1
votes
0
answers
11
views
Ways to create database in oracle XE
I am quite new to the database topic. I have already installed Oracle XE. The questions are
Whether I am going to set up the database as well as the initial creation of tables by using oracles DBCA (...
- 1
0
votes
1
answer
40
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,390
1
vote
0
answers
15
views
PL SQL code that Inserts data from a different table and gives new id values from the same table that the data is being inserted
What could I change to make this insert work and give a new ID value on each row I am inserting? Right now it's giving all the inserted rows the same ID number. Sadly, I can't use triggers and ...
1
vote
2
answers
60
views
PL/pgSQL select statement inside trigger returns null using where NEW
I'm creating a trigger that triggers ON INSERT to a table, and I wish to log the structure of tables inserted so I wrote this function:
CREATE OR REPLACE FUNCTION update_table_log_received()
RETURNS ...
- 36
0
votes
2
answers
62
views
Set up PL/SQL Developer
i have got PL/SQL developer installed. However, it asks me to enter a password, username, and database. I dont know what to enter in this case. Am I going to need "oracle SQL client" to set ...
- 1
0
votes
2
answers
62
views
How to implement "SELECT INTO" in Oracle with PostgreSQL commands?
I have seen many explanations about SELECT INTO in the oracle clause. I found a difference in applying SELECT INTO in Oracle and other SQL databases. In Oracle, the SELECT INTO statement retrieves ...
0
votes
1
answer
44
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,390
0
votes
1
answer
48
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,390
0
votes
0
answers
61
views
Is there a utPLSQL way to mock procedures, functions, or tables?
In tSQLt, you can isolate dependencies for unit testing using procedures like FakeTable to create a dummy version of whatever your test subject depends on, that contains only data you specify. This is ...
- 153
3
votes
1
answer
534
views
check if value exist in another table before insert SQL
I have table: " daysoff " and table: " rdv "
the rdv table looks like this :
temps_rdv
etat_rdv
ID_RDV
12-10-2022
reservee
2
the daysoff table looks like this :
ID
DATE_OFF
...
- 41
0
votes
0
answers
14
views
To_number() function with rpad() function , how it works?
I saw this piece of code and I have no idea how it works . I was wondering if you could help me figure it out :
column Col_one data type is number and you can see the code below:
select to_number([...
- 1,390
0
votes
1
answer
93
views
Oracle Edition-based redefinition(EBR) use case
We have some pretty high-load service in oltp database built using Oracle's procedures, functions, views and etc.
Whenever we want to update our code base, it's usually throws an error like
ORA-...
0
votes
0
answers
137
views
Syntax error near declare statement Cursor %TYPE
As a beginner, I m playing with PL/SQL where I am simply displaying list of employees. however, I am facing this syntax error issue near where I wrote declare statement, I am not able to figure out ...
- 101
0
votes
0
answers
264
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,390
0
votes
1
answer
98
views
Loop through schemas and execute dml on the same table in each schema
I have a table "active_t" in a schema called "test", having the following structure:
instance active
IN 1
SI 0
The ask is to loop ...
- 1
1
vote
1
answer
130
views
Oracle PL/SQL: Trigger Syntax
I'm kinda new(like 1 year of experience) in PL/SQL and i have to make a kind of tricky trigger, and i want to check if my syntax is correct, mainly the selects, inserts and variable value store i make ...
- 11
0
votes
0
answers
28
views
How to pass multiple values while calling a procedure without using comma
I have written a procedure to update the status of a column.
Since there are more than 1000 records it's not easy to pass all values using commas.
I have a list of case_intrl_id's in excel and want to ...
- 1
0
votes
0
answers
34
views
Apex page DML Mail Alert Trigger
I have a Trigger and Function which sends mail with "updated" column(s),
when i am updating in my back-end (sql developer), i receive a mail with that particular column.
But when i do UPDATE ...
0
votes
0
answers
204
views
PL/SQL get the session ID (SPID) during a stored procedure on Oracle 19c
I'm trying to get the session ID (SPID) during a stored procedure on Oracle 19c.
In Oracle 12c the following query was enough:
select s.sid, p.spid, substr(s.username,1,20) MY_SCHEMA, s.terminal, p....
- 1
0
votes
1
answer
29
views
Performance Problem : PL/SQL
SELECT
NULL AS TRX_NUMBER ,
NULL AS CUSTOMER_TRX_ID ,
VW_SAP_MPP_INV1.DOCENTRY AS DOCENTRY ,
VW_SAP_MPP_INV1.LINENUM AS LINENUM ,
VW_SAP_MPP_INV1.ITEMCODE AS ITEMCODE ,
VW_SAP_MPP_INV1....
0
votes
1
answer
23
views
Better ways of concating the value of columns
I have a table with below structure and sample data:
create table CUSTOMER_TEST
(
customer_num Number,
rel_one Number,
rel_two Number,
rel_three Number,
rel_four Number
)
...
- 1,390
-2
votes
1
answer
315
views
Which modern IDE to use when working with Oracle database? [closed]
Good afternoon!
I am working with Oracle Database (mostly writing queries, stored procedures and creating tables). The problem is that I can not find a modern IDE to work with Oracle such as Visual ...
- 11
0
votes
1
answer
116
views
oracle - Calculate sum or avg in one query based on the their value
I've this table
date
customer
amount
percentage
category
20220330
customer1
10
93
01
20220330
customer1
11
91
01
20220330
customer1
13
91
02
20220330
customer1
15
91
02
I' need to calculate
sum ...
- 171
2
votes
1
answer
379
views
How to terminate user's sessions in Oracle DB exactly 8 hours after user connected?
I would like to terminate user sessions (and optionally also lock the user), exactly 8 hours after user established DB connection. I tried creating a profile, but it just doesn't terminate user's ...
- 123
-1
votes
1
answer
29
views
Deleting duplicate value in View from the Base table
BASIC INFO : I have a table : YARDI_PROPERTY -> PK -> HMY, and a VIEW which uses HMY from it.
Issue : Getting duplicate HMY in the view.
Requirement : TO delete those duplicates.
IMPLEMENTATION :...
-1
votes
2
answers
38
views
Struggling with improving efficency of this code
QUERY FOR COLUMN INSIDE A VIEW(need for this to work faster)
CREATE OR REPLACE VIEW "ODS"."VW_YARDI_DLR_PROPERTY_INFORMATION" ("SPCODE", "HMY", "SPNAME&...
0
votes
0
answers
347
views
During the trigger execution getting error : ORA-00604: error occurred at recursive SQL level 1 ORA-00900: invalid SQL statement
When I am trying to execute this trigger getting following error:
CREATE OR REPLACE TRIGGER create_table_trigger AFTER CREATE ON SCHEMA DECLARE
lv_sql VARCHAR2(5000);
obj_name VARCHAR2(...
0
votes
1
answer
52
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,390
-6
votes
1
answer
201
views
cursor syntax to fetch and drop tables in the db
I have a list of tables from a select query which gives me at least 50 tables daily .
Need to drop them as they are temporary. I need a cursor to fetch and drop each one of them in a loop.
I am using ...
2
votes
1
answer
42
views
sp_BlitzBackups alternative for Oracle Database
In the SQL Server world, there is a stored procedure called sp_BlitzBackups from the "First Responder Kit" that helps out a database administrator to compute a Recovery Time Objective and a ...
1
vote
0
answers
102
views
How to check compatibility of PL/SQL written for Oracle 9i but now to be migrated to Oracle 19c
I have lots of PL/SQL scripts which were developed years ago for an Oracle 9i application which should be migrated to Oracle 19c. I have read that normally there shouldn't be major changes in the PL/...
- 11
0
votes
1
answer
38
views
what does the number in the "A_ROWS" column of the execution plan shows for operation "Index scan"?
I have a query as you can see below :
select /*+gather_plan_statistics*/
*
from mi_dimcustomer t
where t.CUSTOMER_NUM = 321937
There's a Unique Index (IDX1_DIMCUSTOMER) on Customer_Num column of ...
- 1,390
1
vote
1
answer
365
views
Three "Nested loop join" in the Execution plan despite using two inner joins in the main query
I have a query as you can see below :
with cte as
(select customer_num
from vmi_segment_customer_relation
where effective_date = to_date('12/30/2021', 'mm/dd/yyyy')
and segment_id = ...
- 1,390
0
votes
1
answer
233
views
Getting warnings while generating query Execution plan in Oracle , PLSQL developer
These are the steps I follow to generate a real Execution Plan in PLSQL developer :
1)Using this hint in my main query /+gather_plan_statistics/
select /*+gather_plan_statistics*/ from mi_factcustomer
...
- 1,390
1
vote
1
answer
259
views
Truncate huge table of 6.4TB - Oracle
I've never truncated a huge table like that. How long can it take? or will be a millisecond or so like usually? Can I have some issue during this operation?
this table has 1.4TB size plus 4.8TB of lob ...
- 588
0
votes
2
answers
140
views
Oracle 12cR2 RAC - ORA-06508
Environment:
DB Version: Oracle Database 12cR2 EE on Exadata
RAC: True
Problem: ORA-06508 on procedure.
One of our users had a DBA role privilege, we don't want such a big
privilege to be defined ...
- 29
0
votes
1
answer
61
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,390
0
votes
3
answers
367
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,390
0
votes
0
answers
102
views
DBMS_HS_PASSTHROUGH Bulk Fetch
i have question about DBMS_HS_PASSTHROUGH geting result set without stored_procedure
i have oracle and i have database link so i am running this query select * from table where id >20 via ...
- 3
0
votes
1
answer
966
views
oracle stored procedure errors: ORA-00904 invalid identifier, dynamic SQL
I am trying to find all tables that contain column called arg_column_name, look up value arg_column_value in that column of those tables, and return names of the tables and counts of found rows to the ...
0
votes
1
answer
273
views
Extract SDO_GEOMETRY line vertices into a nested table column (in a query resultset)
Oracle 18c:
In a related post, I demonstrated how to use the GetVertices() function and the Table() function to extract the vertices of an SDO_GEOMETRY line:
Select SDO_GEOMETRY line vertices as rows
...
- 1,417
0
votes
0
answers
44
views
AND clause with IN function to compare/validate values from subquery
Greeting,
In the hypothetical scenario, I have a procedure with with an update statement which works by updating the values of specified table with the variable inputs. However, I have five AND ...
- 11
0
votes
2
answers
764
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,390
0
votes
0
answers
1k
views
Oracle Pl-SQL: Update for loop iterating for a very long time and not updating records
I trying to create a process in Oracle Apex to update certain columns of a table when a button is pressed which is related to accounting info. Moreover, I would like to validate the existence of ...
- 11
0
votes
1
answer
1k
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
809
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
1k
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
1
answer
413
views
left join working very slow with clause
with t1 as (select id, name from table_1
where t_date between to_date('2021-01-01', 'YYYY-MM-DD'
AND to_date('2021-01-31', 'YYYY-MM-DD'))
t2 as (select ...
0
votes
1
answer
160
views
ORA-00942, table does not exist [duplicate]
I'm trying to do a pl/sql with two loops. Inside second loop it gives me error:
ORA-00942.
I do the query manually and I don't have any error. Someone could help me please?
Thanks
begin
...
- 15