Good Day Buddies! So, here is my Question, it says – Write a update, delete trigger on clientmstr table. The System should keep track of the records that ARE BEING updated or deleted. The old value of updated or deleted records should be added in audit_trade table. (Separate implementation using both ro…
Tag: plsql
Generate Result based on max count in secondary column after a join
I have two tables which have a common key between them, and quite a lot of other important infos ; for the sake of simplicity i will be using Combination A and Combination B. When a combination is met, whichever table has the maximum number of records should be the source where i collect the information ; in …
How to analyze metadata of given schema name and table name dynamically in Oracle?
I am trying to write a dynamic oracle plsql script that takes schema_name and table_name for parameter and returns Primaty Key Column names,Index flag(if table contains index returns 1 else 0),data volume for size of table in db,row count and column count of selected schema and table.Here is my code It gives …
Accpt employee number from user fetch the employee details from the “Employee” table and store it to a PLSQL record type variable and print the same
Accpt employee number from user fetch the employee details from the “Employee” table and store it to a PLSQL record type variable and print the same. Answer Here’s one option (if that’s what you’re looking for):
Oracle APEX master detail page creation: ORA-06531 error
I am creating a flock management application with APEX with the following PL/SQL scheme: In this scheme, the SALE and EVENT tables reference the EID field of the SHEEP table (unique identifier of 5 numbers and one letter – representing the yellow tag they have in their ears). I am trying to create a Mas…
How to declare a cursor inside another cursor
How declare another cursor inside cursor c_employees? Note: cursor c_employees return employees_id and this id pass to other cursor in the where clause. For example Answer Cursors can take parameters hence But generally, when you start seeing cursors with cursors etc etc…its also time to look at whether…
Add new column with Boolean in PL/SQL
I’m learning PL/SQL right now and I have a doubt. I have created the following table called tbProducts: And I have inserted some values so the table is like this: Now what I’m looking for is a boolean variable that can be called bUpdate that returns FALSE if today’s date (26-AUG-2021) is gre…
DELETE By Procedure With String Input – Oracle
I am writing a procedure to delete some records of z_names table by IDs from the input as string (and I need to seprate them by special character (comma ,)). Sample Input: ‘1,4’ z_names table: ID NAME 1 jim 2 john 3 jack 4 alex After running this procedure, the record with ID 1 and 4 (jim and alex…
Sql Join selecting records from A table and selecting matching records with a condition from B table
We have Table A and Table B, Data we need is in table A and we use table B to validate case where we have matching Ids. If ids do not match we can select it without validation, but if they match we need to check if date is in between date1 and date2. We only check records in B
Oracle save updated rows in log
I have an update in my stored procedure. I would like to, save in my log the number of rows updated. How can I achieve this? Answer You can do something like this: