OraFAQ Forum: Application Express, ORDS & MOD_PLSQL » Turning a table model into a DDL to import into APEX

Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Turning a table model into a DDL to import into APEX (APEX 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Turning a table model into a DDL to import into APEX [message #583510]

Wed, 01 May 2013 12:22

verifyjjones
Messages: 12
Registered: May 2013
Location: USA

Junior Member

im a total noob when it comes to oracle but im taking a class where we use apex and my professor wants us to import a table model into apex. everytime i try and import my table it says "your export file is not supported". i asked my professor what to do and he said "You should gen a ddl file from designer. Then use that to upload into apex and then run." im not sure what that means exactly. any ideas?

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583513 is a reply to message #583510]

Wed, 01 May 2013 12:36

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Do you use Oracle Designer? I suppose you do (otherwise, professor wouldn't suggest it. Would he?). Well, I don't. However, I suppose that there must be an option which allows you to generate DDL script from the data model. These would be CREATE TABLE statements, possibly ALTER TABLE (when it comes to creating constraints), CREATE INDEX and stuff.

I'm not sure what you should do with that script, though. Run it through Application Express' SQL Workshop? Although it might be handy in some cases, I almost never used it. I prefer other tools (such as SQL Developer or TOAD or some other GUI, or even SQL*Plus). Anyway, I believe that all of that can be done in Apex SQL Workshop as well.

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583515 is a reply to message #583513]

Wed, 01 May 2013 12:43

verifyjjones
Messages: 12
Registered: May 2013
Location: USA

Junior Member

We use Oracle data modeler to create the ERD model then engineer it into the table model. After that were supposed to turn the table model into a DDL and import it into SQL scripts in Apex.

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583517 is a reply to message #583515]

Wed, 01 May 2013 12:52

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Do you know how to
- use Oracle data modeler to create the ERD model?
- engineer it into the table model?
- turn the table model into a DDL?

I don't, but I hope you do (because, if you don't, you'll have to wait for someone else's guidance).

So, once you have DDL, run Apex, open its SQL Workshop and run these scripts.

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583520 is a reply to message #583517]

Wed, 01 May 2013 12:57

verifyjjones
Messages: 12
Registered: May 2013
Location: USA

Junior Member

I do know how to do all of those however i think there my be something wring with my DDL or possibly my table bc when i try to import it into apex it give me this error "your export file is not supported". Could it be somethings wrong with my table? or would that even affect importing it?
or do you know? haha

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583522 is a reply to message #583520]

Wed, 01 May 2013 13:09

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

DDL is CREATE TABLE, CREATE INDEX, ALTER TABLE ... as I told you earlier. Script you exported from Oracle data modeler *should* be a pure text file (i.e. the one you can easily open in any text editor, such as Notepad).

Have a look at Using SQL Scripts in Apex documentation.

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583524 is a reply to message #583522]

Wed, 01 May 2013 13:16

verifyjjones
Messages: 12
Registered: May 2013
Location: USA

Junior Member

Right, sorry, yeah i have the script and lets say i have it in Word is there a way i can save it as a DDL? if im not makings any sense bc im not sure what im talking about feel free to correct me in any way. im just here to learn.

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583526 is a reply to message #583524]

Wed, 01 May 2013 13:19

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What is that file extension? I don't think that .DOC is accepted; as I said - it should be an ordinary text (.TXT) file (which can have any other extension, such as .SQL, but should be a text file - no formatting at all (bold, italics, colors etc.)).

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583528 is a reply to message #583526]

Wed, 01 May 2013 13:23

verifyjjones
Messages: 12
Registered: May 2013
Location: USA

Junior Member

i can save it in notepad as a .txt.

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583529 is a reply to message #583528]

Wed, 01 May 2013 13:25

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

OK then.

(Did you try to open that .txt in SQL Workshop? What happened?)
(Did you read a document I posted earlier. And?)

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583533 is a reply to message #583529]

Wed, 01 May 2013 13:46

verifyjjones
Messages: 12
Registered: May 2013
Location: USA

Junior Member

When i get to SQL scripts in APEX there is an options to "Upload" then in a small drop down window to the right there is a task menu with one of the tasks being "Import" which one do i need to do?

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583534 is a reply to message #583533]

Wed, 01 May 2013 13:54

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Neither.

- click the Upload button
- choose the file to be uploaded
- click the Upload button
- the file is uploaded

On the left side, where all your scripts are listed (at the moment, that's probably only one - the one you just uploaded), there are two buttons: Edit (on the left) and Run (on the right). So, what do you want to do? Edit it, or Run it?

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583536 is a reply to message #583534]

Wed, 01 May 2013 13:57

verifyjjones
Messages: 12
Registered: May 2013
Location: USA

Junior Member

Run it. So it gives me two options Run Now and Run in Background

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583538 is a reply to message #583536]

Wed, 01 May 2013 14:03

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Man, you should REALLY read the documentation.

Run now runs the script ... now (who'd guess?).
Run in background utilizes DBMS_JOB package and runs your script as a job.

You want to run it now.

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583539 is a reply to message #583538]

Wed, 01 May 2013 14:09

verifyjjones
Messages: 12
Registered: May 2013
Location: USA

Junior Member

Yeah, im sorry again, im really busy and im actually studying for another final as we speak. by brain is about fried.

So Run now takes me back to "manage script results" but when i look at the results its giving me a ton of errors so im guessing my model is all types of screwed however that doesn't seem to make any sense because mine is almost identical to my professors.

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583541 is a reply to message #583539]

Wed, 01 May 2013 14:16

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Could you post an excerpt of the file you uploaded? It is difficult to debug code you can't see.

(Note that this is a public forum and professor might see code you post).

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583542 is a reply to message #583541]

Wed, 01 May 2013 14:22

verifyjjones
Messages: 12
Registered: May 2013
Location: USA

Junior Member

At this point i just want to know how to fix my problem haha

-- Generated by Oracle SQL Developer Data Modeler 3.3.0.747
-- at: 2013-05-01 14:21:13 CDT
-- site: Oracle Database 11g
-- type: Oracle Database 11g

CREATE TABLE PROJECT
(
PRJ_ID VARCHAR2 (10) NOT NULL ,
PRJ_DESC VARCHAR2 (400) NOT NULL ,
PRJ_STARTDATE DATE NOT NULL
) ;
ALTER TABLE PROJECT ADD CONSTRAINT PROJECT_PK PRIMARY KEY
(
PRJ_ID
)
;

CREATE TABLE PROJECT_ASSIGNMENT
(
ID VARCHAR2 (10) NOT NULL ,
ID1 VARCHAR2 (10) NOT NULL ,
PRJ_ID VARCHAR2 (10) NOT NULL ,
EMPNO NUMBER NOT NULL
) ;
ALTER TABLE PROJECT_ASSIGNMENT ADD CONSTRAINT PROJECT_ASSIGNMENT_PK PRIMARY KEY
(
ID
)
;

CREATE TABLE PROJECT_ROLE
(
ID VARCHAR2 (10) NOT NULL ,
PRJ_ROLE_TITLE VARCHAR2 (15) NOT NULL ,
PRJ_ROLE_DESC VARCHAR2 (400) NOT NULL
) ;
ALTER TABLE PROJECT_ROLE ADD CONSTRAINT PROJECT_ROLE_PK PRIMARY KEY
(
ID
)
;

ALTER TABLE PROJECT_ASSIGNMENT ADD CONSTRAINT PROJECT_ASSIGNMENT_EMP_FK FOREIGN KEY ( EMPNO ) REFERENCES EMP ( EMPNO ) ;

ALTER TABLE PROJECT_ASSIGNMENT ADD CONSTRAINT PROJECT_ASSIGNMENT_PROJECT_FK FOREIGN KEY ( PRJ_ID ) REFERENCES PROJECT ( PRJ_ID ) ;

-- ERROR: FK name length exceeds maximum allowed length(30)
ALTER TABLE PROJECT_ASSIGNMENT ADD CONSTRAINT PROJECT_ASSIGNMENT_PROJECT_ROLE_FK FOREIGN KEY ( ID1 ) REFERENCES PROJECT_ROLE ( ID ) ;

-- Oracle SQL Developer Data Modeler Summary Report:
--
-- CREATE TABLE 3
-- CREATE INDEX 0
-- ALTER TABLE 6
-- CREATE 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 SYNONYM 0
-- CREATE TABLESPACE 0
-- CREATE USER 0
--
-- DROP TABLESPACE 0
-- DROP DATABASE 0
--
-- ERRORS 1
-- WARNINGS 0

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583544 is a reply to message #583542]

Wed, 01 May 2013 14:24

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

"Tons of errors"? Where? I see only one - a foreign key constraint name is too long:

SQL> select length('PROJECT_ASSIGNMENT_PROJECT_ROLE_FK') fk_len from dual;

    FK_LEN
----------
        34

SQL>

(max allowed is 30 characters).

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583545 is a reply to message #583544]

Wed, 01 May 2013 14:32

verifyjjones
Messages: 12
Registered: May 2013
Location: USA

Junior Member

Well i attempted to fix that then i re-saved, uploaded to apex and then ran it. after doing so i looked at the results and it said
Statements Processed 15
Successful 6
With Errors 9

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583546 is a reply to message #583545]

Wed, 01 May 2013 14:34

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Find statements that failed, fix them, run the script again. (That's called "debugging").

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583550 is a reply to message #583546]

Wed, 01 May 2013 14:44

verifyjjones
Messages: 12
Registered: May 2013
Location: USA

Junior Member

not sure if debugging comment was sarcasm or serious but ill take it in stride either way.I am about to head to a final exam but i intend to fix my errors when i return. will your much appreciated help be available in a few hours if more questions arise?

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583553 is a reply to message #583550]

Wed, 01 May 2013 14:51

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Not very likely; in a few hours I'll be asleep (time difference between the USA & Europe), but someone else might be here for you. I'll be back online sometime tomorrow (can't specify when exactly).

Good luck with your exam!

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583554 is a reply to message #583553]

Wed, 01 May 2013 14:52

verifyjjones
Messages: 12
Registered: May 2013
Location: USA

Junior Member

Well thank you so much for your help!

Report message to a moderator

Re: Turning a table model into a DDL to import into APEX [message #583555 is a reply to message #583554]

Wed, 01 May 2013 14:54

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

No problem; it's not that difficult once you get into it.

Report message to a moderator

Previous Topic:	Insert into BLOB
Next Topic:	Hyperline on email to run on demand process

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jan 07 11:05:26 CST 2025