Devart Forums

Posted: **Mon 16 Aug 2010 10:47**

Hi,
I'am upgrading a Delphi 2006 application where I was using version 4.20.4 of dbExpress driver to RAD 2007 with version 4.20.11.

Delphi raises an error 'ORA-06550 identifier 'SEC_PKG.SETPROP' must be declared' when calling ExecProc method (same code is working in Delphi 2006).

Here is the code:

procedure ExecSomething (strConnection: String);
var StoreProcedure: TSQLStoredProc;
begin
StoreProcedure := TSQLStoredProc.Create(nil);
with StoreProcedure do
try
SqlConnection := GetConnection (strConnection);
SChemaName := 'SEC';
PackageName := 'SEC_PKG';
StoredProcName := 'SETPROP';
........
ParambyName('value').AsString := 'test';
ExecProc; <--- Exception raises here
........

Thanks.

Posted: **Mon 16 Aug 2010 11:30**

Tried with version 4.50.21 of dbExpress driver, same result.

Posted: **Mon 16 Aug 2010 14:43**

Hi,
While waiting for an answer from devart, you could use this workaround before calling the ExecProc method:

SQLConnection.ExecuteDirect( 'ALTER SESSION SET CURRENT_SCHEMA = schema_name' );

Posted: **Tue 17 Aug 2010 14:54**

hello,

In Delphi 2007 dbExpress was completely changed, thats why its behavior may be other than in Delphi 2006.

To execute a stored procedure under another user, you can create a synonym to your procedure and use it.

Also you can save SqlExpr.pas file in your work directory, and correct TSQLStoredProc.PrepareStatement procedure.

Replace

Code: Select all

if FSchemaName  '' then
SQLText := QuoteIdentifier(FSchemaName + '.' + FNativeCommand, true)
else
SQLText := QuoteIdentifier(FNativeCommand, true);
if FPackageName  '' then
SQLText := QuoteIdentifier(FPackageName + '.' + FNativeCommand, true);

with

Code: Select all

if FPackageName  '' then
SQLText := QuoteIdentifier(FPackageName + '.' + FNativeCommand, true)
else
SQLText := QuoteIdentifier(FNativeCommand, true);

if FSchemaName  '' then
SQLText := QuoteIdentifier(FSchemaName + '.' + SQLText, true);

and add the file to your project.

Posted: **Tue 17 Aug 2010 15:04**

Hi,
In my opinion, creating synonyms for every stored procedure isn't an alternative.

By the way, modify source code is possible but, what's wrong with 'setting SchemaName property that was working fine in Delphi 2006? It's a dbExpress bug? It's a Delphi bug?

Thanks again.

Posted: **Wed 18 Aug 2010 10:40**

hello,

This looks like Delphi bug. You can see it in the TSQLStoredProc.PrepareStatement procedure in the SqlExpr.pas file. If you set schema name and package name, the schema name will be lost.

Posted: **Wed 18 Aug 2010 10:54**

Hi,
Yes, Embarcadero's site confirms the problem http://qc.embarcadero.com/wc/qcmain.aspx?d=66366, and it's resolved in Delphi 2010 (it seems they aren't going to release a update pack for Delphi 2007).

The alternative you propose is better than Embarcadero's one.
Thanks for your help, AlexP.