TOAD 13.0.0.57 Generate Data produces invalid SQL to generate data

Toad for Oracle - Beta
1

Good Morning TOAD Team,

I’ve been using TOAD to populate data for many tables in a new system. I recently upgraded to 13.0.0.57 and the SQL TOAD Generate Data creates is invalid. The SQL created is attached (how do I attach files, now? I’ll simply copy and paste below). I plan to return to TOAD 12.11 to populate this table while you resolve the bug.

/************************************************************
** Table: CMS.CMS_GRANT
** Number of Records: 100
************************************************************/

DECLARE
SUBTYPE MAXVARCHAR2 IS VARCHAR2(32767);
TYPE Used_Array IS TABLE OF MAXVARCHAR2 INDEX BY MAXVARCHAR2;
TYPE GRANTID_Array IS TABLE OF CMS.CMS_GRANT.GRANT_ID%TYPE INDEX BY PLS_INTEGER;
TYPE GRANTNUMBER_Array IS TABLE OF CMS.CMS_GRANT.GRANT_NUMBER%TYPE INDEX BY PLS_INTEGER;
l_GRANTID GRANTID_Array;
l_GRANTID_Used Used_Array;
v_GRANTID CMS.CMS_GRANT.GRANT_ID%TYPE;
l_GRANTNUMBER GRANTNUMBER_Array;
l_GRANTNUMBER_Used Used_Array;
v_GRANTNUMBER CMS.CMS_GRANT.GRANT_NUMBER%TYPE;
t_seed NUMBER(20);
t_exists BOOLEAN;
BEGIN
– Initialize Random Number Generator
SELECT TO_NUMBER(TO_CHAR(SYSDATE, ‘MMDDHH24MISS’))
INTO t_seed
FROM Dual;

DBMS_RANDOM.INITIALIZE(t_seed);

– Bulk Collect existing values into arrays
SELECT GRANT_ID BULK COLLECT INTO l_GRANTID FROM CMS.CMS_GRANT;
FOR t_counter IN 1…l_GRANTID.COUNT LOOP
l_GRANTID_Used(TO_CHAR(l_GRANTID(t_counter))) := TO_CHAR(l_GRANTID(t_counter));
END LOOP;

SELECT GRANT_NUMBER BULK COLLECT INTO l_GRANTNUMBER FROM CMS.CMS_GRANT;
FOR t_counter IN 1…l_GRANTNUMBER.COUNT LOOP
l_GRANTNUMBER_Used(TO_CHAR(l_GRANTNUMBER(t_counter))) := TO_CHAR(l_GRANTNUMBER(t_counter));
END LOOP;

– Generate Random Data
FOR t_counter IN 1…100 LOOP

– Generate random value for GRANT_ID and compare against existing list
LOOP
v_GRANTID := CMS.CMS_GRANT_SQ.NextVal;
t_exists := l_GRANTID_Used.EXISTS(TO_CHAR(v_GRANTID));

IF NOT t_exists THEN
l_GRANTID_Used(TO_CHAR(v_GRANTID)) := TO_CHAR(v_GRANTID);
END IF;

EXIT WHEN (NOT t_exists);
END LOOP;

– Generate random value for GRANT_NUMBER and compare against existing list
LOOP
v_GRANTNUMBER := (SELECT GRANT_NUMBER FROM (SELECT GRANT_NUMBER FROM CMS.CMS_GRANT ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1);
t_exists := l_GRANTNUMBER_Used.EXISTS(TO_CHAR(v_GRANTNUMBER));

IF NOT t_exists THEN
l_GRANTNUMBER_Used(TO_CHAR(v_GRANTNUMBER)) := TO_CHAR(v_GRANTNUMBER);
END IF;

EXIT WHEN (NOT t_exists);
END LOOP;

INSERT INTO CMS.CMS_GRANT (
GRANT_ID, PROGRAM_ID, GRANTEE_ID, CLASS_ID, SCHOOL_TYPE_ID, GRANT_NUMBER, BEGIN_DATE,
ENROLLMENT_GOALS, FISCAL_YEAR, FEDERAL_PROGRAM_OFFICER_EMAIL, IS_CONSTRUCTION_PLUS_GRANTEE,
MODIFIED_DATE, MODIFIED_BY, CREATED_DATE, CREATED_BY, IS_DELETED
) VALUES (
v_GRANTID,
( SELECT PROGRAM_ID FROM (SELECT PROGRAM_ID FROM “CMS”.“CMS_R_PROGRAM” ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT ORGANIZATION_ID FROM (SELECT ORGANIZATION_ID FROM “CMS”.“CMS_ORGANIZATION” ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT CLASS_ID FROM (SELECT CLASS_ID FROM “CMS”.“CMS_R_CLASS” ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT APPLICATION_DATA_ID FROM (SELECT APPLICATION_DATA_ID FROM “CMS”.“CMS_R_APPLICATION_DATA” ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
v_GRANTNUMBER, (SELECT BEGIN_DATE FROM (SELECT BEGIN_DATE FROM CMS.CMS_GRANT ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1),
(SELECT ENROLLMENT_GOALS FROM (SELECT ENROLLMENT_GOALS FROM CMS.CMS_GRANT ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1),
(SELECT FISCAL_YEAR FROM (SELECT FISCAL_YEAR FROM CMS.CMS_GRANT ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1),
(SELECT FEDERAL_PROGRAM_OFFICER_EMAIL FROM (SELECT FEDERAL_PROGRAM_OFFICER_EMAIL FROM CMS.CMS_GRANT ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1),
(SELECT IS_CONSTRUCTION_PLUS_GRANTEE FROM (SELECT IS_CONSTRUCTION_PLUS_GRANTEE FROM CMS.CMS_GRANT ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1),
(SELECT MODIFIED_DATE FROM (SELECT MODIFIED_DATE FROM CMS.CMS_GRANT ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1),
(SELECT MODIFIED_BY FROM (SELECT MODIFIED_BY FROM CMS.CMS_GRANT ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1),
(SELECT CREATED_DATE FROM (SELECT CREATED_DATE FROM CMS.CMS_GRANT ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1),
(SELECT CREATED_BY FROM (SELECT CREATED_BY FROM CMS.CMS_GRANT ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1),
(SELECT IS_DELETED FROM (SELECT IS_DELETED FROM CMS.CMS_GRANT ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1)
);
END LOOP;

DBMS_RANDOM.TERMINATE;
END;
/

COMMIT;
/

2

Good Afternoon TOAD Team,

Not to surprising, that post had no takers; very detailed. Well I've had no issues since that post until today, Version 13.0.0.66; I generated hundred thousands of records. This may be a user input error, too. Ill try again in the morning. Today's error (I only edited the comment): Error message following script...

/************************************************************
** Table: CMS.CMS_CASE_DEMOGRAPHIC__100.sql
** Number of Records: 100
************************************************************/

DECLARE
SUBTYPE MAXVARCHAR2 IS VARCHAR2(32767);
TYPE Used_Array IS TABLE OF MAXVARCHAR2 INDEX BY MAXVARCHAR2;
TYPE CASEID_Array IS TABLE OF CMS.CMS_CASE_DEMOGRAPHIC.CASE_ID%TYPE INDEX BY PLS_INTEGER;
l_CASEID CASEID_Array;
l_CASEID_Used Used_Array;
v_CASEID CMS.CMS_CASE_DEMOGRAPHIC.CASE_ID%TYPE;
t_seed NUMBER(20);
t_exists BOOLEAN;
BEGIN
-- Initialize Random Number Generator
SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'MMDDHH24MISS'))
INTO t_seed
FROM Dual;

DBMS_RANDOM.INITIALIZE(t_seed);

-- Bulk Collect existing values into arrays
SELECT CASE_ID BULK COLLECT INTO l_CASEID FROM CMS.CMS_CASE_DEMOGRAPHIC;
FOR t_counter IN 1..l_CASEID.COUNT LOOP
l_CASEID_Used(TO_CHAR(l_CASEID(t_counter))) := TO_CHAR(l_CASEID(t_counter));
END LOOP;

-- Generate Random Data
FOR t_counter IN 1..100 LOOP

-- Generate random value for CASE_ID and compare against existing list
LOOP
v_CASEID := (SELECT CASE_ID FROM (SELECT CASE_ID FROM CMS.CMS_CASE ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1);
t_exists := l_CASEID_Used.EXISTS(TO_CHAR(v_CASEID));

IF NOT t_exists THEN
l_CASEID_Used(TO_CHAR(v_CASEID)) := TO_CHAR(v_CASEID);
END IF;

EXIT WHEN (NOT t_exists);
END LOOP;

INSERT INTO CMS.CMS_CASE_DEMOGRAPHIC (
CASE_ID, INDIVIDUAL_WITH_DISABILITY_ID, CATEGORY_OF_DISABILITY_ID, DISABILITY_WORK_SETTING_ID,
DISABILITY_EMPLOYMENT_SRVC_ID, DISABILITY_FINANCIAL_CPBLT_ID, HISPANIC_LATINO_ID,
AMERICAN_INDN_OR_ALSKN_NTV_ID, ASIAN_ID, BLACK_ID, NATIVE_HAWAIAN_ID, WHITE_ID,
RESERVATION_ID, TRIBE_ID, DISTRICT_ID, FIELD_OFFICE_ID, DISABILITY_IEP_ID, IS_DISABILITY_SDDA_SERVICES,
IS_DISABILITY_LSMHA_SERVICES, IS_DISABILITY_HCBS_SERVICES, CREATED_DATE, CREATED_BY,
MODIFIED_DATE, MODIFIED_BY, INDV_DISABILITY_REPORT_ID
) VALUES (
( SELECT CASE_ID FROM (SELECT CASE_ID FROM "CMS"."CMS_CASE" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT RESERVATION_ID FROM (SELECT RESERVATION_ID FROM "CMS"."CMS_R_RESERVATION" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT TRIBE_ID FROM (SELECT TRIBE_ID FROM "CMS"."CMS_R_TRIBE" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT DISTRICT_ID FROM (SELECT DISTRICT_ID FROM "CMS"."CMS_R_DISTRICT" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT FIELD_OFFICE_ID FROM (SELECT FIELD_OFFICE_ID FROM "CMS"."CMS_R_FIELD_OFFICE" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
(SELECT IS_DISABILITY_SDDA_SERVICES FROM (SELECT IS_DISABILITY_SDDA_SERVICES FROM CMS.CMS_CASE_DEMOGRAPHIC ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1),
(SELECT IS_DISABILITY_LSMHA_SERVICES FROM (SELECT IS_DISABILITY_LSMHA_SERVICES FROM CMS.CMS_CASE_DEMOGRAPHIC ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1),
(SELECT IS_DISABILITY_HCBS_SERVICES FROM (SELECT IS_DISABILITY_HCBS_SERVICES FROM CMS.CMS_CASE_DEMOGRAPHIC ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1),
TOAD_DATAGEN.RandomDate(2457389, 2458209, 0), (SELECT CREATED_BY FROM (SELECT CREATED_BY FROM CMS.CMS_CASE_DEMOGRAPHIC ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1),
TOAD_DATAGEN.RandomDate(2457389, 2458209, 0), (SELECT MODIFIED_BY FROM (SELECT MODIFIED_BY FROM CMS.CMS_CASE_DEMOGRAPHIC ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1),
( SELECT BUSINESS_DATA_ID FROM (SELECT BUSINESS_DATA_ID FROM "CMS"."CMS_R_BUSINESS_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 )
);
END LOOP;

DBMS_RANDOM.TERMINATE;
END;
/

COMMIT;
/

___________________________________________ Error Message ___________________________________________________________

DECLARE
SUBTYPE MAXVARCHAR2 IS VARCHAR2(32767);
TYPE Used_Array IS TABLE OF MAXVARCHAR2 INDEX BY MAXVARCHAR2;
TYPE CASEID_Array IS TABLE OF CMS.CMS_CASE_DEMOGRAPHIC.CASE_ID%TYPE INDEX BY PLS_INTEGER;
l_CASEID CASEID_Array;
l_CASEID_Used Used_Array;
v_CASEID CMS.CMS_CASE_DEMOGRAPHIC.CASE_ID%TYPE;
t_seed NUMBER(20);
t_exists BOOLEAN;
BEGIN
-- Initialize Random Number Generator
SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'MMDDHH24MISS'))
INTO t_seed
FROM Dual;

DBMS_RANDOM.INITIALIZE(t_seed);

-- Bulk Collect existing values into arrays
SELECT CASE_ID BULK COLLECT INTO l_CASEID FROM CMS.CMS_CASE_DEMOGRAPHIC;
FOR t_counter IN 1..l_CASEID.COUNT LOOP
l_CASEID_Used(TO_CHAR(l_CASEID(t_counter))) := TO_CHAR(l_CASEID(t_counter));
END LOOP;

-- Generate Random Data
FOR t_counter IN 1..100 LOOP

-- Generate random value for CASE_ID and compare against existing list
LOOP
v_CASEID := (SELECT CASE_ID FROM (SELECT CASE_ID FROM CMS.CMS_CASE ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1);
t_exists := l_CASEID_Used.EXISTS(TO_CHAR(v_CASEID));

IF NOT t_exists THEN
l_CASEID_Used(TO_CHAR(v_CASEID)) := TO_CHAR(v_CASEID);
END IF;

EXIT WHEN (NOT t_exists);
END LOOP;

INSERT INTO CMS.CMS_CASE_DEMOGRAPHIC (
CASE_ID, INDIVIDUAL_WITH_DISABILITY_ID, CATEGORY_OF_DISABILITY_ID, DISABILITY_WORK_SETTING_ID,
DISABILITY_EMPLOYMENT_SRVC_ID, DISABILITY_FINANCIAL_CPBLT_ID, HISPANIC_LATINO_ID,
AMERICAN_INDN_OR_ALSKN_NTV_ID, ASIAN_ID, BLACK_ID, NATIVE_HAWAIAN_ID, WHITE_ID,
RESERVATION_ID, TRIBE_ID, DISTRICT_ID, FIELD_OFFICE_ID, DISABILITY_IEP_ID, IS_DISABILITY_SDDA_SERVICES,
IS_DISABILITY_LSMHA_SERVICES, IS_DISABILITY_HCBS_SERVICES, CREATED_DATE, CREATED_BY,
MODIFIED_DATE, MODIFIED_BY, INDV_DISABILITY_REPORT_ID
) VALUES (
( SELECT CASE_ID FROM (SELECT CASE_ID FROM "CMS"."CMS_CASE" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT RESERVATION_ID FROM (SELECT RESERVATION_ID FROM "CMS"."CMS_R_RESERVATION" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT TRIBE_ID FROM (SELECT TRIBE_ID FROM "CMS"."CMS_R_TRIBE" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT DISTRICT_ID FROM (SELECT DISTRICT_ID FROM "CMS"."CMS_R_DISTRICT" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT FIELD_OFFICE_ID FROM (SELECT FIELD_OFFICE_ID FROM "CMS"."CMS_R_FIELD_OFFICE" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
(SELECT IS_DISABILITY_SDDA_SERVICES FROM (SELECT IS_DISABILITY_SDDA_SERVICES FROM CMS.CMS_CASE_DEMOGRAPHIC ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1),
(SELECT IS_DISABILITY_LSMHA_SERVICES FROM (SELECT IS_DISABILITY_LSMHA_SERVICES FROM CMS.CMS_CASE_DEMOGRAPHIC ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1),
(SELECT IS_DISABILITY_HCBS_SERVICES FROM (SELECT IS_DISABILITY_HCBS_SERVICES FROM CMS.CMS_CASE_DEMOGRAPHIC ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1),
TOAD_DATAGEN.RandomDate(2457389, 2458209, 0), (SELECT CREATED_BY FROM (SELECT CREATED_BY FROM CMS.CMS_CASE_DEMOGRAPHIC ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1),
TOAD_DATAGEN.RandomDate(2457389, 2458209, 0), (SELECT MODIFIED_BY FROM (SELECT MODIFIED_BY FROM CMS.CMS_CASE_DEMOGRAPHIC ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1),
( SELECT BUSINESS_DATA_ID FROM (SELECT BUSINESS_DATA_ID FROM "CMS"."CMS_R_BUSINESS_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 )
);
END LOOP;

DBMS_RANDOM.TERMINATE;
END;
Error at line 6
ORA-06550: line 29, column 20:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

( - + case mod new not null

continue avg count current exists max min prior sql stddev
sum variance execute forall merge time timestamp interval
date
pipe

<an alternat
ORA-06550: line 29, column 118:
PLS-00103: Encountered the symbol ")" when expecting one of the following:

  • & - + ; / at for mod remainder rem <an exponent (**)> and
    or group having intersect minus order start union where
    connect || multiset
    ORA-06550: line 39, column 5:
    PLS-00103: Encountered the symbol "INSERT" when expecting one of the following:

end not pragma final instantiable order overriding static
member constructor map
ORA-06550: line 47, column 9:
lumn 9:
r map
ORA-06550: line 47, column 9:
lumn 9:

Script Terminated on line 6 of \eta-940-01\home\Wickliffe.Larry\My Documents\EBSS\CMS\CMS.CMS_CASE_DEMOGRAPHIC_100.sql.
3

Good Afternoon TOAD team,

I've tried this several times; even back in the production TOAD 12.11 with settings cleared. The script generated in TOAD Data Gen fails to run with the following error: I wish I could just attach some files. The script generated by TOAD 12.11 and 13.0.0.69 are identical, so this is a production TOAD bug.

To recreate, start Generate Data

Choose TOAD_DATAGEN

Change 1000 records to 100 records (is not be necessary, but I have found 10 100's run way faster than one 1000, and you get to see progress. I do this from habit - get same error with 1000 records per script)

The table I generating data for 18 foreign keys to 4 tables (one table takes the bulk of foreign keys. I think the error is from this part, as I do the other fields as random or jug handle foreign and get the same error.

------------------ Error Message -----------------------

DECLARE
SUBTYPE MAXVARCHAR2 IS VARCHAR2(32767);
TYPE Used_Array IS TABLE OF MAXVARCHAR2 INDEX BY MAXVARCHAR2;
TYPE CASEID_Array IS TABLE OF CMS.CMS_CASE_DEMOGRAPHIC.CASE_ID%TYPE INDEX BY PLS_INTEGER;
l_CASEID CASEID_Array;
l_CASEID_Used Used_Array;
v_CASEID CMS.CMS_CASE_DEMOGRAPHIC.CASE_ID%TYPE;
t_seed NUMBER(20);
t_exists BOOLEAN;
BEGIN
-- Initialize Random Number Generator
SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'MMDDHH24MISS'))
INTO t_seed
FROM Dual;

DBMS_RANDOM.INITIALIZE(t_seed);

-- Bulk Collect existing values into arrays
SELECT CASE_ID BULK COLLECT INTO l_CASEID FROM CMS.CMS_CASE_DEMOGRAPHIC;
FOR t_counter IN 1..l_CASEID.COUNT LOOP
l_CASEID_Used(TO_CHAR(l_CASEID(t_counter))) := TO_CHAR(l_CASEID(t_counter));
END LOOP;

-- Generate Random Data
FOR t_counter IN 1..100 LOOP

-- Generate random value for CASE_ID and compare against existing list
LOOP
v_CASEID := (SELECT CASE_ID FROM (SELECT CASE_ID FROM CMS.CMS_CASE ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1);
t_exists := l_CASEID_Used.EXISTS(TO_CHAR(v_CASEID));

IF NOT t_exists THEN
l_CASEID_Used(TO_CHAR(v_CASEID)) := TO_CHAR(v_CASEID);
END IF;

EXIT WHEN (NOT t_exists);
END LOOP;

INSERT INTO CMS.CMS_CASE_DEMOGRAPHIC (
CASE_ID, INDIVIDUAL_WITH_DISABILITY_ID, CATEGORY_OF_DISABILITY_ID, DISABILITY_WORK_SETTING_ID,
DISABILITY_EMPLOYMENT_SRVC_ID, DISABILITY_FINANCIAL_CPBLT_ID, HISPANIC_LATINO_ID,
AMERICAN_INDN_OR_ALSKN_NTV_ID, ASIAN_ID, BLACK_ID, NATIVE_HAWAIAN_ID, WHITE_ID,
RESERVATION_ID, TRIBE_ID, DISTRICT_ID, FIELD_OFFICE_ID, DISABILITY_IEP_ID, IS_DISABILITY_SDDA_SERVICES,
IS_DISABILITY_LSMHA_SERVICES, IS_DISABILITY_HCBS_SERVICES, CREATED_DATE, CREATED_BY,
MODIFIED_DATE, MODIFIED_BY, INDV_DISABILITY_REPORT_ID
) VALUES (
( SELECT CASE_ID FROM (SELECT CASE_ID FROM "CMS"."CMS_CASE" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT RESERVATION_ID FROM (SELECT RESERVATION_ID FROM "CMS"."CMS_R_RESERVATION" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT TRIBE_ID FROM (SELECT TRIBE_ID FROM "CMS"."CMS_R_TRIBE" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT DISTRICT_ID FROM (SELECT DISTRICT_ID FROM "CMS"."CMS_R_DISTRICT" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT FIELD_OFFICE_ID FROM (SELECT FIELD_OFFICE_ID FROM "CMS"."CMS_R_FIELD_OFFICE" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
( SELECT PIRL_DATA_ID FROM (SELECT PIRL_DATA_ID FROM "CMS"."CMS_R_PIRL_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 ),
TOAD_DATAGEN.RandomInteger(0, 1, 80), TOAD_DATAGEN.RandomInteger(0, 1, 80), TOAD_DATAGEN.RandomInteger(0, 1, 80),
TOAD_DATAGEN.RandomDate(2457389, 2458209, 0), TOAD_DATAGEN.Email(255, 0), TOAD_DATAGEN.RandomDate(2457389, 2458209, 0),
TOAD_DATAGEN.Email(255, 0),
( SELECT BUSINESS_DATA_ID FROM (SELECT BUSINESS_DATA_ID FROM "CMS"."CMS_R_BUSINESS_DATA" ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1 )
);
END LOOP;

DBMS_RANDOM.TERMINATE;
END;
Error at line 6
ORA-06550: line 29, column 20:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

( - + case mod new not null

continue avg count current exists max min prior sql stddev
sum variance execute forall merge time timestamp interval
date
pipe

<an alternat
ORA-06550: line 29, column 118:
PLS-00103: Encountered the symbol ")" when expecting one of the following:

  • & - + ; / at for mod remainder rem <an exponent (**)> and
    or group having intersect minus order start union where
    connect || multiset
    ORA-06550: line 39, column 5:
    PLS-00103: Encountered the symbol "INSERT" when expecting one of the following:

end not pragma final instantiable order overriding static
member constructor map
ORA-06550: line 47, column 9:
e Random Data
FOR t_counter IN 1..100 LOOP

-- Gen

Commit complete.
4

Hey Larry,

My apologies! For some reason, I never received your previous posts in my inbox. You’re correct: this is a bug. It’s been fixed for the GA version.

-John

5

Hey Larry,

I’m afraid I spoke a bit too soon on this one. The fix won’t be able to get into the GA version of Toad 13, but it will be in the first beta of Toad 13.1. Until then, you can still use Data Gen in Toad 13 by generating the script and sending it to the editor (rather than running it directly). Then, just modify any lines of the form:

v_CASEID := (SELECT CASE_ID FROM (SELECT CASE_ID FROM CMS.CMS_CASE ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1);

to be:

SELECT CASE_ID INTO v_CASEID FROM (SELECT CASE_ID FROM CMS.CMS_CASE ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1;

It’s one extra step, but it’ll let you still use it until Toad 13.1 beta starts up.

Sorry about speaking too soon on this one.

-John

6

Good Afternoon John,

Thank you!

The work around works, but discovered another bug. The script is randomly selecting cases from the case database, but not checking that the case is not already in the target database with a primary key in case_id, so I occasionally an insert fails the primary key. Since the delivery is not until 13.1, you may have time to get both; proposed solution below:

– your fix but into commented out so it may be run stand alone
SELECT CASE_ID --into v_CASEID
FROM (SELECT CASE_ID FROM CMS.CMS_CASE ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1;

– addition primary key fix from Larry but into commented out so it may be run stand alone

SELECT CASE_ID --into v_CASEID
FROM (SELECT CASE_ID FROM CMS.CMS_CASE where case_ID not in (SELECT CASE_ID FROM CMS.CMS_CASE_DEMOGRAPHIC ) ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1;

7

Good Morning John,

Just thought of an issue with my proposed fix; it does not check that you have not run out of cases; unlikely soon in my data senerio for a while, but with time, the cases will be exhausted.

8

Test

From: “Wickliffe, Larry - ETA” Wickliffe.Larry@dol.gov

Date: Thursday, April 19, 2018 at 11:16 AM

To: "toadoraclebeta@toadworld.com" toadoraclebeta@toadworld.com

Subject: RE: [Toad for Oracle - Beta Discussion Forum] TOAD 13.0.0.57 Generate Data produces invalid SQL to generate data

CAUTION:
This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.

Good Afternoon John,

            Did you see my follow up discussion?

·

Follow wickliffe.larry / 13 Apr 2018 at 7:34pm

Good Afternoon John,

 Thank you!

  The work around works, but discovered another bug.  The script is randomly selecting cases from the case database, but not checking that the case is not already in the target database with a primary key in case_id, so I occasionally an insert fails the primary key.  Since the delivery is not until 13.1, you may have time to get both; proposed solution below:

– your fix but into commented out so it may be run stand alone

SELECT CASE_ID --into v_CASEID

FROM (SELECT CASE_ID FROM CMS.CMS_CASE ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1;

– addition primary key fix from Larry but into commented out so it may be run stand alone

SELECT CASE_ID --into v_CASEID

FROM (SELECT CASE_ID FROM CMS.CMS_CASE where case_ID not in (SELECT CASE_ID FROM CMS.CMS_CASE_DEMOGRAPHIC ) ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1;

Larry

Lawrence Wickliffe

Department of Labor

ETA/ OMAS/Office of Management and Administrative Services

(202)693-3485 (Office)

(703)508-4945 (Cell)

Create an account to join the discussion.

·

Follow wickliffe.larry / 16 Apr 2018 at 2:12pm

Good Morning John,

  Just thought of an issue with my proposed fix; it does not check that you have not run out of cases; unlikely soon in my data senerio for a while, but with time, the cases will be exhausted.

Larry

Lawrence Wickliffe

Department of Labor

ETA/ OMAS/Office of Management and Administrative Services

(202)693-3485 (Office)

(703)508-4945 (Cell)

Create an account to join the discussion.

Larry Wickliffe

Monitoring, Performance and Tuning Services Lead, Data and Business Intelligence Services (DABIS)

Office of Information Systems and Technology (OIST)

Employment and Training Administration (ETA). US Department of Labor

Wickliffe.Larry@dol.gov: 202-693-3485 FPB

l.wickliffe@computer.org
703-508-4945 Cell

From:
John Bowman bounce-jbowman@toadworld.com

Sent: Monday, April 9, 2018 6:00 PM

To: toadoraclebeta@toadworld.com

Subject: RE: [Toad for Oracle - Beta Discussion Forum] TOAD 13.0.0.57 Generate Data produces invalid SQL to generate data

** RE: TOAD 13.0.0.57 Generate Data produces invalid SQL to generate data**

Reply by John Bowman

Hey Larry,

I’m afraid I spoke a bit too soon on this one. The fix won’t be able to get into the GA version of Toad 13, but it will be in the first beta of Toad 13.1. Until then, you can still use Data Gen in Toad 13 by generating the script and sending it to the editor (rather than running it directly). Then, just modify any lines of the form:

v_CASEID := (SELECT CASE_ID FROM (SELECT CASE_ID FROM CMS.CMS_CASE ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1);

to be:

SELECT CASE_ID INTO v_CASEID FROM (SELECT CASE_ID FROM CMS.CMS_CASE ORDER BY DBMS_RANDOM.VALUE) WHERE rownum = 1;

It’s one extra step, but it’ll let you still use it until Toad 13.1 beta starts up.

Sorry about speaking too soon on this one.

-John

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - Beta Forum notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

9

Good Morning John and TOAD Team,

Testing out the fix in TOAD 13.1 beta. The initial reported issue has been resolved. Thank you!

However, I’m only getting 300 new rows per 1,000 added due to unique constraint violations (ORA-06512, unique constraint violated). I’m sure this “success” will go down as I create more records, as I’m only at 1,081 records in the target table (87,000 total cases in the source table).

BTW, nothing to do with this bug. The Select Tags button below list on this web page appears to be limited to the beginning of the alphabet, ending in the C’s.

10

Good Morning TOAD Team,

With several hundred thousand attempts to add more data I’ll hit a brick wall at 3,881 records. When I add the where clause to not choose a Case_ID already in the table, the records again increase. I think the where clause to meet the primary key is essential.