ExecSQL + Oracle + OCI_SUCCESS_WITH_INFO

[mwgomez]

I am useing The ZeosLib version 6.5.1 alpha CVS as of 13/10/2005
(downloaded from sourceforge.net 22/09/2006).

I am writeing an appliaction in Delphi 7, connecting to the Oracle database (Oracle 10g Express Edition).
On the form there is a component ZConnection with properties:

Name ZConnection1
Database XE // (default database in Oracle 10g Express Edition)
Hostname 127.0.0.1 // localhost
Protocol oracle

User and password are OK. My programm is connecting to Oracle properly.

Now I want to create the stored procedure.
I am starting "Go To Database Home Page", running the SQL Command
(Enter the SQL Command) and type the sql code for createing my procedure for example:

CREATE OR REPLACE PROCEDURE PROC3(m_nazwisko IN VARCHAR2, m_imie IN VARCHAR2)
AS BEGIN
insert into OSOBY2(nazwisko, imie) values(m_nazwisko, m_imie);
END PROC3;

Now I am checking procedure:

SELECT
OBJECT_NAME, OBJECT_TYPE, CREATED, STATUS
FROM USER_OBJECTS
WHERE
OBJECT_TYPE='PROCEDURE'
AND
OBJECT_NAME='PROC3'

In the column STATUS i see value "VALID"

Now, I know that the SQL code is properly writeing so we can back to the Delphi codeing.


I placed the TZQuery on the form.
Connection ZConnection1
Name queryMake
Than I put a button on the form and create action OnClick:

procedure TForm1.btnMAKEClick(Sender: TObject);
var
txt : String;

begin
with queryMake do
begin
SQL.Clear();

SQL.Add(' CREATE OR REPLACE PROCEDURE PROC3(m_nazwisko IN VARCHAR2, m_imie IN VARCHAR2) ');
SQL.Add(' AS BEGIN ');
SQL.Add(' insert into OSOBY2(nazwisko, imie) values(m_nazwisko, m_imie); ');
SQL.Add(' END PROC3; ');

SQL.Add(txt);


try
queryMake.ExecSQL();
ShowMessage('OK');
except
raise;
end;
end;
end;

When the action is start the SQL error occurs: "OCI_SUCCESS_WITH_INFO"
Now, when I am checking does the procedure exists, I see that the procedure was created but in the column STATUS I see the value "INVALID", and the procedure
code in the oracle (browse procedure) is:

create or replace PROCEDURE PROC3(m_nazwisko IN VARCHAR2, m_imie IN VARCHAR2)
AS BEGIN
insert into OSOBY2(nazwisko, imie) values(m_nazwisko, m_imie);
END PROC3;


Can you help me?

Regards
mwgomez
Poland

[mdaems]

Hi,

Have a look at http://zeos.firmos.at/viewtopic.php?t=680
Somebody had a hack to live with that problem. In fact, we need somebody who can dig into it to find out where the bug is hidden in the coding. I don't know exactly who's using Oracle out there. Do you have some spare time to debug? There must be some error where the statements are split before feeding them to the oracle driver dll's. But where?? We would be very grateful when you can tell us.

Mark

[mwgomez]

1)

When I am createing ZConnection : TZConnection:
ZConnection := TZConnection.Create(Nil);

// Now, I can set transations in my code
ZConnection.AutoCommit := false;
...
And then I am useing Transaction i Have an error.
I change procedure TZConnection.CheckAutoCommitMode()
from
procedure TZConnection.CheckAutoCommitMode();
begin
if not FAutoCommit and (FExplicitTransactionCounter = 0) then
begin
raise EZDatabaseError.Create(SInvalidOpInNonAutoCommit);
end;
end;

to

procedure TZConnection.CheckAutoCommitMode();
begin
if FAutoCommit and (FExplicitTransactionCounter = 0) then
begin
raise EZDatabaseError.Create(SInvalidOpInNonAutoCommit);
end;
end;


and transactions are OK.

Regards
mwgomez

[mwgomez]

2/
According to the error while make procedure

I check, the path in source code is:

Call the function:
function TZOraclePreparedStatement.ExecuteUpdatePrepared() : Integer;

Call the procedure:
procedure ExecuteOracleStatement(PlainDriver : IZOraclePlainDriver;
Connection : IZConnection;
SQL : String;
Handle : POCIStmt;
ErrorHandle : POCIError);

In my program parameters has values:

PlainDriver :
value=TZOracle9iPlainDriver($A737D8) as IZOraclePlainDriver
Connection :
value=TZAbstractConnection($AFB850) as IZConnection
SQL :
value=' CREATE OR REPLACE PROCEDURE PROC3(m_nazwisko IN VARCHAR2, m_imie IN VARCHAR2) AS BEGIN insert into OSOBY2(nazwisko, imie) values(m_nazwisko, m_imie); END PROC3 '#$D#$A
Handle :
value=$FFE21C
ErrorHandle :
value=$FFE628


In this procedure:
procedure ExecuteOracleStatement(PlainDriver : IZOraclePlainDriver;
Connection : IZConnection;
SQL : String;
Handle : POCIStmt;
ErrorHandle : POCIError);
var
Status: Integer;
OracleConnection: IZOracleConnection;

begin
OracleConnection := Connection as IZOracleConnection;
Status := PlainDriver.StmtExecute(OracleConnection.GetContextHandle, Handle, ErrorHandle, 1, 0, nil, nil, OCI_DEFAULT);

// this line display error:
CheckOracleError(PlainDriver, ErrorHandle, Status, lcExecute, SQL);
end;

I don't know this text will help you to find the reason.


Regards
mwgomez

[Lorenz]

I had a similar Problem
I think it is a oracle warning which should not automatically be raised as an exception

ORA-24347 Warning of a NULL column in an aggregate function

Cause: A null column was processed by an aggregate function.

Action: An OCI_SUCCESS_WITH_INFO is returned.

first query would not work in Zquery
but when I modified it with 'Coalesce' to get rid of nulls in aggragte columns it work just fine .
The Query worked fine in questsoft TOAD.

Non working Query only work in sqlplus and or toad

select
sum(decode(a.size_num,1,b.qty1,2,b.qty2,3, b.qty3,4, b.qty4,5,b.qty5,
6,b.qty6,7,b.qty7,8,b.qty8,9,b.qty9,10,b.qty10,11,b.qty11,12,b.qty12)) qty,
max(sty_barcode),
Max(loc_num)
from pro.twelve_sizes a, pro.gar_stk b, pro.sty_defs c,
pro.sty_barcodes d, pro.val_sty_types e
where b.season = c.season
and b.sty_num = c.sty_num
and b.sty_qual = c.sty_qual
and e.sty_type = c.sty_type
and d.bf_mat_char_val = b.bf_mat_char_val
and d.lsty_size = b.sty_size
and d.sty_id = c.sty_code
and decode(a.size_num, 1, e.size1,2, e.size2,3, e.size3,4, e.size4,
5, e.size5,6, e.size6,7, e.size7,8, e.size8,9, e.size9,10, e.size10,
11, e.size11,12, e.size12 ) = d.wsty_size
and b.loc_num in (select loc_num from pro.loc_defs where ploc_type in ('RSC', 'RSO'))
group by sty_barcode,loc_num


Working Query

'select
sum(coalesce (decode(a.size_num,1,b.qty1,2,b.qty2,3, b.qty3,4, b.qty4,5,b.qty5,
6,b.qty6,7,b.qty7,8,b.qty8,9,b.qty9,10,b.qty10,11,b.qty11,12,b.qty12),0) ) qty,
max(coalesce(d.sty_barcode,'<BLANK>' )) sty_barcode,
Max(coalesce(b.loc_num,0)) Loc_num
from pro.twelve_sizes a, pro.gar_stk b, pro.sty_defs c,
pro.sty_barcodes d, pro.val_sty_types e
where b.season = c.season
and b.sty_num = c.sty_num
and b.sty_qual = c.sty_qual
and e.sty_type = c.sty_type
and d.bf_mat_char_val = b.bf_mat_char_val
and d.lsty_size = b.sty_size
and d.sty_id = c.sty_code
and decode(a.size_num, 1, e.size1,2, e.size2,3, e.size3,4, e.size4,
5, e.size5,6, e.size6,7, e.size7,8, e.size8,9, e.size9,10, e.size10,
11, e.size11,12, e.size12 ) = d.wsty_size
and b.loc_num in (select loc_num from pro.loc_defs where ploc_type in ('RSC', 'RSO'))
group by sty_barcode,loc_num'


Hope this helps somebody as it did give me a few grey hairs

[mdaems]

Hi Lorenz,

I propose we treat OCI_SUCCESS_WITH_INFO like this : we don't raise an error but log the error message to a ZSqlMonitor if there is one.
I'll add a patch. Can you confirm this works for your bad query(I just compiled but I'm not using oracle)?

Mark

[Lorenz]

I can will certainly give it a try in 2/3 days as I currently have a massive deadline looming.

By the way Thanks
for all the Zeos DB work they are a great set of components .

[hilam]

works fine for me!!!!

thank you, very very much.

[mdaems]

Finally a real confirmation...

Just running the test suit now to see if this old patch still compiles/works.

SVN Rev. 811

Mark

[josimarz]

Hello mdaems!

I applied the changes in ZDbcOracleUtils unit. But, my SQL Query don't return results. After, I make the following change:

Code: Select all

procedure CheckOracleError(PlainDriver: IZOraclePlainDriver;
  ErrorHandle: POCIError; Status: Integer; LogCategory: TZLoggingCategory;
  LogMessage: string);
{...}
   case Status of
    OCI_SUCCESS:
      Exit;
    OCI_SUCCESS_WITH_INFO:
      begin
        PlainDriver.ErrorGet(ErrorHandle, 1, nil, ErrorCode, ErrorBuffer, 255,
          OCI_HTYPE_ERROR);
        ErrorMessage := StrPas(ErrorBuffer);
      end;
   {...}
   if (ErrorCode <> OCI_SUCCESS) and (ErrorMessage <> '') then
   {...}

The following error was returned:

ORA-24347: Warning of a NULL column in an aggregate function
(http://ora-24347.ora-code.com/)

But, need to change the ZDbcOracleResultSet unit in the TZOracleResultSet.Next method:

Code: Select all

{...}
if Status in [OCI_SUCCESS, OCI_SUCCESS_WITH_INFO] then // change
  begin
    RowNo := RowNo + 1;
    if LastRowNo < RowNo then
      LastRowNo := RowNo;
    Result := True;
  end
  else
  begin
    if RowNo <= LastRowNo then
      RowNo := LastRowNo + 1;
    Result := False;
  end;

I'm using version 6.6.6 stable.

Josimar

[mdaems]

SVN rev 862.

Mark

[mdaems]

josimarz,

Can you please test this change :

Code: Select all

Index: D:/Data/Delphi/ZEOSTesting/src/dbc/ZDbcOracleUtils.pas
===================================================================
--- D:/Data/Delphi/ZEOSTesting/src/dbc/ZDbcOracleUtils.pas	(revision 873)
+++ D:/Data/Delphi/ZEOSTesting/src/dbc/ZDbcOracleUtils.pas	(working copy)
@@ -645,7 +645,11 @@
     OCI_SUCCESS:
       Exit;
     OCI_SUCCESS_WITH_INFO:
-      ErrorMessage := 'OCI_SUCCESS_WITH_INFO';
+      begin
+        PlainDriver.ErrorGet(ErrorHandle, 1, nil, ErrorCode, ErrorBuffer, 255,
+          OCI_HTYPE_ERROR);
+        ErrorMessage := 'OCI_SUCCESS_WITH_INFO: ' + StrPas(ErrorBuffer);
+      end;
     OCI_NEED_DATA:
       ErrorMessage := 'OCI_NEED_DATA';
     OCI_NO_DATA:
@@ -654,7 +658,7 @@
       begin
         PlainDriver.ErrorGet(ErrorHandle, 1, nil, ErrorCode, ErrorBuffer, 255,
           OCI_HTYPE_ERROR);
-        ErrorMessage := StrPas(ErrorBuffer);
+        ErrorMessage := 'OCI_ERROR: ' + StrPas(ErrorBuffer);
       end;
     OCI_INVALID_HANDLE:
       ErrorMessage := 'OCI_INVALID_HANDLE';
@@ -664,15 +668,16 @@
       ErrorMessage := 'OCI_CONTINUE';
   end;
 
-  if (ErrorCode <> OCI_SUCCESS) and (ErrorCode <> OCI_SUCCESS_WITH_INFO) and (ErrorMessage <> '') then
+  if (Status <> OCI_SUCCESS) and (Status <> OCI_SUCCESS_WITH_INFO) and (ErrorMessage <> '') then
   begin
     DriverManager.LogError(LogCategory, PlainDriver.GetProtocol, LogMessage,
       ErrorCode, ErrorMessage);
     raise EZSQLException.CreateWithCode(ErrorCode,
       Format(SSQLError1, [ErrorMessage]));
   end;
-  if (ErrorCode = OCI_SUCCESS_WITH_INFO) and (ErrorMessage <> '') then
+  if (Status = OCI_SUCCESS_WITH_INFO) and (ErrorMessage <> '') then
   begin
     DriverManager.LogMessage(LogCategory, PlainDriver.GetProtocol, ErrorMessage);
   end;
 end;

The previous version only worked by luck, I'm afraid. As the references to Errorcode can also refer to refer to the result of errorget() after an OCI_ERROR situation occurs.
I didn't notice this in the Testing branch runs of the test suite, but now I tried to merge to 6.6-patches and some weird errors popped up. (a unique key violation which was handled as an OCI_SUCCES_WITH_INFO)
Can you please confirm, so I can commit and merge the right patch?

Mark

[josimarz]

Hello mdaems,

As I was on vacation could not answer it before.
Performed tests with SQL that were presenting the message OCI_SUCCESS_WITH_INFO and run successfully!

I will continue making tests and any news will let you know. The principle is fixed.

Tank you!

Josimar