FoxPro, Oracle and PL/SQL???

[mike777]

Hello.
We currently create Oracle views using the SQLConnect, SQLPrepare and SQLExec functions.
Do you know of a way to execute a PL/SQL file via FoxPro. For example, let's say I have a PL/SQL file at R:\PLSQLCode.sql. Through SQL Plus, I say start r:\PLSQLCode.sql. Is there a way to do the equivalent from FoxPro??
Thanking you in advance.
-Mike

 

[Mike Gagnon]

mike777

This might help:

http://foxproadvisor.com/doc/08581

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[1oldfoxman]

It seems like you should be able to pass the path and name of the PL/SQL file to SQLPlus from a command line - like:

SQLPlus -r:\PLSQLCode.sql or something like that. (It's been a few years since I worked with SQLPlus, but it seems to me there is a way to pass the name of a file in a parameter from the operating system.) If so, then it seems to me you could start your run file from FoxPro by issuing :

! sqlplus <parameter syntax>

Wish I could remember what the syntax is. If you have the Oracle manuals, you should be able to look this up. The key here is the FoxPro RUN/! command which executes an operating system command (either RUN or ! will work). Don't forget to supply the path if necessary.

Hope this helps.

 

[mike777]

Wow, if this works, this will be HUGE.
Thanks!!
I'll post you on the results.
-Mike

 

[1oldfoxman]

I don't know if you figured this out yet or not. I dug out my old (really old....) SQL*Plus manual. As an example, if your username/pw are Scott/Tiger, and you want to run your R:\PLSQLCode.sql command file, you would execute the following from FoxPro:

! sqlplus scott/tiger @r:\PLSQLCode.sql

The @ character is the necessary syntax for passing the name of a command file to SQL*Plus. If it doesn't like the path being included in the command line, create another command file, say STARTER.SQL which contains:

START R:\PLSQLCODE.SQL

and change the FoxPro code to :
! sqlplus scott/tiger @starter

Make sure the starter.sql file is in the default directory in this instance. Anyway, I believe the first example will work without the use of an intermediate .SQL command file.

My experience is based on Oracle v6 and v7, so hopefully the syntax is the same in later versions. PL/SQL didn't exist prior to that, so I'm assuming you're not using something earlier.

Dennis

 

[mike777]

Thanks, Dennis.
I tried this and I get the error:
ORA-12203: TNS: Unable to connect to destination.

I know there are a myriad of things that could be wrong with this, but assuming that the server/Oracle instance is running OK (we currently have 400 users attached and it's running fine), do you have any ideas?

I did a search on the Oracle help site for that error and got nothing that I could see was helpful.

Here's my line of code:
! sqlplus MyUserNm/MyPW @c:\orant\bin\testing.sql

I also tried (since c:\orant\bin is the default location for this instance of Oracle):
! sqlplus MyUserNm/MyPW @testing

Thanks for any additional help you can provide.

-Mike

 

[1oldfoxman]

Well, it looks like you've at least got SqlPlus opening and understanding that you want to run an external command file.

Hmmmmmm......

If you open a DOS command window, go to the default FoxPro directory (as possibly established by any application code using SET DEFAULT TO), and execute &quot;sqlplus MyUserNm/MyPW&quot;,
do you get into SqlPlus OK? If so, then from the SqlPlus command line, if you execute the
&quot;start c:\orant\bin\testing.sql&quot; portion of your original command file, do you get the same error? Breaking these steps out might provide useful information about what exactly the ORA-12203 errors is referring to.

I'm wondering if the error is occurring because we're passing the command file parameter incorrectly or not getting into SqlPlus completely. I'm probably overstating the obvious, but it sounds like it might be a problem of insufficient priveleges.

If the &quot;START&quot; portion of the command is generating the error, you might wish to post that question on the Oracle Forum of this site in order to get the attention of more Oracle-knowledgeable folk. Either way, keep me posted - I'm intrigued.

Dennis