Hello my ODC friends :-),
I wanna build a test table with huge volume on my Oracle 19c of Windows 10 laptop. You know, "CONNECT BY" with "LEVEL <= a big number" my cause an error of "out of memory". So I decide to use a workaround to achieve my intention.
Firstly I create a table structure and secondly use "INSERT /*+ append */ ..." to insert 1e4 rows of test data, next use a small pieces of PL/SQL code with "FOR ... LOOP ..." to quickly insert another test data.
My demo is as follows.
c:\>sqlplus c##qwz/qwz
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 13 14:18:03 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Thu Feb 13 2020 12:38:46 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
14:18:03 C##QWZ@ora19c>
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
DROP SEQUENCE seq_id;
DROP TABLE person;
CREATE SEQUENCE seq_id;
CREATE TABLE person
( id NUMBER DEFAULT seq_id.NEXTVAL
, name VARCHAR2(6) NOT NULL
, sex NUMBER(1) NOT NULL
, birth_day DATE NOT NULL
, address VARCHAR2(16) NOT NULL
, email VARCHAR2(15) NOT NULL
, qq NUMBER(9) NOT NULL
, CONSTRAINT person_pk PRIMARY KEY (name)
, CONSTRAINT person_uq UNIQUE (id)
);
ALTER TABLE person NOLOGGING;
DECLARE
v_sql VARCHAR2(2000);
BEGIN
v_sql := q'[INSERT /*+ APPEND */ INTO person (name, sex, birth_day, address, email, qq) ]'
|| q'[SELECT DBMS_RANDOM.string('A', 6) AS name]'
|| q'[ , ROUND(DBMS_RANDOM.value(0, 1)) AS sex]'
|| q'[ , TO_DATE(TO_CHAR(DATE'1980-02-03', 'J')+TRUNC(DBMS_RANDOM.value(0, 365)), 'J') AS birth_day]'
|| q'[ , DBMS_RANDOM.string('L', 16) AS address]'
|| q'[ , DBMS_RANDOM.string('L', 6)]'
|| q'[@]'
|| q'[DBMS_RANDOM.string('L', 4)]'
|| q'[.com]'
|| q'[ AS email]'
|| q'[ , DBMS_RANDOM.value(10000001, 999999999) AS qq ]'
|| q'[FROM DUAL
CONNECT BY level <= 1e4]';
FOR i IN 1 .. 1000
LOOP
EXECUTE IMMEDIATE v_sql;
COMMIT;
END LOOP
END;
/
At that moment I encountered the following error.
END;
*
ERROR at line 22:
ORA-06550: line 22, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
; <an identifier> <a double-quoted delimited-identifier>
The symbol ";" was substituted for "END" to continue.
Could you help me troubleshooting it? Thanks beforehand!
Best Regards
Quanwen Zhao