creating stored procedure to populate empty table using pl/sql

Question

I have created a table and inserted a values one row at a time. However, I am trying to populate the table using a "stored procedure" or "trigger". The values can be a random string or number. I have been researching on it, but couldn't find any related examples.

Could some please provide an example or suggest some reference to it? Cheers!!

CREATE TABLE BOOKING 
(
 BOOKING_ID VARCHAR2(7) PRIMARY KEY
, BOOKING_DATE DATE 
, BRANCH_ID VARCHAR2(7) 
, ROOM_PRICE NUMBER(15) 
, IS_BOOKED VARCHAR(5))
TABLESPACE TS02
PCTFREE 20 PCTUSED 70
STORAGE (INITIAL 200K NEXT 500K PCTINCREASE 20);
--to insert value to BOOKING table for one row per at a time
INSERT INTO BOOKING
VALUES ('HIM001','02-FEB-2016','KTM',500,'NO');

Answer 1

You can do it with simple SQL statements and DBMS_RANDON as demonstrated below:

CREATE TABLE BOOKING 
(
 BOOKING_ID VARCHAR2(7) PRIMARY KEY, BOOKING_DATE DATE 
, BRANCH_ID VARCHAR2(7) 
, ROOM_PRICE NUMBER(15) 
, IS_BOOKED VARCHAR(5));
--You may create a sequence object to generate IDs. 
CREATE SEQUENCE booking_id_seq
 START WITH 1
 INCREMENT BY 1
 NOCACHE
 NOCYCLE;
INSERT INTO booking
SELECT booking_id_seq.nextval, TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)),
dbms_random.string('U',trunc(dbms_random.value(1,7))), trunc(dbms_random.value(1000,100000)), dbms_random.string('U',trunc(dbms_random.value(1,5)))
FROM dual
CONNECT BY level <= 12;-- Here you can define the number of rows that you want to insert.
SQL> INSERT INTO booking
SELECT booking_id_seq.nextval, TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)),
dbms_random.string('U',trunc(dbms_random.value(1,7))), trunc(dbms_random.value(1000,100000)), dbms_random.string('U',trunc(dbms_random.value(1,5)))
FROM dual
CONNECT BY level <= 12; 
12 rows created.
SQL> select * from booking;
BOOKING BOOKING_D BRANCH_ ROOM_PRICE IS_BO
------- --------- ------- ---------- -----
158 06-NOV-16 ULQXM 81615 SH
159 18-DEC-16 RTG 99655 XDY
160 15-JUN-17 UF 45335 MCY
161 01-JUL-17 I 23249 FGS
162 13-MAY-17 KLLL 2177 T
163 07-AUG-17 HFC 10259 UKD
164 12-FEB-17 TVIZQY 98038 PJL
165 10-JUN-17 BIP 94314 ZSRS
166 22-JUL-17 ERQO 45985 QYC
167 27-AUG-16 EJKYB 39139 NJO
168 12-APR-17 AASMB 86838 NY
BOOKING BOOKING_D BRANCH_ ROOM_PRICE IS_BO
------- --------- ------- ---------- -----
169 24-FEB-17 NCQPMS 38296 Z
12 rows selected.
SQL> 

Here are some good examples of using DBMS_RANDOM PL/SQL Package.

• klamgade: Wow!!! this example will be really helpful to get start with and will also check the reference provided. Appreciate that!!
• klamgade: I played around with the above code and created a procedure and executed it, which works fine. However, I am required to make the script robust enough so it can be reused on a daily basis without a need for modification. I am not sure what does that actually mean. Please advise if any idea. CREATE OR REPLACE PROCEDURE load_branch_values IS BEGIN INSERT INTO branch SELECT booking_id_seq.nextval,dbms_random.string( ...... then used execute load_branch_values ;