Solved

Date time?? - unable to write data to table

Posted on 2007-03-19
14
311 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
  • 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 76

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
 
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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 76

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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now