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.

Filter by
Sorted by
Tagged with
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'...
rado's user avatar
  • 119
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 ...
Vincy Wales's user avatar
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 ...
KevinP's user avatar
  • 105
-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 ...
Daniel Soto's user avatar
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 ...
Niki's user avatar
  • 1
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) ...
bk_32's user avatar
  • 103
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 ...
user3792741's user avatar
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&...
deostroll's user avatar
  • 189
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 ...
SeanGaff's user avatar
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 ...
Safwen Hafsawy's user avatar
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 ...
Sergei Basharov's user avatar
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 ...
Akash Reddy's user avatar
-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 ...
rsx's user avatar
  • 1
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 ...
alexherm's user avatar
  • 101
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 ...
Pugzly's user avatar
  • 105
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 ...
a1234's user avatar
  • 1
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 ...
Madhu's user avatar
  • 11
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 ...
user3039977's user avatar
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 ...
Dhiren Singh's user avatar
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 ...
SwaD's user avatar
  • 101
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 ...
Pantea's user avatar
  • 1,412
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 ...
Kristofers Kaprāns's user avatar
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 ...
Moifek Maiza's user avatar
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 ...
Crues's user avatar
  • 3
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 ...
devins10's user avatar
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 ...
Pantea's user avatar
  • 1,412
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),...
Pantea's user avatar
  • 1,412
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 ...
JAF's user avatar
  • 163
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 ...
kylie's user avatar
  • 41
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-...
casmodeus's user avatar
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 ...
Pantea's user avatar
  • 1,412
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 ...
Yuvraj's user avatar
  • 1
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 ...
Nhoj Anec's user avatar
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 ...
shashi's user avatar
  • 1
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 ...
Notadoctor's user avatar
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....
Notadoctor's user avatar
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 ) ...
Pantea's user avatar
  • 1,412
-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 ...
rsx's user avatar
  • 1
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 ...
ciro's user avatar
  • 171
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 ...
korodani's user avatar
  • 123
-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 :...
EAZY_EZ_HE's user avatar
-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&...
EAZY_EZ_HE's user avatar
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(...
ashwani Kumar's user avatar
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 ...
Pantea's user avatar
  • 1,412
-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 ...
mamidipalli satish's user avatar
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 ...
Jefferson B. Elias's user avatar
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/...
IAMYB's user avatar
  • 11
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 ...
Pantea's user avatar
  • 1,412
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 = ...
Pantea's user avatar
  • 1,412
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 ...
Pantea's user avatar
  • 1,412

1
2 3 4 5
12