26. november 2008 - 09:30
Der er
7 kommentarer og
1 løsning
Hjælp til dato format
Hej.
Nedenstående ser I min sql-fil og derefter output med fejl.
Selve problemet ligger i, at jeg normalt bruger SQL Plus på en amerikansk pc i skolen (da jeg læser i USA).
Derimod har jeg installeret SQL Developer på min egen pc og der er jo et andet datoformat da pc'en er europæisk.
Jeg har prøvet at sætte datoformatet, men det vil ikke rigtig lykkes. Er der nogle der kan se fejlen?
Drop table actors;
Drop table movies;
Drop table awards;
Drop table roles;
set dateformat date='DD-MON-YY';
Create table actors(ActorID varchar2(3) constraint pk_actorid primary key, Fname char(50), LName char(50), Gender char(6), Birthdate date, PlaceOfBirth char(50));
Insert into actors values('1', 'Eddie', 'Murphy', 'Male', '05-MAR-65', 'New York');
Insert into actors values('2', 'Charlie', 'Sheen', 'Male', '07-NOV-71', 'Boston');
insert into actors values('3', 'Nicolas', 'Cage', 'Male', '09-DEC-67', 'Dallas');
insert into actors values('4', 'Meryl', 'Streep', 'Female', '05-MAY-64', 'Chicago');
insert into actors values('5', 'Nicole', 'Kidman', 'Female', '07-APR-59', 'Los Angeles');
insert into actors values('6', 'Russell', 'Crowe', 'Male', '18-DEC-63', 'Seattle');
insert into actors values('7', 'Harrison', 'Ford', 'Male', '11-OCT-54', 'Washington');
insert into actors values('8', 'Keanu', 'Reeves', 'Male', '01-FEB-71', 'New Jersey');
insert into actors values('9', 'Cameron', 'Diaz', 'Female', '06-MAY-66', 'Miami');
insert into actors values('10', 'Julia', 'Roberts', 'Female', '06-JAN-58', 'Houston');
insert into actors values('11', 'Leonardo', 'DiCaprio', 'Male', '10-APR-72', 'San Antonio');
insert into actors values('12', 'Will', 'Ferrell', 'Male', '02-JUL-73', 'Detroit');
insert into actors values('13', 'Will', 'Smith', 'Male', '17-SEP-69', 'Los Angeles');
insert into actors values('14', 'Christian', 'Bale', 'Male', '08-NOV-71', 'Milwaukee');
insert into actors values('15', 'Kevin', 'Costner', 'Male', '07-SEP-57', 'Atlanta');
insert into actors values('16', 'Morgan', 'Freeman', 'Male', '06-JUL-52', 'Sacramento');
insert into actors values('17', 'Angelina', 'Jolie', 'Female', '09-AUG-75', 'Charlotte');
insert into actors values('18', 'Brad', 'Pitt', 'Male', '30-AUG-68', 'Minnesota');
insert into actors values('19', 'Jim', 'Carrey', 'Male', '02-NOV-65', 'Boston');
insert into actors values('20', 'Jodie', 'Foster', 'Female', '03-MAY-66', 'Philadelphia');
create table Movies(MovieID varchar2(3) constraint pk_movieid primary key, MovieName char(50), Genre char(50), ReleaseYear varchar2(4), RunningLength varchar2(4), MPAArating char(10));
insert into Movies values(1, 'The Nutty Professor', 'Comedy', '1996', '117', 'G');
insert into Movies values(2, 'Nottingham', 'Action', '2008', '136', 'PG-13');
insert into Movies values(3, 'The Cable Guy', 'Comedy', '1998', '132', 'PG');
insert into Movies values(4, 'Ace Ventura', 'Comedy', '1996', '116', 'G');
insert into Movies values(5, 'Conair', 'Action', '1999', '115', 'PG-13');
insert into Movies values(6, 'Titanic', 'Drama', '1997', '196', 'PG-13');
insert into Movies values(7, 'Indiana Jones and the last Crusade', 'Action', '1989', '134', 'PG-13');
insert into Movies values(8, 'Air Force One', 'Action', '1997', '122', 'PG-13');
insert into Movies values(9, 'Seven years in Tibet', 'Drama', '1998', '156', 'G');
insert into Movies values(10, 'Semipro', 'Comedy', '2007', '106', 'G');
create table Roles(RoleID varchar2(3) constraint pk_roleid primary key, Role char(50), MovieID varchar2(3), ActorID varchar2(3));
insert into Roles values('1', 'Sherman Klump', '1', '1');
insert into Roles values('2', 'Papa Klump', '1', '1');
insert into Roles values('3', 'Mama Klump', '1', '1');
insert into Roles values('4', 'Robin Hood', '2', '6');
insert into Roles values('5', 'Sheriff of Nottingham', '2', '6');
insert into Roles values('6', 'Cable Guy', '3', '19');
insert into Roles values('7', 'Cameron Poe', '5', '3');
insert into Roles values('8', 'Jack Dawsen', '6', '11');
insert into Roles values('9', 'Indiana Jones', '7', '7');
insert into Roles values('10', 'President James Marshall', '8', '7');
insert into Roles values('11', 'Heinrich Harrer', '9', '18');
insert into Roles values('12', 'Jackie Moon', '10', '12');
CREATE TABLE AWARDS (
AWARDNO VARCHAR2(4) CONSTRAINT PK_AWARDS_AWARDNO PRIMARY KEY,
ANAME VARCHAR(30),
AWARDDATE DATE,
AWARDTYPE VARCHAR (30),
MOVIE VARCHAR(50),
ACTOR VARCHAR (50));
INSERT INTO AWARDS VALUES
(1000, 'OSCAR', '04-NOV-97', 'BEST LEADING ROLE', 'Air Force One', 'Harrison Ford');
INSERT INTO AWARDS VALUES
(1001, 'OSCAR', '04-NOV-97', 'BEST SCREENPLAY', 'Titanic', 'James Cameron');
INSERT INTO AWARDS VALUES
(1002, 'MTV MOVIE AWARD', '05-DEC-08', 'BEST LEADING ROLE', 'Titanic', 'Leonardo Dicaprio');
INSERT INTO AWARDS VALUES
(1003,'EMMY', '05-MAY-96', 'BEST COMEDY', 'Ace Ventura', 'Jim Carrey');
Commit;
og mit output:
Drop table actors succeeded.
Drop table movies succeeded.
Drop table awards succeeded.
Drop table roles succeeded.
line 6: SQLPLUS Command Skipped: set dateformat date='DD-MON-YY';
Create table succeeded.
1 rows inserted
1 rows inserted
1 rows inserted
Error starting at line 13 in command:
insert into actors values('4', 'Meryl', 'Streep', 'Female', '05-MAY-64', 'Chicago')
Error report:
SQL Error: ORA-01861: litteral matcher ikke formatstreng
01861. 00000 - "literal does not match format string"
*Cause: Literals in the input must be the same length as literals in
the format string (with the exception of leading whitespace). If the
"FX" modifier has been toggled on, the literal must match exactly,
with no extra whitespace.
*Action: Correct the format string to match the literal.
1 rows inserted
1 rows inserted
Error starting at line 16 in command:
insert into actors values('7', 'Harrison', 'Ford', 'Male', '11-OCT-54', 'Washington')
Error report:
SQL Error: ORA-01861: litteral matcher ikke formatstreng
01861. 00000 - "literal does not match format string"
*Cause: Literals in the input must be the same length as literals in
the format string (with the exception of leading whitespace). If the
"FX" modifier has been toggled on, the literal must match exactly,
with no extra whitespace.
*Action: Correct the format string to match the literal.
1 rows inserted
Error starting at line 18 in command:
insert into actors values('9', 'Cameron', 'Diaz', 'Female', '06-MAY-66', 'Miami')
Error report:
SQL Error: ORA-01861: litteral matcher ikke formatstreng
01861. 00000 - "literal does not match format string"
*Cause: Literals in the input must be the same length as literals in
the format string (with the exception of leading whitespace). If the
"FX" modifier has been toggled on, the literal must match exactly,
with no extra whitespace.
*Action: Correct the format string to match the literal.
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
Error starting at line 29 in command:
insert into actors values('20', 'Jodie', 'Foster', 'Female', '03-MAY-66', 'Philadelphia')
Error report:
SQL Error: ORA-01861: litteral matcher ikke formatstreng
01861. 00000 - "literal does not match format string"
*Cause: Literals in the input must be the same length as literals in
the format string (with the exception of leading whitespace). If the
"FX" modifier has been toggled on, the literal must match exactly,
with no extra whitespace.
*Action: Correct the format string to match the literal.
create table succeeded.
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
create table succeeded.
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
CREATE TABLE succeeded.
1 rows inserted
1 rows inserted
1 rows inserted
Error starting at line 73 in command:
INSERT INTO AWARDS VALUES
(1003,'EMMY', '05-MAY-96', 'BEST COMEDY', 'Ace Ventura', 'Jim Carrey')
Error report:
SQL Error: ORA-01861: litteral matcher ikke formatstreng
01861. 00000 - "literal does not match format string"
*Cause: Literals in the input must be the same length as literals in
the format string (with the exception of leading whitespace). If the
"FX" modifier has been toggled on, the literal must match exactly,
with no extra whitespace.
*Action: Correct the format string to match the literal.
Commit succeeded.
med venlig hilsen Michael
26. november 2008 - 10:38
#2
Hej.
Tak for forslaget, men jeg får bare fejlkode igen:
Drop table actors succeeded.
Drop table movies succeeded.
Drop table awards succeeded.
Drop table roles succeeded.
ALTER SESSION set succeeded.
Create table succeeded.
1 rows inserted
Error starting at line 11 in command:
Insert into actors values('2', 'Charlie', 'Sheen', 'Male', '07-NOV-71', 'Boston')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
Error starting at line 12 in command:
insert into actors values('3', 'Nicolas', 'Cage', 'Male', '09-DEC-67', 'Dallas')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
Error starting at line 13 in command:
insert into actors values('4', 'Meryl', 'Streep', 'Female', '05-MAY-64', 'Chicago')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
Error starting at line 14 in command:
insert into actors values('5', 'Nicole', 'Kidman', 'Female', '07-APR-59', 'Los Angeles')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
Error starting at line 15 in command:
insert into actors values('6', 'Russell', 'Crowe', 'Male', '18-DEC-63', 'Seattle')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
Error starting at line 16 in command:
insert into actors values('7', 'Harrison', 'Ford', 'Male', '11-OCT-54', 'Washington')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
Error starting at line 17 in command:
insert into actors values('8', 'Keanu', 'Reeves', 'Male', '01-FEB-71', 'New Jersey')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
Error starting at line 18 in command:
insert into actors values('9', 'Cameron', 'Diaz', 'Female', '06-MAY-66', 'Miami')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
Error starting at line 19 in command:
insert into actors values('10', 'Julia', 'Roberts', 'Female', '06-JAN-58', 'Houston')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
Error starting at line 20 in command:
insert into actors values('11', 'Leonardo', 'DiCaprio', 'Male', '10-APR-72', 'San Antonio')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
Error starting at line 21 in command:
insert into actors values('12', 'Will', 'Ferrell', 'Male', '02-JUL-73', 'Detroit')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
Error starting at line 22 in command:
insert into actors values('13', 'Will', 'Smith', 'Male', '17-SEP-69', 'Los Angeles')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
Error starting at line 23 in command:
insert into actors values('14', 'Christian', 'Bale', 'Male', '08-NOV-71', 'Milwaukee')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
Error starting at line 24 in command:
insert into actors values('15', 'Kevin', 'Costner', 'Male', '07-SEP-57', 'Atlanta')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
Error starting at line 25 in command:
insert into actors values('16', 'Morgan', 'Freeman', 'Male', '06-JUL-52', 'Sacramento')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
Error starting at line 26 in command:
insert into actors values('17', 'Angelina', 'Jolie', 'Female', '09-AUG-75', 'Charlotte')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
Error starting at line 27 in command:
insert into actors values('18', 'Brad', 'Pitt', 'Male', '30-AUG-68', 'Minnesota')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
Error starting at line 28 in command:
insert into actors values('19', 'Jim', 'Carrey', 'Male', '02-NOV-65', 'Boston')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
Error starting at line 29 in command:
insert into actors values('20', 'Jodie', 'Foster', 'Female', '03-MAY-66', 'Philadelphia')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
create table succeeded.
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
create table succeeded.
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
CREATE TABLE succeeded.
Error starting at line 67 in command:
INSERT INTO AWARDS VALUES
(1000, 'OSCAR', '04-NOV-97', 'BEST LEADING ROLE', 'Air Force One', 'Harrison Ford')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
Error starting at line 69 in command:
INSERT INTO AWARDS VALUES
(1001, 'OSCAR', '04-NOV-97', 'BEST SCREENPLAY', 'Titanic', 'James Cameron')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
1 rows inserted
Error starting at line 73 in command:
INSERT INTO AWARDS VALUES
(1003,'EMMY', '05-MAY-96', 'BEST COMEDY', 'Ace Ventura', 'Jim Carrey')
Error report:
SQL Error: ORA-01858: et ikke-numerisk tegn blev fundet, hvor et numerisk tegn var forventet
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.
Commit succeeded.
