PL/SQL for loop example

Following PL/SQL script updates all phone number in user table using a simple for loop.

DECLARE
  phone_no varchar2(100) := null;
BEGIN
  FOR r1 IN (select user_name from tbl_user where phone is null) LOOP
    select phone into phone_no from tbl_profile where user_name = r1.user_name;
    update tbl_user set phone = phone_no where user_name = r1.user_name;
  END LOOP;
END;

IF ELSE statement in oracle SELECT QUERY

Every time I use and forget the semantics of following two sql queries. And I think the queries are self explanatory.

SELECT emp_name, 
    CASE WHEN emp_type = 0 THEN 'Temporary'
         WHEN emp_type > 0 AND emp_type < 5 THEN 'Permanent'
         ELSE 'UNKNOWN'
    END AS emp_type
FROM employee;

SELECT emp_name, 
    DECODE(emp_type, 0, 'Temporary',
                     1, 'Permanent',
                     2, 'Permanent',
                     3, 'Permanent',
                     4, 'Permanent',
           'UNKNOWN')AS emp_type
FROM employee;

WebLogic deployer: EAR deploy script for weblogic server.

#!/bin/sh

git pull

ant clean ear

WL_PATH=<wl-server>/lib/weblogic.jar
WL_URL=http://localhost:7001/

java -cp $WL_PATH:$CLASSPATH weblogic.Deployer 
     -adminurl $WL_EVR 
     -username weblogic 
     -password weblogic 
     -name my_project 
     -deploy dist/my_project.ear

Before Running this script check if tunneling is no.

Environment->servers->[server-name]->Protocols
'Enable Tunneling' flag should be ON.

WebLogic server: redirect console output to file.

Set following variable with the file path into startWebLogic.sh.

# Call setDomainEnv here.

WLS_REDIRECT_LOG=/tmp/myproject-console.log

WebLogic server Error: Could not obtain an exclusive lock for directory.

Steps to get out of this situation:

1. Shutdown the server.
2. Delete the lok file from <domain-home>/servers/<server-name>/tmp/.
3. Try to start the server.
4. If the server still fails to start then find the process which is still running on this server using following commands.

ps -ef | grep 'weblogic'

OR
ps -ef | grep 'oracle'

kill -9 PID

4. Now start the server.

WenLogic: EJB service ERROR: Two classes have the same XML type name

<Apr 4, 2012 3:39:04 PM BDST> <Error> <HTTP> <BEA-101216> <Servlet: "WSEE_SERVLET" 
failed to preload on startup in Web application: "/HelloEjbServiceBean".

javax.xml.ws.WebServiceException: Unable to create JAXBContext

 at com.sun.xml.ws.model.AbstractSEIModelImpl.createJAXBContext(AbstractSEIModelImpl.java:164)

 at com.sun.xml.ws.model.AbstractSEIModelImpl.postProcess(AbstractSEIModelImpl.java:94)

 at com.sun.xml.ws.model.RuntimeModeler.buildRuntimeModel(RuntimeModeler.java:265)

 at com.sun.xml.ws.server.EndpointFactory.createSEIModel(EndpointFactory.java:363)

 at com.sun.xml.ws.server.EndpointFactory.createEndpoint(EndpointFactory.java:202)

 Truncated. see log file for complete stacktrace



Caused By: java.security.PrivilegedActionException: com.sun.xml.bind.v2.runtime.IllegalAnnotationsException: 
1 counts of IllegalAnnotationExceptions

Two classes have the same XML type name ....

This is a common EJB error for the beginners like me..;)...Very often I forget the fact that EJB creates an XML type with the name [METHOD+"Response"] so there should not be any bean for service request/response with  this name.

WebLogic ERROR: No credential mapper entry found for password indirection

To solve this error follow the steps bellow..

1. Disable following flag form JDev -> application property -> deployment

Uncheck "Auto Generate and Synchronize weblogic-jdbc.xml..."

2. Carefully put the jta-data-source name in your persistence.xml.

<jta-data-source>jdbc/hello_ds</jta-data-source>

<properties>
      <property name="eclipselink.target-server" value="WebLogic_10"/>
      <property name="javax.persistence.jtaDataSource"
                value="jdbc/hello_ds"/>
</properties>

Best way to ensure this is by modifying the EAR archive.

3. Create a data source into your server and put the exact JNDI name as your jta-data-source name.