Varad
As everyone knows, SAP developed the ABAP Managed Database Procedure (AMDP) to create SQL script-based programs known as Database Procedures.By employing AMDP techniques, it has become easier to access data from various database schema using SQL script since we no longer require a database user to program the database operations.
The following is the syntax to retrieve data from an underlying database schema:
FROM “” SELECT *.”” WHERE -> that you’ll include in the AMDP method implementation.
Please take note of the precise distinctions between the Open SQL and SQL script syntax. To accurately identify the underlying way to access the necessary data of that , we must mention the Physical Schema Name.In the event that the physical schema is omitted, the default schema is selected automatically. You can use the function module DB_DBSCHEMA_CURRENT to find the default schema.
You would now be asked the following queries:
Why are we discussing several database schemas?
What would be wrong if I simply retrieved the data using CDS or Open SQL directly?
Since I have written AMDP selection without using a physical schema name, what are you referring to?
Why and when would I need to use such DB procedures to pull data as an ABAP programmer?
According to what I’ve learned, not every table in the underlying database schemas has a dictionary view associated with it. Because of this, not all of them are visible in SE11 or SE16. However, these tables may still exist and contain essential business master and transaction data. Any SAP or non-SAP system could be the source of these data, and by employing the SAP LT replication technique,
The basis person informed you that this table is physically located in a schema named DEV_SCHEMA, but that the names of the schemas in production and quality would be PROD_SCHEMA and QUAL_SCHEMA, respectively (different schema names in various systems is the standard procedure, nothing new).
Using the syntax mentioned above, you would now write the AMDP code below: –
SELECT * FROM “DEV_SCHEMA”.”ZCCARD_DETAILS” WHERE customer_name = ‘SAPYard’.This will function flawlessly in development, but it will collapse in quality as there isn’t a physical schema named “DEV_SCHEMA.” The physical schema for quality is QUAL_SCHEMA.
The Schema Mapping Concept, which is once more an underlying database table in the physical schema “_SYS_BI,” was created to address this issue. It contains the alias for every physical schema. All systems have the same alias, but the physical schema names that are associated to it vary.
Thus, the schema mapping entries in the development system might resemble this: –
| ALIAS ( called as AUTHORING SCHEMA or logical name) | Physical Schema |
| ZS4_ALIAS | DEV_SCHEMA |
| ALIAS ( called as AUTHORING SCHEMA ) | Physical Schema |
| ZS4_ALIAS | QUAL_SCHEMA |
Thankfully, all you need to do now is refer to the Alias name in the AMDP select query as seen below: –
Obtain the physical schema using the alias -> Choose the query first
In the second select query, obtain the card details by correctly referencing the physical schema name that you obtained in step 1.
But is there a syntax specific to SQL scripts?Which allows for the transmission of the schema name in such a dynamic manner?Well, not that I’m aware of.
Are we therefore stuck? Now what are our options?
This can be answered by using AMDP’s standard provided macro, $ABAP. Schema.
The alias is automatically transformed into the physical schema name by this macro, which then inserts it into the SELECT query directly. The way it is written is:
- FROM “$ABAP.Schema( ZS4 )” is selected.Where customer_name = “SAPYard” in “ZCCARD_DETAILS”.
Fantastic! With this technique, giving a dynamic physical name is no longer an issue because you only need to pass the logical name—the macro will take care of the rest. You also avoid writing two select queries.
Please also review my other article, Code.
Is that all there is to it? Well, no!
Before we can use this syntax, there are a few things we need to do.
Let me start by stating that, although I did not use the alias name ZS4_ALIAS that I displayed to you, I did use the logical name ZS4. What is this ZS4 now? From where is this coming?
Now let’s get started:
The logical database schema, or ZS4, can be produced with Eclipse ADT. Click NEW item after opening the project. Go to Others -> Explain the definition of a logical schema. After completing the wizard, turn it on.
Select the logical database schema under other.
You have successfully mapped the underlying physical schema name in the transaction DB_SCHEMA_MAP, according to this screen. Before proceeding with the transaction, please activate the logical schema; else, it won’t be seen there.
You can observe that the entry with the logical name ZS4 has occurred in the transaction DB_SCHEMA_MAP.
Select the record, select EDIT, provide the name of the physical schema, and select SAVE. It is also an option to transfer this logical name to other systems.
It is important to keep in mind that even while the logical schema name can be moved through transport, the physical schema name attachment in transaction DB_SCHEMA_MAP needs to be completed explicitly in the target system. This turns into a cutover task.
