ORA-06550 identifier 'SEC_PKG.SETPROP' must be declared

[jsaumell]

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.

[jsaumell]

Tried with version 4.50.21 of dbExpress driver, same result.

[jsaumell]

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' );

[AlexP]

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.

[jsaumell]

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.

[AlexP]

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.

[jsaumell]

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.