Executing an oracle .sql file with php

Question

I'm trying to execute a simple .sql file that resets my database. It works fine when I run it in sqlplus with @, but returns ORA-00922: missing or invalid option, when I run it using php like that:

$query = file_get_contents($path);
$stid = oci_parse($con, $query);
$r = oci_execute($stid);

Here is the .sql file itself, it's quite simple and I simply can't find anything that is wrong with the commands:

ALTER SESSION SET NLS_LANGUAGE = AMERICAN;
ALTER SESSION SET NLS_TERRITORY = AMERICA;

DROP TABLE users;
DROP TABLE friends;
DROP TABLE replies;
DROP TABLE tweets;
DROP TABLE retweets;
DROP TABLE mentions;
DROP TABLE hashtags;

CREATE TABLE users (
    id_str VARCHAR2(20) NOT NULL PRIMARY KEY,
    name VARCHAR2(40) NOT NULL,
    screen_name VARCHAR2(20) NOT NULL,
    followers NUMBER(9) NOT NULL,
    friends NUMBER(9) NOT NULL
);

CREATE TABLE friends (
    id1_str VARCHAR2(20) NOT NULL,
    id2_str VARCHAR2(20) NOT NULL,
    CONSTRAINT friendship PRIMARY KEY (id1_str, id2_str)
);

CREATE TABLE replies (
    id1_str VARCHAR2(20) NOT NULL,
    id2_str VARCHAR2(20) NOT NULL,
    CONSTRAINT reply PRIMARY KEY (id1_str, id2_str)
);

CREATE TABLE tweets (
    id_str VARCHAR2(20) NOT NULL PRIMARY KEY,
    user_id_str VARCHAR2(20) NOT NULL,
    created DATE NOT NULL
);

CREATE TABLE retweets (
    user_id_str VARCHAR2(20) NOT NULL,
    tweet_id_str VARCHAR2(20) NOT NULL,
    CONSTRAINT retweet PRIMARY KEY (user_id_str, tweet_id_str)
);

CREATE TABLE hashtags (
    tweet_id_str VARCHAR2(20) NOT NULL,
    text VARCHAR2(140) NOT NULL,
    CONSTRAINT hashtag PRIMARY KEY (tweet_id_str, text)
);

CREATE TABLE mentions (
    user_id_str VARCHAR2(20) NOT NULL,
    tweet_id_str VARCHAR2(20) NOT NULL,
    CONSTRAINT mention PRIMARY KEY (user_id_str, tweet_id_str)
);

COMMIT;

I know it's probably silly, but I've spent hours looking for the solution to no avail and would appreciate any tips (even a different method than loading this file).

Answer 1

The problem is simple. Your file contains multiple commands, and oci_parse is meant to work with single statements.

You've got two options:

1. Read the file, parse it for semi-colons, and pass each resulting string (minus the semi-colon) to oci_parse/oci_execute, or
2. Change your file so it's a valid PL/SQL block, with BEGIN at the beginning and END; at the end, with each of the DDL statements you want to execute in an EXECUTE IMMEDIATE as shown below:

    BEGIN
      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE = AMERICAN';
      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY = AMERICA';

      EXECUTE IMMEDIATE 'DROP TABLE users';
      EXECUTE IMMEDIATE 'DROP TABLE friends';
      EXECUTE IMMEDIATE 'DROP TABLE replies';
      EXECUTE IMMEDIATE 'DROP TABLE tweets';
      EXECUTE IMMEDIATE 'DROP TABLE retweets';
      EXECUTE IMMEDIATE 'DROP TABLE mentions';
      EXECUTE IMMEDIATE 'DROP TABLE hashtags';

      EXECUTE IMMEDIATE 'CREATE TABLE users (
          id_str VARCHAR2(20) NOT NULL PRIMARY KEY,
          name VARCHAR2(40) NOT NULL,
          screen_name VARCHAR2(20) NOT NULL,
          followers NUMBER(9) NOT NULL,
          friends NUMBER(9) NOT NULL
      )';

      EXECUTE IMMEDIATE 'CREATE TABLE friends (
          id1_str VARCHAR2(20) NOT NULL,
          id2_str VARCHAR2(20) NOT NULL,
          CONSTRAINT friendship PRIMARY KEY (id1_str, id2_str)
      )';

      EXECUTE IMMEDIATE 'CREATE TABLE replies (
          id1_str VARCHAR2(20) NOT NULL,
          id2_str VARCHAR2(20) NOT NULL,
          CONSTRAINT reply PRIMARY KEY (id1_str, id2_str)
      )';

      EXECUTE IMMEDIATE 'CREATE TABLE tweets (
          id_str VARCHAR2(20) NOT NULL PRIMARY KEY,
          user_id_str VARCHAR2(20) NOT NULL,
          created DATE NOT NULL
      )';

      EXECUTE IMMEDIATE 'CREATE TABLE retweets (
          user_id_str VARCHAR2(20) NOT NULL,
          tweet_id_str VARCHAR2(20) NOT NULL,
          CONSTRAINT retweet PRIMARY KEY (user_id_str, tweet_id_str)
      )';

      EXECUTE IMMEDIATE 'CREATE TABLE hashtags (
          tweet_id_str VARCHAR2(20) NOT NULL,
          text VARCHAR2(140) NOT NULL,
          CONSTRAINT hashtag PRIMARY KEY (tweet_id_str, text)
      )';

      EXECUTE IMMEDIATE 'CREATE TABLE mentions (
          user_id_str VARCHAR2(20) NOT NULL,
          tweet_id_str VARCHAR2(20) NOT NULL,
          CONSTRAINT mention PRIMARY KEY (user_id_str, tweet_id_str)
      )';
    END;