SQL script issue on apex

NicolasG

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

cormaco

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?

NicolasG

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

cormaco

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.

Dave Schleis

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?

NicolasG

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

cormaco

This is no longer an Data Modeler issue.

Open a new thread in the APEX forum.