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
Executing SQL scripts on CTM for Databases
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
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