• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

Invalid datatype wehn doing alter tables in jdbc?

hello experts...

i have the following code:

            
            statem.executeUpdate( "create table population(cid varchar2(5), pop number, pyear date)" );
            System.out.println("Created populations table...");
            
            statem.executeUpdate( "create table encompassed(cid char(5), con varchar2(40), perc number)" );
            System.out.println("Created encompassed table...");
            
            statem.executeUpdate( "create table ethngroups(cid char(5), grp varchar2(30), perc number)" );
            System.out.println("Created ethngroups table...");
            
            statem.executeUpdate( "create table religions(cid char(5), rel varchar2(30), perc number)" );
            System.out.println("Created religions table...");
            
            statem.executeUpdate( "create table languages(cid char(5), lan varchar2(30), perc number)" );
            System.out.println("Created languages table...");
            
            statem.executeUpdate( "create table border(cid char(5), cou varchar2(40), len number, jus varchar2(40))" );
            System.out.println("Created border table...");
            
            statem.executeUpdate( "create table locatedat(cid varchar2(50), type char(5))" );
            System.out.println("Created locatedat table...");
            
            statem.executeUpdate( "create table provloc(cid char(50), pname varchar2(40))" );
            System.out.println("Created provloc table...");
            
            statem.executeUpdate( "create table cityloc(cid char(50), pid varchar2(50), cname varchar2(40))" );
            System.out.println("Created cityloc table...");
       
                  
                        
            //Apply integrity constraints
            //INSERT MASSIVE AMOUNTS OF XML DATA HERE

            System.out.println("Applying integrity constraints...");
            
            statem.executeUpdate( "alter table population add (constraint pop_fk" +
                                    "foreign key (cid) references cities(id))");
            System.out.println("Altered population table...");
                  
            statem.executeUpdate( "alter table encompassed add (constraint enc_fk" +
                                    "foreign key (cid) references countries(ccode))");
            System.out.println("Altered encompassed table...");
                  
            statem.executeUpdate( "alter table ethngroups add (constraint ethn_fk" +
                                    "foreign key (cid) references countries(ccode))");
            System.out.println("Altered ethngroups table...");
                  
            statem.executeUpdate( "alter table religions add (constraint rel_fk" +
                                    "foreign key (cid) references countries(ccode))");
            System.out.println("Altered religions table...");
                  
            statem.executeUpdate( "alter table languages add (constraint lan_fk" +
                                    "foreign key (cid) references countries(ccode))");
            System.out.println("Altered languages table...");
                  
            statem.executeUpdate( "alter table border add (constraint bor_fk" +
                                    "foreign key (cid) references countries(ccode))" );
            System.out.println("Altered border table...");
                  
            statem.executeUpdate( "alter table locatedat add (constraint locat_fk" +
                                    "foreign key (cid) references cities(id))" );
            System.out.println("Altered locatedat table...");
                  
            statem.executeUpdate( "alter table provloc add (constraint provloc_fk" +
                                    "foreign key (cid) references countries(ccode))");
            System.out.println("Altered provloc table...");
                 
            statem.executeUpdate( "alter table cityloc add (constraint cityloc_fk1" +
                                    "foreign key (cid) references countries(ccode)," +
                                    "constraint cityloc_fk2 foreign key (pid)" +
                                    "references provinces(id))");    
            System.out.println("Altered cityloc table...");

well i get to create the tables but it wont let me alter it. i get an invalid datatype from oracle.
help please :(
0
msgolez
Asked:
msgolez
  • 5
  • 4
1 Solution
 
msgolezAuthor Commented:
oh there are more tables but i only posted the relevant ones. when i run the creates/alter as a script on the sqlplus itself, everyhing works...
0
 
CEHJCommented:
Some of those brackets aren't necessary and you have spacing problems. e.g.

>>
statem.executeUpdate( "alter table languages add (constraint lan_fk" +
                              "foreign key (cid) references countries(ccode))");
>>

should be

statem.executeUpdate( "alter table languages add constraint lan_fk " +
                              "foreign key (cid) references countries(ccode)");
0
 
Mayank SAssociate Director - Product EngineeringCommented:
Try running it as a batch using statement.executeBatch ()
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
msgolezAuthor Commented:
thank you for the reply guys, ill try a couple of things tonite and let you guys how it went.
0
 
msgolezAuthor Commented:
should be

statem.executeUpdate( "alter table languages add constraint lan_fk " +
                              "foreign key (cid) references countries(ccode)");

actually, it was the space between the foreign key keyword and (cid) that was messing it up. thanks!
0
 
CEHJCommented:
:-)
0
 
CEHJCommented:
>>actually, it was the space between the foreign key keyword and (cid) that was messing it up.

No, it would have been the *lack* of space before 'foreign key' that was messing it up

>>
statem.executeUpdate( "alter table encompassed add (constraint enc_fk" +
                              "foreign key
>>
0
 
msgolezAuthor Commented:
im sorry but you kinda threw me off...

statem.executeUpdate( "alter table languages add constraint lan_fk " +
                              "foreign key (cid) references countries(ccode)");

isnt there a space after 'lan_fk'? so when the string is built it should be ok right?
0
 
CEHJCommented:
>>isnt there a space after 'lan_fk'? so when the string is built it should be ok right?

There wasn't in your original, no
0
 
msgolezAuthor Commented:
huh.. didnt even notice that :D

thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now