9 Replies Latest reply on Apr 26, 2013 5:15 PM by 1005784

    Problem with LONG datatype in Answers

    Kishore Guggilla
      Hi Experts,

      I have a column of LONGVARCHAR datatype in my physical layer or in database.
      When i use this column in request, Its throwing an error saying that Illegal use of LONG datatype.
      here is the error code:

      State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 997, message: ORA-00997: illegal use of LONG datatype at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)

      I gone through this http://ora-00997.ora-code.com/ ... but i am not using this column in any of those mentioned.
      I also done casting and changing datatype in physical layer itself for that particular column...

      But no use... i need this as early as possible.. please help me in avoiding this error.

      Thank you very much..

      Thanks & Regards
      Kishore Guggilla
        • 1. Re: Problem with LONG datatype in Answers
          You are getting the error because of limitations on sql generated. With Oracle LONG columns cannot appear in certain parts of SQL statements:

          * WHERE, GROUP BY, ORDER BY, or CONNECT BY clauses or with the
          DISTINCT operator in SELECT statements
          * SQL functions (such as SUBSTR or INSTR)
          * expressions or conditions
          * select lists of queries containing GROUP BY clauses
          * select lists of subqueries or queries combined by set operators
          * select lists of CREATE TABLE AS SELECT statements
          * Within a single SQL statement, all LONG columns, sequences,
          updated tables, and locked tables must be located on the same

          Suggestion 1: Make sure your generated query is not doing the above things. First thing Distinct will come in the obiee queries by default. Try to avoid distinct,group by,order by in your reports.
          Suggestion 2: Try to do direct database request for the report which needed LONG column, in the database request avoid all above conditions.

          Hope it helps.,

          1 person found this helpful
          • 2. Re: Problem with LONG datatype in Answers
            Kishore Guggilla
            Hello Sir,

            Thank you for your reply,

            I would be more clear about my question now,
            --> I am not using any measure in report, it's completely with dimensions. So we can't expect this column to be present in GROUP BY clause
            --> For the first time, by watching SQL, i got to know that it's using in ORDER BY clause, to avoid this i mentioned ORDER BY for first column, so that BI Server applies ORDER BY on only that particular column, instead of applying ORDER BY on all dimensions in the criteria.
            --> Finally i am not applying any functions on this column and also not using in conditions means in WHERE clause

            If i can apply your suggestion 2 on my report, i can't put column filter prompts or dashboard prompts which need to be applied on my report...

            Please any other suggestions?

            Thanks & Regards
            Kishore Guggilla

            Edited by: Kishore Guggilla on Nov 5, 2008 7:07 PM

            Edited by: Kishore Guggilla on Nov 24, 2008 2:41 PM
            • 3. How to avoid DISTINCT in OBIEE SQL
              Kishore Guggilla

              I run the query in TOAD, and i was identified the error because of what this error is coming... because of DISTINCT...
              I have taken care of avoiding that column in ORDER BY by putting the order by on another column..

              Now my question is how to avoid the DISTINCT clause in SQL generated by OBIEE...
              If the first column in criteria is measure then it avoids the DISTINCT... but if i use measure... group by will come.. and group by should not be used here...

              If this is done... my problem will be resolved...
              Ofcourse... i am getting duplicate rows...

              Still i want the answer how to avoid DISTINCT?
              Expecting answer from you...

              Thanks & Regards
              Kishore Guggilla
              • 4. Re: How to avoid DISTINCT in OBIEE SQL
                Kishore, try changing the datatype in the pysical layer to varchar(8000) or so and see if it works with distinct.
                • 5. Re: How to avoid DISTINCT in OBIEE SQL
                  Kishore Guggilla

                  I already done this... but no luck.. not working..
                  Any other solution?

                  Thanks & Regards
                  Kishore Guggilla
                  • 6. Re: How to avoid DISTINCT in OBIEE SQL
                    Kishore Guggilla
                    Hi Everybody,

                    Seems no solution for this issue in OBIEE.
                    And need to change the data type in DB only.. i hope..
                    So, i kept this thread open... ;)

                    Thanks & Regards
                    Kishore Guggilla
                    • 7. Re: How to avoid DISTINCT in OBIEE SQL
                      Did you try Typecasting the Longvarchar to Varchar? Then it will not be a problem
                      • 8. Re: Problem with LONG datatype in Answers
                        Create a function which will change the long to varchar and call the same in RPD physical layer...Example

                        create or replace
                        FUNCTION longtovarchar2_4000 (p_tname IN varchar2, p_cname IN varchar2, p_rowid IN ROWID)
                        RETURN varchar2
                        l_cursor integer DEFAULT DBMS_SQL.open_cursor ;
                        l_n number;
                        l_long_val varchar2 (4000);
                        l_long_len number;
                        l_buflen number := 4000;
                        l_curpos number := 0;
                        DBMS_SQL.parse (l_cursor, 'select ' || p_cname || ' from ' || p_tname || ' where row_id = :x1', DBMS_SQL.native);
                        DBMS_SQL.bind_variable (l_cursor, ':x1', p_rowid);

                        DBMS_SQL.define_column_long (l_cursor, 1);
                        l_n := DBMS_SQL.execute (l_cursor);

                        IF (DBMS_SQL.fetch_rows (l_cursor) > 0)
                        DBMS_SQL.column_value_long (l_cursor,
                        END IF;

                        DBMS_SQL.close_cursor (l_cursor);
                        RETURN l_long_val;
                        END longtovarchar2_4000;


                        call: SELECT longtovarchar2_4000 ('Table A','NOTE',Table A.ROW_ID) NOTE,Table A.row_id
                        FROM Table A

                        Edited by: user11954081 on Sep 29, 2009 6:44 AM
                        • 9. Re: Problem with LONG datatype in Answers
                          Hi All,

                          There may be many solution for this, i am providing only one among them.

                          Assuming that Fact and Dimension are directly Connected.In this example I am converting LongVarchar to Varchar

                          There are few steps which should be followed:

                          1)Create a Opaque view with an Alias for the required column and Cast that Column to required data type along with the key column.

                          ex: Select CAST(DESCRIPTION as VarChar(4000)) as DESCRIPTION,ID from DIM_TABLE;

                          Here DESCRIPTION is the required Column.

                          2)Make exact join for the Opaque view with the Fact .

                          3)In BMM, we need to divide fact in to two logical tables.Fact columns in to one L.T and Nonfacts in to one L.T

                          4) Fact L.T should contain only measure columns & non Fact L.T should contain non-measure coulmns.

                          5)We should make a join between Fact and Non-fact L.T in BMM.

                          6)Then create Dimnesion Heirarchy for the Non-Fact,and make sure all the coulmns in the Non-fact are at detail level.

                          7)Drag the Dimnesion column from the Opaque view in to Non- Fact L.T ,which we need and set the logical level to detail.

                          8)And in the Content tab of Fact L.T.S set the level for the Non-Fact to Detail.

                          9)Pull the column required coulmn from the Non-fact in to the required Presentation table of the Presentation layer.

                          by this we can have the our required column in order by, group by or in any criteria.

                          Edited by: 1002781 on Apr 26, 2013 10:13 AM