6 Replies Latest reply on Jan 20, 2012 2:08 AM by 910254

    Error while running a report

    SunilSharma
      Hi,

      In OBIEE11g when i am trying to run a report its throwing this error:


      View Display Error
      Odbc driver returned an error (SQLExecDirectW).
      Error Details
      Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
      State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 17001] Oracle Error code: 942, message: ORA-00942: table or view does not exist at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)
      SQL Issued: SELECT 0 s_0, "Procurement and Spend - Invoice Lines"."Fact - Purchasing - Invoice"."# of Suppliers" s_1 FROM "Procurement and Spend - Invoice Lines"

      what could be the issue? any help is appreciated.

      Thanks,
      RM
        • 1. Re: Error while running a report
          Kishore Guggilla
          Hi check wehther user used in connection pool has got read permissions on the table accessed?

          Make sure the table is existed in database or not?
          Check out the query how it's generated and able to run against db or not
          • 2. Re: Error while running a report
            SunilSharma
            The user i have used has got the read privilages. and how can i check the corresponding table exists or not because i am getting error for every report.
            • 3. Re: Error while running a report
              Kishore Guggilla
              Go to your database and check those tables exist in db or not?

              Did you check "view data" option in physicl layer?

              if you're working with more than one schema/tables used has got different owner(schema) then go to connection pool properties and select "Required Fully qualified names" checkbox.
              • 4. Re: Error while running a report
                SunilSharma
                Hi Kishore,

                Actually i have uploaded/copied the OracleBIAnalyticsApps.rpd mentioned here see 4.16 http://docs.oracle.com/cd/E20490_01/bia.7963/e19038/windows_ic.htm#BABECJED after that uploaded the same in EM. when i am trying to run any report in analytics using any single column it throwing the above error. So could you please guide me with the necessary steps/actions do i need to take here. please appreciate your help.

                Thanks,
                RM
                • 5. Re: Error while running a report
                  Dhar
                  Hi RM,

                  If you know that the tables/views are there,but this kind of error come up in BI then I am suspecting that the database is expecting fully qualified name in the queries.

                  I think this is a case. Say, you use 'System' id to connect BI to the database,however all your source tables say are in OE schema. In this case, for queries you write(or BI writes), you must always mention the tablenames prefixed by OE else they will fail.

                  For the BI Connection Pool set the 'Require fully qualified table names'(as kishore earlier mentioned too) checked in the connection pool. The BI Server will generate the queries with the schema name too and the database will be able to reach the correct table.

                  Hope this helps.

                  Thank you,
                  Dhar

                  Edited by: Dhar on 18-Jan-2012 19:38
                  • 6. Re: Error while running a report
                    910254
                    i am getting the error as given blow

                    Error
                    View Display Error
                    Odbc driver returned an error (SQLExecDirectW).
                    Error Details
                    Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
                    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 17001] Oracle Error code: 907, message: ORA-00907: missing right parenthesis at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)
                    SQL Issued: SELECT 0 s_0, "Procurement and Spend - Purchase Orders"."Fact"."Agreement Leakage Rate" s_1, "Procurement and Spend - Purchase Orders"."Fact"."Non-Agreement Purchase Rate" s_2, "Procurement and Spend - Purchase Orders"."Fact"."PO Amount" s_3, "Procurement and Spend - Purchase Orders"."Fact"."PO Input Ratio" s_4, "Procurement and Spend - Purchase Orders"."Fact"."Rejected Amount" s_5 FROM "Procurement and Spend - Purchase Orders" WHERE ((Time."Year" = '2009'))

                    The process is done as per the process given the doc or link http://docs.oracle.com/cd/E20490_01/bia.7963/e19038/windows_ic.htm#BABECJED, ( 4.Installing and Setting Up Oracle BI Applications)

                    I had not done any modifications.. as it is prebuilt rpd.... plz assit me to clear my issue...


                    find the physical parameters as per Dac

                    Name Connection String Table Owner
                    Datawarehouse BI01 dac_rep
                    ORA_R1212 QA apps

                    so as per the above values in Dac i had done chanes in variables in the rpd


                    NAME DEFAULT INTILIZER

                    OLAP_DSN BI01
                    OLAP_USER dac_rep
                    OLAPTBO dac_rep
                    OLTP_DSN QA

                    Plz assit me to resolve my issue asap....

                    Regards
                    Naveen