I have struggled in the past and have heard of people struggling with writing a SQL statement in Oracle that would mimic mySQL's command LIMIT.
LIMIT doesn't exist in Oracle and there are two ways of displaying the range of records you want from a table. The first one is to use PLSQL. I'm not going to get into details here but all you have to do is do a SELECT statement INTO a CURSOR datatype, iterate your cursor and exit when your loop counter hits the number of rows you want.
As you can see this solution is not very effective and the best way of doing this is by creating a simple statement using the rownum column.
For instance, if you wanted to select the 10 first items from the item table you would do the following:
SELECT * FROM item WHERE rownnum < 11;
This should return the first 10 rows.
Tuesday, March 2, 2010
Tuesday, December 29, 2009
Enum as a class property in Objective-C
For this post I'd like to show how you can define an enum property in Objective-C.
In a nutshell an enum is a datatype you create where in theory only certain values can be assigned to it and none else.
The code is pretty straight forward. Here it goes:
typedef enum
{
North, South, East, West
} Direction;
@interface MyClassName : NSObject
{
Direction direction;
}
@property(nonatomic) Direction direction;
In the above example only the values North, South, East, and West can be assigned to the direction property of a MyClassName instance.
Tuesday, December 8, 2009
Querying an Oracle database using XSLT for dummies
In this post I will explain how you can query an Oracle database using XSLT technology and Xalan as your XML processor.
Requirements:
Tutorial
1. Open Oxygen and create a new document of type XSLT
Requirements:
- Oracle 10g (or above)
- Oxygen (XML Editor)
Tutorial
1. Open Oxygen and create a new document of type XSLT
2. Copy and paste the XSLT code below:
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:sql="org.apache.xalan.lib.sql.XConnection"
extension-element-prefixes="sql">
<xsl:output method="html" />
<xsl:template match="/">
<xsl:variable
name="movies"
select="sql:new('oracle.jdbc.driver.OracleDriver'
,'jdbc:oracle:thin:@ipaddress:sid','username','password')" />
<xsl:variable name="streaming" select="sql:disableStreamingMode($movies)" />
<xsl:variable
name="queryResults"
select="sql:query($movies,'SELECT movie, actor FROM movie')" />
<html>
<head><title>Oracle Result Set</title></head>
<body style="font-family: sans-serif;">
<table border="1" cellpadding="5">
<tr>
<xsl:for-each select="$queryResults/sql/metadata/column-header">
<th><xsl:value-of select="@column-label" /></th>
</xsl:for-each>
</tr>
<xsl:apply-templates select="$queryResults/sql/row-set/row" />
</table>
</body>
</html>
<xsl:value-of select="sql:close($movies)" />
</xsl:template>
<xsl:template match="row">
<tr><xsl:apply-templates select="col" /></tr>
</xsl:template>
<xsl:template match="col">
<td><xsl:value-of select="text()" /></td>
</xsl:template>
</xsl:stylesheet>
3. Be sure to make your changes accordingly
4. After pasting your code in the text area click on the XSLT debugger button as shown in the image below:
5. Click on the Run button
6. Voilà! Now look at the result, and here is what I got from my XSLT:
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:sql="org.apache.xalan.lib.sql.XConnection"
extension-element-prefixes="sql">
<xsl:output method="html" />
<xsl:template match="/">
<xsl:variable
name="movies"
select="sql:new('oracle.jdbc.driver.OracleDriver'
,'jdbc:oracle:thin:@ipaddress:sid','username','password')" />
<xsl:variable name="streaming" select="sql:disableStreamingMode($movies)" />
<xsl:variable
name="queryResults"
select="sql:query($movies,'SELECT movie, actor FROM movie')" />
<html>
<head><title>Oracle Result Set</title></head>
<body style="font-family: sans-serif;">
<table border="1" cellpadding="5">
<tr>
<xsl:for-each select="$queryResults/sql/metadata/column-header">
<th><xsl:value-of select="@column-label" /></th>
</xsl:for-each>
</tr>
<xsl:apply-templates select="$queryResults/sql/row-set/row" />
</table>
</body>
</html>
<xsl:value-of select="sql:close($movies)" />
</xsl:template>
<xsl:template match="row">
<tr><xsl:apply-templates select="col" /></tr>
</xsl:template>
<xsl:template match="col">
<td><xsl:value-of select="text()" /></td>
</xsl:template>
</xsl:stylesheet>
3. Be sure to make your changes accordingly
4. After pasting your code in the text area click on the XSLT debugger button as shown in the image below:
5. Click on the Run button
6. Voilà! Now look at the result, and here is what I got from my XSLT:
| MOVIE | ACTOR |
|---|---|
| Indiana Jones and the Last Crusade | Alison Doody |
| Indiana Jones and Raiders of the Lost Ark | Harrison Ford |
| Indiana Jones and Raiders of the Lost Ark | Denholm Elliott |
| Indiana Jones and the Last Crusade | Sean Connery |
| Indiana Jones and Raiders of the Lost Ark | Jonn Rhys-Davies |
Friday, February 27, 2009
Turning off the PC Speaker on Ubuntu
Here is how you turn off the PC Speaker on Ubuntu. This command migh even work with other Linux distros.
modprobe -r pcspkr
modprobe -r pcspkr
Friday, February 6, 2009
Deleting files/folders with the same name characteristics in Linux
I was trying to delete the .svn folders from a project in my linux machine and I had two options of doing that. Going folder by folder and find those .svn folders, which were about 20 or delete them all with one line of command connected by a pipe "|".
Here goes the trick and the explanation will follow it.
find /path/projectFolder -type d -name '.svn' | xargs rm -rf
Notice that we start this batch of commands with the find command. The find command takes 3 arguments. The find command will go through all directories and subdirectories starting from the path specified.
The first argument is the path to the folder where you want to perform the lookup of the file/folder. That's pretty straight forward
The second argument is the option "-type" where you define whether you are looking up a file or a directory. In the example given above I defined the type as being a directory. If I wanted to do a lookup for a file I would have an "f" there rather than a "d".
The third argument is the option "-name" where you define the name of the file or folder you want to look for enclosed by single quotes. You can also use wildcards in the name.
The second command I want to talk about is the rm -rf. This command removes all folders that are empty or has something in it.
Now here comes the trick. Every line that the find command returns is passed in to rm -rf through | xargs.
Now, having that in mind if you read from right to left this line commands should make more sense to you.
Google the commands find and xargs to see some cool stuff that you can do with them.
Hope this helps!
Here goes the trick and the explanation will follow it.
find /path/projectFolder -type d -name '.svn' | xargs rm -rf
Notice that we start this batch of commands with the find command. The find command takes 3 arguments. The find command will go through all directories and subdirectories starting from the path specified.
The first argument is the path to the folder where you want to perform the lookup of the file/folder. That's pretty straight forward
The second argument is the option "-type" where you define whether you are looking up a file or a directory. In the example given above I defined the type as being a directory. If I wanted to do a lookup for a file I would have an "f" there rather than a "d".
The third argument is the option "-name" where you define the name of the file or folder you want to look for enclosed by single quotes. You can also use wildcards in the name.
The second command I want to talk about is the rm -rf. This command removes all folders that are empty or has something in it.
Now here comes the trick. Every line that the find command returns is passed in to rm -rf through | xargs.
Now, having that in mind if you read from right to left this line commands should make more sense to you.
Google the commands find and xargs to see some cool stuff that you can do with them.
Hope this helps!
Friday, December 5, 2008
Configure JBoss for Oracle
- Oracle and JBoss use the same port by default, 8080. If you are using Oracle and JBoss on the same server, modify the http port for Oracle to something other than 8080 (i.e, 8081). Skip this step if you are running Oracle on a different server.
- JBoss needs to know where the Oracle jdbc driver classes are located. This can be done by either, copying the Oracle jdbc archive file to the server default lib folder. Search and locate the ojdbc14.jar file in the Oracle \jdbc\lib folder. Copy the ojdbc14.ja file to the JBoss \server\default\lib folder.
- Define a datasource in JBOSS to access a specific Oracle database instance. Copy the /docs/examples/jca/oracle-ds.xml file from the jboss directory to the /server/default/deploy folder in JBoss. Open a text editor (e.g., WordPad or xCode) and modify the file as follows.
- Modify the standardjbosscmp-jdbc.xml configuration file in the JBoss /server/default/conf folder. Add the following and sub-tags to the tag. These tags map the datasource, OracleDS, to the data mapping, Oracle9i, defined further down in this file. The data mapping tag defines how the specific Oracle database types map to the corresponding Java data types.
Login to Oracle using SQLPLUS as the SYSTEM user and enter the following script command:
BEGIN
dbms_xdb.sethttpport(’8081′);
END;
/
Locate the online_help file in the Oracle \server folder, right click, select properties, and modify the url to:
http://127.0.0.1:8081/apex/wwv_flow_help.show_help?p_flow_id=4500&p_step_id=1000
Locate the postDBCreation file in the Oracle server\config\log folder, open it and modify the following line:
URL=http://127.0.0.1:8081/htmldb/wwv_flow_help.show_help
…
…
…
Configure JBoss for Oracle
- Oracle and JBoss use the same port by default, 8080. If you are using Oracle and JBoss on the same server, modify the http port for Oracle to something other than 8080 (i.e, 8081). Skip this step if you are running Oracle on a different server.
- JBoss needs to know where the Oracle jdbc driver classes are located. This can be done by either, copying the Oracle jdbc archive file to the server default lib folder. Search and locate the ojdbc14.jar file in the Oracle \jdbc\lib folder. Copy the ojdbc14.ja file to the JBoss \server\default\lib folder.
- Define a datasource in JBOSS to access a specific Oracle database instance. Copy the /docs/examples/jca/oracle-ds.xml file from the jboss directory to the /server/default/deploy folder in JBoss. Open a text editor (e.g., WordPad or xCode) and modify the file as follows.
- Modify the standardjbosscmp-jdbc.xml configuration file in the JBoss /server/default/conf folder. Add the following and sub-tags to the tag. These tags map the datasource, OracleDS, to the data mapping, Oracle9i, defined further down in this file. The data mapping tag defines how the specific Oracle database types map to the corresponding Java data types.
Login to Oracle using SQLPLUS as the SYSTEM user and enter the following script command:
BEGIN
dbms_xdb.sethttpport(’8081′);
END;
/
Locate the online_help file in the Oracle \server folder, right click, select properties, and modify the url to:
http://127.0.0.1:8081/apex/wwv_flow_help.show_help?p_flow_id=4500&p_step_id=1000
Locate the postDBCreation file in the Oracle server\config\log folder, open it and modify the following line:
URL=http://127.0.0.1:8081/htmldb/wwv_flow_help.show_help
…
…
…
Subscribe to:
Posts (Atom)
