PL/SQL and SQL Developer different results from each other

Question

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:

Results from Oracle SQL Developer:

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.

You have a nice **bug** in your code `EXCEPTION WHEN OTHERS THEN RETURN 0;` Read [**WHEN OTHERS - A Bug**](https://lalitkumarb.wordpress.com/2014/05/02/when-others-then-null-a-bug/) — Lalit Kumar B, May 14 '20 at 13:55
Great, I didn't know that, it wasn't even showing the erros. The first error is because there was some tables that doesn't exist anymore. So, I'm using `WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF;` What does `RAISE` means in `EXCEPTION`? — Guilherme Matheus, May 14 '20 at 14:42
And there is still curiosity as to how SQL Developer managed to "overcome" these errors and PL / SQL did not, which confused even more. — Guilherme Matheus, May 14 '20 at 14:57
"*I executed the procedure through PL / SQL and Oracle SQL Developer*" doesn't make sense. PL/SQL is a programming language and SQL Developer will simply run your PL/SQL code — , May 14 '20 at 15:23
@a_horse_with_no_name I ran that select on both softwares, that what it means, as those pictures I attached to the question. — Guilherme Matheus, May 14 '20 at 16:13
PL/SQL isn't a software where you can take a screenshot from. It's a programming language. — , May 14 '20 at 16:24
@GuilhermeMatheus Read https://docs.oracle.com/database/121/LNPLS/errors.htm#LNPLS007 — Lalit Kumar B, May 14 '20 at 16:38
SQL Developer did not ***overcome*** that exception, ***your code did***. A PLSQL block contains 3 distinct sections: Declaration, Execution, Exception. When an exception occurs control immediately passes to the Exception handler. A WHEN for it without raise afterward, that tells PLSQL to ignore it. A WHEN OTHERS says for all possible exceptions. In your case return 0, which is exactly what PLSQL did. I suggest you take some time a read the documentation. A good starting point [2day devalopers guide](https://docs.oracle.com/database/121/TDDDG/tdddg_subprograms.htm#TDDDG46000). — Belayer, May 14 '20 at 16:40

score 0 · Accepted Answer · answered May 14 '20 at 16:59

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.

PL/SQL and SQL Developer different results from each other

1 Answers1