Is it possible to access a Z custom table using ORACLE native SQL?

[Former Member]

We have created a Z custom table in our SAP system and we would like to exchange data (read from and write to it) with an external table. Our SAP system is running on an ORACLE database, and the external table is also an ORACLE database table. As it is a custom table, and not a SAP standard table, is it possible to access it using ORACLE native SQL interface? Could it cause any problem to our SAP system?

Thank you for your help,

Regards,

Jose

[matt]

Yes it's possible. But, IIRC, it can muck up database statistics - especially the writes. There's also security concerns.

I'd do it the other way round. Access your other Oracle db from ABAP using dbcon.

[Former Member]

Thank you Matt. I understand the concerns about security and statistics, but we wanted to do it this way because, in most cases, we will have to upload data from the external table to the SAP table, and it was easier to do it the other way because we know when the table has changed, and we can launch a job that updates the table in SAP. If we do it from SAP, as we do not know when the table has changed, I guess we will have to prepare an external process that detects this change and launches a background job in SAP to upload the data, which appears to be a more complex solution. Am I right?. Additionally, we have not used dbcon. Do you know of any link about it that we can look at?

Thanks again,

Jose

[Former Member]

Where does the external table reside? Is it in your SAP database instance, or in another Oracle instance?

If it is in another instance, you could use Native SQL with what's called a [DATABASE LINK|http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/ds_concepts002.htm]

Or, you could use dbcon, as the other poster suggested.

Statistics will be managed by Oracle in the external table as normal.

[Former Member]

Hi

Just as the guys said: U CAN.

When a dictionary table is activated (standard or custom table is the same), the system generates the table in the database layer.

The SAP database is a "common" database, in your case is a ORACLE one: u can link two Oracle database by a DBlink.

DBcon is a SAP procedure to link SAP database to external one: but it's tool for ABAP program.

If I understand your problem, u need to read the SAP table by an external database: if this other database is also Oracle, u can create a DBLINK.

This is a normal procedure for Oracle database.

Max

[Former Member]

If you have two different Oracle databases, create a database link. Then you can use for example PL/SQL code in the other database to read and write the Z table in the Oracle database of your SAP instance.

When writing data to the Z table you must follow rules for allowed values. For example, never write a NULL value to a column. Use one space for character fields instead. And remember leading zeroes for NUMC fields.

[Former Member]

Thank you to all of you for your information. We will take a look at both, the dbcon and the database link options. The database appears to be closer to what we wanted to do, but we will have to consider the security issues, as it can give you access to all the tables in SAP database. And to prevent it I imagine we would have to modifiy SAP database authorizations, which is something that does not appear to be a good idea. Do you know if it is possible to do it in a secure way?

Jose

[Former Member]

Hi

U should ask to your basis. I don't know if it's possible to define a DBlink for a certain number of tables or for whole database only.

Anyway it could solve by your program: i.e. u can allowa to change only certain table: this solution should be valid for a SAP program and for a program out of SAP.

Max

[Former Member]

In the database link method you can achieve good security as follows:

- Create a new Oracle user in the SAP database

- Grant select and insert (and maybe delete and update?) to that user on the Z table only

- When creating the db link in the other Oracle database, use the new user

As the result only the one table can be seen or accessed. Just three Oracle-level SQL commands!

[jayanthi_jayaraman]

Hi,

This is an example to insert data in oracle table.

exec sql.

insert into stu_det values ('R1000', 'Ganesh', 500)

endexec.

This is an example of selecting data from oracle table.

exec sql.

CREATE or replace PROCEDURE PROC2 (p_regno in char, v_total out char)

IS

BEGIN

select total into v_total from stu_det

where regno = p_regno;

END;

endexec.

• This is the code to execute second Procedure.

EXEC SQL.

EXECUTE PROCEDURE PROC2 ( in :p_regno, out :v_total )

ENDEXEC.

write : / 'Total of ', p_regno, ' is ', v_total.