Re: create.sql for Oracle

Hi Eric

On Apr 18, 2012, at 5:27 AM, Eric Prud'hommeaux wrote:

> How have folks interpreted 1table1primarykey10columns3rowsSQLdatatypes
> in Oracele??  (BTW, I propose a name like allTypes if that works for
> others.)

So, you are suggesting to replace SQLdatatypes by allTypes?
If everyone agrees I'll rename it.


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

I followed Richard suggestions, so now the sql script is sth like this
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" VARBINARY(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,
X'89504E470D0A1A0A0000000D49484452000000050000000508060000008D6F26E50000001C4944415408D763F9FFFEBFC37F062005C3201284D031F18258CD04000EF535CBD18E0E1F0000000049454E44AE426082');

INSERT INTO "Patient" ("ID", "FirstName","LastName","Sex","Weight","Height","BirthDate","EntranceDate","PaidInAdvance","Photo") 
VALUES (11,'Rachel','Green','female',70.22,1.70,'1982-11-12','2008-11-12 09:45:44',TRUE,
X'89504E470D0A1A0A0000000D49484452000000050000000508060000008D6F26E50000001C4944415408D763F9FFFF3FC37F062005C3201284D031F18258CD04000EF535CBD18E0E1F0000000049454E44AE426082');

INSERT INTO "Patient" ("ID", "FirstName","LastName","Sex","Weight","Height","BirthDate","EntranceDate","PaidInAdvance","Photo") 
VALUES (12,'Chandler','Bing','male',90.31,1.76,'1978-04-06','2007-03-12 02:13:14',TRUE,
X'89504E470D0A1A0A0000000D49484452000000050000000508060000008D6F26E50000001C4944415408D763F9FFFEBFC37F062005C3201284D031F18258CD04000EF535CBD18E0E1F0000000049454E44AE426082');

        
i.e., do not use CAST, as Richard suggested Don't use a CAST but use a binary literal. The standard SQL way of encoding binary literals is this   X'0123456789ABCDEF'


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

I dunno, I haven't tested with ORACLE

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

It seems to be ok …. http://www.postgresql.org/docs/8.1/static/datatype-binary.html

Boris



> -- 
> -ericP
>