Oracle 12c - Inserting Records into Tables using PLSQL Extensions

Inserting Records into Tables

The PL/SQL extension to the SQL INSERT statement lets you insert a record into a table. The record must represent a row of the table.

Example creates the table schedule and initializes it by putting default values in a record and inserting the record into the table for each week.

Example: Initializing Table by Inserting Record of Default Values

DROP TABLE schedule;

CREATE TABLE schedule (week NUMBER,
Mon VARCHAR2(10),
Tue VARCHAR2(10),
Wed VARCHAR2(10),
Thu VARCHAR2(10),
Fri VARCHAR2(10),
Sat VARCHAR2(10),

Sun VARCHAR2(10));

DECLARE
default_week schedule%ROWTYPE;
i NUMBER;

BEGIN
default_week.Mon := '0800-1700';
default_week.Tue := '0800-1700';
default_week.Wed := '0800-1700';
default_week.Thu := '0800-1700';
default_week.Fri := '0800-1700';
default_week.Sat := 'Day Off';
default_week.Sun := 'Day Off';

FOR i IN 1..6 LOOP
default_week.week := i;
INSERT INTO schedule VALUES default_week;
END LOOP;
END;
/

COLUMN week FORMAT 99
COLUMN Mon FORMAT A9
COLUMN Tue FORMAT A9
COLUMN Wed FORMAT A9
COLUMN Thu FORMAT A9
COLUMN Fri FORMAT A9
COLUMN Sat FORMAT A9
COLUMN Sun FORMAT A9

SELECT * FROM schedule;

Result:

WEEK MON TUE WED THU FRI SAT SUN
---- --------- --------- --------- --------- --------- --------- ---------
1 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off Day Off
2 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off Day Off
3 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off Day Off
4 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off Day Off
5 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off Day Off
6 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off Day Off

Comments

Post a Comment

Popular posts from this blog

Query to get FSG report details with row/column set

Below query is to fetch FSG report titles, names, and description along with will give a view to list of row sets and column sets associated with the report. -- FSG REPORTS -- SELECT fst.id_flex_structure_name , R.NAME , R.report_title , R.DESCRIPTION , R.column_set "COLUMN SET" , rw2.structure_id , rw2.DESCRIPTION "COL DESC" , R.row_set "ROW SET" , rw.DESCRIPTION "ROW DESC" , R.report_display_set "DISPLAY SET" , R.content_set "CONTENT SET" , R.row_order "ROW ORDER" , R.rounding_option "RND" , u.user_name , u.DESCRIPTION , R.creation_date FROM rg_reports_v R    , fnd_id_flex_structures_v fst    , fnd_user u    , rg_report_axis_sets_v rw    , rg_report_axis_sets_v rw2 WHERE r.structure_id = fst.id_flex_num AND R.row_set_id     = rw.axis_set_id AND r.column_set_id  = rw2.axis_set_id AND SUBSTR(fst.id_flex_structure_name,1,2) IN ('DC') AND R.created_by     = u.user_id OR...
Read more

Sub Ledger Period Close Exception Report - R12

Image
Sub Ledger period close exception Report is used by finance and accounts department to view list of un-accounted transactions in Accounts receivables before closing GL Period Un-processed transactions will be listed in the report for GL period Report summary shows following details: Against a specific ledger, period, and journal source how many exceptions are there (i.e., unprocessed transactions with count) Detail of transactions which are un-processed will be grouped against journal category and event classes This report helps in resolving exceptions quickly so that exceptions are not left to slow down the period-end close It is automatically submitted by General Ledger when closing a GL period if there are unprocessed accounting events or non-transferred journal entries. 
Read more

Sub Inventory Transfer API.

Image
Sub Inventory transfer is used to move materials from one sub-inventory to another within same organization. This is especially used to 1. Adjust on-hand quantities between sub-inventories 2. Transfer to another sub-inventories for specific business need like additional sales 3. Damages/expiry products can be moved to non-asset sub-inventories to discard it from inventory valuation report and so on., Key elements 1. From sub inventory 2. To sub inventory 3. Organization 4. Inventory item 5. Transact quantity 6. Transact unit of measure 7. Transaction type 8. Transaction action 9. Transact Reference 10. Transact date 11. Lot Number Open Interfaces mtl_transactions_interface - is used to populate sub inventory transfer details mtl_transaction_lots_interface - is used to populate lot details for particular sub inventory transfer Base Tables mtl_material_transactions - is used to get all type of material transactions like sub-inventory transfer, inter-org tra...
Read more