Dynamic sql and sorting columns

[Chrisdaems]

Hi,

I have some problems with sorting columns of a DBGrid in Delphi. I have a Dataset that executes a stored procedure on the Oracle server. The stored procedure has a out parameter with datatype Ref Cursor. The order by in the SQL is filled with a parameter. But I know that this is the problem. What I can do is using substition variables but then I can't create my select statement on the server. I also tried to create a WEAK REF CURSOR, in that case I write a concatenated SQL. But DOA has problems with this WEAK REF CURSORs.

Here are some examples about the case I am trying.

ORACLE
Type empRec IS RECORD
name emp.Name%Type,
loc emp.loc%type
end;
-- strong ref cursor
type rc_emp is ref cursor return emprec;

Procedure Select_Record(p_cursor IN OUT rc_emp,
p_Order IN p_Order) is
begin
open p_Cursor for Select name, loc from emp order by p_Order;
end;

Delphi
Setvariable('p_Order', 'name');

This doesn't work. Orcale can handle dynamic SQL like this.

If I Do it like this :
type c_emp is ref cursor; -- weak ref cursor

Procedure Select_Record(p_cursor IN OUT rc_emp,
p_Order IN p_Order) is
begin
open p_Cursor for 'Select name, loc from emp order by ' || p_Order;
end;

Then it is possible on the oracle site but I believe that DOA Has problems with ref cursors.

What I can do is using substitution on the doa site but I like to keep all DB actions on the oracle server and try to work only with stored procedures.

Is there anyone who can give me a tip to sort records in a DBGrid filled by a dataset that executes a stored procedure, without using another component?

Another question is also, HOW can I Use dynamic sql??

thanks chris

 

[Marco Kalter]

There shouldn't be a problem. Look at the PkgApply demo that comes with the Direct Oracle Access package. It also uses a dataset based on a cursor from a packaged function that takes the sort order as a parameter.

 

[Chrisdaems]

Hi Marco,

Package PkgApply isn't so dynamic. I tried to do it like this but the SQL isn't dynamic at all. For every field they used a new query.

I don't think that ORCALE can handle Dynamic SQL in a STRONG ref cursor. It is possible in a WEAK ref cursor but then I have problems with DOA.
Isn't there a way to use DYNAMIC SQL. SO I mean a SQL that handle a parameter that provides the tablename or columnname.

In Delphi I can create a concatenated string (which is the sql). When I completed the string i can pass it to ORACLE. But i like to keep all sql on the ORACLE Server.

Chris

 

[Marco Kalter]

The PkgApply demo does indeed use a strong typed cursor. However, if I try the same with a weak untyped cursor, it works fine as well:

type RefCursor is ref cursor;

procedure OpenAnyCursor(p_Cursor in out RefCursor,
                        p_Statement in varchar2) is
begin
  open p_Cursor for p_Statement;
end;

In Delphi I create a dataset with the following SQL:

begin
  mypackage.openanycursor(p_cursor => :p_cursor,
                          p_statement => :p_statement);
end;

No problem. What exactly goes wrong in you case?