Using stored procedure with Dynamic SQL in AMDP Procedure

[#1]

I have a requirement where I have to use table name dynamically in my AMDP procedure. As I googled countless threads I found out that the only way is to use EXEC statement combined with dynamic SQL. Thus, I created the following procedure:

But, when trying to use the procedure in AMDP Method I get the following error:

explain plan and call statement are not allowed

What's funny is that yesterday I didn't have this error, I was able to execute AMDP and eventually I got an error because my user does not have execution rights on _SYS_BIC scheema. Today I added "systemid" parameter and all of a sudden I can't use it in read-only procedure.

I would really appreciate any help because I've pretty much hit the dead end here. At the end, is there any chance to use dynamic table name in the combination of AMDP and Store Procedures?

Thanks

[pfefferf]

Can you show what options you have used in for the AMDP Method (METHOD xxx BY DATABASE PROCEDURE ... ).

Regards,

Florian

[Former Member]

Hi Florian,

READ-ONLY is used. But, this is due to the fact that the class and method is generated automatically by BW Sistem. AMDP is used as transformation logic between two DSOs. If I try to remove READ-ONLY option transformation gets deactivated and when I active it, logic inside method gets deleted.

Regards,

Mijodrag

[pfefferf]

And that is the restriction. AMDPs used in transformations are always read-only. So you cannot execute that EXEC/Insert logic with that and there is also no option to overwrite that in a consistent way. Why do you not use an old-school ABAP routine for your case. Your AMDP coding above shows nothing what brings a benefit compared to ABAP, cause it is a simple select statement w/o aggregation etc.

Regards,

Florian

[Former Member]

Yeah, that is the thing that we are trying to circumvent, using old school ABAP. We already have a solution in ABAP but it just takes to long.

Idea is to use AMDP to validate source data (particular fields) against Master Data Tables, Customizing Tables, Derivation Tables etc. identifying invalid values by NULL fields after the JOIN is applied. This works great, execution time is under 10 sec for approx 1m records.But, we hit the problem in the scenario where the derivation rules are stored in the tables which are created dynamically so their name is not consistent across the landscape.

It just crossed my mind, maybe we could use ABAP Program to extract derivation rules from dynamic tables, store them in the fixed Z table and use that table later in AMDP.

Regards,

Mijodrag