Collect - Transparent Table - DB Connection - Build Package successful and Extraction Error

Answered

Comments

16 comments

  • Best answer
    Avatar
    Ben Bauer

    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.

    Comment actions Permalink
  • Avatar
    Joseph Flesche

    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.

    • Click the vertical to look at the SQL and double check it (under the General tab).
    • On the horizontal, click on the log icon (second from the right). Click on the vertical of the log entry to get further details on the error.

    Let me know if this helps.

    0
    Comment actions Permalink
  • Avatar
    Vamshi Krishna Akinapally

    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

    0
    Comment actions Permalink
  • Avatar
    Andrew Lund

    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.

    1
    Comment actions Permalink
  • Avatar
    Joseph Flesche

    Andrew Lund,

    Good point!

    Vamshi Krishna Akinapally,

    Do a test connection and see if there are errors there.

    0
    Comment actions Permalink
  • Avatar
    Vamshi Krishna Akinapally

    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

    0
    Comment actions Permalink
  • Avatar
    Andrew Lund

    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.

    0
    Comment actions Permalink
  • Avatar
    Ben Bauer

    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.

    0
    Comment actions Permalink
  • Avatar
    Andrew Lund

    Ben Bauer - i assume that also means that the case of the schema is also important for Oracle i.e SAPSR3 versus sapsr3

    0
    Comment actions Permalink
  • Avatar
    Ben Bauer

    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.

    0
    Comment actions Permalink
  • Avatar
    Vamshi Krishna Akinapally

    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

    0
    Comment actions Permalink
  • Avatar
    Ben Bauer

    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?

    0
    Comment actions Permalink
  • Avatar
    Vamshi Krishna Akinapally

    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

    0
    Comment actions Permalink
  • Avatar
    Ben Bauer

    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.

    0
    Comment actions Permalink
  • Avatar
    PSA Support

    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. 

    0
    Comment actions Permalink
  • Avatar
    Vamshi Krishna Akinapally

    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

    0
    Comment actions Permalink

Please sign in to leave a comment.