Friday, 23 November 2012

Application Migration - Part 3

Ok, Finally, we have got to part 3 of Application Migration.  In Part 1, we outlined a program which runs in Sybase through iSQL.  We then followed this, in part 2 with 2 important pieces.
  1. Recognizers to identify the file types of the source we post
  2. Rules to identify items within the files and report on the them
In this part, We will take the rules we used for the previous part, and add some replacement rules.  So, lets recap.  Our recogniser is set for shell files as below.

<?xml version="1.0" encoding="UTF-8"?>
<rulesfile version="1.0" name="Shell file recognizer" description="Recognize .sh files">
  <recognizer name="sh" description="sh recognizer" enabled="true">
    <fileExtension extension="sh" />
    <expression><![CDATA[#!/bin/sh]]></expression>
  </recognizer>
</rulesfile>


Our rules file is now extended to include replacement rules.  Looking at the rules file below, we have the two main sections
  1. The required section, which defines the expressions which are used to see if we should scan a file
  2. Rules section which can have 3 sections
    1. Example clause which contains the expression which shows what should be found by the rule.
    2. Expression clause which defines a regular expression which is used to identify the items to be change
    3. Replacement clause which can be configured in 3 different stances to cope with different replacements.
Review the file below:

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="../schema/scanner.xsd"?>
<rulesfile version="1.0" name="Sample sh scanner rules"
    description="Sample rules to show whats possible" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation="../../schema/scanner.xsd">
    <ruleset name="isql sample rules" enable="true" type="SYBASE"
        description="" source="sh" codetype="isql">
        <required>
            <regex>
                <expression><![CDATA[go|isql]]></expression>
            </regex>
        </required>
        <rules>
            <regex>
                <expression><![CDATA[go *\n]]></expression>
                <replacement type="text"><![CDATA[]]>
                </replacement>
            </regex>
            <regex>
                <expression><![CDATA[use.*\n]]></expression>
                <replacement type="regex"><![CDATA[]]>
                </replacement>
            </regex>
            <regex>
                <expression><![CDATA[isql.*EOF]]></expression>
                <replacement type="regex"><![CDATA[sqlplus barry/barry <<EOF]]></replacement>
            </regex>
        </rules>
    </ruleset>
    <ruleset name="sql sample rules" enable="true" type="SYBASE"
        description="" source="sh" codetype="sql">
        <required>
            <regex>
                <expression><![CDATA[select]]></expression>
            </regex>
        </required>
        <rules>
            <regex>
                <expression><![CDATA[select.*\n]]></expression>
                <replacement type="translator"/>
            </regex>
        </rules>
    </ruleset>
</rulesfile>


The replacement tags are
  1. Text
    • This is the simplest type of replacement, taking the source strings found and replacing them with the string in the replacement tag.
  2. regex
    • The regular expression replacement can either simply replace text, or it can also use regular expressions to rearrange the string that was found.  For example,  function(a,b,c) can be switched to myfunction(c,a,b)
  3. Translator
    • The translator type allows the user to take the string found and pass it to a language translator denoted by the type.  In our example, the type is SYBASE, which will call our sybase translator and translate the source string.
In the rules file above, we have 2 rulesets defined, the first doing text and regex replacements, and the second doing translator replacements.  All these can be mixed together, though.  If you have a lot of rules, it makes sense to delineate them in rulesets so the tool can filter out what is not required.
Now, taking a look at the source we had in Part 1

bamcgill-macbook-pro:src bamcgill$ cat test.sh
#!/bin/sh
isql -UMYUSER -PMYPASS -SMYSERVER <<EOF
use pubs2
go
select count(*) from authors
go
select top 5 au_lname,au_fname,postalcode from authors
go
EOF
bamcgill-macbook-pro:src bamcgill$ 

we can now run the scanner and make the replacements.  Using a similar command to the that used in part 1, We can replace code in this script.

bamcgill-macbook-pro:demo bamcgill$ migration -actions=scan -dir=/Users/bamcgill/code/demo/src -rulesdir=/Users/bamcgill/code/demo/rules -inplace=true

Now, we when we look at the file, test.sh again, we have

#!/bin/sh
sqlplus barry/barry <<EOF
SELECT COUNT(*) 
  FROM authors ;

SELECT au_lname ,
       au_fname ,
       postalcode 
  FROM authors  WHERE ROWNUM <= 5;

EOF

So, there we a translated file which was running iSQL on Sybase, but is now running SQL*Plus and Oracle specific SQL.

Now, if you are interested, heres what the scanner said as it was running.  It prints out individual statements as they are found and where in the source they were found. At the end, the tool summarises what was found and where.
bamcgill-macbook-pro:demo bamcgill$ migration -actions=scan -dir=/Users/bamcgill/code/demo/src -rulesdir=/Users/bamcgill/code/demo/rules -inplace=true

Oracle SQL Developer
 Copyright (c) 1997, 2011, Oracle and/or its affiliates. All rights reserved. 

Finding files....
Default Application Name

                test.sh
                        3:go

                        5:go

                        7:go

                        2:use pubs2

                        1:isql -UMYUSER -PMYPASS -SMYSERVER <<EOF
                        2:select count(*) from authors

                        3:select top 5 au_lname,au_fname,postalcode from authors

------------------------ Application Results -----------------

Call Breakdown Summary by File
------------------------------
/Users/bamcgill/code/demo/src/test.sh
        3:      go

        1:      use pubs2

        1:      isql -UMYUSER -PMYPASS -SMYSERVER <<EOF
        1:      select count(*) from authors

        1:      select top 5 au_lname,au_fname,postalcode from authors


-------------------------------------------------------------

Call Breakdown Summary
----------------------
        3:      go

        1:      use pubs2

        1:      isql -UMYUSER -PMYPASS -SMYSERVER <<EOF
        1:      select count(*) from authors

        1:      select top 5 au_lname,au_fname,postalcode from authors


-------------------------------------------------------------

File Type Summary
----------------------
         sh      1 file

-------------------------------------------------------------
------------------------    Summary   -----------------------
High Level Overview
-------------------
        7 total calls found
        5 distinct calls found
        1 files scanned
        1 language types
        2 total files in source
        9 lines of code in scanned application
-------------------------------------------------------------
-------------------------------------------------------------
scan completed successfully
If you want to know more about this, drop me line on twitter @bamcgill, or by email on barry.mcgillin@oracle.com

You can download SQL Developer from OTN.

Thursday, 19 July 2012

Using History Keys in SQL*Plus

I was working through a bug the other day and using SQL*Plus, which for the most part doesn't annoy me too much.  However, one of the things that does, is having to retype lots of stuff. (We dont have that problem in SQL Developer).

Having hunted around for a few minutes, I found rlwrap which is a GNU readline wrapper.  All this means is that when we use it on SQL*Plus, it give us keyboard history and user defined completion.  I've found a few posts about it too, which are referred to below, but I wanted to do this for our virtual machine.

We use our Oracle Developer Days VM a lot internally as its great for spooling a DB having a full environment ready to play with and test features.  I'm using that for this post.

You can download rlwrap from here.  There are also RPMs available too.  I pulled down the tar ball.   Expand it out and you have a bunch of files for a standard build

Firstly, we need to run the ./configure script to find all the dependencies.  You can see a cut down version of the output of that below.



checking for tgetent in -lcurses... no
checking for tgetent in -lncurses... no
checking for tgetent in -ltermcap... no
configure: WARNING: No termcap nor curses library found
checking for readline in -lreadline... no
configure: error: 

You need the GNU readline library(ftp://ftp.gnu.org/gnu/readline/ ) to build
this program!


[root@localhost rlwrap-0.37]#

Running configure on my system flagged that I didnt have the readline package installed.   However, when I went to install it with

[root@localhost ~]# yum install readline
Loaded plugins: security
Setting up Install Process
Package readline-5.1-3.el5.i386 already installed and latest version
Nothing to do

I discovered it was already installed.  A quick look through the config.log tho, from the configure process shows that the -lreadline library dependency could not be satisfied.  It needed the development package to build.


[root@localhost rlwrap-0.37]# yum install readline-devel


Total download size: 202 k
Is this ok [y/N]: y
Downloading Packages:
(1/2): libtermcap-devel-2.0.8-46.1.i386.rpm              |  56 kB     00:00     
(2/2): readline-devel-5.1-3.el5.i386.rpm                 | 146 kB     00:01     
--------------------------------------------------------------------------------
Total                                            85 kB/s | 202 kB     00:02     
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing     : libtermcap-devel                                         1/2 
  Installing     : readline-devel                                           2/2 

Installed:
  readline-devel.i386 0:5.1-3.el5                                               

Dependency Installed:
  libtermcap-devel.i386 0:2.0.8-46.1                                            

Complete!
[root@localhost rlwrap-0.37]# 


Ok, Now to try configure again..

configure: creating ./config.status
config.status: creating Makefile
config.status: creating filters/Makefile
config.status: creating doc/Makefile
config.status: creating src/Makefile
config.status: creating doc/rlwrap.man
config.status: creating config.h
config.status: executing depfiles commands

Now do:
    make (or gmake)  to build rlwrap
    make check       for instructions how to test it
    make install     to install it

[root@localhost rlwrap-0.37]# 


Running the configure again, succeeded creating my makefile. Great.  Now run the following to build it and install it in the right place and we should be getting places.


[root@localhost rlwrap-0.37]# make

and

[root@localhost rlwrap-0.37]# make install


Great. Now, rlwrap is installed in /usr/local/bin and we can use it in our oracle terminal window.


[oracle@localhost rlwrap-0.37]$ rlwrap
Usage: rlwrap [options] command ...

Options:
  -a[password:]              --always-readline[=password:]
  -A                         --ansi-colour-aware
  -b  <chars>                --break-chars=<chars>


Now we can use rlwrap to run SQL*Plus, which gets me back to what I wanted to do at the start.  I've kicked this off with the '-c' option.


[oracle@localhost ~]$ rlwrap -c sqlplus barry/barry

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jul 19 17:51:51 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

BARRY@ORCL>

Now my up and down arrows work AND with the '-c' option for rlwrap, we get filename completion for free.

BARRY@ORCL> @re
remote.sql          reset_imdbcache     reset_xmldb
repos/              reset_sqldev        reset_xmldb~
reset_OE.sql        reset_svn           
reset_apex          reset_xdbPorts.sql  
BARRY@ORCL> @re


So, now I'm a lot happier and can zip through loading files and getting my previous statements.

Now, I know there are issues with using this when we redirect files into SQL*Plus, on other blogs like this from Lutz Hartmann, but for me and working with plus in a terminal window, this will do nicely.

Wednesday, 9 May 2012

Add a new hard drive to your Oracle Developer Days VM

For those of you who end up using the Oracle Developers Day VM for more that just demo's but playing with other things too, will find that at some point, you'll need more space. (Like I did)   Today's post is about just that.  We're going to add a new VMDK drive to our virtual machine and configure it so its available to you in the machine.


First thing we want to do is to have a list of the devices in your linux box.  This will save you searching for it once you add it later.



[oracle@localhost ~]$ cd /dev
[oracle@localhost dev]$ ls -al hd*
brw-r----- 1 root disk  3,  0 May  4 05:50 hda
brw-r----- 1 root disk  3,  1 May  4 05:51 hda1
brw-r----- 1 root disk  3,  2 May  4 05:50 hda2
brw-r----- 1 root disk  3, 64 May  4 05:50 hdb
brw-r----- 1 root disk  3, 65 May  4 05:51 hdb1
[oracle@localhost dev]$ 


Now we can power down the machine and add the drive.



You need to make sure your VM is powered down so we can make changes to the server.




Checking the storage frame of this VM, we can see that there is only two drives connected.  Double clicking on the storage frame pops up the storage window where we can add the drive.



Clicking on add drive, asks us if we want to add an already built drive or add a new one.  We want to add a new one.


We then choose a VMDK to use.  There are other types, but we're using this one for now.


On the next page of the wizard, we choose dynamically allocated. which will size the disk to just the be the size of the data that is in it.  So if there is no data on the drive, this file will be tiny.


Next we give it a name and size.


Clicking ok to finish the wizard show the new drive added.  One las thing we do is to change the type of the hard drive to be a secondary slave.



And there we have it, one file added.  This is useless to us though until we go in and format configure it in linux, then bring it online so its of use to us.




Ok, Now we have a drive attached to our virtual machine.  All that remains is for us to configure it in in the machine so it is formatted and mounted.


Firing up the VM as normal, we want to SU as root for the next phase.  The first thing we need to do is to format the disk.

[oracle@localhost ~]$ cd /dev
[oracle@localhost dev]$ ls -al hd*
brw-r----- 1 root disk  3,  0 May  4 05:50 hda
brw-r----- 1 root disk  3,  1 May  4 05:51 hda1
brw-r----- 1 root disk  3,  2 May  4 05:50 hda2
brw-r----- 1 root disk  3, 64 May  4 05:50 hdb
brw-r----- 1 root disk  3, 65 May  4 05:51 hdb1
brw-r----- 1 root disk  3, 65 May  4 05:53 hdd
[oracle@localhost dev]$ 


Now looking at the top device listing versus this one we can see that the new device that has been add is /dev/hdd


This disk that we've added is blank and raw so the first thing we need to do is to set up partitions and then format the disk.

[oracle@localhost dev]$ sudo fdisk /dev/sdd

Command (m for help): m
Command action
   a   toggle a bootable flag
   b   edit bsd disklabel
   c   toggle the dos compatibility flag
   d   delete a partition
   l   list known partition types
   m   print this menu
   n   add a new partition
   o   create a new empty DOS partition table
   p   print the partition table
   q   quit without saving changes
   s   create a new empty Sun disklabel
   t   change a partition's system id
   u   change display/entry units
   v   verify the partition table
   w   write table to disk and exit
   x   extra functionality (experts only)

Command (m for help): 


Choose 'n' to create a new partition and then choose 'e' and then pick the defaults through that option.

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)

Finally, when this comes back, choose the 'w' to write the partition table back to disk.

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[oracle@localhost dev]$  

Now we can build the file system on the disk we have partitioned with mkfs.

[oracle@localhost dev]$  sudo mkfs -t ext3 /dev/sdd
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
6111232 inodes, 12211400 blocks
610570 blocks (5.00%) reserved for the super user
First data block=0
373 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks: 
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 
        4096000, 7962624, 11239424

Writing inode tables: done                            
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done

[oracle@localhost dev]$  


Now you have a drive, but its not mounted anywhere so you still cant see it.    You will now need to create a mount point for your drive in the root file system.

[oracle@localhost ~]$ sudo mkdir -p /newdrive
[sudo] password for oracle: 
[oracle@localhost ~]$ 

 And lastly you can issue the mount command to mount the drive to that mount point.

[oracle@localhost ~]$ sudo mount -t ext3 /dev/hdd /newdrive
[oracle@localhost ~]$ 

Now you can list your drive with 'ls -al /newdrive' and it is listed and usable.  However, the next time, the machine is rebooted, you will not have this drive mounted.  We need to add a line to the file /etc/fstab to allow it to be mounted automatically.

LABEL=/                 /                       ext3    defaults        1 1
LABEL=/home             /home                   ext3    defaults        1 2
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
LABEL=SWAP-hda2         swap                    swap    defaults        0 0
http://localhost:80     /home/oracle/dav        davfs   noauto,users    0 0
/dev/hdd                /newdrive               ext3    defaults        1 2     

Adding the line above to this file will allow the drive to be mounted each time the machine reboots.  


Now you have a drive which you can use for data or install other Oracle software on, like Oracle Golden Gate  to help synchronise data between databases.  Find out more about the Oracle developer day VM on OTN


Thursday, 19 April 2012

SQL Tuning Advisor - 101

The DBMS_SQLTUNE package is the interface for tuning SQL on demand. Its Doc pages are here.  Have a look.  There is a lot of stuff to do to set a tuning task, run it, report on it and then get it to do something useful.  We've wrapped all that into our SQL Tuning Advisor function which means you dont need to start writing plsql API calls to make this work.  Stick in your dodgy query, click the advisor button and visualize the results.

Here's a look at how to do this.  Firstly, we need to grant a few permissions to our user. I'm doing this as sys.
 Then, for this demo, I want to clean out all the statistics on the tables I want to look at.
 Now, here's my initial query, getting total and mean salary grouped by departments.
When we then run the tuning advisor, a new tab appears on the worksheet which has the main results from the tuning sesstion.  This tab has four main sections to it. These are the statistics which the advior found on the objects in the query, changes to the profile which is in use, any indexes which need to be added.  Finally, if needed, there is a restructuring tab which may have some sql to help you restructure your query.


Finally, on the right hand side, we can see the details of tuning job.  The SQL_TUNE package generate text which we organise into the tabs.




We can see from the output above that the statistics are not available and the tool is recommending refreshing statistics on the objects in the original query.

We can then go and analyze the tables to see if that helps.


We can then check that the stats are fresh and at the time of posting, this is current.



Now, going back the tuning advisor and running it again, shows some different stats

Heres the final look at what the Tuning advisor tells us at the end of the second run.  This is the standard text output that comes from the tuning package

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : staName14054
Tuning Task Owner  : HRDEMO
Tuning Task ID     : 9295
Workload Type      : Single SQL Statement
Execution Count    : 1
Current Execution  : EXEC_9255
Execution Type     : TUNE SQL
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 04/19/2012 07:33:50
Completed at       : 04/19/2012 07:33:50

-------------------------------------------------------------------------------
Schema Name: HRDEMO
SQL ID     : 028hrurkuc6ah
SQL Text   : SELECT SUM(E.Salary),
               AVG(E.Salary),
               COUNT(1),
               E.Department_Id
             FROM Departments D,
               Employees E
             GROUP BY E.Department_Id
             ORDER BY E.Department_Id

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  An expensive cartesian product operation was found at line ID 2 of the
  execution plan.

  Recommendation
  --------------
  - Consider removing the disconnected table or view from this statement or
    add a join condition which refers to it.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 2187233893

 
---------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |    11 |    77 |    35   (3)| 00:00:01 |
|   1 |  SORT GROUP BY          |             |    11 |    77 |    35   (3)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN  |             |  2889 | 20223 |    34   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL    | EMPLOYEES   |   107 |   749 |     3   (0)| 00:00:01 |
|   4 |    BUFFER SORT          |             |    27 |       |    32   (4)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN| DEPT_ID_PKX |    27 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   5 - SEL$1 / D@SEL$1
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=1) "E"."DEPARTMENT_ID"[NUMBER,22], COUNT(*)[22], 
       COUNT("E"."SALARY")[22], SUM("E"."SALARY")[22]
   2 - (#keys=0) "E"."SALARY"[NUMBER,22], "E"."DEPARTMENT_ID"[NUMBER,22]
   3 - "E"."SALARY"[NUMBER,22], "E"."DEPARTMENT_ID"[NUMBER,22]
   4 - (#keys=0) 

-------------------------------------------------------------------------------

For doing this without SQL Developer, there are several things which you need to do. I have a little graphic which looks at each of the steps which need to be taken to create a tuning job in the normal SQL*Plus interface.  The main steps are creating task tuning tasks, and then interpreting the output.
Finally, this functionality is part of the SQL Worksheet in SQLDeveloper, which together with trace file editing, explain plan and autotrace, hierarchical profiler and monitoring sessions adds more tools to the toolbox for trying to find issues with you code.

Wednesday, 18 April 2012

Using Hierarchical Profiler in SQL Developer

One of the features exposed since SQL Developer 1.5 is the hierarchical profiler.  There have been several blogs and things about this but none I think that really get into the detail of what you are seeing and how to do it.

The hierarchical profiler allows you to see what happens when your piece of PL/SQL is running.  More specifically, it allows you to see where it is spending most of its time, which means you can concentrate on hammering that down, rather than wondering where to start.

For today, I'm using a really basic reference table with a few rows in it to allow us to do something.  I have also created two procedures, one of which calls the other so we have some nested dependencies.

drop table hier_demo;
create table hier_demo (id number, name varchar2(200));
insert into hier_demo values (1, 'Barry');
insert into hier_demo values (2, 'Lisa');
insert into hier_demo values (3, 'Rebecca');
insert into hier_demo values (4, 'Katie-Ellen');

CREATE OR REPLACE
PROCEDURE PRINTER(
    NAME IN VARCHAR2 )
AS
BEGIN
  dbms_output.put_line(NAME);
END PRINTER;
/
CREATE OR REPLACE
PROCEDURE SHOW_PEEPS
AS
  CURSOR hiercur
  IS
    SELECT * FROM hier_demo;
  -- hierrec hiercur%type;
  -- type  hiertab is table of hierrec%TYPE;
BEGIN
  FOR myrec IN hiercur
  LOOP
    dbms_output.put_line(myrec.name);
  END LOOP;
END;
/

Running the main procedure normally gives us some nice and simple out put.


When we click on the profile button in the plsql editor, SQL Developer will check that you have the proper permissions and the associated table to use the profiler.

When you hit the profiler button , it first comes up with the run dialog to set the parameters for the stored procedure.  Hitting ok on this diualog will run the stored procedure and any issues it has will also pop up while you are profiling.  


As this happens, the profiler  first checks to see if the there is a profiler log directory.  and if there is not one, it will prompt you to create one and get the appropriate permissions.






Hitting ok on this makes the tool then set up the directory for the profile.  To do this, it needs to run some SQL as sys to do it.


If the user agrees with all this, then he is prompted for SYS passwd to actually create the tables for the profiler statistics in the local user, in this case, hrdemo.
Finally, when they agree, the tool asks if it can setup a local set of tables for the profiler,  We'll agree to this too and make sure the profile is captured.

 Now, when we look at the profile tab of the PLSQL editor, we should have a new editor with the results of the profile.


This shows us a breakdown of the how the procedure actually executed all the way down to the actual fetch which returned the rows.  A very slight change to the procedure, in this case adding another procedure as a dependency which we also described above, we can show the nesting in the profile going further down.
create or replace
PROCEDURE SHOW_PEEPS
AS
  CURSOR hiercur
  IS
    SELECT * FROM hier_demo;
  -- hierrec hiercur%type;
  -- type  hiertab is table of hierrec%TYPE;
Begin
  FOR myrec IN hiercur
  Loop
    PRINTER(myrec.name);
  END LOOP;
END;

This now shows us that we have another profile in the set and clicking on it gives us the hierarchy of calls in the stored procedures execution.  The main point here is that we can now see the further level of indirection through the printer procedure.


So thats all of this profiler for now,  If you want to see how to do this with your own tables, the easiest thing to do is to download the Oracle Developer Days VM from OTN.  This particular blog will make an appearance as part of a bigger set later which we will discuss Tuning in general..

Thursday, 12 April 2012

Remote Debugging with SQL Developer revisited.

As part of the development process, we all have to work out the bugs in our code.  For all of us who use SQLDeveloper , we know how to debug with SQL Developer. Compile for Debug, breakpoint and go.  However, People still get confused by what remote debugging is and how it works.  At its most basic, it allows us to run a procedure in a session and debug if from another.

So, Lets say we have a simple procedure on employees table like this.

create or replace
FUNCTION GET_EMP_NAME 
(
  ID IN NUMBER  
) RETURN VARCHAR2 AS 
name varchar2(100);
BEGIN
 select first_name||' '||last_name into name from employees
 where employee_id = ID;
  RETURN name;
END GET_EMP_NAME;


We can compile this for debug in SQLDeveloper as normal.  Now, for remote debugging, we want to go to another session and run this function there.  For clarity, we can do it in SQL*Plus.  Before that however, we need to switch on the remote debugger listener so we can attach to a session.  So, firstly, right click on your connection and choose remote debug, which will pop up a little window

For our purposes, on localhost, we dont need to add any other information, but if you are connecting to another database on another machine, add the host name to the local address field and choose an appropriate port.  When you click ok on this, the Run manager is shown with the listener details on there as shown above.

Now, here we are with SQL*Plus, fire it up with our demo user and make sure to execute the command

 execute DBMS_DEBUG_JDWP.CONNECT_TCP('127.0.0.1',4000);

and then we can run our function as described above.

[oracle@localhost ~]$ sqlplus hrdemo/hrdemo

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 12 19:16:37 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

HRDEMO@ORCL> set serveroutput on
HRDEMO@ORCL> execute DBMS_DEBUG_JDWP.CONNECT_TCP('127.0.0.1',4000);

PL/SQL procedure successfully completed.

HRDEMO@ORCL> begin
  2  dbms_output.put_line(get_emp_name(100));
  3  end;
  4  /


Once we run the anonymous bock, the remote debugger kicks in and we stop at the appropriate breakpoint in the code.

On a last note, this works well in Application Express too so when you make a call to a function which you have remote debug switched on for, the debugger will break on the line as long as you have debug switched on in the developer toolbar.

Wednesday, 11 April 2012

Setting up SVN for SQL Development with Oracle Developer Days VM

We've done a number of posts on using the Oracle Developer Day VM's and this is an addition to it, showing how we can set up a subversion repository using apache web dav for access.    I'll keep this really simple so the steps should doable, straight one after the other.

On the Oracle Developer Day image, we have installed SVN so we can use it as our source control system.  Lets find our svn.
[oracle@localhost ~]$ which svn
/usr/bin/svn
[oracle@localhost ~]$ 


We can check if we have the right modules installed for apache, which in this case is mod_dav_svn.
[oracle@localhost ~]$ ls /usr/lib/httpd/modules/|grep svn
[oracle@localhost ~]$ 
For this we need to make sure we have the proper SVN modules installed for apache.  We can do this with Yum. (The default repositories should be enough)
[oracle@localhost /]$ sudo yum install mod_dav_svn
Loaded plugins: security
Setting up Install Process
Resolving Dependencies
There are unfinished transactions remaining. You might consider running yum-complete-transaction first to finish them.
The program yum-complete-transaction is found in the yum-utils package.
--> Running transaction check
---> Package mod_dav_svn.i386 0:1.6.11-7.el5_6.4 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package            Arch        Version                 Repository         Size
================================================================================
Installing:
 mod_dav_svn        i386        1.6.11-7.el5_6.4        el5_latest         78 k

Transaction Summary
================================================================================
Install       1 Package(s)
Upgrade       0 Package(s)

Total download size: 78 k
Is this ok [y/N]: y
Downloading Packages:
mod_dav_svn-1.6.11-7.el5_6.4.i386.rpm                    |  78 kB     00:00     
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing     : mod_dav_svn                                              1/1 

Installed:
  mod_dav_svn.i386 0:1.6.11-7.el5_6.4                                           

Complete!
[oracle@localhost /]$ cd /usr/lib/httpd/modules/
[oracle@localhost modules]$ ls *svn*
mod_authz_svn.so  mod_dav_svn.so

Now, we can create a repository somewhere to store some code which we'll save in here later.  I've added two repositories, just to show you can :).

[oracle@localhost svn]$ svnadmin create /home/oracle/svn/repo1
[oracle@localhost svn]$ svnadmin create /home/oracle/svn/repo2
[oracle@localhost svn]$ ls repo1
conf  db  format  hooks  locks  README.txt
[oracle@localhost svn]$ cat repo1/README.txt 
This is a Subversion repository; use the 'svnadmin' tool to examine
it.  Do not add, delete, or modify files here unless you know how
to avoid corrupting the repository.

Visit http://subversion.tigris.org/ for more information.
[oracle@localhost svn]$ 


Now since we're going to access this over apache, lets change the permissions

[oracle@localhost svn]$ sudo chown -R apache:apache /home/oracle/svn/repo1/
[sudo] password for oracle: 
[oracle@localhost svn]$ ls -al
total 16
drwxrwxr-x  3 oracle oracle 4096 Apr  9 21:53 .
drwxr-xr-x 45 oracle oracle 4096 Apr  9 21:51 ..
drwxrwxr-x  6 apache apache 4096 Apr  9 21:53 repo1
drwxrwxr-x  6 apache apache 4096 Apr  9 21:54 repo2
[oracle@localhost svn]$ 

We want to be able to secure svn, and for now, lets use basic svn authentication.  In order to do that, we need to configure subversion to allow users to connect.  To do that, go to your new repo, identify your svnserve.conf file and uncomment the following 2 lines

auth-access = write
password-db = passwd
[oracle@localhost svn]$ cd repo1
[oracle@localhost repo1]$ ls
conf  db  format  hooks  locks  README.txt
[oracle@localhost repo1]$ cd conf/
[oracle@localhost conf]$ vi svnserve.conf 
[oracle@localhost conf]$ sudo vi svnserve.conf 

Now setting up webdav for svn is relatively easy too.  Since we have the dav svn installed, we just need to edit the httpd.conf in /etc/httpd/conf/httpd.conf.
The first we need to do is to modify the file and add the mod dav svn modules to apache.
 In the same file, we also need to define a location for the our URLs to point to.    I have set this initial setup with the most basic setup which needs no authorization yet.  We'll amend that later.
 Lastly, save the file, and we need to restart the httpd daemon.  This is in the /etc/init.d directory.
 Now, we can check out if we can see this with a browser? Lets see.  We know that we set up the httpd.conf with a port number of 9999, so we can use the 'repos' location as the uri, and then we can specify the repository.  So, the url would be http://localhost:9999/repos/repo1 (or repo2)
 Going back to our image, we now have several port forwarding rules.  We add another for apache svn which pushes calls to port 9999 through to the guest.

 And now we can use our host to see the new repo.





After all this, we can add a user to svn so we can use it.  Primarily, me :)

[sudo] password for oracle: 
[oracle@localhost conf]$ htpasswd -c /home/oracle/svn/repo1/
conf/       db/         format      hooks/      locks/      README.txt
[oracle@localhost conf]$ htpasswd -c /home/oracle/svn/repo1/conf/
authz          passwd         svnserve.conf  
[oracle@localhost conf]$ sudo htpasswd -c /home/oracle/svn/repo1/conf/passwd bamcgill
New password: 
Re-type new password: 
Adding password for user bamcgill
[oracle@localhost conf]$ 


Now we have users we can use svn. However, we need to let apache know that we want to use it.  Rememeber the tags we filled out earlier.  Change the location tags to have the following now.

<Location /repos>
  DAV svn
  SVNParentPath /home/oracle/svn
  AuthType Basic
  AuthName "Subversion Repository"
  AuthUserFile /home/oracle/svn/repo1/conf/passwd
  Require valid-user
</Location>

Now, we restart the httpd daemon again

cd /etc/init.d
sudo ./httpd stop
sudo ./httpd start

And now we have authentication, albeit basic, but there are other blows to allow ldap and ypmaster access.


Need to check in something from the directory, which we will look at in another post.    Today, we set up subversion on the Oracle Developer Day Virtual Machine.  In later posts, we'll use this to check in some code and use it with Hudson to automate some integration tasks.

Tuesday, 10 April 2012

Continuous Integration for SQL Tasks

One of my favourite integration tools is hudson.   Today, we're going to show you how to setup hudson on the Oracle Developer Day image.  Since the image is built on Enterprise Linux, we'll need to either add a yum repository from which to install, or, even easier, just download the RPM from the hudson site
Clicking on the Oracle Linux link, we'll download hudson-redhat-2.2.0.rpm.
When its downloaded, you can install it on your linux image.
Now, its installed, we need to configure it.  Since we put in the RPM, there are a couple of standard directories to check.  Firstly, we have an init.d script for starting and stopping hudson
So, to configure hudson, the actual configuration file is under /etc/sysconfig.
At this point, the main thing we want to do is to configure the port that hudson will operate on.  We'll change ours to 8888, since the image has several other ports doing different things.

Now, once thats done, come back to /etc/init.d and run hudson start.


So now, fireup firefox on the image and punch in localhost:8888


Which will give us this.


 Tada!.  now we have hudson up and running.  Lets run a dumb job to see what happens,  I'll do something really simple so you can try this immediately.
ls -altr
touch barry.txt
echo "Something $BUILD_NUMBER" >> barry.txt
cat barry.txt


creating a new job on hudson is easy.  Click on the new job icon and enter the name and description of your job.


Scroll down and choose a build option of Shell script.
And now add our little script.


and click save at the bottom.  Job done, so to speak.
Now run the job and see what happens.  It will queue it up and run it and when its finished, will show an icon as to whether there is thunder coming or the sun is still shining.
Drilling into the job, you can click on the console output to see how the job actually ran.  
There it is running.  One last step.  We need to add a port forwarding rule to the image so we can check this out from outside the image. and we're done. 


 I've just noticed too, that apex seems to be configured with port 8888, so we could get a clash later.  I will change that on this image.  Anyways, have fun with this for now.  I'll come back to this when we get subversion setup and linked to this so we can checkout the sql/plsql and run tests using this hudson install.