Home » SQL & PL/SQL » SQL & PL/SQL » Invalid characters, but have correct sql?? (Oracle SQL)
icon5.gif  Invalid characters, but have correct sql?? [message #603273] Thu, 12 December 2013 20:07 Go to next message
7oak_
Messages: 7
Registered: December 2013
Location: US
Junior Member
From what I understand if I have an invalid character I'll get the error but I looked over my SQL and it does look correct:


CREATE TABLE Report_Team
  
(
    
Department_ID INTEGER NOT NULL ,
    
Client       VARCHAR2 (20 CHAR) NOT NULL ,
    
Report_Date   DATE ,
    
Problem_Type  VARCHAR2 (10 CHAR)
  
)
;


ALTER TABLE Report_Team ADD CONSTRAINT Report_Team_PK PRIMARY KEY

(
  
Department_ID

)

;


CREATE TABLE Sales
  
(
    
Project_Code    INTEGER NOT NULL ,
       
Open_Date       DATE NOT NULL ,
    
Close_Date      DATE ,
    
Resolution      VARCHAR2 (10) ,
    
Priority_Number INTEGER NOT NULL ,
    
Assign_To       VARCHAR2 (20 CHAR),   

Open_By         VARCHAR2 (20 CHAR) ,
    
Department_ID   INTEGER NOT NULL ,
    
Manager_ID      INTEGER NOT NULL ,
    

ALTER TABLE Issues ADD CONSTRAINT Sales_PK PRIMARY KEY

(
  
Project_Code

)

;


CREATE TABLE Manager
  
(
    
First_Name CHAR (25) ,
    
Last_Name  CHAR (25) ,
    
Manager_ID INTEGER NOT NULL
  
) 
;


ALTER TABLE Manager ADD CONSTRAINT Manager_PK PRIMARY KEY

(
  
Manager_ID

)

;


CREATE TABLE Staff
  
(
    
Staff_ID     INTEGER NOT NULL ,
    
Last_Name    VARCHAR2 (15 CHAR) NOT NULL ,
    
First_Name   VARCHAR2 (15 CHAR) ,
        
Address      VARCHAR2 (30 CHAR) ,
    
City         VARCHAR2 (20 CHAR) ,
    
State        CHAR (2 CHAR) ,
    
Zipcode      VARCHAR2 (5 CHAR) ,
    
Phone        CHAR (10 CHAR)
  
) 
;


ALTER TABLE Staff ADD CONSTRAINT Staff_PK PRIMARY KEY
(
  Staff_ID
)
;

ALTER TABLE Issues ADD CONSTRAINT Issues_Report_Team_FK FOREIGN KEY ( Department_ID ) REFERENCES Report_Team ( Department_ID ) ;

ALTER TABLE Issues ADD CONSTRAINT Issues_Manager_FK FOREIGN KEY ( Manager_ID ) REFERENCES Manager ( Manager_ID ) ;



I have checked it over and I don't think any of the semicolons are incorrect but I could be wrong. I spent hours running the script to see what the problems were. Interestingly enough, when I run the schemas separately, I get no errors. There are also no errors when I run the ALTER separately. Instead of running everything individually, I'd rather run it all as a script so all of the output is listed one after another. I used the oracle cloud database that I was given access to for school. This is an assignment and I've done a lot of searching on the problem, and the only thing I can come up with is there is an "invisible character" I need to pinpoint. One of the errors after running the entire sql was on Line 4 near Client that said "invalid character near VARCHAR2." Can someone point me in the right direction? Thank you.

Report message to a moderator

Re: Invalid characters, but have correct sql?? [message #603274 is a reply to message #603273] Thu, 12 December 2013 20:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>CREATE TABLE Sales
statement above is incomplete & has syntax problem

why all the extra blank lines?

SQL> set term on echo on
SQL> @team1
SQL> CREATE TABLE report_team
  2    (
  3       department_id INTEGER NOT NULL,
  4       client        VARCHAR2 (20 CHAR) NOT NULL,
  5       report_date   DATE,
  6       problem_type  VARCHAR2 (10 CHAR)
  7    );

Table created.

SQL> 
SQL> ALTER TABLE report_team
  2    ADD CONSTRAINT report_team_pk PRIMARY KEY ( department_id );

Table altered.

SQL> 
SQL> CREATE TABLE sales
  2    (
  3       project_code    INTEGER NOT NULL,
  4       open_date       DATE NOT NULL,
  5       close_date      DATE,
  6       resolution      VARCHAR2 (10),
  7       priority_number INTEGER NOT NULL,
  8       assign_to       VARCHAR2 (20 CHAR),
  9       open_by         VARCHAR2 (20 CHAR),
 10       department_id   INTEGER NOT NULL,
 11       manager_id      INTEGER NOT NULL
 12    );

Table created.

SQL> 
SQL> ALTER TABLE issues
  2    ADD CONSTRAINT sales_pk PRIMARY KEY ( project_code );
ALTER TABLE issues
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> 
SQL> CREATE TABLE manager
  2    (
  3       first_name CHAR (25),
  4       last_name  CHAR (25),
  5       manager_id INTEGER NOT NULL
  6    );

Table created.

SQL> 
SQL> ALTER TABLE manager
  2    ADD CONSTRAINT manager_pk PRIMARY KEY ( manager_id );

Table altered.

SQL> 
SQL> CREATE TABLE staff
  2    (
  3       staff_id   INTEGER NOT NULL,
  4       last_name  VARCHAR2 (15 CHAR) NOT NULL,
  5       first_name VARCHAR2 (15 CHAR),
  6       address    VARCHAR2 (30 CHAR),
  7       city       VARCHAR2 (20 CHAR),
  8       state      CHAR (2 CHAR),
  9       zipcode    VARCHAR2 (5 CHAR),
 10       phone      CHAR (10 CHAR)
 11    );

Table created.

SQL> 
SQL> ALTER TABLE staff
  2    ADD CONSTRAINT staff_pk PRIMARY KEY ( staff_id );

Table altered.

SQL> 
SQL> ALTER TABLE issues
  2    ADD CONSTRAINT issues_report_team_fk FOREIGN KEY ( department_id ) REFERENCES
  3    report_team ( department_id );
ALTER TABLE issues
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> 
SQL> ALTER TABLE issues
  2    ADD CONSTRAINT issues_manager_fk FOREIGN KEY ( manager_id ) REFERENCES manager
  3    ( manager_id );
ALTER TABLE issues
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

[Updated on: Thu, 12 December 2013 20:49]

Report message to a moderator

Re: Invalid characters, but have correct sql?? [message #603275 is a reply to message #603274] Thu, 12 December 2013 21:10 Go to previous messageGo to next message
7oak_
Messages: 7
Registered: December 2013
Location: US
Junior Member
Sorry about the blank lines, I saved my SQL on notepad and then copied and pasted it here. I'll correct what I have and rerun it.

Report message to a moderator

Re: Invalid characters, but have correct sql?? [message #603276 is a reply to message #603275] Thu, 12 December 2013 21:25 Go to previous messageGo to next message
7oak_
Messages: 7
Registered: December 2013
Location: US
Junior Member
I just made the changes for the schemas and alters, but still have ORA-00911: invalid character.

CREATE TABLE Report_Team  
(
Department_ID INTEGER NOT NULL ,
Client        VARCHAR2 (20 CHAR) NOT NULL ,
Report_Date   DATE ,
Problem_Type  VARCHAR2 (10 CHAR)
);

ALTER TABLE Report_Team ADD CONSTRAINT Report_Team_PK PRIMARY KEY
(
Department_ID
);

CREATE TABLE Sales  
(
Project_Code    INTEGER NOT NULL ,
Open_Date       DATE NOT NULL ,
Close_Date      DATE ,
Resolution      VARCHAR2 (10) ,
Priority_Number INTEGER NOT NULL ,
Assign_To       VARCHAR2 (20 CHAR),   
Open_By         VARCHAR2 (20 CHAR) ,
Department_ID   INTEGER NOT NULL ,
Manager_ID      INTEGER NOT NULL ,
);    

ALTER TABLE Sales ADD CONSTRAINT Sales_PK PRIMARY KEY
(
Project_Code
);

CREATE TABLE Manager
(
First_Name CHAR (25) ,
Last_Name  CHAR (25) ,
Manager_ID INTEGER NOT NULL  
);

ALTER TABLE Manager ADD CONSTRAINT Manager_PK PRIMARY KEY
(
Manager_ID
);

CREATE TABLE Staff
(
Staff_ID     INTEGER NOT NULL ,
Last_Name    VARCHAR2 (15 CHAR) NOT NULL ,
First_Name   VARCHAR2 (15 CHAR) ,
Address      VARCHAR2 (30 CHAR) ,
City         VARCHAR2 (20 CHAR) ,
State        CHAR (2 CHAR) ,
Zipcode      VARCHAR2 (5 CHAR) ,
Phone        CHAR (10 CHAR)  
);

ALTER TABLE Staff ADD CONSTRAINT Staff_PK PRIMARY KEY
(
Staff_ID
);

ALTER TABLE Issues ADD CONSTRAINT Sales_Report_Team_FK FOREIGN KEY ( Department_ID ) REFERENCES Report_Team ( Department_ID );
ALTER TABLE Issues ADD CONSTRAINT Sales_Manager_FK FOREIGN KEY ( Manager_ID ) REFERENCES Manager ( Manager_ID );


When I run the schemas independently, no error. Is there a way to work around this? I'd rather have all of the output show up together, than separately.

Report message to a moderator

Re: Invalid characters, but have correct sql?? [message #603277 is a reply to message #603276] Thu, 12 December 2013 21:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
still syntax error in CREATE TABLE SALES in your code but not in mine
SQL> CREATE TABLE Report_Team
  2  (
  3  Department_ID INTEGER NOT NULL ,
  4  Client        VARCHAR2 (20 CHAR) NOT NULL ,
  5  Report_Date   DATE ,
  6  Problem_Type  VARCHAR2 (10 CHAR)
  7  );

Table created.

SQL> 
SQL> ALTER TABLE Report_Team ADD CONSTRAINT Report_Team_PK PRIMARY KEY
  2  (
  3  Department_ID
  4  );

Table altered.

SQL> 
SQL> CREATE TABLE Sales
  2  (
  3  Project_Code       INTEGER NOT NULL ,
  4  Open_Date       DATE NOT NULL ,
  5  Close_Date DATE ,
  6  Resolution VARCHAR2 (10) ,
  7  Priority_Number INTEGER NOT NULL ,
  8  Assign_To       VARCHAR2 (20 CHAR),
  9  Open_By         VARCHAR2 (20 CHAR) ,
 10  Department_ID      INTEGER NOT NULL ,
 11  Manager_ID INTEGER NOT NULL
 12  );

Table created.

SQL> 
SQL> ALTER TABLE Sales ADD CONSTRAINT Sales_PK PRIMARY KEY
  2  (
  3  Project_Code
  4  );

Table altered.

SQL> 
SQL> CREATE TABLE Manager
  2  (
  3  First_Name CHAR (25) ,
  4  Last_Name  CHAR (25) ,
  5  Manager_ID INTEGER NOT NULL
  6  );

Table created.

SQL> 
SQL> ALTER TABLE Manager ADD CONSTRAINT Manager_PK PRIMARY KEY
  2  (
  3  Manager_ID
  4  );

Table altered.

SQL> 
SQL> CREATE TABLE Staff
  2  (
  3  Staff_ID     INTEGER NOT NULL ,
  4  Last_Name    VARCHAR2 (15 CHAR) NOT NULL ,
  5  First_Name   VARCHAR2 (15 CHAR) ,
  6  Address      VARCHAR2 (30 CHAR) ,
  7  City         VARCHAR2 (20 CHAR) ,
  8  State        CHAR (2 CHAR) ,
  9  Zipcode      VARCHAR2 (5 CHAR) ,
 10  Phone        CHAR (10 CHAR)
 11  );

Table created.

SQL> 
SQL> ALTER TABLE Staff ADD CONSTRAINT Staff_PK PRIMARY KEY
  2  (
  3  Staff_ID
  4  );

Table altered.

SQL> 
SQL> ALTER TABLE Issues ADD CONSTRAINT Sales_Report_Team_FK FOREIGN KEY ( Department_ID ) REFERENCES Report_Team ( Department_ID );
ALTER TABLE Issues ADD CONSTRAINT Sales_Report_Team_FK FOREIGN KEY ( Department_ID ) REFERENCES Report_Team ( Department_ID )
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> ALTER TABLE Issues ADD CONSTRAINT Sales_Manager_FK FOREIGN KEY ( Manager_ID ) REFERENCES Manager ( Manager_ID );
ALTER TABLE Issues ADD CONSTRAINT Sales_Manager_FK FOREIGN KEY ( Manager_ID ) REFERENCES Manager ( Manager_ID )
*
ERROR at line 1:
ORA-00942: table or view does not exist

Report message to a moderator

Re: Invalid characters, but have correct sql?? [message #603278 is a reply to message #603277] Thu, 12 December 2013 22:22 Go to previous messageGo to next message
7oak_
Messages: 7
Registered: December 2013
Location: US
Junior Member
I corrected my code and deleted the extra comma (syntax error). I also corrected all alter tables with constraints, and still getting invalid character error...UGH. I might need to rewrite this or something because I can't figure it out!

CREATE TABLE Report_Team  
(
Department_ID INTEGER NOT NULL ,
Client        VARCHAR2 (20 CHAR) NOT NULL ,
Report_Date   DATE ,
Problem_Type  VARCHAR2 (10 CHAR)
);

ALTER TABLE Report_Team ADD CONSTRAINT Report_Team_PK PRIMARY KEY
(
Department_ID
);

CREATE TABLE Sales  
(
Project_Code    INTEGER NOT NULL ,
Open_Date       DATE NOT NULL ,
Close_Date      DATE ,
Resolution      VARCHAR2 (10) ,
Priority_Number INTEGER NOT NULL ,
Assign_To       VARCHAR2 (20 CHAR),   
Open_By         VARCHAR2 (20 CHAR) ,
Department_ID   INTEGER NOT NULL ,
Manager_ID      INTEGER NOT NULL 
);    

ALTER TABLE Sales ADD CONSTRAINT Sales_PK PRIMARY KEY
(
Project_Code
);

CREATE TABLE Manager
(
First_Name CHAR (25) ,
Last_Name  CHAR (25) ,
Manager_ID INTEGER NOT NULL  
);

ALTER TABLE Manager ADD CONSTRAINT Manager_PK PRIMARY KEY
(
Manager_ID
);

CREATE TABLE Staff
(
Staff_ID     INTEGER NOT NULL ,
Last_Name    VARCHAR2 (15 CHAR) NOT NULL ,
First_Name   VARCHAR2 (15 CHAR) ,
Address      VARCHAR2 (30 CHAR) ,
City         VARCHAR2 (20 CHAR) ,
State        CHAR (2 CHAR) ,
Zipcode      VARCHAR2 (5 CHAR) ,
Phone        CHAR (10 CHAR)  
);

ALTER TABLE Staff ADD CONSTRAINT Staff_PK PRIMARY KEY
(
Staff_ID
);

ALTER TABLE Sales ADD CONSTRAINT Sales_Report_Team_FK FOREIGN KEY ( Department_ID ) REFERENCES Report_Team ( Department_ID );
ALTER TABLE Sales ADD CONSTRAINT Sales_Manager_FK FOREIGN KEY ( Manager_ID ) REFERENCES Manager ( Manager_ID );

Report message to a moderator

Re: Invalid characters, but have correct sql?? [message #603279 is a reply to message #603278] Thu, 12 December 2013 22:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
it works OK for me
SQL> @team3
SQL> DROP   TABLE Report_Team  ;

Table dropped.

SQL> DROP   TABLE Sales  ;

Table dropped.

SQL> DROP   TABLE MANAGER ;

Table dropped.

SQL> DROP   TABLE Staff  ;

Table dropped.

SQL> CREATE TABLE Report_Team
  2  (
  3  Department_ID INTEGER NOT NULL ,
  4  Client        VARCHAR2 (20 CHAR) NOT NULL ,
  5  Report_Date   DATE ,
  6  Problem_Type  VARCHAR2 (10 CHAR)
  7  );

Table created.

SQL> 
SQL> ALTER TABLE Report_Team ADD CONSTRAINT Report_Team_PK PRIMARY KEY
  2  (
  3  Department_ID
  4  );

Table altered.

SQL> 
SQL> CREATE TABLE Sales
  2  (
  3  Project_Code       INTEGER NOT NULL ,
  4  Open_Date       DATE NOT NULL ,
  5  Close_Date DATE ,
  6  Resolution VARCHAR2 (10) ,
  7  Priority_Number INTEGER NOT NULL ,
  8  Assign_To       VARCHAR2 (20 CHAR),
  9  Open_By         VARCHAR2 (20 CHAR) ,
 10  Department_ID      INTEGER NOT NULL ,
 11  Manager_ID INTEGER NOT NULL
 12  );

Table created.

SQL> 
SQL> ALTER TABLE Sales ADD CONSTRAINT Sales_PK PRIMARY KEY
  2  (
  3  Project_Code
  4  );

Table altered.

SQL> 
SQL> CREATE TABLE Manager
  2  (
  3  First_Name CHAR (25) ,
  4  Last_Name  CHAR (25) ,
  5  Manager_ID INTEGER NOT NULL
  6  );

Table created.

SQL> 
SQL> ALTER TABLE Manager ADD CONSTRAINT Manager_PK PRIMARY KEY
  2  (
  3  Manager_ID
  4  );

Table altered.

SQL> 
SQL> CREATE TABLE Staff
  2  (
  3  Staff_ID     INTEGER NOT NULL ,
  4  Last_Name    VARCHAR2 (15 CHAR) NOT NULL ,
  5  First_Name   VARCHAR2 (15 CHAR) ,
  6  Address      VARCHAR2 (30 CHAR) ,
  7  City         VARCHAR2 (20 CHAR) ,
  8  State        CHAR (2 CHAR) ,
  9  Zipcode      VARCHAR2 (5 CHAR) ,
 10  Phone        CHAR (10 CHAR)
 11  );

Table created.

SQL> 
SQL> ALTER TABLE Staff ADD CONSTRAINT Staff_PK PRIMARY KEY
  2  (
  3  Staff_ID
  4  );

Table altered.

SQL> 
SQL> ALTER TABLE Sales ADD CONSTRAINT Sales_Report_Team_FK FOREIGN KEY ( Department_ID ) REFERENCES Report_Team ( Department_ID );

Table altered.

SQL> ALTER TABLE Sales ADD CONSTRAINT Sales_Manager_FK FOREIGN KEY ( Manager_ID ) REFERENCES Manager ( Manager_ID );

Table altered.

SQL>

Report message to a moderator

Re: Invalid characters, but have correct sql?? [message #603280 is a reply to message #603279] Thu, 12 December 2013 22:38 Go to previous messageGo to next message
7oak_
Messages: 7
Registered: December 2013
Location: US
Junior Member
Okay, I just checked it using sql fiddle and there were no errors. I should use a different IDE or something because I have no idea why the oracle workspace would keep reporting an error.

Report message to a moderator

Re: Invalid characters, but have correct sql?? [message #603281 is a reply to message #603280] Thu, 12 December 2013 22:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
sqlplus shows EXACTLY where syntax errors occur; which is how I quickly saw the extra comma

Report message to a moderator

Re: Invalid characters, but have correct sql?? [message #603282 is a reply to message #603281] Thu, 12 December 2013 22:49 Go to previous messageGo to next message
7oak_
Messages: 7
Registered: December 2013
Location: US
Junior Member
Is that available off the Oracle website?

Report message to a moderator

Re: Invalid characters, but have correct sql?? [message #603283 is a reply to message #603282] Thu, 12 December 2013 23:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Is that available off the Oracle website?
sqlplus is included in every Oracle bundle.
You can install Oracle DB on your own system.

Report message to a moderator

Re: Invalid characters, but have correct sql?? [message #603284 is a reply to message #603283] Thu, 12 December 2013 23:10 Go to previous messageGo to next message
7oak_
Messages: 7
Registered: December 2013
Location: US
Junior Member
Installed basic client and sqlplus, just figuring out where to store the files so I can open sqlplus. I've been told Oracle DB is too heavy on a system-- is it better to use Oracle virtual box for that?

Thanks for helping me locate the errors.

Report message to a moderator

Re: Invalid characters, but have correct sql?? [message #603287 is a reply to message #603280] Fri, 13 December 2013 00:17 Go to previous messageGo to next message
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
7oak_ wrote on Fri, 13 December 2013 05:38
I have no idea why the oracle workspace would keep reporting an error.


Are you, by any chance, trying to run that code in Application Express' SQL Workshop?

/forum/fa/11385/0/

It won't allow you to run multiple statements at a time, just one by one. I'd suggest you to download and install SQL Developer, a free Oracle GUI tool. Alternatively, use SQL*Plus (you have it already, it is installed along with the database).

I don't use Apex SQL Workshop unless I have to; it is probably the worst tool I've ever used to communicate with an Oracle database.

Report message to a moderator

Re: Invalid characters, but have correct sql?? [message #603288 is a reply to message #603287] Fri, 13 December 2013 00:21 Go to previous messageGo to next message
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh, as of your "virtualbox" question: if Oracle is too heavy for the host system, why do you think that it will run better on a virtual machine which depends on host's resources (and "steals" its processor, memory ... everything)? Based on my own experience: if your computer isn't powerful enough (at least two processor cores, plenty of RAM), don't even try to run virtual machine as CPU and RAM indicators will raise up to 100% and you won't be able to do anything (not to mention running Oracle).

I do use virtual machine, because I have to develop applications in Developer Suite 9.0.4; it runs smoothly on Windows XP, but not that well on Windows 7 (my current primary operating system). So - I'm not against virtual machines, but I believe that you shouldn't use it "just because" - you have to have a valid reason. Seeing what you have at the moment - your reason's aren't valid.

Report message to a moderator

Re: Invalid characters, but have correct sql?? [message #603332 is a reply to message #603284] Fri, 13 December 2013 07:54 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
7oak_ wrote on Thu, 12 December 2013 23:10
Installed basic client and sqlplus, just figuring out where to store the files so I can open sqlplus. I've been told Oracle DB is too heavy on a system-- is it better to use Oracle virtual box for that?

Thanks for helping me locate the errors.


If you've actually installed the client, then there is nothing to figure out "where to store the files". The installer already did that. All you have to do is open sqlplus at a command prompt, and learn to work at a command line.

You could additionally install SQL Developer, a free gui client from oracle.



You don't need to install a database. You obviously already have one on some other server that you are trying to work with. All you need on your workstation is the client.

Report message to a moderator

Previous Topic: Global variable in Oracle Object (Super/Sub) Types?
Next Topic: num_rows blank
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 23 06:05:49 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.