8 Replies Latest reply on Dec 7, 2018 3:18 PM by 1765073

    Sql Developer return null (number)

    1765073

      Hi, i have a question. This sql sentence return null in  SQL Developer but in TOAD return number value.

       

      select MLIN_TLF_FL_NUMERO_ENTE

      from tdf.movlinks21new

      where mlin_feproc_s21 = 20181203 and mlin_s21_nrcta = 20206091

      and MLIN_HTH_ISOHORA = 162957;

       

      The same sentences return different in Toad vs. SQL Developer

      Toad=24080

      Sql developer=null

       

      MLIN_TLF_FL_NUMERO_ENTE        NUMBER(4)

       

      TOAD                                                                               

      NLS_LANGUAGESPANISH
      NLS_TERRITORYSPAIN

       

      SQL DEVELOPER

      NLS_LANGUAGELATIN AMERICAN SPANISH
      NLS_TERRITORYARGENTINA

       

      I change this values with alter session but is the same.

      Any idea?

      Thank.

        • 1. Re: Sql Developer return null (number)
          Gaz in Oz

          Your frugal code snippets/information supplied  show that you can not be selecting from the same table/view....

          Hi, i have a question. This sql sentence return null in  SQL Developer but in TOAD return number value.

           

          select MLIN_TLF_FL_NUMERO_ENTE

          from tdf.movlinks21new

          where mlin_feproc_s21 = 20181203 and mlin_s21_nrcta = 20206091

          and MLIN_HTH_ISOHORA = 162957;

           

          The same sentences return different in Toad vs. SQL Developer

          Toad=24080

          Sql developer=null

           

          MLIN_TLF_FL_NUMERO_ENTE        NUMBER(4)

          It is impossible to get a 5digit integer into an Oracle NUMBER(4) table column. The Toad query must be selecting from a different table/view.

          1 person found this helpful
          • 2. Re: Sql Developer return null (number)
            1765073

            yes, Gaz! I don´t know why Toad return a number (5) but the same query in SQL Developer return null.

            Everyday exists some inserts with value 5 but i don´t know why or maybe toad show this and really is trash.

            Do you know some query to return value trash in the rows?

            Thank.

            • 3. Re: Sql Developer return null (number)
              Gaz in Oz

              ...so you think you are logged in as the same user to the same database via tOad and via sqldev?...

              Do some checking.,,

              Run the following, once while you are in tOad and once while you are in sqldeveloper.

              Copy/paste the results back here if you still have an issue:

              SELECT CASE r
                        WHEN 0 THEN 'Date:           '||sysdate                                 -- sysdate#,
                        WHEN 1 THEN 'Username:       '||user                                    -- username,
                        WHEN 2 THEN 'Client Machine: '||SYS_CONTEXT('userenv', 'host')          -- client_machine,
                        WHEN 3 THEN 'Client IP Addr: '||SYS_CONTEXT('userenv', 'ip_address')    -- client_ip_address,
                        WHEN 4 THEN 'Client App:     '||SYS_CONTEXT('userenv', 'module')        -- client_app,
                        WHEN 5 THEN 'Client User:    '||SYS_CONTEXT('userenv', 'os_user')       -- client_user,
                        WHEN 6 THEN 'Server Machine  '||SYS_CONTEXT('userenv', 'server_host')   -- db_server,
                        WHEN 7 THEN 'global_name:    '||global_name                             -- global_name,
                        WHEN 8 THEN 'Service Name:   '||SYS_CONTEXT('userenv', 'service_name')  -- service_name,
                        WHEN 9 THEN 'Instance Name:  '||SYS_CONTEXT('userenv', 'instance_name') -- instance_name
                    END connection_info
              FROM  global_name,
                    (SELECT level - 1 r FROM dual CONNECT BY level < 11);
              
              describe tdf.movlinks21new;
              

               

              If you still think you are logged into the same db as the same user and If tdf.movlinks21new is a view, you need to examine the view definition to see if there is anything the view is doing that would cause your issue:

              set long 50000;
              select text
              from   all_views
              where  owner = 'TDF'
              and    view_name = 'MOVLINKS21NEW';
              
              • 4. Re: Sql Developer return null (number)
                1765073

                Thank Gaz! Copy the result in differents tools. Can you see that the same user in differents tools.

                  

                toad devsqlplus devsql developer dev
                Date:           06/12/18Date:           06-DEC-18Date:           06/12/2018
                Username:       MLAGRANAUsername:       MLAGRANAUsername:       MLAGRANA
                Client Machine: CENTRAL_DOM\TECHDBAClient Machine: sxxi-devClient Machine: TECHDBA
                Client IP Addr: 10.1.20.204Client IP Addr:Client IP Addr: 10.1.20.204
                Client App:     Toad.exeClient App:     SQL*PlusClient App:     SQL Developer
                Client User:    mlagranaClient User:    mlagranaClient User:    mlagrana
                Server Machine  sxxi-devServer Machine  sxxi-devServer Machine  sxxi-dev
                global_name:    ORADB10global_name:    ORADB10global_name:    ORADB10
                Service Name:   OraDB10Service Name:   SYS$USERSService Name:   SYS$USERS
                Instance Name:  OraDB10Instance Name:  OraDB10

                Instance Name:  OraDB10

                 

                Nombre                        ¿Nulo?   Tipo         

                ----------------------------- -------- -------------

                MLIN_FEPROC_S21               NOT NULL NUMBER(8)    

                MLIN_FEPROC_LINK              NOT NULL NUMBER(8)    

                MLIN_HTH_ISOFECHA             NOT NULL CHAR(4)      

                MLIN_HTH_ISOHORA              NOT NULL CHAR(6)      

                MLIN_HTH_ISOTERMID            NOT NULL CHAR(16)     

                MLIN_HTH_ISONROMSG            NOT NULL CHAR(12)     

                MLIN_HTH_ISOTIPOMSG           NOT NULL CHAR(4)      

                MLIN_TEF_FEINGRESO            NOT NULL NUMBER(8)    

                MLIN_TEF_COTIUNIORG           NOT NULL CHAR(1)      

                MLIN_TEF_COUNIORG             NOT NULL NUMBER(5)    

                MLIN_TEF_NRUSERID             NOT NULL CHAR(8)      

                MLIN_TEF_COMON                NOT NULL NUMBER(3)    

                MLIN_TEF_TMING                NOT NULL NUMBER(8)    

                MLIN_TEF_COESTTRA             NOT NULL CHAR(1)      

                MLIN_TEF_IMVALTRA             NOT NULL NUMBER(17,2) 

                MLIN_TEF_IMVALPLUTRA          NOT NULL NUMBER(17,2) 

                MLIN_S21_COTIUNIORGCTA        NOT NULL CHAR(1)      

                MLIN_S21_COUNIORGCTA          NOT NULL NUMBER(5)    

                MLIN_S21_COPRODCTA            NOT NULL NUMBER(3)    

                MLIN_S21_COSBPCTA             NOT NULL NUMBER(5)    

                MLIN_S21_NRCTA                NOT NULL NUMBER(11)   

                MLIN_S21_NRSUFCTA             NOT NULL NUMBER(2)    

                MLIN_TLF_FL_T_CDE             NOT NULL CHAR(2)      

                MLIN_TLF_FL_ORIG_CRNCY_CDE    NOT NULL NUMBER(3)    

                MLIN_TLF_COMON_S21_EQUIV      NOT NULL NUMBER(3)    

                MLIN_TLF_FL_SEQ_NRO_TRAN      NOT NULL NUMBER(6)    

                MLIN_TLF_NUM_FL_AMT_1         NOT NULL NUMBER(15,2) 

                MLIN_TLF_NUM_FL_AMT_2         NOT NULL NUMBER(15,2) 

                MLIN_TLF_NUM_FL_AMT_3         NOT NULL NUMBER(15,2) 

                MLIN_TLF_FL_TIP_EXCHA_COMP    NOT NULL NUMBER(8,3)  

                MLIN_TLF_FL_TIP_EXCHA_VEND    NOT NULL NUMBER(8,3)  

                MLIN_TLF_FL_ARBITRAJE         NOT NULL NUMBER(8,3)  

                MLIN_TLF_FL_INTEREST_RATE     NOT NULL NUMBER(6,2)  

                MLIN_TLF_FL_CASH_FEE          NOT NULL NUMBER(8,2)  

                MLIN_TLF_FL_NOMBRE_ENTE       NOT NULL CHAR(18)     

                MLIN_TLF_FL_NUMERO_ENTE       NOT NULL NUMBER(4)    

                MLIN_TLF_FL_LOCALIDAD_ENTE    NOT NULL CHAR(13)     

                MLIN_TLF_FL_LN_ATM            NOT NULL CHAR(4)      

                MLIN_TLF_FL_FIID_ATM          NOT NULL CHAR(4)      

                MLIN_TLF_FL_TERM_ID_NRO_ATM   NOT NULL CHAR(4)      

                MLIN_TLF_FL_TERM_ID_ATM       NOT NULL CHAR(16)     

                MLIN_TLF_FL_T_FROM            NOT NULL CHAR(2)      

                MLIN_TLF_FL_SUC_FROM_ACCT     NOT NULL NUMBER(2)    

                MLIN_TLF_FL_CTA_FROM_ACCT     NOT NULL NUMBER(9)    

                MLIN_TLF_FL_T_TO              NOT NULL CHAR(2)      

                MLIN_TLF_FL_SUC_TO_ACCT       NOT NULL NUMBER(2)    

                MLIN_TLF_FL_CTA_TO_ACCT       NOT NULL NUMBER(9)    

                MLIN_APA_COD_ESTADO_TLF       NOT NULL CHAR(2)      

                MLIN_APA_ULT_TIPOMSG_TLF      NOT NULL CHAR(4)      

                MLIN_APA_COD_ESTADO_HTH       NOT NULL CHAR(2)      

                MLIN_APA_ULT_TIPOMSG_HTH      NOT NULL CHAR(4)      

                MLIN_APA_RESULTADO_FINAL      NOT NULL NUMBER(3)    

                MLIN_APA_RESULTADO_TEXTO      NOT NULL VARCHAR2(250)

                MLIN_TEF_IMPORTE3             NOT NULL NUMBER(17,2) 

                MLIN_HTH_IMPORTE_PERCE_RG3379 NOT NULL NUMBER(12,2) 

                MLIN_HTH_TCBIO_BCO_NAC_VEND   NOT NULL NUMBER(8,3)  

                MLIN_HTH_PORCENTAJE_APLICADO  NOT NULL NUMBER(4,2)  

                MLIN_TLF_NUMERO_ENTE_ALFA     NOT NULL CHAR(4)      

                MLIN_TLF_IMPORTE_PERCE_RG3379 NOT NULL NUMBER(12,2) 

                MLIN_TLF_TCBIO_BCO_NAC_VEND   NOT NULL NUMBER(8,3)  

                MLIN_TLF_PORCENTAJE_APLICADO  NOT NULL NUMBER(4,2)  

                MLIN_TLF_IMPORTE3             NOT NULL NUMBER(17,2) 

                MLIN_HTH_FL_TERM_CNTRY        NOT NULL CHAR(2)  

                 

                And the last query return "no rows selected"

                • 5. Re: Sql Developer return null (number)
                  Gaz in Oz

                  Thanks for posting the info... it does indeed look like it is the same user and db, as you said.

                  When running your original query you posted, in sqlplus, what does that return?

                  To clarify what the object "movlinks21new"  is please run these queries  too:

                  select owner, object_type, object_name
                  from   all_objects
                  where  regexp_like(object_name, 'movlinks21new', 'i');
                  
                  select owner, synonym_name, table_owner, table_name, db_link
                  from   all_synonyms
                  where  regexp_like(synonym_name, 'movlinks21new', 'i');
                  
                  • 6. Re: Sql Developer return null (number)
                    HarbourGhost

                    You can put the value "24080" in a column defined as NUMBER(4,-1)

                    And a SQL Developer DESC will show that as a NUMBER(4) though an INFO will show the NUMBER(4,-1) definition (and the SQL Plus DESC shows NUMBER(4,-1) as well)

                    Try selecting a DUMP of the column to see the actual bytes.

                     

                    create table t (id number(4,-1));

                     

                    insert into t values(24080);

                     

                    desc t

                    info t

                    select DUMP(MLIN_TLF_FL_NUMERO_ENTE)

                    from tdf.movlinks21new

                    where mlin_feproc_s21 = 20181203 and mlin_s21_nrcta = 20206091

                    and MLIN_HTH_ISOHORA = 162957;

                    • 7. Re: Sql Developer return null (number)
                      Gaz in Oz

                      ...so a bug in sql developer of the version you're using.

                      • 8. Re: Sql Developer return null (number)
                        1765073

                        When running your original query you posted, in sqlplus, what does that return?

                        - no rows selected

                         

                        select owner, object_type, object_name 

                        from   all_objects 

                        where  regexp_like(object_name, 'movlinks21new', 'i');

                         

                        TDF    INDEX    MOVLINKS21NEW_IDX4

                        TDF    INDEX    MOVLINKS21NEW_IDX5

                        TDF    TABLE    MOVLINKS21NEW

                        TDF    INDEX    MOVLINKS21NEW_IDX1

                        TDF    INDEX    MOVLINKS21NEW_IDX2

                        TDF    INDEX    MOVLINKS21NEW_IDX3

                        TDF    INDEX    AKMOVLINKS21NEW 

                         

                        select owner, synonym_name, table_owner, table_name, db_link 

                        from   all_synonyms 

                        where  regexp_like(synonym_name, 'movlinks21new', 'i'); 

                        -- 0 rows