Oracle sql query to group two sets of dates if sequencials

I have the next table: EMPLOYEE CODE START_DATE END_DATE 02097368 F7H3 09/07/2018 20/10/2018 02097368 F7H3 21/10/2018 05/01/2019 02097368 F7H3 06/01/2019 12/01/2019 02097368 F7H3 13/01/2019 02/02/…

How to test PL/SQL procedure with varray input

I’ve made a procedure that uses a varray as input, and while everything compiles correctly, I can’t figure out how exactly to test/execute the procedure that actually works. The varray takes in at …

Failing to produce or update a table row using a trigger in SQL

I am trying to create a trigger in Oracle SQL 12c. The concept is that i have two tables, a storage table (Storage) that holds items and a delivery table (Supplier_Orders) that notes how many items(…

PL/SQL Dynamic SQL : Table name not valid

I’m currently learning PL/SQL. I need to create a PL/SQL block to create a backup of all my tables like this : myTable -> myTable_old. Here’s what I got right now : DECLARE Cursor c IS SELECT …

Loop through JSON array in PLSQL 12.1

I have JSON array inside varchar DECLARE JsonArray varchar2(1000); arrayCars varchar2(1000); BEGIN JsonArray :={“Cars”: [{“name”:”Honda”, “color”:&…

Create Children and Parent of a Person

I have the following Type Address and PhoneNumber: CREATE TYPE Adress AS Object (Street varchar2(50), PostalC number, Ville varchar2(50)); CREATE TYPE PhoneNumber AS Object (Ind varchar2(3), PhNumber …

How to filter json in PLSQL?

My data look like: CLUID EVENT_PAYLOAD STEP 1998-06-22-23.58.16.792243 {“type”:”page”,”name&…

Oracle optimize select after update status performance

I have a store procedure that will Update maximum 500 rows status from 0 to 1 Return those rows to program via cursor Here is my store procedure code PROCEDURE process_data_out ( o_rt_cursor …

Calculate age from date of birth with formatting

I would like to calculate the age from the date of birth stored as a string using the format in the comments in the code. Here’s my code: –pnr values –‘19490321-7000’ –‘19540201-7000’ –‘19650823-…

Update table inside a loop in a procedure pl/sql

So i want to hire a new manager for a department. This procedure has 2 parameters, the department name i want to change, and the new id for manager (taken from employee’s id). So to change it, i need …