Solved

Date time?? - unable to write data to table

Posted on 2007-03-19
14
319 Views
Last Modified: 2013-12-07
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);
0
Comment
Question by:TechMonster
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
14 Comments
 
LVL 1

Expert Comment

by:lonerangerls
ID: 18749377
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18749503
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
 

Author Comment

by:TechMonster
ID: 18749516
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Accepted Solution

by:
lonerangerls earned 250 total points
ID: 18749759
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
 

Author Comment

by:TechMonster
ID: 18749804
Using Oracle Application Express client within Oracle Database XE.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18749920
sqlWorksheet in app express?

I think you can only run 1 command at a time.  Paste them in individually.
0
 

Author Comment

by:TechMonster
ID: 18749925
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
 
LVL 1

Expert Comment

by:lonerangerls
ID: 18749998
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18750070
You can save all the commands in a file and execute the file from app express.
0
 

Author Comment

by:TechMonster
ID: 18750091
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
 

Author Comment

by:TechMonster
ID: 18750099
Your solution does work however I am unable to do it with one execution...which could be from the limitation of PSQL...
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18750214
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
 

Author Comment

by:TechMonster
ID: 18750446
Yeah,,the script thing..worked..

Thanks slightwv!  I already awared the points to lonerangerls though..sorry.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18750526
No problem.  As long as you get the answer that you need (and learn to love Oracle over SQL Server).
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question