Skip to Main Content

SQL Developer Data Modeler

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!

SQL script issue on apex

NicolasGMay 13 2020 — edited May 14 2020

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

pastedImage_0.pngpastedImage_1.png

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:

pastedImage_2.png

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

Comments

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

Screen Shot 2020-05-14 at 7.34.09 AM.png

Select the "Columns" item in the left-hand Panel, and then double-click on the primary key column:

Screen Shot 2020-05-14 at 7.36.08 AM.png

This opens the column dialog.

Screen Shot 2020-05-14 at 7.40.08 AM.png

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.

Screen Shot 2020-05-14 at 7.44.21 AM.png

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.

1 - 6

Post Details

Added on May 13 2020
6 comments
860 views