Home » Developer & Programmer » Application Express & MOD_PLSQL » Turning a table model into a DDL to import into APEX (APEX 11g)
Turning a table model into a DDL to import into APEX [message #583510] Wed, 01 May 2013 12:22 Go to next message
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?
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 Go to previous messageGo to next message
Littlefoot
Messages: 19690
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.
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Littlefoot
Messages: 19690
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.
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Littlefoot
Messages: 19690
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.
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Littlefoot
Messages: 19690
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.)).
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 Go to previous messageGo to next message
verifyjjones
Messages: 12
Registered: May 2013
Location: USA
Junior Member
i can save it in notepad as a .txt.
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 Go to previous messageGo to next message
Littlefoot
Messages: 19690
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?)
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 Go to previous messageGo to next message
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?
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 Go to previous messageGo to next message
Littlefoot
Messages: 19690
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?
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 Go to previous messageGo to next message
verifyjjones
Messages: 12
Registered: May 2013
Location: USA
Junior Member
Run it. So it gives me two options Run Now and Run in Background
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 Go to previous messageGo to next message
Littlefoot
Messages: 19690
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.
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Littlefoot
Messages: 19690
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).
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Littlefoot
Messages: 19690
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).
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Littlefoot
Messages: 19690
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Find statements that failed, fix them, run the script again. (That's called "debugging").
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 Go to previous messageGo to next message
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?
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 Go to previous messageGo to next message
Littlefoot
Messages: 19690
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!
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 Go to previous messageGo to next message
verifyjjones
Messages: 12
Registered: May 2013
Location: USA
Junior Member
Well thank you so much for your help!
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 Go to previous message
Littlefoot
Messages: 19690
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No problem; it's not that difficult once you get into it.
Previous Topic: Insert into BLOB
Next Topic: Hyperline on email to run on demand process
Goto Forum:
  


Current Time: Thu Oct 23 00:38:04 CDT 2014

Total time taken to generate the page: 0.10338 seconds