Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Analysis works as Agent's content but throws error when run by itself.

Received Response
12
Views
4
Comments
User_SI7HK
User_SI7HK Rank 4 - Community Specialist
edited Aug 13, 2024 5:11PM in Oracle Analytics Cloud and Server

Hi SMEs,

We have a formula in an analysis (to convert full ....hh:mm:ss date to MM/DD/YYYY) as

CAST(LEFT ("XYZ Date",10) AS DATE)

This analysis runs fine when used in an agent but when run by itself gives an error, as below:

 Error Details

Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)

State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)

State: HY000. Code: 43119. [nQSError: 43119] Query Failed: (HY000)

State: HY000. Code: 17001. [nQSError: 17001] Oracle Error code: 1861, message: ORA-01861: literal does not match format string at OCI call OCIStmtFetch. (HY000)

State: HY000. Code: 17012. [nQSError: 17012] Bulk fetch failed. (HY000)

SQL Issued: SELECT 0 s_0, "Pro.. . . ...

Any input is highly appreciated.

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Very first thing: Have you look at the detailed log file to see what exactly happens?

    If not, then you can only guess about the calling context between Answers and the Agent and what kind of data is being grabbed and processed.

  • User_SI7HK
    User_SI7HK Rank 4 - Community Specialist

    we are using OAS 5.9, it is a varchar150 datatype containing YYYY/MM/DD HH:MM:SS at the source. It is being converted to date as CAST (LEFT ("XYZ Date",10) AS DATE) in the analysis, in the session log it comes as :

    cast(substr(XYZ Date , 1, 10) as DATE )   and errors out for ORA-01861: literal does not match format string.

  • Gianni Ceresa
    Gianni Ceresa Mod

    The error is a database error, therefore something is wrong with your data.

    Isn't your agent retrieving only a subset of data that, luckily, does respect the format you expect, while the analysis as you run it is missing some filters and retrieve data not matching the format?

    As it's a database error it is fairly simple: look at your logs, take the physical SQL query generated by OAS, run it against your database and correct the error you have (either by fixing your data in the database or by adding enough filters in the analysis to not hit the problematic data).

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    It's all about data quality and data formatting.

    But I repeat my first statement: Have you looked at the detailed log file to see what exactly happens? What does the SQL fetch? Which fetched row contains what? Where is the source of the error?

    Again, without that it's all just guesswork.