Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PLS-00103: Encountered the symbol "END" when expecting one of the following

Quanwen ZhaoFeb 13 2020 — edited Feb 15 2020

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

This post has been answered by gsalem-Oracle on Feb 13 2020
Jump to Answer
Comments
Post Details
Added on Feb 13 2020
13 comments
9,044 views