Oracle Functions Tips

This is a quick list of tips to keep in mind when working with Oracle (being a complete Oracle newb, I wish someone would have put these up on a blog instead of having to search for them).

Getting setup to do SQL commands Oracle
In the Oracle Enterprise Manager Console, you can do "straight-SQL" to Oracle. Click on the left icon "Database Applications". It will expand out to show a few icons. Click on the "SQL+ Plus Worksheet". This will open a SQL editor similar to SQL Server Enterprise Manager's (well, not that similar because Oracle's tools suck).

Now you need to connect to the session. Go to File -> Change Database Connection.

A prompt will come up to login to the database specified. Enter the UserName, Password, and Database and click OK.

Now use the command:

alter session set current_schema = [your database name]

Now you are connected and ready to run SQL commands in the editor window.

Creating an Oracle Function
You can create an Oracle function by using the CREATE FUNCTION command. Here's an easy example that shows how to return a varchar2 from a standard function:

my_function_name(MyFunctionParameter in int)
RETURN varchar2
IS MyVariableName varchar2(4000);
BEGIN
SELECT UsersTable.UserName
INTO MyVariableName
FROM UsersTable
WHERE UsersTable.PrimaryKey = MyFunctionParameter
RETURN(MyVariableName);
END;

Calling an Oracle Function in SQL+ Plus
In order to call a function in the query window we created earlier, we have to use some wonky syntax:

variable varMyReturnValue varchar2
exec :varMyReturnValue := my_function_name(1000);
print :varMyReturnValue;

An alternative syntax you can use is:

select my_function_name(1000) from dual;