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.
596
questions
0
votes
1
answer
47
views
how to get explain plan from pl/sql function call?
consider this working pl/sql block:
declare
o_result boolean;
io_error varchar2(30000);
tab1 someTable1;
tab2 someTable2;
begin
tab1(1).col1 := 'str'...
0
votes
1
answer
44
views
How to create a stored procedure from another stored procedure dynamically in OracleI have
I have a Stored Procedure that outputs the DDL of another Stored Procedure. I need to execute this DDL dynamically. Meaning, the second Stored Procedure should get created when the first Stored ...
0
votes
1
answer
28
views
WHY is NO DATA FOUND a predefined exception?
In MSSQL, neither SELECT queries that return no rows nor INSERT\UPDATE\DELETE queries that affect no rows are treated as an exception. In my tSQL code, there are plenty of situations where an empty ...
-1
votes
1
answer
84
views
Need help with DBMS_COMPRESSION.GET_COMPRESSION_RATIO
I need some help on how to run the DBMS_COMPRESSION.GET_COMPRESSION_RATIO package on all the tables of a database to see if is worth the implementation or not.
This is the procedure I'm running alone ...
0
votes
1
answer
140
views
Receiving error "ORA:22905-can not access rows from a non-nested table item" while working with ref cursors in JSON in PLSQL
I have created below procedure. I am getting ORA:22905-can not access rows from a non-nested table item at last line : INTO JSON_DATA FROM TABLE(InvoiceDetails). What I am doing here is retrieving out ...
0
votes
1
answer
46
views
Error when creating trigger to insert values
I am new to databases and I know this is a simple question but I cannot find an answer anywhere, so I appreciate the help.
I have 2 tables, each with the same structure.
Table1
CaseNum Number(9,0)
...
0
votes
0
answers
33
views
Export query result to Oracle Server
I have written PL/SQL code to store XML into cursor.
Refer the below code
Requirement:
As of line at line 83, I am inserting the result into another table (XML_GEN) as XMLTYPE datatype.
Line: insert ...
2
votes
2
answers
128
views
Getting wrong/misleading info related to oracle table index
Creating a table as follows:
CREATE TABLE "HOTEL3"
( "NAMEX" VARCHAR2(4000 CHAR),
"CITY" VARCHAR2(4000 CHAR),
"PRICE" NUMBER,
"ID&...
3
votes
1
answer
436
views
Oracle database: how to change varchar length of same column in multiple tables in one go?
My company uses Oracle 19 and we have many thousands of tables which have two columns called CRBY and MODBY in them, which are varchar2(200). These columns get populated by a script called on an ...
1
vote
0
answers
249
views
Password hash in apex oracle
i'm trying to hash or encrypt the password provided by the user in my apex oracle before store it in the DB table How can i do it ? i tried using DBMS_CRYPTO but it always generate an error saying
...
0
votes
2
answers
21
views
Compare dev/prod databases functions
I have two databases - dev and prod. I modify logic and debug in dev then pull everything to prod. A significant part of my app are PostgreSQL PL/pgSQL functions. I write them in dev, test, make sure ...
0
votes
1
answer
54
views
Update or insert records to the same table based on a similar table but with conditions
I'm tryin to update or insert records on the same table based on a similar table using Merge but based on the count of records in the source table.
I have 2 tables say one is POV [pov, dc, item](the ...
-1
votes
1
answer
59
views
How to rollback data mart update if data quality checks are not passed?
Good afternoon!
I am working with Oracle database. I have a PL/SQL procedure which is scheduled with DBMS job to update every night a table SOME_TABLE:
CREATE OR REPLACE PROCEDURE MY_PROC
IS
...
0
votes
0
answers
115
views
Advice on Oracle Flashback Data Archive
I am looking for some help / advice on using Flashback Data Archive.
In a test system we enabled FDA on a handful of tables. Before enabling FDA, our UNDO tablespaces were empty - 0 MB.
After ...
0
votes
2
answers
117
views
Script disabling/enabling foreign keys
I want to script disabling and enabling Foriegn KEY constraints.
I'm testing with the following setup. As you can see from below I am able to successfully disable the constraints but I can't seem to ...
0
votes
1
answer
186
views
Set column value to different randomly chosen values from another table
My goal: Set PID_FM in every row to a randomly picked value out of all the values matched by the subquery.
However the FM_ID is only picked once, and therefore the whole column has the same value. I ...
0
votes
1
answer
119
views
Remove all characters except two letters
I am looking for a logic to remove all characters in a string except AD & Numeric values.
Examples
String
Expected Result
AD-892839 statement
AD-892839
BOD AD-89283
AD-89283
BOD AD-89283 ACGD
...
1
vote
2
answers
838
views
DBMS_SCHEDULER running 10 instance of the same job
I'm trying to have a single scheduler that will run multiple (from 4 to 10) jobs at the same time.
I know this is doable if I just make multiple dbms_scheduler of the same job, (job1, job2....) . but ...
1
vote
1
answer
434
views
Need a PL/SQL code to drop many partition in 1 single SQL statements
I have a requirement where we need to drop historical partitions which are not dropped .
Requirement is dropping multiple partitions in one step instead of dropping one by one.
like
ALTER TABLE sales ...
0
votes
0
answers
22
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 ...
0
votes
1
answer
768
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
vote
0
answers
154
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
515
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 ...
0
votes
2
answers
197
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 ...
0
votes
2
answers
244
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 ...
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 ...
0
votes
1
answer
350
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
vote
0
answers
196
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 ...
3
votes
1
answer
2k
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
...
0
votes
1
answer
222
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
475
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 ...
0
votes
1
answer
160
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
vote
1
answer
221
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 ...
0
votes
0
answers
41
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 ...
0
votes
0
answers
64
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
1
answer
34
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
27
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
)
...
-2
votes
1
answer
935
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 ...
0
votes
1
answer
378
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 ...
2
votes
1
answer
564
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 ...
-1
votes
1
answer
33
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
40
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
544
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
111
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 ...
-6
votes
1
answer
458
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
61
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
171
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/...
0
votes
1
answer
48
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
vote
1
answer
578
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 = ...
0
votes
1
answer
438
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
...