[icinga-checkins] icinga.org: icinga-core/test/ido: * idoutils: oracle related changes

git at icinga.org git at icinga.org
Fri Jun 24 10:19:56 CEST 2011


Module: icinga-core
Branch: test/ido
Commit: 5f9712f69c3bedf8a13f48f626860e5b1f1a5f79
URL:    https://git.icinga.org/?p=icinga-core.git;a=commit;h=5f9712f69c3bedf8a13f48f626860e5b1f1a5f79

Author: Thomas Dressler <tdressler at tdressler.net>
Date:   Sat Jun 18 15:31:59 2011 +0200

* idoutils: oracle related changes
usage of newly created functions in earlier commited code,
change memory allocation in execute_out function,
change flow in set_event function,
changed debug messages

---

 .../db/oracle/create_icinga_objects_oracle.sql     |   38 +++++---------
 .../db/oracle/upgrade/oracle-upgrade-1.5.0.sql     |   37 +++++---------
 module/idoutils/include/db.h                       |    2 +-
 module/idoutils/src/db.c                           |   54 +++++++++++++-------
 4 files changed, 63 insertions(+), 68 deletions(-)

diff --git a/module/idoutils/db/oracle/create_icinga_objects_oracle.sql b/module/idoutils/db/oracle/create_icinga_objects_oracle.sql
index bbbe852..ace8c88 100644
--- a/module/idoutils/db/oracle/create_icinga_objects_oracle.sql
+++ b/module/idoutils/db/oracle/create_icinga_objects_oracle.sql
@@ -153,8 +153,6 @@ END;
 CREATE or replace procedure set_trace_event(trace_level integer) 
 /*
 requires explicit alter session privilege
-select on v$session and v$process is recommanded
---trace_level valid values (for explanations see oracle docs)
 0 - pseudo level TRACE OFF
 1 – standard SQL trace no, no wait events, or bind variables.
 4 – Bind variables only
@@ -167,10 +165,10 @@ select on v$session and v$process is recommanded
     output varchar(200);
     mypid integer;
     myfile varchar2(255);
-  no_table EXCEPTION;
-  no_rights EXCEPTION;
-  invalid_name exception;
-  invalid_level Exception;
+    no_table EXCEPTION;
+    no_rights EXCEPTION;
+    invalid_name exception;
+    invalid_level Exception;
   
   PRAGMA EXCEPTION_INIT(no_table, -942);
   PRAGMA EXCEPTION_INIT(invalid_name, -904);
@@ -192,20 +190,11 @@ select on v$session and v$process is recommanded
       output:='Session trace event set to level '||to_char(trace_level)|| ' for SID '||to_char(mysid);
     end if;
     --dbms_output.put_line('Execute:'||text);
-    begin
-      execute immediate text; 
-    exception
-    /* surpress errors*/
-    when no_rights then
-      /* ora 1031 indicates no alter session priviledge */
-      dbms_output.put_line('Error: No "Alter session" right');
-    
-    end;
-    
+    execute immediate text;   
     dbms_output.put_line(output);
     /* optional */
-    begin
-      if trace_level>0 then
+    
+    if trace_level>0 then
         text:='select p.spid  from v$process p,v$session s where s.paddr=p.addr and s.sid='||to_char(mysid);
         --dbms_output.put_line('Execute:'||text);
         EXECUTE IMMEDIATE text  into mypid;
@@ -223,8 +212,12 @@ select on v$session and v$process is recommanded
            dbms_output.put_line(sqlerrm);
         end;
         dbms_output.put_line(output);
-      end if;
+    end if;
     exception
+    /* surpress errors*/
+    when no_rights then
+      /* ora 1031 indicates no alter session priviledge */
+      dbms_output.put_line('Error: No "Alter session" right');
     when invalid_level then
       dbms_output.put_line('Error:Only levels 0,1,4,8,12 are valid');
     when no_table then
@@ -232,12 +225,9 @@ select on v$session and v$process is recommanded
         dbms_output.put_line('Warning:No access to v$session and/or v$process');
     when others then
         dbms_output.put_line('Warning:Cannot get ProcessID:'||sqlerrm);      
-    end;
-    
-    
 END set_trace_event;
-/ 
-  
+/
+
 -- --------------------------------------------------------
 -- database table creation: icinga
 -- --------------------------------------------------------
diff --git a/module/idoutils/db/oracle/upgrade/oracle-upgrade-1.5.0.sql b/module/idoutils/db/oracle/upgrade/oracle-upgrade-1.5.0.sql
index 84bf1bc..6ee3839 100644
--- a/module/idoutils/db/oracle/upgrade/oracle-upgrade-1.5.0.sql
+++ b/module/idoutils/db/oracle/upgrade/oracle-upgrade-1.5.0.sql
@@ -36,8 +36,6 @@ spool oracle-upgrade-&&ICINGA_VERSION..log
 CREATE or replace procedure set_trace_event(trace_level integer) 
 /*
 requires explicit alter session privilege
-select on v$session and v$process is recommanded
---trace_level valid values (for explanations see oracle docs)
 0 - pseudo level TRACE OFF
 1 – standard SQL trace no, no wait events, or bind variables.
 4 – Bind variables only
@@ -50,10 +48,10 @@ select on v$session and v$process is recommanded
     output varchar(200);
     mypid integer;
     myfile varchar2(255);
-  no_table EXCEPTION;
-  no_rights EXCEPTION;
-  invalid_name exception;
-  invalid_level Exception;
+    no_table EXCEPTION;
+    no_rights EXCEPTION;
+    invalid_name exception;
+    invalid_level Exception;
   
   PRAGMA EXCEPTION_INIT(no_table, -942);
   PRAGMA EXCEPTION_INIT(invalid_name, -904);
@@ -75,20 +73,11 @@ select on v$session and v$process is recommanded
       output:='Session trace event set to level '||to_char(trace_level)|| ' for SID '||to_char(mysid);
     end if;
     --dbms_output.put_line('Execute:'||text);
-    begin
-      execute immediate text; 
-    exception
-    /* surpress errors*/
-    when no_rights then
-      /* ora 1031 indicates no alter session priviledge */
-      dbms_output.put_line('Error: No "Alter session" right');
-    
-    end;
-    
+    execute immediate text;    
     dbms_output.put_line(output);
     /* optional */
-    begin
-      if trace_level>0 then
+    
+    if trace_level>0 then
         text:='select p.spid  from v$process p,v$session s where s.paddr=p.addr and s.sid='||to_char(mysid);
         --dbms_output.put_line('Execute:'||text);
         EXECUTE IMMEDIATE text  into mypid;
@@ -106,8 +95,12 @@ select on v$session and v$process is recommanded
            dbms_output.put_line(sqlerrm);
         end;
         dbms_output.put_line(output);
-      end if;
+    end if;
     exception
+    /* surpress errors*/
+    when no_rights then
+      /* ora 1031 indicates no alter session priviledge */
+      dbms_output.put_line('Error: No "Alter session" right');
     when invalid_level then
       dbms_output.put_line('Error:Only levels 0,1,4,8,12 are valid');
     when no_table then
@@ -115,12 +108,8 @@ select on v$session and v$process is recommanded
         dbms_output.put_line('Warning:No access to v$session and/or v$process');
     when others then
         dbms_output.put_line('Warning:Cannot get ProcessID:'||sqlerrm);      
-    end;
-    
-    
 END set_trace_event;
-/ 
-  
+/
 
 -- -----------------------------------------
 -- finally update dbversion
diff --git a/module/idoutils/include/db.h b/module/idoutils/include/db.h
index 8e41572..328388d 100644
--- a/module/idoutils/include/db.h
+++ b/module/idoutils/include/db.h
@@ -163,7 +163,7 @@ void ido2db_ocilib_err_handler(OCI_Error *);
 unsigned long ido2db_ocilib_insert_id(ido2db_idi *, char *);
 int ido2db_oci_prepared_statement_bind_null_param(OCI_Statement *, char *);
 int ido2db_oci_set_trace_event(OCI_Connection *,unsigned int);
-int ido2db_oci_execute_out(OCI_Connection *,OCI_Statement *,char *);
+int ido2db_oci_execute_out(OCI_Statement *,char *);
 int ido2db_oci_set_appinfo(OCI_Connection *, char *);
 void ido2db_oci_print_binds(OCI_Statement *,int,char **);
 void ido2db_oci_statement_free(OCI_Statement *,char *);
diff --git a/module/idoutils/src/db.c b/module/idoutils/src/db.c
index 3cf62c4..fe863bc 100644
--- a/module/idoutils/src/db.c
+++ b/module/idoutils/src/db.c
@@ -6158,36 +6158,53 @@ int ido2db_oci_set_trace_event(OCI_Connection *cn,unsigned int trace_level) {
 	 	 return IDO_ERROR;
 	 }
 	 /* execute statement and log output */
-	 ret=ido2db_oci_execute_out(cn,st,fname);
-	 if (ret==IDO_OK) {
-		 ido2db_log_debug_info(IDO2DB_DEBUGL_PROCESSINFO, 2, "%s:Event set successfully\n",fname);
-	 }else{
+	 ret=ido2db_oci_execute_out(st,fname);
+	 if (ret!=IDO_OK) {
 		 ido2db_log_debug_info(IDO2DB_DEBUGL_PROCESSINFO, 2, "%s:Event set failed\n",fname);
 	 }
-	 OCI_StatementFree(st);
+	 ido2db_oci_statement_free(st,fname);
+	 ido2db_log_debug_info(IDO2DB_DEBUGL_PROCESSINFO, 2, "%s end\n",fname);
 	 return ret;
 }
 /**
  * executes a statement and handle DBMS_OUTPUT
- * @param OCI_Connection
- * @param OCI_Statement
+ * @param OCI_Statement statement to execute
  * @return IDO_OK
  */
-int ido2db_oci_execute_out(OCI_Connection *cn,OCI_Statement *st, char * fname) {
+int ido2db_oci_execute_out(OCI_Statement *st, char * fname) {
 	const dtext *p;
 	int ret;
-	/* print binds in Level SQL */
-	char binds[1600];
-	ido2db_oci_print_binds(st,sizeof(binds),(char **)binds);
-	ido2db_log_debug_info(IDO2DB_DEBUGL_SQL, 2, "%s Binds:%s\n",fname,binds);
+	OCI_Connection *cn;
+
+	/* check parameter */
+	if (!st) {
+		ido2db_log_debug_info(IDO2DB_DEBUGL_PROCESSINFO, 2, "ido2db_oci_execute_out: %s No valid statement handle supplied\n",fname);
+		return IDO_ERROR;
+	}
+	if (!fname) {
+		fname=strdup("ido2db_oci_execute_out");
+	}
+	/* get current connection */
+	cn=OCI_StatementGetConnection(st);
 
+	/* print binds in Level SQL */
+	char * binds=NULL;
+	binds=malloc(OCI_VARCHAR_SIZE*4);
+	if (binds) {
+		ido2db_oci_print_binds(st,sizeof(binds),(char **)binds);
+		ido2db_log_debug_info(IDO2DB_DEBUGL_SQL, 2, "%s Binds:%s\n",fname,binds);
+		free(binds);
+	}
+	/**
+	 * enable dbms_output, execute statement and retrieve dbms_output lines
+	 */
 	OCI_ServerEnableOutput(cn , 32000, 1, 2000);
 	ret=OCI_Execute(st);
 	while ((p = OCI_ServerGetOutput(cn)))
 	{
 		ido2db_log_debug_info(IDO2DB_DEBUGL_PROCESSINFO, 2, "%s DBMSOUT:%s\n",fname,p);
 	}
-
+	/* return execute status */
 	if (!ret) {
 		return IDO_ERROR;
 	}
@@ -6236,14 +6253,13 @@ int ido2db_oci_set_appinfo(OCI_Connection *cn, char * action) {
 		   	 return IDO_ERROR;
 		 }
 		 /* execute statement */
-		 ret=ido2db_oci_execute_out(cn,st,fname);
-		 if (ret==IDO_OK) {
-			 ido2db_log_debug_info(IDO2DB_DEBUGL_PROCESSINFO, 2, "%s:AppInfo set successfully to %s:%s\n",fname,module,action);
-		 }else{
-			 ido2db_log_debug_info(IDO2DB_DEBUGL_PROCESSINFO, 2, "%s:AppInfo set failed\n",fname);
+		 ret=ido2db_oci_execute_out(st,fname);
+		 if (ret!=IDO_OK) {
+			 ido2db_log_debug_info(IDO2DB_DEBUGL_PROCESSINFO, 2, "%s:AppInfo execute failed\n",fname);
 		 }
-		 OCI_StatementFree(st);
+		 ido2db_oci_statement_free(st,fname);
 		 if (app_info) free(app_info);
+		 ido2db_log_debug_info(IDO2DB_DEBUGL_PROCESSINFO, 2, "%s end\n",fname);
 		 return ret;
 }
 /**





More information about the icinga-checkins mailing list