Search code examples
sqloracleplsqloracle-sqldeveloper

PL/SQL and SQL Developer different results from each other

I am executing a query in PL / SQL in version 7 and version 14, with a function created by me, and both bring me some results, the rest bring 0. However, when executing the same query in Oracle SQL Developer, the query brings all the results correctly.

I executed the procedure through PL / SQL and Oracle SQL Developer as well, but then none brought me the right result, all the lines were left as "0". I can't find the problem at all, even on Google.

Basically, the function multiplies the number of rows by columns that start with "ID_", as shown below.

Function:

CREATE OR REPLACE FUNCTION DS_FUNCESP.FNBIGB_CheckDataCells
(pOwn IN VARCHAR2, 
 pTab IN VARCHAR2)
RETURN NUMBER 
IS

 v_Qtd NUMBER;
 v_str VARCHAR2(2000);
 
BEGIN
   v_Qtd := 1;
   v_str := ' SELECT
                SUM((SELECT COUNT(1) AS QTY_ROWS FROM ' || pOwn || '.' || pTab || ' d WHERE d.LINORIGEM <> ''CARGA MANUAL'')) AS QTY_DATA
              FROM DW_FUNCESP.D_BI_COLUMNS a
                LEFT JOIN
                  DW_FUNCESP.D_BI_TABLES b
                  ON a.ID_TABLE  = b.ID_TABLE
                  AND a.ID_OWNER = b.ID_OWNER
                LEFT JOIN DW_FUNCESP.D_BI_OWNERS c
                  ON a.ID_OWNER  = c.ID_OWNER
              WHERE b.NM_TABLE = ''' || pTab || '''
              AND a.IN_PRIMARYKEY       = ''NAO''
              AND SUBSTR(a.NM_COLUMN,1,3) = ''ID_'' ';

   DBMS_OUTPUT.put_line(v_str);
   EXECUTE IMMEDIATE v_str into v_Qtd ;

   return (v_Qtd);
  
EXCEPTION WHEN OTHERS THEN
       RETURN 0;
       
END FNBIGB_CheckDataCells;

Select statement:

SELECT
  c.NM_OWNER ,
  b.NM_TABLE ,
  DS_FUNCESP.FNBIGB_CHECKDATACELLS(c.NM_OWNER, b.NM_TABLE) AS QTY_DATA
FROM DW_FUNCESP.D_BI_TABLES b
  LEFT JOIN DW_FUNCESP.D_BI_OWNERS c
    ON b.ID_OWNER = c.ID_OWNER;

Results from PL/SQL:

enter image description here

Results from Oracle SQL Developer:

enter image description here

Clearly we can see the difference from any row, the right one is the Oracle SQL Developer. So I'd like to know what is the problem, how to fix, because the procedure is adding "0" to all the rows, no matter where I run.

Solution

  • Reading those examples from WHEN OTHERS - A Bug, thanks to @Lalit Kumar B for that, I changed: 

    EXCEPTION WHEN OTHERS THEN
       RETURN 0;

    To:

    EXCEPTION 
   WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE);
        DBMS_OUTPUT.PUT_LINE('Message: '||SQLERRM);
        RAISE;

    To find out the problem, and thanks for that I found that it was trying to count from a table where it doesn't exist anymore.

    So I using an error handling as below, from @Jeffrey Kemp

    EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;

    Also, thanks for @Belayer, my code was the problem, agreed on that. Also, executing on both softwares, made me even more confused. I'll read also that documentation for sure.