1

I have the following ksh, test.ksh, which call an sql script:

sqlplus user/passwrd @testChange.sql

My testChange.sql is as follows:

spool testChange.log
SET serveroutput ON;
SET linesize 350;
ALTER SESSION SET nls_date_format='dd/mm/yyyy hh24:mi:ss';
DECLARE

  nbCharAlterer NUMBER;
BEGIN

  nbCharAlterer:= 3;
  USER.PKG_TEST_CHANGE.ALTER (  nbCharAlterer => nbCharAlterer) ;
END;
/
spool off;
exit;

The head of my package is as follows:

create or replace package PKG_TEST_CHANGE AUTHID CURRENT_USER is
  nbCharAlterer number:=3;   
  TYPE t_array IS TABLE OF VARCHAR2(50)    INDEX BY BINARY_INTEGER; 
  procedure alterer(nbCharAlterer in number );   
end PKG_TEST_CHANGE;

The body of my package is as follows:

create or replace package body PKG_TEST_CHANGE as

compteur PLS_INTEGER      := 0;
nbCharAlterer PLS_INTEGER   := 3;
nbParallel PLS_INTEGER       := 8

PROCEDURE ALTER(
        nbCharAlterer IN NUMBER)
IS

CURSOR SQL_TABLE     IS
    SELECT TABLE_OWNER,TABLE_NAME,COLUMN_NAME,TYPE,SEPARATE,PATTERN,ID FROM 

ALTERE_PARAM ;

  sTable                                  sys.ALL_TAB_COLUMNS.table_name%TYPE;
  sOwner                                  sys.ALL_TAB_COLUMNS.owner%TYPE;
  sColumn                                 sys.ALL_TAB_COLUMNS.COLUMN_NAME%TYPE;
  sType                                 sys.ALL_TAB_COLUMNS.COLUMN_NAME%TYPE;
  sPattern                                 ALTERE_PARAM.PATTERN%TYPE;
  sSeparator                                 ALTERE_PARAM.SEPARATE%TYPE;
  sId                                 sys.ALL_TAB_COLUMNS.COLUMN_NAME%TYPE;

  nNbTable number;


BEGIN
dbms_output.put_line('   ' ||TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI')||' => Database changing');
DBMS_APPLICATION_INFO.SET_CLIENT_INFO('Alter');

....Some code

END ALTER;

end PKG_TEST_CHANGE;

I want my dbms_output.put_line('') to be recorded in a log file. I have run my ksh as below:

nohup ./test.ksh > alterTest.log &

My log file has only one line with 'Session altered.'. Any idea why my log is not working in the proper way? I am using Oracle 10g.

Cœur
  • 37,241
  • 25
  • 195
  • 267
refresh
  • 1,319
  • 2
  • 20
  • 71
  • 1
    The call `PKG_TEST_CHANGE.ALTER` doesn't match the procedure name in your package (`alterer`). But then your package names don't match - is `pkg_test_change` or `pkg_alterer_remu`? In short, how do you expect us to figure out what's going wrong with your code when what you've posted won't even compile let alone run? Please give us a fighting chance and post a reproducible test case. – APC Jul 27 '17 at 05:46
  • @APC : These are more copy/paste errors. The code function well – refresh Jul 27 '17 at 05:51
  • Have you tried Adding `set echo on` ? – Kaushik Nayak Jul 27 '17 at 06:02
  • @KaushikNayak : No. Should I add it in the testChange.sql, the one which calls the package method? – refresh Jul 27 '17 at 06:05
  • Check this link : https://stackoverflow.com/questions/19843858/how-to-echo-text-during-sql-script-execution-in-sqlplus – Kaushik Nayak Jul 27 '17 at 06:15
  • Which log file are you talking about? The `testChange.log` file you spool to in the shell script? Or the `alterTest.log` you have as the stdout target? – APC Jul 27 '17 at 07:41
  • 1
    As for "copy/paste errors", you are asking us to explain your code's apparent weird behaviour. In such cases the details absolutely matter. Posting a mangled version of the code in play increases the chance that you've left out something crucial to the problem. – APC Jul 27 '17 at 07:44
  • If you indeed want to log ONLY your dbms_output.put_line, you might as well create a procedure to write to a specific log file, and then replace the call of dbms_output.put_line to your procedure, instead of using spool. – Renato Afonso Jul 27 '17 at 14:37

0 Answers0