Good day.

There is a task: to synchronize data from Active Directory via LDAP. Those. when you click on some button "Synchronization" in a program written in Delphi, update all existing and add new entries to the Oracle database.

As I understand it, for this in Oracle there is an operation MERGE INTO [...]. But in order not to load the database and not force it to parse 1500 queries each time, I would like to do all this using prepared (parametric) queries. I worked a little with this database, so questions immediately arose. I got a query like this (I can't say for sure yet, but it seems like it is):

MERGE INTO testtable USING( SELECT :field1_s field1, :field2_s field2, :field3_s field3, :field4_s field4 FROM dual ) data ON (data.field4 = testtable.field4) WHEN MATCHED THEN UPDATE SET testtable.field1 = data.field1, testtable.field2 = data.field2, testtable.field3 = data.field3 WHEN NOT MATCHED THEN INSERT VALUES(NULL, data.field1, NULL, data.field2, data.field3, NULL, data.field4)

On field4 in the testtable table there is a unique index and this field checks for the existence of this entry in the table.

In Delphi, I use ADO components (AdoConnection, AdoQuery) to work with both the Oracle database and Active Directory. I assign the parameters field1_s , field2_s , etc. to this query, and then set the values ​​for the parameters in AdoQuery.Parameters.ParamByName('field1_s').Value through AdoQuery.Parameters.ParamByName('field1_s').Value . When I call the AdoQuery.ExecSQL procedure, Oracle gives me its error that not all the parameters were filled in, and specifically:

 ORA-01008 not all variables bound

Already that only did not do. All that is achieved is another error: ORA-01036: illegal variable name/number , which of course is no better :)

Now I would also like to note that the same parametric normal INSERT works fine and all data is entered into the table:

 INSERT INTO testtable VALUES(NULL, :field1_s, NULL, :field2_s, :field3_s, NULL, :field4_s)

And therefore it begs the conclusion that the whole thing is in the SQL query. But that's what's with him - I'll never know! Maybe it's all about the SELECT subquery I use for MERGE? For example, the line can be guilty ... FROM dual , which I added only because orakl swore on the absence of the keyword FROM? If so, how can you rewrite this query, so that Oracle inserts data not from the existing table, but new data that my program transmits?

The Oracle version on the server just can not say, but it seems - 11. At least, the client I use is version 11.

Tell me where to dig? Hope explained everything is clear. Thank you in advance.

UPD:

The Oracle version on the server is still 9.2. Parameters in the Delphi Bindu like this:

 q_temp.SQL.Clear; q_temp.Close; q_temp.SQL.Add('MERGE INTO testtable USING ' + '(SELECT :field1_s, :field2_s, :field3_s, :field4_s FROM dual) ' + .... ); q_temp.ParamCheck := true; q_temp.Prepared := true; for i := 0 to 10 do begin q_temp.Parameters.ParamByName('field1_s').Value := ldapQuery.FieldByName('field1').AsString; q_temp.Parameters.ParamByName('field2_s').Value := ldapQuery.FieldByName('field2').AsString; q_temp.Parameters.ParamByName('field3_s').Value := ldapQuery.FieldByName('field3').AsString; q_temp.Parameters.ParamByName('field4_s').Value := outbuf; ldapQuery.Next; end;

I also tried to assign ftString types manually to parameters, but this did not change anything. Now I tried to make an identical non-parametric SQL query - everything works.

UPD2:

So far, in order not to transmit the entire request many times, I made the stored procedure and now call it. But it's all the same - not a solution. I wanted to see the server logs to really see what comes to it, but something did not work out. For example, the Toad utility swears at the absence of the dbms table (I have root rights). I also tried this little thing, but it doesn’t display requests and doesn’t work correctly. Can anyone know any good SQL profiler for Oracle, so that you can not look at the logs on the server (I have no access there), but on the client? For example, for MSSQL, this is SQL Server Profiler .

  • Show the code, how to set the parameters for the request. - Drac5
  • Updated the answer. - Dobby007
  • Does your query to AD accurately return all data for field1-3? Debazh not tried? - Drac5
  • @ Drac5, of course, debazhil. Where do without it :) AD returns everything correctly. Now everything is working without parametric queries. Those. the same cycles, the same SQL, but instead of the parameters I compose and send each time a new query. - Dobby007
  • And maybe calling q_temp.Prepare will help solve the problem? - Drac5

1 answer 1

ParamCheck is enabled before assigning an SQL query. Try it.

  • Thanks for the answer. ParamCheck is my default in labor. I did this just in case. By the way, Prepared also tried to set up an SQL query, it did not help. Right now it so happened that you have to remake the program so that it works directly without the participation of the Oracle client ... I chose the Oracle Data Access Components from DevArt for this purpose. I'll try to do the same with him. Plain SELECT with a parameter in the WHERE clause at least works on it. - Dobby007 4:05 pm
  • I simply when used in SQL query dynamic stored procedures with parameters, there was a problem that parameters appeared empty. ParamCheck by default I had False and I installed it after adding an SQL query. The problem was solved when he put it forward. - Timenzzo
  • By the way, to mine what goes in the block between SELECT and WHERE, in TQuery Delphi cannot be set via parameters. I can be mistaken, I only succeeded in the condition. - Timenzzo
  • Maybe you are right. For the purity of the experiment, it would be necessary, of course, to try to rewrite this code in another language ... As I do something, I will unsubscribe. - Dobby007