Dynamic SQL execution with in Read only procedure

[Former Member]

Hi,

I have a scenario where i need to execute the SELECT query (Which will be generated on the fly) with in a read only procedure WITH RESULT VIEW and then return the result as out parameter.

I tried the below but it did not work.  Do we have any other approaches for this ?

create procedure PROC_TEST_DLS(out wrapOut CONTRACT_TYPE)

  LANGUAGE SQLSCRIPT

  READS SQL DATA WITH RESULT VIEW CONTRACT_SECURE AS

  VAR_DLS_QUERY  NVARCHAR(5000) DEFAULT '(1=1)';

  v_user_name NVARCHAR(500);

BEGIN

  CALL getapplicationcontext('USER_NAME',v_user_name);

  SELECT P_QUERY into VAR_DLS_QUERY  FROM getDLSFilter('CONTRACT',:v_user_name);

  wrapOut = select * from :VAR_DLS_QUERY ;

END;

Error: scalar type is not allowed: line 13 col 27 (at pos 446)

I need to put this in Result view procedure as i need this code to be executed when we just say "select * from CONTRACT_SECURE".

So this is basically applying some restrictions for the user to see only eligible data.

I there any better idea here ?

Thanks

Venkatesh

[SergioG_TX]

i think you may have a few issues here:

1) the select p_quert statement may not know how to resolve the from clause

2) the wrapOut variable must have the same type and column order as the select * statement assigned to it.

if you need to do dynamic query execution,  you can check the apply_filter function .. it takes a table and a where clause as parameters, see here: Using Dynamic Filters in SAP HANA | SCN

also, there is a less performing approach if you use a dynamic query execution such as :

execute immediate '<your select statement as string>'

hope this helps

[Former Member]

Thanks For your quick response Sergio.

Below are the answers.

1. Basically p_query will have complete executable select query.  We just need to return the result by executing this query.

2. wrapOut will have the same type as table structure.

I tried apply_filter function. The major drawback is that it can be applied on only one table. We may have joins to multiple tables as part of the query.

If i use execute immediate (execute immediate 'select * from :VAR_FILTER';)

i get the below error.

feature not supported: Dynamic SQL is not supported in the READ ONLY procedure/function

I am running out of options now..

Thanks

Venkatesh

[SergioG_TX]

try

execute immediate ('select * from ' || :VAR_FILTER);  --- concat the var filter which is your table name right ?

[Former Member]

Tried  execute immediate ('select * from ' || :VAR_FILTER);

same error...

SAP DBTech JDBC: [7]: feature not supported: Dynamic SQL is not supported in the READ ONLY procedure/function: line 13 col 3 (at pos 422)

I think we cannot execute any SQL dynamically in read only proc's.

[SergioG_TX]

you should be able to... i have one example running in sp10 and sp11

execute immediate 'select * from ' || :tbl_name

try this and see if it works:

execute immediate 'select 1 from dummy'

[SergioG_TX]

also make sure you remove the read sql data  piece at the top of your stored proc..

[Former Member]

Correct me if i am wrong.

I thought we can only expose procedure as view only if we put

READS SQL DATA WITH RESULT VIEW CONTRACT_SECURE.

If we remove Reads SQL Data and make it Read/Write  procedure, i can surely use execute immediate 'select * from ' || :tbl_name. But i will not be able to execute this proc as part of select query. For Ex. I would like to execute this proc when I issue this sql command.

select * from CONTRACT_SECURE

So according to my proc declaration, this is the view which is exposed by this PROC.

[SergioG_TX]

you are correct.. however, in order to use dynamic execute immediate, I believe you need to have a read/write proc... you may be able to do more digging on the official documentation.

in my case it wouldnt work until i removed that declaration