FileReadAll has a limit of 2 Gbytes so it's not the cause of the problem. Here is what you can do to find out why the job fails:
Turn on both "Trace enabled" and "Database Trace Enabled" options in the Tools/Options/Log menu.
Run the job and first check the script trace file script.log (in 24x7 installation directory). If the trace of the job looks OK check the database trace file c:\winnt\pbtrace.log (if you use Win 9x system, the file name is c:\windows\ptrace.log).
The database trace file should provide complete details on all executed SQL statements and complete database response. There you should be able to found why the SQL fails. :
: Still using the second version of the 24x7 scheduler (v2.4.15), I decided,
: after having tested it on a local machine, to upgrade our (licensed)
: version from v2.4.13 to v2.4.15 (the latest patch before v3) downloaded
: from your website. : All scripts seem to work fine, except that we're having trouble with one of
: the scripts that drops and creates tables in an Oracle database. The
: script is running fine since more than one year on the same computer. : The script (see below) reads the table names from an INI file, sends through
: ODBC a "DROP TABLE" command (in relation to the table name to be
: handeled) and reads the CREATE command from a file. It then sends an SQL
: command through a DatabaseExecute command to the ORACLE database. : When testing if the created table really exists, only one table from the
: nearly 25 tables to be manipulated doesn't want to be created. : The Oracle Database Administrator has verified if an problem exists with the
: defined "spaces" like TableSpace, etc. Everything ok for him. : The table that refuses to created can be created "by hand" using
: the same script with the help of and Oracle Utility : The script file (INSERTION.SQL in this case) that is send to the Oracle
: database to create the table is a file of 3353 bytes. : Our question : is the file size that is read through the FileReadAll
: statement limited ?? The variable that holds the contents of the file,
: seems to be complete. : Any suggestions,
: Thanks for responding, it's an urgent problem : Robbert de GROOT
: please respond to rdegroot@free.fr
: licence 00115-341445-04518 : *************************************************
: 1) DROP/DREATE script
: *************************************************
: //-----------------------------------------------------------------
: // Nom : JOB4
: // Creation : 7 novembre 2000
: // Dernier modification : 13 février 2001
: // Copyright : CREDOME (c) 2000-2001
: // Auteur : R. de GROOT
: // : Publicis Technology
: //-----------------------------------------------------------------
: DIM success, boolean
: DIM OkFlag, boolean : DIM rows, number
: DIM numItemTable, number
: DIM numItemIndex, number
: Dim rows, number
: Dim counter, number : DIM ReceiverMailAddress, string
: DIM SenderMailAddress, string
: DIM SenderPassword, string
: DIM buffer, string
: DIM bufferSQL, string
: DIM profile, string
: DIM Entry, string
: DIM Subject, string
: DIM Message, string
: DIM FileNameParams, string
: DIM dirSCRIPT, string
: DIM fileSCRIPT, string
: DIM TableName, string
: DIM ErrorMessage, string
: DIM CR, string
: DIM LF, string
: DIM CRLF, string
: DIM TraceFile, string
: DIM MessageTrace, string : //-----------------------------------------------------------------
: //
: //-----------------------------------------------------------------
: Char (13, CR)
: Char (10, LF)
: ConcatEx (CR, LF, CRLF) : // Vérification présence fichier parametres
: set FileNameParams, "i:\mpo\sumer\sqlload.ini"
: FileExists (FileNameParams, success)
: If (success, INIT, FIN_0) : // Lecture parametres
: INIT: IniFileGetKey (FileNameParams, "Parametres",
: "TraceFile", TraceFile)
: Tracing (4, "DROP_CREATE", "Début", TraceFile) : IniFileGetKey (FileNameParams, "Parametres",
: "ReceiverMailAddress", ReceiverMailAddress)
: IniFileGetKey (FileNameParams, "Parametres",
: "SenderMailAddress", SenderMailAddress)
: IniFileGetKey (FileNameParams, "Parametres",
: "SenderPassword", SenderPassword) : IniFileGetKey (FileNameParams, "ORACLE", "profile",
: profile)
: IniFileGetKey (FileNameParams, "DROP_CREATE",
: "dirSCRIPT", dirSCRIPT) : // Start procédure
: Send_Mail_Message ReceiverMailAddress, SenderMailAddress, SenderPassword,
: "24x7 scheduler : Début mise à jour base MPO", "Début mise
: à jour base MPO" : // Connection Base de Données ORACLE
: DatabaseConnect (profile) : // Lancement du traitement
: Set ErrorMessage, ""
: Set OkFlag, true : Set numItemTable, 0
: LoopWhile (OkFlag, ENDLOOP_TABLE) : Add (numItemTable, 1, numItemTable) : // Recherche nom de la prochaine table : ConcatEx ("Item_table", numItemTable, Entry) : IniFileGetKey (FileNameParams, "Tables_ORACLE", Entry, TableName) : IsEqual (TableName, "", success) : If (success, EXIT_LOOP_TABLE, DO_TABLE)
: EXIT_LOOP_TABLE: Break : // Création des TABLES
: DO_TABLE: ConcatEx (dirSCRIPT, "\\", TableName, ".sql",
: FileSCRIPT) : FileExists (FileSCRIPT, success) : If (success, CHECK_TABLE1, FINAL)
: CHECK_TABLE1: Table_Exist (TableName, profile, success) : If (success, DROP_TABLE, CREATE_TABLE) : // Destruction de la table
: DROP_TABLE: ConcatEx ("DROP TABLE ", TableName, bufferSQL) : DatabaseExecute (bufferSQL, counter) : // Création de la table
: CREATE_TABLE: FileReadAll (FileSCRIPT, bufferSQL) : DatabaseExecute (bufferSQL, counter) : // Vérification si la table a été créée : Table_Exist (TableName, profile, success) : If (success, DO_INDEX, ERROR_TABLE)
: ERROR_TABLE: ConcatEx (ErrorMessage, CR, LF, TableName, ErrorMessage) : Continue : // Création des INDEX su la table TABLENAME
: DO_INDEX: Set numItemIndex, 0 : LoopWhile (OkFlag, ENDLOOP_INDEX) : Add (numItemIndex, 1, numItemIndex) : // Construction nom complet fichier SCRIPT INDEX : ConcatEx (dirSCRIPT, "\\", TableName, "_", numItemIndex,
: ".idx", fileSCRIPT) : FileExists (FileSCRIPT, success) : If (success, CREATE_INDEX, EXIT_LOOP_INDEX)
: EXIT_LOOP_INDEX: Break
: CREATE_INDEX: FileReadAll (FileSCRIPT, bufferSQL) : DatabaseExecute (bufferSQL, counter)
: ENDLOOP_INDEX: ENDLOOP_TABLE: // Deconnection Base de Données ORACLE
: DatabaseDisConnect : // Une erreur s'est produite ??
: IsEqual (ErrorMessage, "", success)
: If (success, TRACE, SHOW_ERROR)
: SHOW_ERROR: MessageBox (ErrorMessage)
: GOTO FINAL
: TRACE: Tracing (4, "DROP_CREATE", "Fin", TraceFile)
: GOTO FINAL : //-----------------------------------------------------------------
: // Error Handling
: //-----------------------------------------------------------------
: FIN_0: Tracing (4, "DROP_CREATE", "ERROR: 0", TraceFile)
: ConcatEx ("24x7 schedule r : ERREUR JOB 'DROP_CREATE'", Subject)
: ConcatEx ("FIN_0 : Le fichier de parametres ", FileNameParams,
: " n'existe pas !!!", Message)
: Send_Mail_Message ReceiverMailAddress, SenderMailAddress, SenderPassword,
: Subject, Message
: MessageBox (Message)
: GOTO FINAL : FIN_1: Tracing (4, "DROP_CREATE", "ERROR: 1", TraceFile)
: ConcatEx ("24x7 scheduler : ERREUR JOB 'DROP_CREATE'", Subject)
: ConcatEx ("FIN_1 : La création de la table ", TableName, " a
: echoué !!!", Message)
: Send_Mail_Message ReceiverMailAddress, SenderMailAddress, SenderPassword,
: Subject, Message
: MessageBox (Message)
: GOTO FINAL : FINAL: *************************************************
: 2) INSERTION.SQL
: *************************************************
: CREATE TABLE INSERTION : (ORIGINE_DEVIS varchar2(1) NOT NULL, : NUMERO_DEVIS number(7,0) NOT NULL, : NUMERO_SOUS_DEVIS number(3,0) NOT NULL, : NUMERO_VERSION_DEVIS number(3,0) NOT NULL, : CODE_PAE number(7,0) NOT NULL, : NUMERO_DIFFERENTES_INSERTIONS number(4,0) NOT NULL, : NUMERO_SEQUENCE_INSERTION number(3,0) NOT NULL, : DATE_PREMIERE_PARUTION date NOT NULL, : QUANTITE_COMMANDEE number(9,0) NOT NULL, : EQUIVALENCE_PAGE number(5,3) NULL, : INSERTION_GRACIEUSE varchar2(1) NULL, : REMUNERATION_CONNUE varchar2(1) NOT NULL, : ECHANGE_MARCHANDISE varchar2(1) NULL, : ESPACE_OU_FRAIS_ANNEXE varchar2(1) NOT NULL, : QUANTITE_SERVIE number(9,0) NOT NULL, : DATE_DERNIERE_PARUTION date NOT NULL, : OFFRE_SPECIALE varchar2(1) NULL, : TEXTE_PIGE varchar2(30) NULL, : CODE_GRAVITE_LITIGE varchar2(1) NULL, : INSERTION_REFACTUREE varchar2(1) NULL, : FORFAIT_OU_COMPTE_RENDU varchar2(1) NULL, : TARIF_DE_BASE number(13,2) NULL, : TARIF_BRUT number(13,2) NULL, : TARIF_NET number(13,2) NOT NULL, : ACHAT_DE_BASE number(13,2) NULL, : ACHAT_BRUT number(13,2) NULL, : ACHAT_NET number(13,2) NULL, : ACHAT_NET_NET number(13,2) NOT NULL, : VENTE_DE_BASE number(13,2) NULL, : VENTE_BRUT number(13,2) NULL, : VENTE_NET number(13,2) NULL, : VENTE_NET_NET number(13,2) NOT NULL, : STATUT varchar2(2) NOT NULL, : CODE_FORMAT varchar2(5) NULL, : FIN_ORDRE varchar2(1) NULL, : DATE_FACTURE date NULL, : CODE_PRODUIT_CLIENT varchar2(7) NOT NULL, : CODE_SOUS_PRODUIT_CLIENT varchar2(3) NOT NULL, : CODE_CAMPAGNE number(7,0) NOT NULL, : CODE_VAGUE_CAMPAGNE number(3,0) NOT NULL, : NOM_CAMPAGNE_VAGUE varchar2(30) NOT NULL, : NOM_FORMAT varchar2(30) NULL, : CODE_ENTITE_PUBLICITAIRE char(7) NOT NULL, : NOM_ENTITE_PUBLICITAIRE varchar2(30) NULL, : CODE_SUPPORT number(7,0) NOT NULL, : CODE_SOCIETE char(2) NOT NULL, : CONCATSIZE varchar2(25) NOT NULL, : TEXTE_PIGE_2 varchar2(30) NULL, : TEXTE_PIGE_3 varchar2(30) NULL, : TEXTE_PIGE_4 varchar2(30) NULL, : NUMERO_ORDRE number(7,0) NOT NULL, : NET_CGV number(13,2) NULL, : BRUT_SECODIP number(13,2) NULL, : TVA_ESPACE number(6,2) NULL, : EMPL_PREVU_1 varchar2(30) NULL, : EMPL_PREVU_2 varchar2(30) NULL, : EMPL_PREVU_3 varchar2(30) NULL)
: TABLESPACE PCM_TAB
: STORAGE (INITIAL 60M NEXT 1000K PCTINCREASE 0);
|
