Home » SQL & PL/SQL » SQL & PL/SQL » Invalid characters, but have correct sql?? (Oracle SQL)
 Invalid characters, but have correct sql?? [message #603273] |
Thu, 12 December 2013 20:07  |
 |
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.
|
|
|
|
| Re: Invalid characters, but have correct sql?? [message #603274 is a reply to message #603273] |
Thu, 12 December 2013 20:39  |
 |
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 #603276 is a reply to message #603275] |
Thu, 12 December 2013 21:25 |
|
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.
|
|
|
|
| Re: Invalid characters, but have correct sql?? [message #603277 is a reply to message #603276] |
Thu, 12 December 2013 21:41 |
|
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
|
|
|
|
| Re: Invalid characters, but have correct sql?? [message #603278 is a reply to message #603277] |
Thu, 12 December 2013 22:22 |
|
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 );
|
|
|
|
| Re: Invalid characters, but have correct sql?? [message #603279 is a reply to message #603278] |
Thu, 12 December 2013 22:30 |
|
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>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Invalid characters, but have correct sql?? [message #603287 is a reply to message #603280] |
Fri, 13 December 2013 00:17 |
 |
Littlefoot
Messages: 21824
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
7oak_ wrote on Fri, 13 December 2013 05:38I 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?
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.
|
|
|
|
| Re: Invalid characters, but have correct sql?? [message #603288 is a reply to message #603287] |
Fri, 13 December 2013 00:21 |
|
Littlefoot
Messages: 21824
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.
|
|
|
|
| Re: Invalid characters, but have correct sql?? [message #603332 is a reply to message #603284] |
Fri, 13 December 2013 07:54 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
7oak_ wrote on Thu, 12 December 2013 23:10Installed 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.
|
|
|
|
Goto Forum:
Current Time: Sun May 18 16:28:34 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
