Sunday, August 10, 2014

Oracle Splitting String into multiple rows


I used below query to split a comma separated string to rows in oracle

select regexp_substr(a.col1,'[^,]+', 1, level) RT_PARAM_NAME from

        (select 'a,b,c,d' col1 from dual) a

        connect by regexp_substr(a.col1,'[^,]+', 1, level) is not null  



Note the regular expression '[^,]+' which basically tells the query to use , as a delimeter , and note the use of level and connect by.

Wednesday, August 6, 2014

ORA-01422: exact fetch returns more than requested number of rows


This error normaly means that the query fetched more than one records , while it was expected to return only one record (in cases where you are using in clause in a query to assign the result to a variable , you are assuming it will always retun only one row but at the time of this exception it is returning more rows).

If this is the case the solution is very simple , introduce more strict criteria in your query to make sure that it alwasy returns only one row.

How ever I also faced this exception in another interesting case.

Problem:

In my oracle function I was passing a parameter and the name of the parameter was "alert_id" , then I was comparing the value of this parameter with a field in the query below


select a.message into html from  My_Table a where a.alert_id=alert_id;

I double checked there was only one record in the databas with that alert_id , but inside trhe function when i was running this query it was giving me ORA-01422 again which did not make sense.

Solution:

I found that the problem was that the name of the field in the database and the name of the parameter was same (alert_id in my case) , when you do that you will face this problem because oracle will pace the current value in the database field after the = sign , so my query was getting interprated as (suppose alertid in the data base is 5380)

select a.message  from  rtoc.rtoc_alert a where 5380=5380;

when I ran the above query in PL/SQL it showed me more than one records.

Moral of the story :)

Naver use the parameter names ina  function which have any possibility of exsitance in tables you are using in your function.

Suggessted Naming Convention

function parameters should start with "p_" ==> "p_alert_id"
 variables declared whould start with  "v_"


 

Monday, August 4, 2014

Caused by: java.lang.NoClassDefFoundError: org/apache/commons/io/output/DeferredFileOutputStream

I was getting this error in my eclipse project , pressing ctrl+t and finding for this class I found that it was avaialable in one of my jar files in the maven m2 directory under below jar file

commonns-io-2.1.jar

So I was considering that it is in my class path already , but I was wrong.

I soon found out that the dependency related to this jar file in my pom was mistakenly commented out and because of that the jar file was not included in my war.

so I uncommented the below dependency in my pom file

  <dependency>
   <groupId>commons-io</groupId>
   <artifactId>commons-io</artifactId>
   <version>2.1</version>
  </dependency>
and recompiled the project nd nwo it's running fine.
 

Summary:

NoClassDefFoundError means the related jar is not in your classpath and ctrl+t shows you only to which jar file a class belongs to . it doesn't mean that the jar file is in your classpath ( It's a reminder for myself :)  ) .

Sunday, August 3, 2014

java.lang.IllegalAccessError: tried to access class com.documentum.bpm.WorkflowVariables from class com.documentum.bpm.impl.DfWorkflowEx

We recently migrated from documentum 6.5 to 7.1 , and had to retest our code with new documentum jar files.

I found that bpm_infra.jar file was not any more in the new documentum lib folder so removed it from my class path , but I started getting "IDfWorkflowEx cannot be resolved to a type" compiler error.

This was understood as in the old version we had the related jar file "dpm_infra.jar". I also tried to keep the jar file in class path and then was able to compile and execute the project and even initiate the workflow successfully , but when i was trying to process a workflow item I was getiing the exception

java.lang.IllegalAccessError: tried to access class com.documentum.bpm.WorkflowVariables from class com.documentum.bpm.impl.DfWorkflowEx


 Luckily I found an epert friend who adviced me to just comment the code as it was not really being used anywhere in my applicaiton (I was using a custom API to do docuemntum operations ) , so after commenting the related code and removing the dpm_infra.jar file from my classpath I was able to do all the workflow operations and file uploads and downloads.





 

Java was started but returned exit code=13

I was getting this error when i was trying to run my eclipse

Here is the solution

  • Open your eclipse.ini file and see which javaw.exe file you are trying to use
    • in my case it was C:\Program Files\Java\jdk1.7.0_55\bin\javaw.exe
  • I am using windows64bit platform.
  • Open cmd and run java -version
    • This will tell you which java version you are currently using (like below)
java version "1.7.0_55"
Java(TM) SE Runtime Environment (build 1.7.0_55-b13)
Java HotSpot(TM) 64-Bit Server VM (build 24.55-b03, mixed mode)
  • Notice the 3rd line which is telling my I am currently using the 64bit java , but that is not what I see in my eclipse.ini file (There it's 32 bit java)
  • So I changed it like below
    • C:\Program Files (x86)\Java\jdk1.7.0_55\bin/javaw.exe
And now I can run my eclipse without any error , hoope this helps somebody out there.

Summary

This problem happens when your java version and OS java version is different , for example if you are using 32 bit java version and the OS has 64 bit java version you will see this error. the easy solution is to have same version of java for both your OS and your eclipse.