I am trying to write a stored procedure but it is failing due to the length of the query. I've simplified it below:
Create Or Replace Procedure MyProcedure(
vInClause in varchar,
vsWhere in varchar,
vsOrderBy in varchar,
aData out REFCURSOR
)
As
vstr varchar(4000);
Begin
If vsWhere='' then
Select 'Select From Table1 Where Field1 In (' || vInClause || ') || vsOrderBy Into vstr From Dual;
Open aData For vstr;
ElsIf vInClause<>'' then
Select 'Select * From Table1 Where Field1 In (' || vInClause || ') And Field1 In(' || vsWhere || ') ' || vsOrderBy Into vstr From Dual;
Open aData For vstr;
End if;
End;
/
Essentially the vInClause represents the ID of the rows that should be selected. If nothing is specified if the vsWhere then the procedure just selects everything from the table. If the vsWhere is specified then it acts as a filter and restricts the results. The reason I use two parameters is that the first represents everything the user should be able to retrieve and the second is only those rows the user has specified to include but even it I change the ElsIf to drop the "In (' || vInClause || ')" part it will not succeed is the overall vstr variable exceeds 4000 characters. I am running into a specific situation where the resulting vstr is 5,274 characters. I tried declaring the vstr as a Long and a Clob but still have had no success. I have to support Oracle 9 and higher and I would appreciate any help anyone would be able to offer.
As additional info the vInClause is determined in the program I have that basically determines what rows from the primary table that the user should be able to see based on their application level permissions. The vsWhere is constructed in the application based on whatever filtering the user chooses to do on the dataset. There is always a default vsOrderBy sent to the procedure even if the user has not selected a column to order by. Each of the vInClause and vsWhere can easily be more than 2,000 characters.