topic SQL queries against a database view from an external system? in Application Development and Automation Discussions

SQL queries against a database view from an external system?

Former Member — Fri, 03 Jul 2009 11:29:19 GMT

Hi,

I have a question about Database views in se11.

Is it possible to create a database view and that do SQL queries against this view from an

external system, Not a SAP system?

Please, I need you help.

Best Regards

Annika

Re: SQL queries against a database view from an external system?

vinod_vemuru2 — Fri, 03 Jul 2009 12:43:09 GMT

Hi,

I dont think its possible. Because Views doesn't have relation to data base. When you execute the view it created the inner join on view tables in backend and retrieves the data. But SQL query directly interacts with data base.

Workaround could be create RFC function module. Ask the 3rd party to call SAP FM. In this FM fetch the data from view and send it back to them through tables parameter. But make sure that volume of data is not huge as it may cause performance issues.

Thanks,

Vinod.

Re: SQL queries against a database view from an external system?

Former Member — Mon, 06 Jul 2009 07:14:59 GMT

Hi Annika,

it is possible , yes... but depends on your database systems in the SAP source DB and the external DB

(easier if they are the same, i.e. both ORACLE) - check out with your BASIS team (they have to create something like a "database link" in the external DB system that you can use to access the tables in the SAP source).

In the external DB you sure can create a view on these "remote" tables.

We used this to pull data form SAP DB to another DB system (both ORACLE based).

But this is NOT supported by SAP , so be carefull. Below is the restriction for ORACLE (as well for other DB systems )

see SAP note 581312 "Oracle database: licensing restrictions"



...

As of point 3, it follows that direct access to the Oracle database is
only allowed for tools from the areas of system administration and
monitoring. If other software is used, the following actions, among
other things, are therefore forbidden at database level:
...

* Querying/changing/creating data in the database

* Using ODBC or other SAP external access methods

....

This means that additional application software is only allowed if this
accesses the database through SAP interfaces (for example, RFC, SAP J2EE
or BAPI).

I would say if you KNOW the tables involved (using valid WHERE conditions and joins )

and don't start queries from hell (ad-hoc type) wich can bring down your SAP system performance

you can try it.

But be warned...

good luck...

bye