473,324 Members | 2,356 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Need some help with Visual Basic and Oracle

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.
Oct 18 '05 #1
1 2106
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.

Oct 18 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Ian DeRock | last post by:
I'm fairly new to XML. As I can see it, XML is a way to organize data like in a table in a RDB, or the RDB itself. I have used XML in application data, but did not design the actual XML. I...
29
by: RAY | last post by:
Hi , my boss has asked I sit in on an interview this afternoon and that I create some interview questions on the person's experience. What is C++ used for and why would a company benefit from...
1
by: tristant | last post by:
Hi All, We want to develope Windows and Web .NET application on Oracle 9i on AIX. Also we will use Crystal Report for reporting. I have question about the what Visual Studio.NET edition do we...
1
by: Tom Rahav | last post by:
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...
9
by: D. Patrick | last post by:
I need to connect to an Oracle database. I've never done it before. I see that with framework 1.1 you had to download ODP.NET from Oracle's site in order for the framework classes to even work....
1
by: kiran261 | last post by:
visual basic and oracle connectivity i want to know how can i access records from oracle in visual basic what is the diffrence between connect & connection
0
by: keshabtsk | last post by:
I have a problem that to create an application which demands to transfer tthe data from Visual Fox Pro 6 to Oracle 8i. Oracle 8i is hosted on Windows 2003 Server. The application is required to be...
1
by: subratamaji | last post by:
Hi, I am new to crystal report. Can any one plz guide how to use crystal report 11.5 with visual basic 6.0. Is it already included in visual basic 6.0 or will have to include it from...
1
by: Mike001 | last post by:
my question is that for version control for oracle database at object level i.e. at table etc, oracle 10g SCM will be more feasible soultion or alternatively using Visual Source Safe. Prefereably, i...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.