Re: PHP/Oracle SQL statements, OciParse

From: gmuldoon (gmuldoon_nospam_at_scu.edu.au)
Date: 02/13/04 

Date: Fri, 13 Feb 2004 00:06:23 GMT

In article <UYOWb.35$ao3.32634@news.uswest.net>, pdheady@comcast.net
says...
> Ok I'm a newbie to using Oracle9i w/ PHP, been a MySQL guy... What is the
> proper sql statement to use insert tables, etc. as below.? I don't know much
> about how OciParse works. I can connect successfuly, just my sql syntax is
> pretty bad.

For starters, WHY, WHY, WHY would you want to run CREATE TABLE in a PHP
script? If I did that my Oracle Database Administrator would (rightly)
do nasty things to sensitive parts of my anatomy. You should be using a
purpose-built tool for creating database objects, then just create PHP
scripts to manipulate data in those objects.

If you're going to use Oracle and don't have access to a DBA, be
prepared to do LOTS AND LOTS of reading. Actually, even if you DO have
a DBA, that is the case.

Go to http://otn.oracle.com and register (free), then start with some of
the basic fundamentals and concept guides. Start with:
Oracle9i Application Developer's Guide - Fundamentals

> $sql = "
> CREATE TABLE inventory
> (
> "item_id" numeric PRIMARY KEY auto_increment,;
> "transaction_id" numeric NULL,
> "vendor_id" numeric NULL,
> "user_id" numeric NULL,
> "item_barcode" varchar(16) NULL,
> "item_description" BLOB NULL,
<snip>

FWIW:
1. You haven't escaped the double-quotes around the column names in your
$sql definition, but in Oracle you shouldn't normally be using them
anyway.

2. Use "number" not "numeric" and set its size (and if required, its
precision), eg: item_price number(8,2).

3. Columns are by default nullable.

4. "auto_increment" doesn't exist in Oracle. Read up about sequences
(and probably about triggers - a combination of a sequence and a before-
insert trigger can simulate auto-increment).

5. Use "varchar2" instead of "varchar".

Example from the manual:

CREATE TABLE Emp_tab (
   Empno NUMBER(5) PRIMARY KEY,
   Ename VARCHAR2(15) NOT NULL,
   Job VARCHAR2(10),
   Mgr NUMBER(5),
   Hiredate DATE DEFAULT (sysdate),
   Sal NUMBER(7,2),
   Comm NUMBER(7,2),
   Deptno NUMBER(3) NOT NULL,
              CONSTRAINT dept_afkey REFERENCES Dept_tab(Deptno))

   PCTFREE 10
   PCTUSED 40
   TABLESPACE users
   STORAGE ( INITIAL 50K
              NEXT 50K
              MAXEXTENTS 10
              PCTINCREASE 25 );

Good luck,

Geoff M

Relevant Pages