Running Oracle Package

Running Oracle Package

Postby lmuhsmann » Thu 02 Jun 2005 18:47

I'm trying to run DDL against my Oracle database and have had success using OracleScript. I have been able to run traditional DDL statement such as CREATE TABLE. But I'm trying to run a SQL script that will CREATE OR REPLACE PACKAGE...
I assign OracleScript.ScriptText the SQL that creates the package then issue OracleScript.Execute(). The package is created in my database but it doesn't compile it. I have intentionally put an error in the package so I would expect OracleScript to throw an error stating that the package did not compile - but this is not the case.
Can you please let me know the proper way to create / replace packages and compile them via OraDirect?
lmuhsmann
 
Posts: 3
Joined: Thu 02 Jun 2005 18:39

Postby Paul » Fri 03 Jun 2005 13:34

Errors in CREATE OR REPLACE PACKAGE are considered as warnings in Oracle.
It is possibly because PACKAGE can be created with errors. You can get information
about warnings using OracleConnection.InfoMessage event in OraDirect .NET
3.0
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Postby lmuhsmann » Fri 03 Jun 2005 17:12

Paul,
I agree it creates the package but w/ error(s) so I tried setting the delegate for the OracleConnection.InfoMessage but it doesn't get called when OracleScript.Execute() is run.
I'm currently evaluating 2.50.1 should I move to 3.0 and have a go w/ that version?
lmuhsmann
 
Posts: 3
Joined: Thu 02 Jun 2005 18:39

Postby lmuhsmann » Mon 06 Jun 2005 20:45

I have upgraded to 3.0 and the call back now works. I'm getting a message via OracleInfoMessageEventArgs stating:

"ORA-24344: success with compilation error\n"

Inspection OracleInfoMessageEventArgs.Errors[0] has:

"PLS-00103: Encountered the symbol \"\" when expecting one of the following:\n\n end function package pragma private procedure subtype type\n use \n form current cursor"

My SQL is:
create or replace package Common AS
PROCEDURE testIT;
END Common;

There is not "\" anywhere but I'm still getting this error. As I stated above I'm using OracleScript to handle this - I have also tried OracleCommand but I get the exact same errors. Any ideas?
lmuhsmann
 
Posts: 3
Joined: Thu 02 Jun 2005 18:39

Postby Paul » Tue 07 Jun 2005 07:07

Send us please small demo project to demonstrate the problem to OraDirect .NET support address and include script to create server objects.
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Postby Paul » Thu 09 Jun 2005 12:46

We reproduced your problem. \"\" is a C# notation of empty string (""). The problem is in line separators.
OraDirect .NET like other Oracle data providers can receive SQL text with line separator 10. Windows text file contains 13,10 codes in line breaks. You must replace all "\r\n" in your SQL text to "\n" before assigning to OracleScript.ScriptText and OracleCommand.CommandText. We will correct this behaviour for OracleScript to allow text with 13,10 and 10 line separators.
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06


Return to dotConnect for Oracle