Date time?? - unable to write data to table

PL-SQL...error Invalid Character...unable to write data to table,.


DROP TABLE titles;
CREATE TABLE titles(
title_id CHAR(3) NOT NULL,
title_name VARCHAR(40) NOT NULL,
type VARCHAR(10) NULL,
pub_id CHAR(3) NOT NULL,
pages INTEGER NULL,
price DECIMAL(5,2) NULL,
sales INTEGER NULL,
pubdate DATETIME NULL,
contract SMALLINT NOT NULL,
CONSTRAINT titles_pk PRIMARY KEY (title_id)
);



INSERT INTO titles VALUES ('T01','1977!','history','P01',107,21.99,566,'2000-08-01',1);
TechMonsterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lonerangerlsCommented:
You need to to_timestamp the date value which is currently a string
INSERT INTO titles VALUES ('T01','1977!','history','P01',107,21.99,566,to_timestamp('2000-08-01','YYYY-MM-DD),1);

Here's a reference:
http://www.techonthenet.com/oracle/functions/to_timestamp.php

L
0
slightwv (䄆 Netminder) Commented:
Just to add:
By default the date format must follow the value of 'NLS_DATE_FORMAT'.  To insert different formats, you must convert it first as lonerangerls mentioned.  You can also use the TO_DATE function since timestamps are pretty new to Oracle.
0
TechMonsterAuthor Commented:
Still get ORA-00911: invalid character...

Even with the simple code:

DROP TABLE titles;
CREATE TABLE titles(title_id CHAR(3) NOT NULL);
INSERT INTO titles VALUES ('TO1');
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

lonerangerlsCommented:
what version of Oracle are you using?
Try this
drop table titles;
CREATE TABLE titles
(
  Title_id    VARCHAR2(3 BYTE),
  title_name  VARCHAR2(40 BYTE),
  Type        VARCHAR2(10 BYTE),
  pub_id      VARCHAR2(3 BYTE),
  pages       INTEGER,
  price       DECIMAL(5,2),
  sales       INTEGER,
  pubdate     TIMESTAMP,
  contract    SMALLINT
)

ALTER TABLE titles ADD (
  PRIMARY KEY
 (Title_id));


INSERT INTO TITLES ( TITLE_ID, TITLE_NAME, TYPE, PUB_ID, PAGES, PRICE, SALES, PUBDATE,
CONTRACT ) VALUES (
'T01', '1977!', 'history', 'P01', 107, 21.99, 566,  TO_TIMESTAMP( '03/19/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1);
COMMIT;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TechMonsterAuthor Commented:
Using Oracle Application Express client within Oracle Database XE.
0
slightwv (䄆 Netminder) Commented:
sqlWorksheet in app express?

I think you can only run 1 command at a time.  Paste them in individually.
0
TechMonsterAuthor Commented:
THIS does work but i have to run it separately..
I have to drop the table in one execution
Create the table in another
Alter the table
then insert..

How to you make it so that it runs as one execution istead of 4.
drop table titles;

CREATE TABLE titles
(
  Title_id    VARCHAR2(3 BYTE),
  title_name  VARCHAR2(40 BYTE),
  Type        VARCHAR2(10 BYTE),
  pub_id      VARCHAR2(3 BYTE),
  pages       INTEGER,
  price       DECIMAL(5,2),
  sales       INTEGER,
  pubdate     Date,
  contract    SMALLINT
)


ALTER TABLE titles ADD (
  PRIMARY KEY
 (Title_id));


INSERT INTO TITLES
      (TITLE_ID, TITLE_NAME, TYPE, PUB_ID, PAGES, PRICE, SALES, PUBDATE,CONTRACT )

VALUES
     ('T01', '1977!', 'history', 'P01', 107, 21.99, 566,  TO_TIMESTAMP( '03/19/2007', 'MM/DD/YYYY')
, 1);
0
lonerangerlsCommented:
Is your table going to be dynamic?
or will you continue to use this table?
if you run this as a script (one file with a "/" after each block it will run with out any interaction, however I'm not sure what you are trying to accomplish.

LS
0
slightwv (䄆 Netminder) Commented:
You can save all the commands in a file and execute the file from app express.
0
TechMonsterAuthor Commented:
Just need to create a table and insert a few rows in it with one execution. I believe it is dynamic If i understand you right.  
 
Normal sql you just do everything with one execution..You can create the table and add values in it...Unable to save my code as a script..I am new to this oracle stuff...Just learning how to code using the PL-Sql...I am finding it harder to use than normal SQL (T-SQL).

0
TechMonsterAuthor Commented:
Your solution does work however I am unable to do it with one execution...which could be from the limitation of PSQL...
0
slightwv (䄆 Netminder) Commented:
A couple of things:
PL/SQL is the procedural language part of Oracle.  You are just executing regular SQL commands.

Not allowing multiple commands is a limitation of SQL Worksheet.

Using app express, select the SQL Scripts icon instead of SQL Worksheet.

This will let you create a script file with all the commands which you can then execute.
0
TechMonsterAuthor Commented:
Yeah,,the script thing..worked..

Thanks slightwv!  I already awared the points to lonerangerls though..sorry.
0
slightwv (䄆 Netminder) Commented:
No problem.  As long as you get the answer that you need (and learn to love Oracle over SQL Server).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.