create.sql for Oracle

How have folks interpreted 1table1primarykey10columns3rowsSQLdatatypes
in Oracele??  (BTW, I propose a name like allTypes if that works for
others.) I've interpreted
[[
CREATE TABLE "Patient" (
"ID" INTEGER,
"FirstName" VARCHAR(50),
"LastName" VARCHAR(50),
"Sex" VARCHAR(6),
"Weight" REAL,
"Height" FLOAT,
"BirthDate" DATE,
"EntranceDate" TIMESTAMP,
"PaidInAdvance" BOOLEAN,
"Photo" BINARY VARYING(200),
PRIMARY KEY ("ID")
);

INSERT INTO "Patient" ("ID", "FirstName","LastName","Sex","Weight","Height","BirthDate","EntranceDate","PaidInAdvance","Photo") 
VALUES (10,'Monica','Geller','female',80.25,1.65,'1981-10-10','2009-10-10 12:12:22',FALSE, CAST(
'iVBORw0KGgoAAAANSUhEUgAAAAUAAAAFCAYAAACNbyblAAAAHElEQVQI12P4\ux2F\ux2F8\ux2Fw38GIAXDIBKE0DHxgljNBAAO9TXL0Y4OHwAAAABJRU5ErkJggg\ux3D\ux3D'
AS BINARY VARYING(200)));
]]
as
[[
CREATE TABLE "Patient" (
"ID" INTEGER,
"FirstName" VARCHAR(50),
"LastName" VARCHAR(50),
"Sex" VARCHAR(6),
"Weight" REAL,
"Height" FLOAT,
"BirthDate" DATE,
"EntranceDate" TIMESTAMP,
"PaidInAdvance" NUMERIC(1), -- takes 0 or 1
"Photo" BLOB,               -- what's a good varying binary type?
PRIMARY KEY ("ID")
);

INSERT INTO "Patient" ("ID", "FirstName", "LastName", "Sex", "Weight", "Height", "BirthDate", "EntranceDate", "PaidInAdvance", "Photo") VALUES (10, 'Monica', 'Geller', 'female', 80.25, 1.65, TO_DATE('1981-10-10', 'yyyy-mm-dd'), TO_DATE('2009-10-10 12:12:22', 'yyyy-mm-dd HH24:MI:SS'), 0, CAST('iVBORw0KGgoAAAANSUhEUgAAAAUAAAAFCAYAAACNbyblAAAAHElEQVQI12P4//8/w38GIAXDIBKE0DHxgljNBAAO9TXL0Y4OHwAAAABJRU5ErkJggg==' AS BLOB))
]]
but I get
"ORA-00932: inconsistent datatypes: expected - got BLOB" at "0, CAST('iV"
                                                                ↑
and if I get rid of the CAST I get "ORA-01465: invalid hex number" at ", 0, 'iVBO"
                                                                             ↑
Also, gives me ORA-00957: duplicate column name:
CREATE TABLE "成分" ("皿" VARCHAR(10), "植物名" VARCHAR(10), "使用部" VARCHAR(10))
                                                                                          ↑
The apparent collision seems to be the last two column names.
CREATE TABLE "成分" ("皿" VARCHAR(10), "植物名" VARCHAR(10)) works

One more thing, for Postgresql, I used BYTEA for BINARY. Any better advice?
-- 
-ericP

Received on Wednesday, 18 April 2012 03:28:10 UTC