CODEWITHSUNDEEP
bashoracle-databasestored-proceduressqlplus
BlindSp0t
BlindSp0t

Reputation: 121

Stored procedure doesn't have the same result when calling from PL/SQL developer and SQL*plus

I'm trying to call a procedure from a shell script using sql*plus but I'm running into a weird issue.

My procedure starts by loading data into a cursor from a table. Then, for each line of the cursor, it's calling another procedure that's supposed to insert data into another table. After that, it updates the lines read from the first table to include a "work done" timestamp.

When I call this procedure from PL/SQL Developer, everything works fine, and I get my data into the second table. When I call the same procedure from sql*plus, it tells me everything works fine (returns 0), the "work done" timestamp is added, but I get no data inserted into the second table.

I'm at a loss as to what could be causing that.

here's the line used in the script to call the procedure:

echo exit | sqlplus -S $schema @call_to_procedure.sql

$schema is schema/pwd@db

call_to_procedure.sql is

set serveroutput on feedback off
declare
  V_RETURN number(1);
begin
  SCHEMA.PACKAGE.PROCEDURE(V_RETURN);
  dbms_output.put_line(V_RETURN);
end;
/

And here is the procedure code (I hope I didn't remove too much, but basically what is gone is the cursor's select query's filters)

PROCEDURE MYPROC (v_result out NUMBER) IS
    v_id_smth number(10);
    v_id_smthelse number(10);
    v_data varchar2(10);
    v_errmsg CLOB;

    CURSOR data_to_load IS
        SELECT t1.id_smth as id_smth, t1.data as data, t2.smthelse as smthelse
        FROM t1, t2
        WHERE t1.id_smth = t2.id_smth
        AND other controls
    ;

BEGIN
    FOR data in data_to_load
    LOOP
        v_id_smth := data.id_smth;
        v_id_smthelse := data.id_smthelse;
        v_data := data.data; -- sorry about that renaming scheme :/

        BEGIN
            INSERT_DATA_INTO_OTHER_TABLE(v_id_smth, v_id_smthelse, v_data);
        END;
    END LOOP;

    UPDATE T1
    SET date_processing = SYSDATE
    WHERE date_processing IS NULL;

    v_result = 0;

EXCEPTION
    WHEN OTHER THEN
    v_errmsg := SQLCODE || '-' || SQLERRM || '; v_id_smth : ' || v_id_smth || '; v_id_smthelse : ' || v_id_smthelse;
    LOG_PACKAGE.LOG_PROC('MY_PROC', v_errmsg);
    v_result := 1;

END MYPROC;

edit for clarification: Both procedures are in the same schema/package, and I'm logged in as schema owner in both instances (PL Developper & sql*plus).

Upvotes: 2

Views: 214

Answers (2)

Michael Broughton
Michael Broughton

Reputation: 4055

Another option, is who you log in as the same on SQLPlus as SQLDeveloper? If a procedure is created with INVOKER RIGHTS, different user accounts executing it may have different results depending on their permissions - which may not be obvious, especially if the procedure suppresses exceptions.

Upvotes: 1

Dmitry Demin
Dmitry Demin

Reputation: 2113

Most likely, the problem is in the environment variables and national calendar settings. Here is a simple example when the day of the week is 4 or 5, depending on the national calendar. For example, bat file

set NLS_LANG=AMERICAN_RUSSIA.CL8MSWIN1251
(
echo  connect  / as sysdba
echo  @E:\upwork\stackoverflow\bat_sql\sqltest.sql
echo exit
)| sqlplus   /nolog

E:\upwork\stackoverflow\bat_sql\sqltest.sql

select to_char(sysdate,'D') from dual;

Output 

SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 6 22:53:20 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> Connected.


SQL>
T
-
4

change to AMERICA in NLS_LANG

set NLS_LANG=AMERICAN_AMERICA.CL8MSWIN1251
(
echo  connect  / as sysdba
echo  @E:\upwork\stackoverflow\bat_sql\sqltest.sql
echo exit
)| sqlplus   /nolog

E:\upwork\stackoverflow\bat_sql\sqltest.sql

 select to_char(sysdate,'D') from dual;

Output:

SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 6 22:56:42 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> Connected.

SQL>
T
-
5

Upvotes: 2

Related Questions