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 …

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 …

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 …

Oracle PL/SQL SUM OVER( ) starting from certain row

I have a table who looks like this: Pam_A Week Value_1 A 1 10 A 2 13 B 3 15 B 4 10 B 5 11 B 6 10 I want to achieve …

Why PLSQL optimize level is not providing expected result?

I am trying to understand complier optimizations in plsql in depth. Theoretically, default optimisation level PLSQL_OPTIMIZE_LEVEL is set to 2. To get better performance, we can set it to 3. To …

How to implement AFTER INSERT Trigger in Oracle PL/SQL?

I am trying to implement after insert trigger in PLSQL. The goal is to check if there are multiple (>1) rows having specific status for each client. If so I’d like to rise an exception and roll …

How to insert from old table to new table using trigger in pl/sql

There is a column which calls as text (column’s name) inside a table. I want to create different columns from this column. How to insert to another table these columns using trigger. I tried few …

offset with parameter in PL/SQL procedure

I have a PL/SQL procedure. It executes a SQL statement and returns a json responce. I would like to limit the rows returned using the offset clause. eg: select * from wherever where something = …

how to resolve error while using grant admin option in execute immediate

I am trying to run the below script. My script works fine without ‘ WITH ADMIN OPTION’ in EXECUTE IMMEDIATE. But when using ‘ WITH ADMIN OPTION’ i get below error. “Error report – ORA-00900: …

Can I combine these two queries into a single query?

Note:AUT_GROUP_ID column in Table CBS_OWNER.AUT_GROUP_MAP is the only factor differentiating both queries. AUT_GROUP_ID IN (20,4,7) —>ONNET AUT_GROUP_ID IN (3,6) —> OFFNET The first query …