0

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');
2

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.

  • Wow!!! this example will be really helpful to get start with and will also check the reference provided. Appreciate that!! – klamgade Aug 26 '16 at 6:50
  • 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 ; – klamgade Aug 28 '16 at 7:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.