User input in SQL

Sometimes you need to accept user input in an Oracle SQL script. There are a couple of ways to achieve this. 

Suppose you have a Student table that holds information about all students in a school. You want to display all students that share a specific first name. You want the user to be able to enter this first name and the script should take care of the rest. You could use:

SELECT 

std_id

std_fname, 

std_lname 

FROM 

student

WHERE 

std_fname = '&fname';

Oracle will automatically ask the user to enter the desired value to substitute &fname with.

In a script in which you only reference &fname once, this works perfectly. However, sometimes you want to refer to this value more than once in the script. Oracle would ask you for the value each time. 

A solution to this is to use double ampersands &&. The script would then say 

WHERE

std_fname = '&&fname';

What Oracle does in this case is store the &&fname value in memory for the duration of the current session. That way, if in subsequent commands you need to use the same user-provided value, it will be readily available and require no further user input. The problem with this is, sometimes you want to use this same value, but you may at other times in the same session require the user to provide another value for it. If you call this script twice within the same session, it will use the same value the second time, without requiring the user's input.

The way around this is to use the PROMPT...ACCEPT clause.

PROMPT

ACCEPT FNAME PROMPT "Please enter the student's first name: "

Then you'd reference fname in this way:

WHERE 

std_fname = '&fname';

This method will allow for user input every time the script is run, and allow for different first names to be used for comparison within the same session.

More about EXISTS and IN

This is a very useful link that will definitely answer your questions regarding whether to use EXISTS or IN on your SQL statements.


http://asktom.oracle.com/pls/asktom/f?p=100:11:3263397840860471::::P11_QUESTION_ID:953229842074

Basically the rule of thumb is:

Small query, large subquery = EXISTS
Large query, small subquery = IN
Large query, large subquery = either

Oracle Spool command

The Oracle Spool command is a nice little feature that allows you to record what goes on in your sqlplus session on the fly. Enabling it is simple and can be a very good logging/debugging tool.

To record your sqlplus session commands, simply type:

SQL> spool /tmp/myfile.txt

where myfile.txt is the name you want to give to your log file.
The file will not appear or be usable until you turn off spooling:

SQL> spool off


Voila!

EXISTS is your friend

I recenty came across a problem that required me to retrieve data from a table based on certain criteria. I decided to use the SQL 'IN' condition. This, as I learned later, was a poor choice given the fact that the subquery in the 'IN' clause returned several hundred thousand records. 

My query construct was the following: 

SELECT foo, bar FROM table1 

WHERE foo IN (SELECT foo FROM table2 WHERE condition1 AND condition2)

AND bar IN (SELECT bar FROM table2 WHERE condition1 AND condition2);

Each of those two subqueries returned ~260,000 records, which, needless to say, took forever and a day to complete. I later discovered that the IN clause should mostly be used for small result sets, basically for when the subquery returns less than 1024 records. For result sets as massive as the ones I was getting, it is better to use the EXISTS clause. My query above could easily be modified to use EXISTS in the following way:

SELECT table1.foo, table1.bar FROM table1

WHERE EXISTS (SELECT table2.foo, table2.bar from table2 

                                WHERE  table1.foo = table2.foo 

AND table1.bar = table2.bar

AND condition1

AND condition2);

As you can see, you have to join the two tables being used in order for it to work, otherwise EXISTS will return true and the query will just return the results of the master query.

Remember, when dealing with large result sets, EXISTS is your friend!

Random numbers in Oracle

Oracle provides a set of neat functions to generate and manipulate random numbers and strings (Yes, random strings!). Here's an example of the generation of a random number:

select dbms_random.random from dual;

Result: 688382086

The following example allows you to limit the generated number between 1 and 1000:

select dbms_random.value(1,1000) num from dual;

Result: 800.627594089089637807565678078832430488

Yikes! This number has a gazillion digits after the decimal point. So let's round it up:

select round(dbms_random.value(1,1000),0) num from dual;

Result: 709


Conclusion:
Random number generation is a useful tool that can have many applications in your SQL code.

A more complete list of things you can do with this is here:
http://www.databasejournal.com/features/oracle/article.php/3341051

Chuck Norris Facts

A lot of people with time on their hands have been adding "Chuck Norris facts" to this ongoing list, which contains several things Chuck Norris can do that you can't.

This is some serious hilarity:

'Chuck Norris’ hand is the only hand that can beat a Royal Flush.'

See the rest at http://chucknorrisfacts.com/

Connecting to an Oracle database using JDBC

Oracle has a proprietary JDBC driver that allows for native Java connections to an Oracle Database. First, you must download Oracle's JDBC Driver here and then include the jar archive into your project.

The code to connect to the database would be as follows:

public void createDBConn() {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@//server.local:1521/prod";
Connection conn = DriverManager.getConnection(url,"scott", "tiger");
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");

while (rset.next()) {
System.out.println (rset.getString(1));
}
stmt.close();
System.out.println ("Ok.");
}