Hi All,
Generally, We collect Transparent tables using SAP Data services or BOA RFC as it is not possible to collect them using Cranport package (DB Connection).
But When I add Transparent table into Collect with type as Cranport Package I am able to Build the Package. Can Anyone Please let me know Why we are able to bulid the Package Sucessfully?
Extraction Issue:
Regards,
Vamshi
Hi Vamshi,
Since you mentioned SAP/RFC package types above, I presume the {SAP Settings} tab is filled out on the vertical of the Collect Target Source record. This means the ODBCORACLE connection type's [SAP Download SQL] query will be used during the package build. This query would run against Data Dictionary tables instead of SYS tables. That could explain why the build works if the Oracle user has permissions for the DD tables but not for TVSWZ.
Within my instance of dspCollect, I was able to extract this table without an issue.
To potentially get further details as to why this is failing, I would suggest looking at the associated Assembly package.
Collect Package -> Vertical -> Advanced Settings -> Click on Assembly Package name.
Once here, here are the things that I look at to troubleshoot an Assembly Package.
Let me know if this helps.
Hi Joseph,
Thank you for your response.
I see Query is fine in General Tab. But I got Error that "ORA-00942: table or view does not exist". If Table Don't exists in Source why Bulid Package is Sucessfull.
Regards,
Vamshi
Is it possible that the oracle user used in the data source does not have access to the table that you're trying extract?
The query used to build the package would use one of the meta data tables to get the columns etc, hence the package may build, but the execution may fail.
@...,
Good point!
Do a test connection and see if there are errors there.
Hi Andrew,
Thanks for your reponse.
If Oracle User don't have access we should get error while Building the package. But here we are getting error while extracting the data.
Regards,
Vamshi
The query used to build the package would use one of the meta data tables (e.g. table ALL_TAB_COLS in schema SYS) to get the columns etc, hence the package may build, but the execution may fail.
Maybe the user has not been given permission the schema that the table exists in.
Other than user permissions, another common cause of that Oracle error is just having the [Schema Owner] set incorrectly on the Target Source record in Collect. RFC package types don't use the [Schema Owner], so it may have never been set to a correct value for the Oracle database.
Ben Bauer - i assume that also means that the case of the schema is also important for Oracle i.e SAPSR3 versus sapsr3
Yes, case does matter. But looking at the image in the original post here, I regret my note a bit. I see the query does show what appears to be the proper schema.
@Vamshi - Do you have an Oracle query tool that can show the TVSWZ object existing in the SAPSR3 Oracle schema? If yes, then I agree with Andy that the Oracle user likely has permissions to SYS objects that let it build the package but it doesn't have permissions to TVSWZ to extract the data rows.
Hi Ben,
I Checked in Oracle Query Database and I see it don't exist in ALL_TAB_COLS table. Please find the below screenshot for the same.
Regards,
Vamshi
Hi Vamshi,
I think that can also happen if your Oracle User lacks permissions for the TVSWZ table. You will need to access that query tool with an admin-level Oracle User to check for the TVSWZ table and its schema. Perhaps there is an Oracle DBA who can assist?
Hi Ben,
Thank you for your Response.
I have Connected to Source system using the Technical User ID credentials (which we are using it to extract the data).
Correct me If I am wrong.
select * from sys.ALL_TABLES
select * from sys.ALL_VIEWS
Irrespective to the User Access, Will the Above Queries gives me the list of tables and views which are present in Source Schema?
I will also check with Oracle DBA.
Regards,
Vamshi
Hi Vamshi,
I would recommend relying on the Oracle DBA's advice. I'm no expert on Oracle user permissions, but I have certainly seen cases where the results of querying SYS objects were impacted by Oracle user permissions.
I can confirm - you will only see tables in ALL_TABLES if you have access to them. If you don't have access to a table, you won't see it in ALL_TABLES in Oracle.
Hi PSA Support,
Thanks for Confirming.
But If Technical User (Using for Data Extraction from Source) Don't have Access to TVSWZ.
So ideally below query Should return no data.
select * from sys.ALL_TABLES where table_name =N'TVSWZ'
But May I know why Build Package is successful and It is failing while Extracting the data?
Regards,
Vamshi
Please sign in to leave a comment.
Comments
16 comments