Category Archives: databases

Convert Oracle Date to UNIX Time

Posted on by
1

Oracle unfortunately does not have a UNIX conversion function. However, you can do it mathematically in SQL. I’ve written a function in PHP that will convert a date field to a UNIX timestamp in Oracle.

/**
* Inserts the SQL necessary to format a date to a UNIX time in Oracle.
* @param string Field name to convert.
* @param string (Optional) Field Alias
* @return string The SQL necessary to convert to UNIX time.
*/
function toUnixTime($field, $fieldAlias=null){
if($fieldAlias== null){
$fieldAlias= $field;
}

return "(ROUND((".$field." - to_date('01-01-1970', 'DD-MM-YYYY')) * (86400) + (28800)) ".$fieldAlias;
}





Usage:


$sql = “SELECT “.toUnixTime(“created”).” FROM users”;


or


//CreatedOn alias

$sql = “SELECT “.toUnixTime(“created”, “createdOn”).” FROM users”;

					

		
		
	


				
					
	

		

						
Oracle 10g Grid Agent Woes (+Solution!)

			
						

				Posted on   by  			

			
						

				Reply			

					


				

			
I started to use the Oracle 10g Grid at work, and have been configuring Agents for our various Oracle servers so I would have a central location to monitor them, as well as eventually attempt replication.


The BIGGEST problem I’ve had was trying to get the agents to talk to the 10g Grid Enterprise Manager server. I would do something like this:


> emctl upload


And get something like this:


“EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet”


I’ve read so many different forums on how to handle this error, but none of them solved my problem.


What was going on was the hostname for the 10g grid upload read something like:


Agent URL         : https://some.internal.domain.in:3872/emd/main/

Repository URL    : https://some2.internal.domain.in:1159/em/upload/


The funny thing was, if I went to the Repo URL on the actual server, I get a nice page that says it works, but if I tried it on the server with the agent installed, I wouldn’t get anything at all. After around 13 hours of fiddling around with this, looking at emagent.trc, listener.ora, emd.properties, trying emctl clearstate agent, securing the agent, unsecuring the agent, etc, I found out what was up:


For some reason that fully qualified domain name does not register with the other servers in the network. What I did was in my hosts file, I added a definition for the Repo URL and the IP it went to.


After that, I got:


> emctl upload


EMD upload completed successfully


You don’t know how happy I am right now.


Also, just an FYI: 10g x86 does not install well on a Windows x64 edition. What happens is the entire server hangs on a file in the %agent%/bin directory called fix_4198214.exe. During the installation phase of the agent (or the Grid), when that directory has that file copied over, just stop the installation (does not actually stop, but pauses it), and rename the file to something else. When the installer encounters the file, it’ll give an error message. Ignore it and you can happily proceed with the install :D 

					

		
		
	


				
					
	

		

						
CLOB and PDO, specifically Oracle

			
						

				Posted on   by  			

			
						

				Reply			

					


				

			
So, my new job has me working with Oracle which is in my book, a way different beast compared to mySQL. Everything I know about databases from mySQL basically had to be re-tooled for Oracle.


I spent around 3 hours trying to figure out how to work in a set of prepared statements (one of which returned a value) in PHP PDO, and thought I might share how I did this.


Okay, so I have one stored procedure and one function.


PROCEDURE RETURN_CLOB(:CLOB_ID, :CLOB_OUT IN CLOB) — All this does was search a table with key/ CLOB value pairs. It basically selected a row with the :CLOB_ID, and :CLOB_OUT returned the CLOB value.


FUNCTION CONVERT_CLOB_TO_VARCHAR2(:CLOB_OUT IN CLOB) RETURN VARCHAR2  — this was a function definition that  took in the :CLOB_OUT from RETURN_CLOB and spit it out as a VARCHAR2


Note that I did not write these two procedures, it’s something out of a package I used at work, but was having trouble trying to get them to work in PDO.


I’m using PHP Propel 1.3, but you can easily rewrite this in standard PDO calls, since the $con object is actually a combination of the PDO class and PDO Statement class.

Here’s the code:


$con = Propel::getConnection(‘…’); //All this does is just use PDO to create a connection

/*


The call below is the same as PDOStatement::prepare(). I’ve condensed the following into one line:


DECLARE


CLOB_Output CLOB;


BEGIN


PackageName.RETURN_CLOB( :Clob_Id, CLOB_Output );


:Str := PackageName.CONVERT_CLOB_TO_VARCHAR2( CLOB_Output );


END;


//So the declare part is to define an internal variable in Oracle, which I’ve named CLOB_Output. It is of type CLOB.            The Begin … End block processes the statements in one container.


1. Return_CLOB returns the CLOB data in CLOB_Output


2. Call CONVERT_BLOB_TO_VARCHAR2 with CLOB_Output


3. The converted CLOB is returned into :Str

*/

$stmt = $con->prepare(“DECLARE CLOB_Output CLOB; BEGIN PackageName.RETURN_CLOB( :Clob_Id, CLOB_Output ); :Str := PackageName.CONVERT_CLOB_TO_VARCHAR2( CLOB_Output ); END;”);


$m = “”;


$stmt->bindValue(‘:Clob_Id’, ‘Clob_Id_goes_here’, PDO::PARAM_INT);


$stmt->bindParam(‘:Str’, $m, PDO::PARAM_STR, 32767); //replace 32767 with the size of the VARCHAR2 that you are returning from CONVERT_BLOB_TO_VARCHAR2


$stmt->execute();

//$m now contains the CLOB as a VARCHAR2!

Phew! Anyways, I’m going to say that Propel 1.3 isn’t ready for Oracle yet, and neither is PHP_PDO for Oracle. There’s too many hassles and tricks I had to use to do the things I needed in Oracle, and I’m going to recommend people continue to use the OCI8 extension.