6 Replies Latest reply on Oct 24, 2015 9:42 PM by rp0428

    inconsistent datatypes: expected NUMBER got LONG

    Shadow123

      Version 3.2.20.09

      Hi

      I'm using SQL Developer and want to see trigger body by using like function but unfortunately its data type is long so that I'm getting error, is there any way through which I can see trigger body....

       

      Sample query

       

      TRIGGER_BODY

      SELECT TRIGGER_BODY FROM user_triggers

      WHERE TRIGGER_BODY LIKE '%HEAR';

       

      Error

      ORA-00932: inconsistent datatypes: expected NUMBER got LONG

      00932. 00000 -  "inconsistent datatypes: expected %s got %s"

      *Cause:   

      *Action:

      Error at Line: 2 Column: 7

       

        • 1. Re: inconsistent datatypes: expected NUMBER got LONG
          Gary Graham-Oracle

          This is not a SQL Developer question.  A quick, but partial, answer is that displaying a LONG column in SQL*Plus / SQL Developer may be done by merely issuing a

          set long 1000  -- (or some other, larger number)

          Referencing a LONG in a WHERE-clause, however, is absolutely forbidden as far as I know.  Possibly something new (system-generated invisible columns to mirror LONG columns in the data dictionary?) is available in 12c, but deprecation of LONG was announced way back in Oracle 8 in favor of LOBs.

           

          Anyway, if you do not wish to ask this question in the PL/SQL forum, check out Laurent Schneider's code solution in this old discussion...

          search in long datatype column

          • 2. Re: inconsistent datatypes: expected NUMBER got LONG

            I'm using SQL Developer and want to see trigger body by using like function but unfortunately its data type is long so that I'm getting error, is there any way through which I can see trigger body....

            Yes - you can just select the trigger in the navigation tree. the trigger body/code will be displayed in a pane on the right.

             

            Or you can extract the DDL yourself using a query like this:

            select dbms_metadata.get_DDL('TRIGGER', 'MY_TRIGGER_NAME', 'SCOTT') FROM DUAL

            That produces a CLOB that you can search.

             

            But, as already noted, you can NOT use LONG datatypes the way you want.

             

            A bigger question is why don't you know what trigger you want to look at? Why do you need to query for it among ALL users/schemas and all triggers?

            1 person found this helpful
            • 3. Re: inconsistent datatypes: expected NUMBER got LONG
              Shadow123

              Thanks for the reply rp0428


              Actually we don't have any documentation for this system and I just joined this company...

              • 4. Re: inconsistent datatypes: expected NUMBER got LONG

                Actually we don't have any documentation for this system and I just joined this company...

                Then the FIRST thing you should do is make sure the ENTIRE SYSTEM and DB is backed up and then do a test restore to make sure the backup actually works.

                 

                The SECOND thing to do is extract ALL of the DDL and put it into a source code repository.

                 

                Don't make ANY changes to ANY object in the database until you have successfully extracted the DDL for that object and tested the ddl to make sure you can execute it properly.

                1 person found this helpful
                • 5. Re: inconsistent datatypes: expected NUMBER got LONG
                  Shadow123

                  The SECOND thing to do is extract ALL of the DDL and put it into a source code repository.

                   

                  Can you please explain how come I do that?

                  • 6. Re: inconsistent datatypes: expected NUMBER got LONG

                    Mitchels wrote:

                     

                    The SECOND thing to do is extract ALL of the DDL and put it into a source code repository.

                     

                    Can you please explain how come I do that?

                    I showed you in my first reply one way you can do that.

                     

                    And in Sql Dev every object in the nav tree will has a SQL tab in the right pane. So just select an object, select the sql tab and there is the DDL for that object.

                     

                    You can just copy & paste it into an editor.

                     

                    Or you can use Tools=>Database Export and the export wizard will guide you thru. DESELECT the 'data' so you don't export the data.

                     

                    Then just follow the screens and select the objects whose DDL you want to save.

                     

                    Download the Sql Dev user guide and read it.

                    1 person found this helpful