How to execute sets of DDL Statements in 1 command?

[adshocker]

Hi All,

I've been trying to look for a way to run a set of DDL statements. I normally have text files with DDL Statements on it. So what I do is load the contents of this text file into a Textbox and then set the Textbox.Text as the CommandText of my command object. Unfortunately I get errors.

Here's my code...

    Private Sub btnLoadFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim OpenDiag As New OpenFileDialog
        Dim txtReader As StreamReader

        OpenDiag.ShowDialog()
        If OpenDiag.FileName <> "" Then
            txtReader = File.OpenText(OpenDiag.FileName)
            CommandTextBox.Text = txtReader.ReadToEnd
        End If
    End Sub


    Private Sub btnExecute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim myConn As New OracleConnection(My.Settings.ConnectionString)
        Dim myComm As New OracleCommand(CommandTextBox.Text, myConn)
        Try
            myConn.Open()
            myComm.ExecuteNonQuery()
            myConn.Close()
        Catch ex As OracleException
            MessageBox.Show(ex.Code & ": " & ex.Message)
        End Try
    End Sub

The errors that I get are
"ORA-00900: Invalid SQL Statement"
- Not sure how I get this but I noticed that if i had remarks/comments in my text file, this is the error that i get. Although i don't get this error if i execute in SQL Plus.
"ORA-00911: Invalid Character"
- after removing the remarks/comments, i get this error. i tried removing the semi-colon ";" then it works okay if i'm running 1 DDL statement in my file.. but if i my file contains to DDL statements for example, then i get the this error "ORA-00922: Missing or Invalid Options".

can anyone help me with this?
thanks.

 

[adshocker]

just like to add the codes in the text file.

CREATE TABLE SM_USERS
(
  USER_NUM NUMBER NOT NULL,
  USERNAME VARCHAR2(30) NOT NULL,
  PASSWORD VARCHAR2(32) NOT NULL,
  FIRST_NAME VARCHAR2(30) NOT NULL,
  LAST_NAME VARCHAR2(30) NOT NULL,
  MIDDLE_NAME VARCHAR2(30),
  EMAIL_ADDRESS VARCHAR2(30) NOT NULL,
  CREATE_DATE DATE,
  LAST_UPDATE_DATE DATE,
  LAST_LOGIN_DATE DATE,
  LAST_PWD_CHG_DATE DATE,
  ADMINISTRATOR VARCHAR2(1)
);

ALTER TABLE SM_USERS ADD CONSTRAINT PK_SM_USERS PRIMARY KEY (USER_NUM);

CREATE TABLE SM_ROLES
(
  ROLE_NUM NUMBER NOT NULL,
  DESCRIPTION VARCHAR2(30) NOT NULL,
  PARENT_ROLE_NUM NUMBER,
  IN_USE VARCHAR2(1) NOT NULL,
  LOOK_ONLY VARCHAR2(1) NOT NULL
);

ALTER TABLE SM_ROLES ADD CONSTRAINT PK_SM_ROLES PRIMARY KEY (ROLE_NUM);
ALTER TABLE SM_ROLES ADD CONSTRAINT FK_SM_ROLES FOREIGN KEY (PARENT_ROLE_NUM) REFERENCES SM_ROLES (ROLE_NUM);

CREATE TABLE SM_APPLICATIONS
(
  APPLICATION_NUM NUMBER NOT NULL,
  DESCRIPTION VARCHAR2(30) NOT NULL,
  URL VARCHAR2(255) NOT NULL,
  APPLICATION_ID NUMBER NOT NULL,
  MORE_INFORMATION VARCHAR2(4000),
  PARENT_APP_NUM NUMBER,
  IN_USE VARCHAR2(1) NOT NULL
);

ALTER TABLE SM_APPLICATIONS ADD CONSTRAINT PK_SM_APPLICATIONS PRIMARY KEY (APPLICATION_NUM);
ALTER TABLE SM_APPLICATIONS ADD CONSTRAINT FK_SM_APPLICATIONS FOREIGN KEY (PARENT_APP_NUM) REFERENCES SM_APPLICATIONS (APPLICATION_NUM);

CREATE TABLE SM_APP_ROLES
(
	APP_ROLE_NUM NUMBER NOT NULL,
	APPLICATION_NUM NUMBER NOT NULL,
	ROLE_NUM NUMBER NOT NULL
);

ALTER TABLE SM_APP_ROLES ADD CONSTRAINT PK_SM_APP_ROLES PRIMARY KEY (APP_ROLE_NUM);
ALTER TABLE SM_APP_ROLES ADD CONSTRAINT FK_SM_APP_ROLES_01 FOREIGN KEY (APPLICATION_NUM) REFERENCES SM_APPLICATIONS (APPLICATION_NUM);
ALTER TABLE SM_APP_ROLES ADD CONSTRAINT FK_SM_APP_ROLES_02 FOREIGN KEY (ROLE_NUM) REFERENCES SM_ROLES (ROLE_NUM);

CREATE TABLE SM_USER_ROLES
(
	USER_ROLE_NUM NUMBER NOT NULL,
	USER_NUM NUMBER NOT NULL,
	ROLE_NUM NUMBER NOT NULL
);

ALTER TABLE SM_USER_ROLES ADD CONSTRAINT PK_SM_USER_ROLES PRIMARY KEY (USER_ROLE_NUM);
ALTER TABLE SM_USER_ROLES ADD CONSTRAINT FK_SM_USER_ROLES_01 FOREIGN KEY (USER_NUM) REFERENCES SM_USERS (USER_NUM);
ALTER TABLE SM_USER_ROLES ADD CONSTRAINT FK_SM_USER_ROLES_02 FOREIGN KEY (ROLE_NUM) REFERENCES SM_ROLES (ROLE_NUM);

CREATE SEQUENCE USER_NUM_S
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE
INCREMENT BY 1
/

CREATE OR REPLACE FUNCTION hash_value(p_string in varchar2)
RETURN VARCHAR2 IS
 h RAW(32767);
 n NUMBER;
 x NUMBER;
BEGIN
  x :=  dbms_utility.get_sql_hash(p_string, h, n);
  RETURN(TO_CHAR(h));
  --dbms_output.put_line('Return Value: ' || TO_CHAR(x));
  --dbms_output.put_line('Hash: ' || h);
  --dbms_output.put_line('Pre10iHash: ' || TO_CHAR(n));
END hash_value;
/

CREATE OR REPLACE FUNCTION authenticate_s_user(
p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN BOOLEAN IS
CURSOR s_cur( username_p VARCHAR,password_p VARCHAR) IS
SELECT COUNT(*) user_count FROM SM_USERS
  WHERE UPPER(username) = username_p
  AND PASSWORD = password_p;
result_v   BOOLEAN := FALSE;
BEGIN
  FOR s_rec IN s_cur(p_username, hash_value(p_password))
  LOOP
     IF s_rec.user_count = 1 THEN result_v    := TRUE; END IF;
  END LOOP; 
RETURN result_v;
END authenticate_s_user;
/

CREATE OR REPLACE FUNCTION authorize_s_user(
app_id_in IN PLS_INTEGER, username_in IN VARCHAR2)
RETURN BOOLEAN IS
CURSOR app_cur(app_id_p PLS_INTEGER,user_p VARCHAR2) IS
SELECT DISTINCT sa.application_num
FROM SM_USERS su, SM_USER_ROLES sur, SM_ROLES sr,
SM_APP_ROLES sar, SM_APPLICATIONS sa
  WHERE su.user_num = sur.user_num
    AND sur.role_num = sr.role_num
    AND sr.role_num = sar.role_num
    AND sar.application_num = sa.application_num
    AND UPPER(su.username) = user_p
    AND sa.application_id = app_id_p;
result_v   BOOLEAN := FALSE;
BEGIN
FOR app_rec IN app_cur(app_id_in, username_in)
LOOP result_v    := TRUE; END LOOP;
RETURN result_v;
END authorize_s_user;
/

CREATE OR REPLACE FUNCTION authorize_s_user2(
app_num_in IN PLS_INTEGER,username_in IN VARCHAR2)
RETURN varchar IS
CURSOR app_cur(app_num_p PLS_INTEGER, user_p VARCHAR2) IS
SELECT DISTINCT sa.application_num
FROM SM_USERS su,SM_USER_ROLES sur,
SM_ROLES sr,SM_APP_ROLES sar,SM_APPLICATIONS sa
  WHERE su.user_num = sur.user_num
    AND sur.role_num = sr.role_num
    AND sr.role_num = sar.role_num
    AND sar.application_num = sa.application_num
    AND UPPER(su.username) = user_p
    AND sa.application_num = app_num_p;
result_v   varchar2(1) := 'N';
BEGIN
FOR app_rec IN app_cur(app_num_in, username_in)
LOOP result_v    := 'Y'; END LOOP;
RETURN result_v;
END authorize_s_user2;
/

CREATE OR REPLACE TRIGGER BI_SM_USERS
	BEFORE INSERT ON SM_USERS
	FOR EACH ROW
BEGIN

	SELECT USER_NUM_S.NEXTVAL
	  INTO :NEW.USER_NUM
	  FROM DUAL;

	:NEW.PASSWORD := HASH_VALUE(:NEW.PASSWORD);
	:NEW.USERNAME := UPPER(:NEW.USERNAME);
	:NEW.CREATE_DATE := SYSDATE;
END BI_SM_USERS;
/

CREATE TABLE LP_MODULES
(
	MODULE_CD VARCHAR2(3) NOT NULL,
	DESCRIPTION VARCHAR2(30)
);

ALTER TABLE LP_MODULES ADD CONSTRAINT PK_LP_MODULES PRIMARY KEY (MODULE_CD);

INSERT INTO LP_MODULES (MODULE_CD, DESCRIPTION) VALUES ('MD', 'METADATA');
INSERT INTO LP_MODULES (MODULE_CD, DESCRIPTION) VALUES ('DM', 'DATAMART');
INSERT INTO LP_MODULES (MODULE_CD, DESCRIPTION) VALUES ('ODS', 'OPERATIONAL DATA STORAGE');

COMMIT;

CREATE TABLE LP_SEVERITIES
(
	SEVERITY_CD VARCHAR2(3) NOT NULL,
	DESCRIPTION VARCHAR2(30)
);

ALTER TABLE LP_SEVERITIES ADD CONSTRAINT PK_LP_SEVERITIES PRIMARY KEY (SEVERITY_CD);

INSERT INTO LP_SEVERITIES VALUES ('LOW', 'LOW');
INSERT INTO LP_SEVERITIES VALUES ('MED', 'MEDIUM');
INSERT INTO LP_SEVERITIES VALUES ('HIG', 'HIGH');

COMMIT;

CREATE TABLE LP_RESOLUTIONS
(
	RESOLUTION_CD VARCHAR2(3) NOT NULL,
	DESCRIPTION VARCHAR2(30)
);

ALTER TABLE LP_RESOLUTIONS ADD CONSTRAINT PK_LP_RESOLUTIONS PRIMARY KEY (RESOLUTION_CD);

INSERT INTO LP_RESOLUTIONS VALUES ('OPN', 'OPEN');
INSERT INTO LP_RESOLUTIONS VALUES ('CLD', 'CLOSED');
INSERT INTO LP_RESOLUTIONS VALUES ('REJ', 'REJECTED');
INSERT INTO LP_RESOLUTIONS VALUES ('FIX', 'FIXED');
INSERT INTO LP_RESOLUTIONS VALUES ('RON', 'RE-OPEN');

COMMIT;

CREATE TABLE FL_MODS
(
  ID NUMBER NOT NULL,
  TITLE VARCHAR2(30) NOT NULL,
  MODULE VARCHAR2(3) NOT NULL,
  SEVERITY VARCHAR2(4) NOT NULL,
  REPORTED_BY NUMBER NOT NULL,
  CREATE_DATE DATE NOT NULL,
  SUBMITTER NUMBER NOT NULL
);

ALTER TABLE FL_MODS ADD CONSTRAINT PK_FL_MODS PRIMARY KEY (ID);
ALTER TABLE FL_MODS ADD CONSTRAINT FK_FL_MODS_01 FOREIGN KEY (REPORTED_BY) REFERENCES SM_USERS (USER_NUM);
ALTER TABLE FL_MODS ADD CONSTRAINT FK_FL_MODS_02 FOREIGN KEY (SUBMITTER) REFERENCES SM_USERS (USER_NUM);

CREATE TABLE FL_MOD_DTLS
(
  ID NUMBER NOT NULL,
  VERSION NUMBER NOT NULL,
  OWNER NUMBER NOT NULL,
  RESPONSE VARCHAR2(4000) NOT NULL,
  RESOLUTION VARCHAR2(10) NOT NULL,
  CREATE_DATE DATE NOT NULL,
  SUBMITTER NUMBER NOT NULL
);

ALTER TABLE FL_MOD_DTLS ADD CONSTRAINT PK_FL_MOD_DTLS PRIMARY KEY (ID, VERSION);
ALTER TABLE FL_MOD_DTLS ADD CONSTRAINT FK_FL_MOD_DTLS_01 FOREIGN KEY (ID) REFERENCES FL_MODS (ID);
ALTER TABLE FL_MOD_DTLS ADD CONSTRAINT FK_FL_MOD_DTLS_02 FOREIGN KEY (SUBMITTER) REFERENCES SM_USERS (USER_NUM);

CREATE SEQUENCE FCR_ID_S
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE
INCREMENT BY 1
/

if i run the file directly to sql plus i don't get any errors. i'm guessing there might be something that the OracleCommand doesn't support.

 

[adshocker]

i found out that removing linefeeds and semi-colons somehow fixes the problem for DDL statements. also, if i use a RichTextBox and uses its RichTextBox.LoadFile(filename, RichTextBoxStreamType.PlainText) instead of the File.OpenText(filename), it works fine as well. although its not something i would want. so what i did was to create a PL/SQL Anonymous Block instead. this too works if i use a RichTextBox.LoadFile instead of a textbox. but i'm using these controls to see the contents. eventually i will need to use a variable to store the contents of the file and i'm worried that it won't work with it.

any suggestions?
thanks.

 

[cjard]

Oracle cannot execute multiple SQL statements per command (http://www.net-security.org/dl/articles/IntegrigyIntrotoSQLInjectionAttacks.pdf) without using an anonymous plsql block which, in turn, will be subect to certain security restrictions such as needing explicit grants to create stored procs, etc (cannot be acquired from a role)

I would suggest that executing these statements requires you to tokenise the string, quite simple logic:

replace the entire string: find '' (two apostrophes), replace with ###reallyunlikelytoken###
read the string a character at a time
if a ' is encountered, toggle the state of in_a_string boolean
if a ; is encountered and in_a_string is false, then substring at this point: all that has gone before is a statement.
replace ###reallyunlikelytoken### with '' and put it in a List(Of String)

when you reached the end of the string and have built a list of string statemetns, ensure that no ; are present, then run them using a loop, updating the commandtext of your oraclecommand each loop

Note.. you dont have to read a file into a textbox! THere are other ways of getting a string variable into your code, the simplest of which is:

Dim s as String = File.ReadAllText("c:\temp.txt")

 

[adshocker]

thanks for the suggestion.

i'm gonna give it a try.

i also tried using the pl/sql anonymous block and it worked ok.