Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 398 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
SQL script issue on apex

Hello,
I'm new to using oracle developer data modeler and oracle apex and I have very little knowledge of DB, so I apologize if I make big mistakes.
context: I recently started a course in DB management and I have to realize for a lab an application on apex allowing to access several tables and to add / modify / search data.
So I made a logical model on data modeler then I regenerate the physical model.
here is what i get
I then generated the sql script by exporting a ddl file with all my tables, here is what I get
-- Généré par Oracle SQL Developer Data Modeler 19.4.0.350.1424
-- à : 2020-05-14 03:40:44 EEST
-- site : Oracle Database 11g
-- type : Oracle Database 11g
CREATE TABLE actors (
actors_id INTEGER NOT NULL,
actors_firstname VARCHAR2(255 CHAR),
actors_lastname VARCHAR2(255 CHAR),
actors_date_of_birth DATE,
actors_nationality VARCHAR2(255 CHAR)
);
ALTER TABLE actors ADD CONSTRAINT actors_pk PRIMARY KEY ( actors_id );
CREATE TABLE directors (
directors_id INTEGER NOT NULL,
directors_fistname VARCHAR2(255 CHAR),
directors_lastname VARCHAR2(255 CHAR),
directors_date_of_birth DATE,
directors_nationality VARCHAR2(255 CHAR)
);
ALTER TABLE directors ADD CONSTRAINT directors_pk PRIMARY KEY ( directors_id );
CREATE TABLE distributors (
distributors_id INTEGER NOT NULL,
distributors_name VARCHAR2(255 CHAR),
distributors_country VARCHAR2(255 CHAR)
);
ALTER TABLE distributors ADD CONSTRAINT distributors_pk PRIMARY KEY ( distributors_id );
CREATE TABLE genre (
genre_id INTEGER NOT NULL,
genre_genre VARCHAR2(255 CHAR)
);
ALTER TABLE genre ADD CONSTRAINT genre_pk PRIMARY KEY ( genre_id );
CREATE TABLE movie (
attribute_1 INTEGER NOT NULL,
movie_year DATE,
movie_title VARCHAR2(255 CHAR),
movie_length INTEGER
);
ALTER TABLE movie ADD CONSTRAINT movie_pk PRIMARY KEY ( attribute_1 );
CREATE TABLE "Movie/Actors" (
movie_attribute_1 INTEGER NOT NULL,
actors_actors_id INTEGER NOT NULL
);
ALTER TABLE "Movie/Actors" ADD CONSTRAINT "Movie/Actors_PK" PRIMARY KEY ( movie_attribute_1,
actors_actors_id );
CREATE TABLE "Movie/Directors" (
movie_attribute_1 INTEGER NOT NULL,
directors_directors_id INTEGER NOT NULL
);
ALTER TABLE "Movie/Directors" ADD CONSTRAINT "Movie/Directors_PK" PRIMARY KEY ( movie_attribute_1,
directors_directors_id );
CREATE TABLE "Movie/Distributor" (
movie_attribute_1 INTEGER NOT NULL,
distributors_distributors_id INTEGER NOT NULL
);
ALTER TABLE "Movie/Distributor" ADD CONSTRAINT "Movie/Distributor_PK" PRIMARY KEY ( movie_attribute_1,
distributors_distributors_id );
CREATE TABLE "Movie/Genre" (
movie_attribute_1 INTEGER NOT NULL,
genre_genre_id INTEGER NOT NULL
);
ALTER TABLE "Movie/Genre" ADD CONSTRAINT "Movie/Genre_PK" PRIMARY KEY ( movie_attribute_1,
genre_genre_id );
ALTER TABLE "Movie/Actors"
ADD CONSTRAINT "Movie/Actors_Actors_FK" FOREIGN KEY ( actors_actors_id )
REFERENCES actors ( actors_id );
ALTER TABLE "Movie/Actors"
ADD CONSTRAINT "Movie/Actors_Movie_FK" FOREIGN KEY ( movie_attribute_1 )
REFERENCES movie ( attribute_1 );
ALTER TABLE "Movie/Directors"
ADD CONSTRAINT "Movie/Directors_Directors_FK" FOREIGN KEY ( directors_directors_id )
REFERENCES directors ( directors_id );
ALTER TABLE "Movie/Directors"
ADD CONSTRAINT "Movie/Directors_Movie_FK" FOREIGN KEY ( movie_attribute_1 )
REFERENCES movie ( attribute_1 );
ALTER TABLE "Movie/Distributor"
ADD CONSTRAINT "Movie/Distrib_FK" FOREIGN KEY ( distributors_distributors_id )
REFERENCES distributors ( distributors_id );
ALTER TABLE "Movie/Distributor"
ADD CONSTRAINT "Movie/Distrib_Movie_FK" FOREIGN KEY ( movie_attribute_1 )
REFERENCES movie ( attribute_1 );
ALTER TABLE "Movie/Genre"
ADD CONSTRAINT "Movie/Genre_Genre_FK" FOREIGN KEY ( genre_genre_id )
REFERENCES genre ( genre_id );
ALTER TABLE "Movie/Genre"
ADD CONSTRAINT "Movie/Genre_Movie_FK" FOREIGN KEY ( movie_attribute_1 )
REFERENCES movie ( attribute_1 );
-- Rapport récapitulatif d'Oracle SQL Developer Data Modeler :
--
-- CREATE TABLE 9
-- CREATE INDEX 0
-- ALTER TABLE 17
-- CREATE VIEW 0
-- ALTER VIEW 0
-- CREATE PACKAGE 0
-- CREATE PACKAGE BODY 0
-- CREATE PROCEDURE 0
-- CREATE FUNCTION 0
-- CREATE TRIGGER 0
-- ALTER TRIGGER 0
-- CREATE COLLECTION TYPE 0
-- CREATE STRUCTURED TYPE 0
-- CREATE STRUCTURED TYPE BODY 0
-- CREATE CLUSTER 0
-- CREATE CONTEXT 0
-- CREATE DATABASE 0
-- CREATE DIMENSION 0
-- CREATE DIRECTORY 0
-- CREATE DISK GROUP 0
-- CREATE ROLE 0
-- CREATE ROLLBACK SEGMENT 0
-- CREATE SEQUENCE 0
-- CREATE MATERIALIZED VIEW 0
-- CREATE MATERIALIZED VIEW LOG 0
-- CREATE SYNONYM 0
-- CREATE TABLESPACE 0
-- CREATE USER 0
--
-- DROP TABLESPACE 0
-- DROP DATABASE 0
--
-- REDACTION POLICY 0
--
-- ORDS DROP SCHEMA 0
-- ORDS ENABLE SCHEMA 0
-- ORDS ENABLE OBJECT 0
--
-- ERRORS 0
-- WARNINGS 0
once this done, i was on apex> utilities> quick sql and pasted my sql script
however, once the sql is generated and saved, when I try to review and run the script the following error appears:
I searched the net and I did not find an answer correcting my error (maybe I was looking badly)
if anyone would know how to fix this error it would help me a lot
Answers
-
I think Quick SQL is the wrong place to execute your script:
About Quick SQL
Quick SQL provides a quick way to generate the SQL required to create a relational data model from an indented text document. This tool is designed to reduce the time and effort required to create SQL tables, triggers, and index structures. This tool is not designed to be a replacement for data modeling, it is simply a quick way to develop a script for simple tables and views. Once the SQL is generated it can be tweaked and expanded upon.
Use SQL Scripts instead.
PS:
Please change you display name into something readable:
How can I change my Display Name?
-
Thanks, I did it on sql script and it generated my model well and I was able to create an application, however, when I try to add data to my movie table for example, I get the following error:
ORA-01400: impossible d'insérer NULL dans ("NGUILMINIR2022"."ACTORS"."ACTORS_ID")
I imagine that my problem is at the ID level, indeed when I want to create a movie, I do not have enough to enter the Id and I imagine that I have no default values and auto increment so I tried to add a constraint and trigger in the script to fix the problem but it had no effect, maybe I did it wrong?
If you know how to help me I would be very grateful
-
A typical approach is to create a sequence (in APEX in the Object Browser)
and then select this sequence in the Page Designer as default value for this item.
Repeat this step for each primary key column in your model.
-
Hello Nicolas
You can also have SQL Developer Data Modeler create the sequences and the triggers for you.
If you double-click on a table in your relational model it will open the Table Properties dialog
Select the "Columns" item in the left-hand Panel, and then double-click on the primary key column:
This opens the column dialog.
Make sure that "Auto Increment", "Identity Column" and "Engineer" boxes are checked, and then select OK.
Click OK to exit the Table Properties dialog.
By right-clicking on the table in your model and selecting DDL preview, you should see the DDL for the sequence and the trigger.
You will need to check the "Auto Increment", "Identity Column" and "Engineer" boxes for all of the tables and then regenerate the table DDL.
Hope that helps
--dave
PS I noticed that the primary key of your movie table is named "Attribute_1" Are you sure that is what you want?
-
I manage to create sequence with the object browser but now, my problem is that I do not manage to select the sequence in page designer.
Among my pages generated automatically (interactive state or pannel) during the creation of my application, some allow me to modify an id element and to apply a default values of type sequence with the name of my sequence
detail of my sequence:
- minvalue: 1
- maxvalue: 9999
- increment by 1
- cycle N
- order N
- cache 0
- lastnumber 1
However, once the page run and the application reloads the application and I try to create a new data in my table from this one the following error appears
ORA-06550: Line 1, column 44 : PLS-00201: identifier 'seq_genre.NEXTVAL' must be declared
so my question are:
- why some page allow me to modify the default value and sequence when others don't
- does my sequence are well made for primary_key
- how to fix my identifier issue
-