Oracle dynamic sql OPEN-FOR won't take SQL string in form of a string variable

[THY02K]

hello

I'm using "OPEN-FOR", not "EXECUTE IMMEDIATE" because I want to do multi rows query. However, "OPEN-FOR" won't take a variable "strSQL" in its FOR clause, it only takes fixed strings?


create or replace function fnEnumSystemUser
(
LogonFilter nvarchar2,
...
AdditionalWhereClause nvarchar2,
OrderByClause nvarchar2
)
RETURN xxxxx.cursorType
AS
strSQL nvarchar2(2000);

FormatAdditionalWhereClause nvarchar2(2000);
FormatOrderByClause nvarchar2(2000);

SystemUserCursor xxxxx.cursorType;
BEGIN

IF AdditionalWhereClause IS NULL THEN
FormatAdditionalWhereClause := ' ';
ELSE
FormatAdditionalWhereClause := TRIM(AdditionalWhereClause);
END IF;

IF OrderByClause IS NULL THEN
FormatOrderByClause := ' ';
ELSE
FormatOrderByClause := TRIM(OrderByClause);
END IF;

strSQL := 'select Id,FirstName,MiddleName,LastName,PrimaryEmail,PersonType,CreateDate,CreatedBy,LastUpdate,LastUpdateBy,Logon,PasswdHash,IsSuspended,ExpiryDate from SystemUser left join Person on SystemUser.PersonId = Person.Id ';
-- This won't work, simply says "Warning: compiled but with compilation errors" when I tried to create function
open SystemUserCursor FOR strSQL;

-- This won't either, on invoke "ORA-01006: bind variable does not exist"
open SystemUserCursor FOR
'select Id,FirstName,MiddleName,LastName,PrimaryEmail,PersonType,CreateDate,CreatedBy,LastUpdate,LastUpdateBy,Logon,PasswdHash,IsSuspended,ExpiryDate from SystemUser left join Person on SystemUser.PersonId = Person.Id WHERE SystemUser.Logon like :x :y ' using LogonFilter, OrderByClause ;

-- This also failed (on invocation, "ORA-00900: invalid SQL statement")
open SystemUserCursor FOR
'select Id,FirstName,MiddleName,LastName,PrimaryEmail,PersonType,CreateDate,CreatedBy,LastUpdate,LastUpdateBy,Logon,PasswdHash,IsSuspended,ExpiryDate from SystemUser left join Person on SystemUser.PersonId = Person.Id WHERE SystemUser.Logon like :x' || OrderByClause using LogonFilter;

RETURN SystemUserCursor ;
END;


This is how I invoked it:
declare
NumItemsSelected int;
lstResult xxxxx.cursorType;

TYPE SystemUserRecordType IS RECORD
(
Id numeric(19,0),
FirstName nvarchar2 (50),
MiddleName nvarchar2 (50),
LastName nvarchar2 (50),
PrimaryEmail nvarchar2 (190),
PersonType int,
CreateDate timestamp,
CreatedBy numeric(19,0),
LastUpdate timestamp,
LastUpdateBy numeric(19,0),

Logon nvarchar2 (75),
PasswdHash int,
IsSuspended char,
ExpiryDate timestamp
);
oUser SystemUserRecordType;
begin

lstResult := fnEnumSystemUser (... '%',... ' Person.PrimaryEmail like ''%a%'', ' order by Logon ASC',0,10,NumItemsSelected);
LOOP
fetch lstResult into oUser;
exit when lstResult%notfound;
dbms_output.put_line('Id: ' || oUser.Id);
END LOOP;
end;



I need ability to append to SQL string because in the end say the least I need to append:
1. ORDER BY clause
2. Call a FUNCTION in WHERE Clause
3. Paging and ROW_NUMBER() clause

What can I do? Thanks!