Solved

Date time?? - unable to write data to table

Posted on 2007-03-19
14
317 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying 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

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 …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

751 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