FireDAC “table or view does not exist” when insert into ORACLE TABLE Delphi Belin 10.1 upd 2

Question

We are migrating our codebase from Delphi XE3 with FireDAC 8.0.5 to Delphi Berlin 10.1 Upd 2 with FireDAC 15.0.1 (Build 86746). Everything is working smoothly using MS Sql Server, but using ORACLE it has been another history.

Throughout the application source code we use lots of TAdQuery with sql instructions like

AdQuery1.Sql.Text := 'SELECT FIELD1, FIELD2 FROM TABLE1';

In order to insert a record, we use Append or Insert methods, like this

AdQuery1.Insert; //or AdQuery1.Append;

Just after invoking its Post method, the component internally creates an INSERT sql statement, that goes like this

INSERT INTO TABLE1 (FIELD1, FIELD2) VALUES(:FIELD1, :FIELD2)

So the record gets inserted successfully.

Now, using TFdQuery in Delphi Berlin, the component internally creates an INSERT sql statement, like this

INSERT INTO USERNAME.TABLE1 (FIELD1, FIELD2) VALUES(:FIELD1, :FIELD2)

Failing with a [FireDAC][Phys][Ora] ORA-00942: table or view does not exist

This happens because in our Oracle database, TABLE1 is created in a schema called MAIN_SCHEMA, and we access it by using a public synonym.

Trying to find a workaround, we compared FireDAC source code, finding that

in Delphi XE3, the unit uADDAptManager.pas, on its function TADDAptTableAdapter.GetUpdateRowCommand, calls oConn.CreateCommandGenerator(oCmdGen, nil);

in Delphi Berlin, the unit FireDAC.DApt.pas, on its function TFDDAptTableAdapter.GetUpdateRowCommand calls oConn.CreateCommandGenerator(oCmdGen, GetSelectCommand);

Whenever that second parameter (called ACommand: IFDPhysCommand) is not nil, the name of the table is returned concatenating the user name (in a function called TFDPhysCommandGenerator.GetFrom).

If we add 'MetaCurSchema=MAIN_SCHEMA' to the TFdConnection params, it works with the applications that not use a pooled connection, but We have several process that use a pooled connection with the same params, even MetaCurSchema param, but it doesn't work

What can we do?

thanks for your help

TOPX TOPX · Accepted Answer · 2019-10-02T22:02:14

What I understand is that you would do better making the connection avoid the use of any schema name, rather than specifying it. Also, keeping in mind that you already use public synonyms.

So, according to the documentation:

Full object names

FireDAC supports full object names, which include the catalog and/or schema names.

When a short object name is specified to StoredProcName, TableName, etc, they will be expanded into the full object names, using the current catalog and/or schema names. To override or avoid usage of the current catalog and/or schema names, use the MetaCurCatalog and MetaCurSchema connection definition parameters. For example:
[Oracle_Demo]
 DriverID=Ora  
 ...
 MetaCurCatalog=*
 MetaCurSchema=*

~ Source: Object Names (FireDAC) - docWiki

MetaCurSchema

Specifies the current schema for the application. If not specified, then its value will be received from the DBMS. When an application is asking for metadata and do not specify a schema name, then FireDAC will implicitly use the current schema.
If MetaCurSchema is '*', then schema names will be me omitted from the metadata parameters.

~ Source: Common Connection Parameters (FireDAC) - docWiki

That asterisk (*) should do the trick, let us know if that's the case.

FireDAC “table or view does not exist” when insert into ORACLE TABLE Delphi Belin 10.1 upd 2

1 Answers