Executing SQL scripts on CTM for Databases

[osafr71]

Hello.

I need help of someone because i've started today running one job in CTM for Databases, but it gives me an error.

The account was sucessfully created, and i have choose to the option "SQL Script". When i run the job it gaves me the error 900 ( Invalid SQL Statemen ).

I use this SQL in a bat file every week, bu when i try to run in the Control-M it gives this error.

The sysout and script is this below:

Environment information:
+--------------------+--------------------------------------------------+
|Account Name |POWERON_RA01ADB1 |
+--------------------+--------------------------------------------------+
|Database Vendor |Oracle |
+--------------------+--------------------------------------------------+
|Database Version |Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64|
+--------------------+--------------------------------------------------+


Request statement:
------------------
alter session set nls_numeric_characters ='.,';
variable rcode number;
set serveroutput on
declare

rc NUMBER := 0;
inf_sem_file_prefix VARCHAR(255);
init_date VARCHAR(9);
error_code NUMBER;
error_msg VARCHAR(255);
log_filename VARCHAR(20);
log_filedir VARCHAR(20);
default_rc NUMBER;
log_file_ext VARCHAR(20);
log_file_separator VARCHAR(20);

begin

select directory_name, inf_sem_log_file_name, inf_sem_default_rc, file_type, fi\
le_field_separator, inf_sem_success_rc, file_prefix
into log_filedir, log_filename, default_rc, log_file_ext, log_file_separator, r\
c, inf_sem_file_prefix from cfg_sem_geral;
:rcode := rc;

init_date := to_char(sysdate, inf_sem_file_prefix);
edp_sa_fich_inf.write_param_file(log_filedir, init_date || log_filename || '.'\
|| log_file_ext, '');

-- Use following 2 lines for checksums suport
-- execute immediate 'ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE';
-- edp_sa_inf_checksum.gather_weekly_csdata;

edp_sa_inf_semanal.processa_inf_semanal;

-- Use following line for old DWS/SA suport
edp_sa_fich_inf_semanal.export_inf_semanal_to_file;

edp_sa_fich_inf.write_param_file(log_filedir, init_date || log_filename || '.'\
|| log_file_ext, to_char(rc));

exception
when others then

--handle return codes
error_code := SQLCODE;
error_msg := SQLERRM;

begin
select return_code into rc from log_return_codes where errcode = error_code and\
inf_type = 'WEEKLY';
EXCEPTION when others then
rc := default_rc;
end;

:rcode := rc;
edp_sa_fich_inf.write_param_file(log_filedir, init_date || log_filename || '.'\
|| log_file_ext, to_char(rc) || log_file_separator || to_char(error_code) || l\
og_file_separator || error_msg);

end;

/




Job output:
-----------
alter session set nls_numeric_characters ='.,'

<0> rows have been updated
Job failure message:
ORA-00900: invalid SQL statement




Job statistics:
+-------------------------+-------------------------+
|Start Time |20110324221604 |
+-------------------------+-------------------------+
|End Time |20110324221604 |
+-------------------------+-------------------------+
|Elapsed Time |0 |
+-------------------------+-------------------------+
|Number Of Affected Rows |0 |
+-------------------------+-------------------------+
Exit Code = 900
Exit Message = ORA-00900: invalid SQL statement


Could someone give any idea?

Thanks

osafr71

[rchkevin]

Hi,

Please review the steps in links below

http://www.dba-oracle.com/sf_ora_00900_ ... tement.htm
http://www.techonthenet.com/oracle/errors/ora00900.php