ORA-00907 missing right parenthesis

[Helmut B]

Using a substitution variable in TOracleDataset SQL with a value of
' ORDER BY 1 ASC' does result in
"ORA-00907 missing right parenthesis ."
when opening the DataSet.
Setting the variable to a single blank
this message does not show up.
The variable name used was :SORT, but error happens also with :SORTIERUNG
Any ideas?

 

[jpickup]

There must be something odd about the SQL in your query. Perhaps you should post it here.

I've just tried a simple project with:

procedure TForm1.Button1Click(Sender: TObject);
begin
OracleDataSet1.Close;
OracleDataSet1.SetVariable(':SORT',Edit1.Text);
OracleDataSet1.Open;
end;

the SQL is
SELECT * FROM some_table :SORT

a substitution of ORDER BY 1 ASC works fine.

 

[Helmut B]

The error does not show up when there is no data aware component (DBGrid, RxDBGrid) being linked to the query (TOracleDataset)!
Also, the error only occurs with "CountAllRecords" being set to True and with DOA 3.4.3.
The DOA 3.4.6 does not show this error!

 

[Helene]

When countAllRecords is true, the dataset will generate a "select count(*) from table" based on the actual SQL in your dataset, in order to get the count number. I suppose this is the SQL that gets messed up by the substitution variable.

If you cannot use the DOA version where this error is fixed, then maybe it would work if you only include the actual sort columns in the substition variable. The only thing you lose doing this is the ability to run the query without sorting at all.
select c1,c2 from table order by :SORT

Another option may be to use the QueryAllRecords instead. In this case all records are fetched immediately, so be careful with doing this if your query returns a very large dataset. Your users may be kept waiting a long time...