March 3rd, 2010 Matt
I recently had to install SQL Developer on my Linux box at home to communicate with my R12.1.1 instance. I could have used the version that I have installed in my W2K virtual machine (I need some kind of Windows installed to use Workflow Builder, so have a VM running it), but sometimes I just need to access the database without wanting or needing to have a VM running which uses 1GB of RAM. In this post, I will detail the steps that I took to get it working.
Firstly, download the right version from Oracle here. I downloaded the file “sqldeveloper-2.1.1.64.39-no-jre.zip”
Once the program has downloaded, unzip the file into a temporary location. I unzipped mine into /home/Downloads where it created a new subdirectory called “sqldeveloper” for me.
Move the sqldeveloper folder into /opt (you may need root privileges to do this, so either do this as root or use sudo to move the folder).
Navigate to /opt/sqldeveloper and change the privileges on the sqldeveloper.sh file using chmod:
sudo chmod 755 sqldeveloper.sh
Create a new file called “sqldeveloper” in /usr/local/bin/sqldeveloper which contains the following line:
/opt/sqldeveloper/sqldeveloper.sh
Change the permissions on this new file using chmod again:
sudo chmod 755 /usr/local/bin/sqldeveloper
Finally, you can run the program just by typing “sqldeveloper” at the command prompt window. Run this now from a terminal window, and you will be prompted to confirm the location of the JDK:
Oracle SQL Developer
Copyright (c) 1997, 2009, Oracle and/or its affiliates.All rights reserved.
Type the full pathname of a J2SE installation (or Ctrl-C to quit), the path
will be stored in ~/.sqldeveloper/jdk
In my instance, the JVM is located at /usr/lib/jvm/java-6-sun so enter that path and SQL Developer should start. Because the path to ~/.sqldeveloper does not exist yet (it will be created the first time the program runs), you will see errors. Close SQL Developer down and run the command again – you will be prompted to enter the path a second time, which will then be saved to ~/.sqldeveloper/jdk. If you ever change the location of the JVM, you will need to modify this file or SQL Developer will fail.
The last thing that I did was to create a new shortcut in my Ubuntu menu – if you are doing this, then the icon file can be found at /opt/sqldeveloper/icon.png. Now when I click on the icon, SQL Developer starts fine:
Posted in Oracle, Technical | No Comments »
December 4th, 2009 Matt
There’s a line from C3-PO in Attack of the Clones where he sees robots making battle droids, and he says something like “machines making machines! Huh! How perverse”.
So now that I have VMWare running, here’s my version – Linux running Linux 
Posted in General Computing, Technical | No Comments »
October 14th, 2009 Matt
A while back, I wrote this post about the dreaded “existing state of the packages” error when recompiling code that is used in a Workflow process.
There is some degree of official guidance on what you need to do now, contained in Metalink note 754993.1 – “How can you Avoid Bouncing the Database after Changing a WF Package”. The advice is, to be honest, perhaps a little Draconian and unwieldy, but the guidance from Oracle is that all you need to do is:
- Shut down the concurrent managers
- Flush the shard pool
- Update the package
- Restart the concurrent managers
Now, that certainly will do it (so would bouncing the database, though!) – the likelihood of a developer shutting down the managers, then flushing the pool, and THEN compiling their code is probably quite low, though 
Posted in Oracle, Technical | 1 Comment »
July 24th, 2009 Matt
A colleague of mine recently asked me why I always started Oracle programs using a “/nolog” and then explicitly connecting rather than just passing in the username and password in one go. Some years back, I worked with an Oracle guru who showed me exactly why you shouldn’t, just by using one simple command in UNIX:
ps -ef
UID PID PPID C STIME TTY TIME CMD
oracle 4325 5180 0 12:00:04 ? 0:00 sqlplus system/manager
appluser 3118 3012 0 12:00:03 ? 0:00 sqlldr scott/tiger
appluser 26332 24101 0 11:00:15 ? 0:00 imp matt/matt full=y
As you can see – if you pass in the username and password, then anyone can easily find it out using ps!
So, how go you get round it? If you can’t connect from within the tool (as you can in SQL*Plus), you can use a password file, which you should then delete once you have done the job, e.g. “sqlldr parfile=$PWDFILE….” where the file contains “userid=matt/matt”.
In order to make it even more secure, then you could use temporary files for your password file, e.g.
PWDFILE=$(mktemp)
echo "userid=matt/matt" > $PWDFILE
sqlldr parfile=$PWDFILE ....
rm $PWDFILE
mktemp is a utility which will create a temporary file with a unique name.
Posted in Oracle, Technical | No Comments »
July 15th, 2009 Matt
I was playing about recently with some basic calculations and rounding within the Oracle database, using release 11.1.0.6 (and I’m sure it’s the same in earlier databases releases). I’d been busy with Dr. Kawashima’s Brain Training: How Old is Your Brain? on my Nintendo DS and doing the mental calculations, and was thinking about the basic calculation 1/3*3.
Now, following my PEMDAS (I had two years in an American school when I was younger – for those in the UK, think BODMAS) – the sum would be calculated as (1/3) * 3 so would give 1 as the result – try it in any calculator and you should get the same result.
Try it in Oracle, and you get the same:
SQL> SELECT 1/3*3 FROM DUAL;
1/3*3
-----
1
That’s all fine – now try a slight variation:
SQL> SELECT 'OK' FROM DUAL WHERE 1/3*3 = 1;
no rows selected
Hmmmm – so even though it returns 1, it doesn’t equal 1…. Is it true for all values?
SQL> SELECT * FROM DUAL WHERE 1/3*3=1;
no rows selected
SQL> SELECT level, CASE WHEN 1/level*level = 1 THEN 'OK' END ok FROM DUAL CONNECT BY level <= 20;
L O
———- -
1 OK
2 OK
3
4 OK
5 OK
6 OK
7 OK
8 OK
9
10 OK
11
12
13
14
15 OK
16 OK
17 OK
18 OK
19
20 OK
So, there you have it – a handful (and there are more) of examples where 1 !=1 in Oracle!!
Posted in Oracle, Technical | 1 Comment »
July 8th, 2009 Matt
I read this recently on Tom Kyte’s blog about three questions that he asks at interviews:
I have a table:
create table t ( ….., month number, ….. );
Month is always a number between 1 and 12.
I ask three questions about this table:
1) how many rows are in the table
2) how many rows BY MONTH are in the table (i want to know how many rows for month one, month two
and so on)
3) what MONTH has the most rows (and for a special bonus, tell me why this question is ambiguous)
At an interview for a role that I did at some stage, I was asked the following:
I have a table EMP with columns NAME, SALARY, DEPTNO. How would you write a query to give me the top three earners in each department?
I’m not sure if I got the answer right, but I got the gig
Posted in Oracle, Technical | No Comments »
March 10th, 2009 Matt
I wrote this a while back on my old blog, and have only just got round to moving it here…
Some time ago, I started to look into the functionality behind the “Expedite” button in the Workflow monitor, just to see a bit more about what it actually did. This became more important with release 2.6.3 of Workflow, where the button was replaced with the “Rewind” functionality – which is in no way a substitute for it. Finally, I’ve got round to writing my findings up as a technical white paper.
The white paper makes (I think) interesting reading – it shows exactly how you can jump from one activity in a process to another one, whether they are linked together in the process flow or not. I’ve also written some code that allows you to jump to another activity based on the activity ID, or failing that, you can specify the name of the activity instance, item type and process name, and the code will jump to that point in the process flow for you.
A recent client of mine had exactly this requirement – based on some other process logic, they wanted to jump to a completely different point in the process. Whilst this is possible to do diagrammatically (and this is normally the best practice to employ, for sure), in certain circumstances it involves over-complicating the process flow a great deal. In the past, I worked with a customer who wanted to implement a notification at the start of the flow, which allowed the responder to jump to any other activity in the process, so that everything could be revisited at any time – after much more business analysis, we decided that this wasn’t something that they really wanted to do, it was just a management idea of “wouldn’t it be nice if…”, without any business need.
I’ve uploaded the following files as part of the white paper here:
The code is provided without any warranty, etc. etc. – please feel free to use it, but remember to give credit where credit is due
Posted in Oracle, Technical | No Comments »
February 26th, 2009 Matt
Just a quick post, since it’s been a while – I’ve been VERY busy recently, and am only now taking a decent break with the family.
Anyway – reading some of the R12 documentation yesterday, I saw that there is a new “enhancement” to Workflow Notifications in R12, which I suggest is the most useless enhancement for a while.
In R12, there is now a facility to use a document attribute as the subject of an email, as long as the subject that is generated is less than 240 characters long. The “idea” seems to be so that you can “easily” include code top make the subject of a Notification dynamic. Now, forgive me if I’m wrong, but couldn’t you do that in EVERY version of Workflow, by using a text attribute??
What benefit does a document for a subject line give you? I can think of only one – you no longer need to include a PL/SQL activity within the process to set the attribute that is used for the Notification subject. That’s the only one I can think of – and personally, I would rather see an explicit step in the process which says “Set Subject” (or the like) rather than hiding the place where the subject is determined.
I can’t see a situation where I will ever be looking to use a document API instead of setting a text attribute, but you never know – if someone can come up with a reason why you should use it, please let me know!
Posted in Oracle, Technical | No Comments »
January 24th, 2009 Matt
One of the services that I’ve done for a while now through my company, is running design and implementation reviews – spending time with customers getting to understand what they are expecting their Workflow system to do, and then looking at their workflow functional and / or technical design document, or their code that has been written, and providing a detailed analysis of the strengths, weaknesses and opportunities for improvements. One thing that comes up time and time again is that generally the code that has been written to support a Workflow has been written without reference to the Workflow build standards that Oracle wrote some years ago – these have been out there for some time now, but here’s a brief list of what I normally find:
- Procedure signature does not conform to the recommended signature from Oracle;
- Use of hard-coded values for function mode and results rather than using WF_ENGINE constants;
- No logging, or if any is in place, it doesn’t use the Workflow supplied logging package;
- Missing or incorrect error handling.
One thing that this has really got me thinking was about how great it would be if there was some kind of framework that made it easier to write good Workflow code.
So, I sat down and put together a utility to provide well-formatted code, which provides a framework that you can plug in your specific logic. The most important thing that a workflow developer should be focusing on is getting the flow of the process right – here’s where the developer should be devoting their efforts
- Determining what activities are needed in the process;
- Deciding what level of granularity is needed
- Should you have an activity that does lots of processing, a sub-process, or a couple of activities in one process?
- Should you build a series of activities that perform simple steps, or use one activity which has a series of IF conditions in it?
- etc.
- Putting the right activities in the right order;
- Determining error conditions;
- Determining revisit conditions;
- Deciding whether business events can and should be used;
- Deciding whether to defer the process or not.
Instead of which, developers tend to be spending time on writing the PL/SQL to support the Workflow, and not enough time focusing on getting the process(es) right.
I wrote a utility which is designed to help shift this effort from PL/SQL developer onto becoming a Workflow developer. If you install the package attached to this post, a PL/SQL package is installed which can then be used to generate code to support your Workflow. You should define the Workflow process, and this code will then look for a specific Item Type and pick up the PL/SQL activity which you have defined and generate code for you. If you have left any of the activities blank, then the code will create a PL/SQL package with the name of your item type, and procedures within the package named as the internal name of the activity. The code adds logging to the procedures, error handling, and if the activity uses lookup codes, it will also generate the results at the end of the code for you.
It can also update your Workflow for you, if you set it to. So for each Activity where you have not defined the Activity function, the code will generate the code for you to amend to support the business logic, and then update the Workflow with the name of the package and procedure which has been generated to support the process.
The code is copyright TS Fifteen Ltd (the parent company of WorkflowFAQ.com), but I’m prepared to make the code available free for use. If you install the code, then it must include the module header which is supplied with the code. If you make any changes to the code, or have any suggestions for extra functionality, please contact me or add comments below. I will be writing a fuller user guide to add to the module, but here’s the current version.
Package Specification
Package Body
Posted in Oracle, Technical | No Comments »
January 14th, 2009 Matt
I’ve just hit (for the nth time) the limit in DBMS_OUTPUT.PUT_LINE that (prior to 10g) you can only have 1000000 characters in the output. I know that that sounds a lot, but if you’re debugging complex workflows (with lots written out to the screen), and processing multiple flows in the same transaction, then this can easily be blown.
So I “googled” (yes, it’s a verb!) the error that I was getting thrown, and (as ever) found this thread on AskTom.
Tom has provided a new PL/SQL package which replicates pretty much what DBMS_OUTPUT does by storing it in a PL/SQL table, and then has a view on top of it to retrieve (and delete) the data from the table. You can then just select from the view to get the output back.
I’ve copied the code into the attached SQL file, just for ease.
Enjoy!
Matt
SQL script
Posted in Oracle, Technical | No Comments »
