PL/SQL proc to add a column to a table if it does not exist

create or replace function ddl_column_exists(p_table     in varchar2,
                                             p_fieldname in varchar2) return number is
  
  result number;

begin
  select count(*)
    into result
    from user_tab_columns c
   where lower(c.COLUMN_NAME) = lower(p_fieldname)
     and lower(c.TABLE_NAME) = lower(p_table);

  if result >0 then
    result := 1;
  else
    result := 0;
  end if;

  return(result);
end;
/
CREATE OR REPLACE PROCEDURE "DDL_USP_ADDCOL" (p_tblName varchar2,
                                              p_fldName varchar2,
                                              p_dtype   varchar2) iS
    v_ret number(2) := 0;
    v_sql varchar2(2000);
begin

    select count(*)
      into v_ret
      from user_tab_columns c
     where lower(c.COLUMN_NAME) = lower(p_fldName)
       and lower(c.TABLE_NAME) = lower(p_tblName);

    if v_ret = 0 then

        --print 'adding field'
        v_sql := 'ALTER TABLE ' || p_tblName || ' add ' || p_fldName || ' ' || p_dtype;
        execute immediate v_sql;

    end if;
end;

Script to export all pl/code in an oracle database

---------------- getcode.sql -------------------------------
prompt Exporting User code....
set feedback off
set heading off
set termout off
set linesize 3000
set trimspool on
set verify off
prompt set define off
spool c:\policecode.sql
select text from (
select a.name, a.type, a.line, decode(line,1,'create or replace ', '' ) || text  as text
  from user_source a
union select a.name, a.type, 9000000000 as line, '/' from user_source a
order by name,type, line);
spool off
prompt set define on
set feedback on
set heading on
set termout on
set linesize 100
-----------------------------------------------------

Installing SSL certificates on GlassFish

Scenario: Java EE programs run on a GlassFish server and exchange web service messages with remote servers using SSL. The remote servers' SSL certificates must be imported to the glass fish server trusted certificate store.

1. Use any browser to save the SSL certicate to personal certificates store.
2. Run mmc and add certificates snapin (File/Add Remove Snapin) .
3. View certificate saved in step 1 and export to a file, for example c:\GlassFish_Server_3\glassfish\domains\domain1\config\webservice1.cer
4. Import to glassfish trusted domain cacerts. Example code below:

   cd c:\GlassFish_Server_3\glassfish\domains\domain1\config
   "c:\Java\jdk1.6.0_21\bin\keytool" -delete -trustcacerts -alias register_webservice_1 -keystore cacerts.jks
   "c:\Java\jdk1.6.0_21\bin\keytool" -import -trustcacerts -alias register_webservice_1 -file server.cer -keystore cacerts.jks
   

   Keystore password is "changeit", no quotes
5. Modify DNS or hosts file to map the common name in SSL certificate to the ip address

Registering for the "Oracle Certified Master, Java EE 5 Enterprise Architect"

First of all, I do not like this name.  "Master" sounds too serious.  Anyway, information on the exams on the oracle site is here:

• http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=326
• After that you will want to go register for step 1 of the exam .  Oracle says that the cost is $300, ( 277 euros )

For the actual registration, the Oracle web site sents you to Prometric, and from there you just goto the Exam Selection page

A JSP limitation: You can't share JSP files between web applications

This one has been a real frustration of mine:  Say you have a couple of Java Servlet/JSP web applications.  Each of these applications shares common jsp files.  For example, a login.jsp page, header.jsp and footer.jsp, etc.  There is no "easy" way to package these common jsps into a jar and have each web application share these jsps.

There are a couple of workarounds:

1. Manually convert the jsp into a servlet.  This one is a hard one, and it results into a lot of request.getWriter().print() statements into your servlet.
2. Pre-compile the jsp into a servlet, and then package the servlet into a jar.  Then map the servlet into the web.xml file.

With method 1, it is easy to make changes.  With method 2, it is hard since the most jsp compilers produce difficult to manage code.

Adding a pre-comit hook to simulate "global ignores" at visual svn server

This one is one of the few (but kinda big) problems we are having with Subversion:  The inability to set global, server side ignores.  Unless all developers remember to add the usual java *.class, classes etc (or .dll, .exe, .suo, etc for .net) from their SVN client,  then you end up with all kinds of unwanted files in your svn repository. 

So here's the setup:  Visual SVN Server on Windows Server 2003, development using jdeveloper 10.1.2 (our customers are yet to upgade their OAS) on Windows 7 and Tortoise SVN clients.

And here's what we did:
Step 1:
We entered all ignored file extensions and file names in a file called ignoreFiles.txt, and all ignored folders in file ignoreFolders.txt.  The entries are one per line.

Step 2:

Developed a vb script that opens these 2 files and compares them agains all added files and folders in the current svn transation.  The vbscript is here

Step 3:
Access your SVN Server, right click on the repository name, and choose Propetrties.  From the properties page, choose "Hooks", then Pre-commit hook, edit and copy/paste the following pre comit trigger.
Then click OK, and you are done.  To test this, SVN-Checkout a test directory, create a couple of "illegal" files and folders and then try to commit.  You should get an error message.

How to run the latest ANT with jdeveloper 10.1.2.3

1. Download latest ANT from ant.apache.org web site and unzip it in directory \jdev\lib\apache-ant--bin.
   
   So if your jdev dir is at C:\jdev10.1.2.3\jdev\lib, then and you downloaded ant 1.8.1, then you will have c:\jdev10.1.2.3\jdev\lib\apache-ant-1.8.1-bin
2. Edit \jdev\bin\jdev.conf and comment line that loads ant.jar and load the new ant jar you downloaded. Example below:
   

   #AddJavaLibFile ../lib/ant.jar
   AddJavaLibFile ../lib/apache-ant-1.8.1-bin/lib/ant.jar
   

3. Open file \jdev\lib\libraries.xml and change library "Apache Ant" to include jars in the new ant jar you downloaded. Example below:
   

   <item class="oracle.jdeveloper.library.JLibrary">
   <defaultlibrarydefinition>
   <jdkversion>
   <classpath>
   <entries>
   <item jar-entry="" path="apache-ant-1.8.1-bin\lib\ant.jar"/>
   <item jar-entry="" path="apache-ant-1.8.1-bin\lib\ant-launcher.jar/">
   <item jar-entry="" path="apache-ant-1.8.1-bin\lib\ant-commons-net.jar/">
   <item jar-entry="" path="apache-ant-1.8.1-bin\lib\ant-jsch.jar/">
   <item jar-entry="" path="apache-ant-1.8.1-bin\lib\com.jcraft.jsch_0.1.31.jar"/>
   </entries>
   </classpath>
   <docpath>
   <entries class="[Ljava.net.URL;">
   </docpath>
   <locked>false</locked>
   <sourcepath>
   <entries class="[Ljava.net.URL;">
   </sourcepath>
   </defaultlibrarydefinition>
   <definitionlist class="java.util.ArrayList">
   <locked>true</locked>
   <name>Apache Ant</name>
   </item>