Need some help with Visual Basic and Oracle

[Tom Rahav]

Hello all!

I develop application in Visual Basic .NET and ORACLE database.
My question is how do I "send" script file to the database using visual
basic .net.
Other words, is there any way to send to the database a script file to run,
as done by: "@FullPath\FileName" in SQL PLUS ?
If not, then I need your help with something else: I tried to send to the
database the script file's content, in order to run the commands in it
(create table) in my case, but I got an error message: "ORA-00911: Invalid
character".
In order to run the script file content, I read it into a string variable
called sqlQuery and than use command to run it:

Dim command As New OracleCommand(sqlQuery, connection)
command.ExecuteNonQuery()

At this point I get the error message. I thoght it might related to syntax
or so, but when I copy and paste the same code (the script file content) in
SQL PLUS it creates the table without problem.
Last, here is the file content I need to run by visual basic:

//////////////////////////////////////// Begin of examle
////////////////////////////////////////////////////////////
PURGE TABLESPACE USERS USER SCOTT;

CREATE TABLE inventory
(
item_num number (9) REFERENCES items ( item_num ) ON DELETE CASCADE ,
stock_num number (9) REFERENCES stocks ( stock_num ),
from_month number (2) CHECK ( from_month <= 12 AND from_month >= 1 ),
quantity number (9,2) ,
PRIMARY KEY( item_num , stock_num )
);

//////////////////////////////////////// End of examle
////////////////////////////////////////////////////////////

Thanks!
Tom.

[P. Van Den Goess]

Tom, I believe it to be the multiple statements. Just in way to diagnose
problem, separate both of the statements and so that you just have the Purge
statement and then the create table statement. At such point, try to run
each and see if they run. I know in Sql Server there is for instance a GO
statement which is perfectly valid in Query analyzer but when used in a
string, it creates exceptions.

As far as Sql Plus - you can use the command shell to start it but at last
glimpse at least with version 9, I don't think you can execute statement.
If you pass file name as parameter, it will open it up and load file, but I
can't think of if it can run script. Perhaps send keys woudl do it but this
is very not elegant and doubtful what you seek. I will see though if I
can - but in meantime see if it is possible for you to split all statements
and see if it were to still run.
"Tom Rahav" <to******@nospam.nospam> wrote in message
news:eF*************@TK2MSFTNGP10.phx.gbl...

Hello all!

I develop application in Visual Basic .NET and ORACLE database.
My question is how do I "send" script file to the database using visual
basic .net.
Other words, is there any way to send to the database a script file to run, as done by: "@FullPath\FileName" in SQL PLUS ?
If not, then I need your help with something else: I tried to send to the
database the script file's content, in order to run the commands in it
(create table) in my case, but I got an error message: "ORA-00911: Invalid
character".
In order to run the script file content, I read it into a string variable
called sqlQuery and than use command to run it:

Dim command As New OracleCommand(sqlQuery, connection)
command.ExecuteNonQuery()

At this point I get the error message. I thoght it might related to syntax
or so, but when I copy and paste the same code (the script file content) in SQL PLUS it creates the table without problem.
Last, here is the file content I need to run by visual basic:

//////////////////////////////////////// Begin of examle
////////////////////////////////////////////////////////////
PURGE TABLESPACE USERS USER SCOTT;

CREATE TABLE inventory
(
item_num number (9) REFERENCES items ( item_num ) ON DELETE CASCADE ,
stock_num number (9) REFERENCES stocks ( stock_num ),
from_month number (2) CHECK ( from_month <= 12 AND from_month >= 1 ),
quantity number (9,2) ,
PRIMARY KEY( item_num , stock_num )
);

//////////////////////////////////////// End of examle
////////////////////////////////////////////////////////////

Thanks!
Tom.