quicksql
quicksql copied to clipboard
oracle
Sample Data Model (Departments and Employees) produces bad SQL. Missing semi-colon, and bad BLOB insert.
Although this is uncoupled from APEX, there are a few basic data models we have preloaded. Testing, and realized that some do not work. Here is one that is broken:
departments /insert 4
name /nn
location
country
employees /insert 14
name /nn vc50
email /lower
cost center num
date hired
job vc255
view emp_v departments employees
This is a simple departments and employees table with a view and some fake rows.
Here is the script it generates:
-- create tables
create table departments (
id number default on null to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
constraint departments_id_pk primary key,
name varchar2(255 char) not null,
location varchar2(4000 char),
country varchar2(4000 char)
);
create table employees (
id number default on null to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
constraint employees_id_pk primary key,
department_id number constraint employees_department_id_fk
references departments,
name varchar2(50 char) not null,
email varchar2(255 char),
cost_center number,
the_start date,
job varchar2(255 char),
image blob
);
-- table index
create index employees_i1 on employees (department_id);
-- triggers
create or replace trigger employees_biu
before insert or update
on employees
for each row
begin
:new.email := lower(:new.email);
end employees_biu;
/
-- create views
create or replace view emp_v as
select
departments.id department_id,
departments.name department_name,
departments.location location,
departments.country country,
employees.id employee_id,
employees.name employee_name,
employees.email email,
employees.cost_center cost_center,
employees.the_start the_start,
employees.job job,
employees.image image
from
departments,
employees
where
employees.department_id(+) = departments.id/
-- load data
insert into departments (
id,
name,
location,
country
) values (
1,
'Delivery',
'Garukme',
'IL'
);
insert into departments (
id,
name,
location,
country
) values (
2,
'Manufacturing',
'Covdiiku',
'MH'
);
insert into departments (
id,
name,
location,
country
) values (
3,
'Sales',
'Imaerosed',
'VU'
);
insert into departments (
id,
name,
location,
country
) values (
4,
'Manufacturing',
'Cugewpap',
'CR'
);
commit;
insert into employees (
id,
department_id,
name,
email,
cost_center,
the_start,
job,
image
) values (
1,
4,
'Elnora Payne',
'[email protected]',
84,
sysdate-86,
'Analyst',
'N/A'
);
insert into employees (
id,
department_id,
name,
email,
cost_center,
the_start,
job,
image
) values (
2,
1,
'Katie Anderson',
'[email protected]',
78,
sysdate-9,
'Architect',
'N/A'
);
insert into employees (
id,
department_id,
name,
email,
cost_center,
the_start,
job,
image
) values (
3,
3,
'Myrtie Maldonado',
'[email protected]',
7,
sysdate-87,
'Salesman',
'N/A'
);
insert into employees (
id,
department_id,
name,
email,
cost_center,
the_start,
job,
image
) values (
4,
1,
'Carrie Carlson',
'[email protected]',
12,
sysdate-77,
'Manager',
'N/A'
);
insert into employees (
id,
department_id,
name,
email,
cost_center,
the_start,
job,
image
) values (
5,
3,
'Lucas Larson',
'[email protected]',
48,
sysdate-79,
'Consultant',
'N/A'
);
insert into employees (
id,
department_id,
name,
email,
cost_center,
the_start,
job,
image
) values (
6,
2,
'Leo Vargas',
'[email protected]',
58,
sysdate-75,
'Engineer',
'N/A'
);
insert into employees (
id,
department_id,
name,
email,
cost_center,
the_start,
job,
image
) values (
7,
3,
'Verna Greene',
'[email protected]',
68,
sysdate-62,
'Architect',
'N/A'
);
insert into employees (
id,
department_id,
name,
email,
cost_center,
the_start,
job,
image
) values (
8,
1,
'Walter Hodges',
'[email protected]',
82,
sysdate-17,
'Consultant',
'N/A'
);
insert into employees (
id,
department_id,
name,
email,
cost_center,
the_start,
job,
image
) values (
9,
3,
'Franklin Nunez',
'[email protected]',
68,
sysdate-95,
'Consultant',
'N/A'
);
insert into employees (
id,
department_id,
name,
email,
cost_center,
the_start,
job,
image
) values (
10,
4,
'Seth Tran',
'[email protected]',
78,
sysdate-25,
'Engineer',
'N/A'
);
insert into employees (
id,
department_id,
name,
email,
cost_center,
the_start,
job,
image
) values (
11,
1,
'Della Page',
'[email protected]',
88,
sysdate-73,
'Manager',
'N/A'
);
insert into employees (
id,
department_id,
name,
email,
cost_center,
the_start,
job,
image
) values (
12,
2,
'Nicholas Harrison',
'[email protected]',
25,
sysdate-29,
'Architect',
'N/A'
);
insert into employees (
id,
department_id,
name,
email,
cost_center,
the_start,
job,
image
) values (
13,
2,
'Walter Lane',
'[email protected]',
10,
sysdate-44,
'Manager',
'N/A'
);
insert into employees (
id,
department_id,
name,
email,
cost_center,
the_start,
job,
image
) values (
14,
3,
'Edgar Little',
'[email protected]',
72,
sysdate-38,
'Salesman',
'N/A'
);
commit;
-- Generated by Quick SQL 1.2.12 5/3/2024, 9:38:24 AM
/*
departments /insert 4
name /nn
location
country
employees /insert 14
name /nn vc50
email /lower
cost center num
start date date
job vc255
image blob
view emp_v departments employees
Non-default options:
# settings = {}
*/
You can see the that view here is missing a ; and if you try to run this whole script, it fails with Error at line 22/1: ORA-00936: missing expression
Also the inserts ALL fail with : ORA-01465: invalid hex number
This is due to the image column being a blob and trying to insert 'N/A' into it. BLOB columns rather should just get null.
