Pl/Sql Exception

Exceptions

 

Oracle includes about 20 predefined exceptions (errors) – we can allow Oracle to raise these implicitly.

For errors that don’t fall into the predefined categories – declare in advance and allow oracle to raise an exception.

For problems that are not recognised as an error by Oracle – but still cause some difficulty within your application – declare a User Defined Error and raise it explicitly
i.e IF x >20 then RAISE …

Syntax:

EXCEPTION

WHEN exception1 [OR exception2...]] THEN

...

[WHEN exception3 [OR exception4...] THEN

...]

[WHEN OTHERS THEN

...]

Where exception is the exception_name e.g. WHEN NO_DATA_FOUND… Only one handler is processed before leaving the block.

Trap non-predefined errors by declaring them You can also associate the error no. with a name so that you can write a specific handler.
This is done with the PRAGMA EXCEPION_INIT pragma.

PRAGMA (pseudoinstructions) indicates that an item is a ‘compiler directive’ Running this has no immediate effect but causes all subsequent references to the exception name to be interpreted as the associated Oracle Error.

 

Trapping a non-predefined Oracle server exception

 

DECLARE

-- name for exception

e_emps_remaining EXCEPTION

PRAGMA_EXCEPTION_INIT (

e_emps_remaining, -2292);

v_deptno dept.deptno%TYPE :=&p_deptno;

 

BEGIN

DELETE FROM dept

WHERE deptno = v_deptno

COMMIT;

EXCEPTION

WHEN e_emps_remaining THEN

DBMS_OUTPUT.PUT_LINE ('Cannot remove dept '||

TO_CHAR(v_deptno) || '. Employees exist. ');

END;

When an exception occurs you can identify the associated error code/message with two supplied functions SQLCODE and SQLERRM

SQLCODE – Number

SQLERRM – message

 

An example of using these:

DECLARE
v_error_code NUMBER;
v_error_message VARCHAR2(255);

 

BEGIN

 

...

 

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

v_error_code := SQLCODE

v_error_message := SQLERRM

INSERT INTO t_errors

VALUES ( v_error_code, v_error_message);

END;

Trapping user-defined exceptions

DECLARE the exception

RAISE the exception

Handle the raised exception

 

e.g.

DECLARE

e_invalid_product EXCEPTION

BEGIN

update PRODUCT

SET descrip = '&prod_descr'

WHERE prodid = &prodnoumber';

IF SQL%NOTFOUND THEN

RAISE e_invalid_product;

END IF;

COMMIT;

EXCEPTION

WHEN e_invalid_product THEN

DBMS_OUTPUT.PUT_LINE ('INVALID PROD NO');

END;

Propagation of Exception handling in sub blocks

If a sub block does not have a handler for a particular error it will propagate to the enclosing block – where it can be caught by more general exception handlers.

RAISE_APPLICATION_ERROR (error_no, message[,{TRUE|FALSE}]);

 

This procedure  allows user defined error

messages from stored sub programs – call only from stored sub prog.

error_no = a user defined no (between -20000 and -20999)

 

TRUE = stack errors

FALSE = keep just last

 

This can either be used in the executable section of code or

the exception section

 

e.g.

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR (-2021,

‘manager not a valid employee’);

END;

Standard Exceptions, from the the STANDARD package

Oracle Exception Name

Oracle Error

Explanation

DUP_VAL_ON_INDEX ORA-00001 You attempted to create a duplicate value in a field restricted by a unique index.
TIMEOUT_ON_RESOURCE ORA-00051 A resource timed out, took too long.
TRANSACTION_BACKED_OUT ORA-00061 The remote portion of a transaction has rolled back.
INVALID_CURSOR ORA-01001 The cursor does not yet exist. The cursor must be OPENed before any FETCH cursor or CLOSE cursor operation.
NOT_LOGGED_ON ORA-01012 You are not logged on.
LOGIN_DENIED ORA-01017 Invalid username/password.
NO_DATA_FOUND ORA-01403 No data was returned
TOO_MANY_ROWS ORA-01422 You tried to execute a SELECT INTO statement and more than one row was returned.
ZERO_DIVIDE ORA-01476 Divide by zero error.
INVALID_NUMBER ORA-01722 Converting a string to a number was unsuccessful.
STORAGE_ERROR ORA-06500 Out of memory.
PROGRAM_ERROR ORA-06501 Generic "Contact Oracle support" message.
VALUE_ERROR ORA-06502 You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data.
ROWTYPE_MISMATCH ORA-06504  
CURSOR_ALREADY_OPEN ORA-06511 The cursor is already open.
ACCESS_INTO_NULL ORA-06530  
COLLECTION_IS_NULL ORA-06531  
       

Named exceptions

An exception can be given a name. In order to create such a named exceptions, two steps are necessary: first, it needs a line that reads exception_name exception. Then, this exception must be connected to a number with the pragma exception_init(exception_name, exc-number). The number must be in the range -20999 through -20000 (these are negative numbers!).
create or replace package exc as

  some_exc exception;

  pragma exception_init(some_exc, -20005);

  procedure raise_exc;
end exc;
/

create or replace package body exc as

  procedure raise_exc is begin 
    raise some_exc;
  end raise_exc;

end exc;
/
create or replace package use_exc as
  procedure do;
end use_exc;
/

create or replace package body use_exc as

  procedure do is begin

    exc.raise_exc;

    exception 
      when exc.some_exc then dbms_output.put_line('some_exc');
      when others       then dbms_output.put_line('others'  );

  end do;

end use_exc;
/
set serveroutput on
begin
  use_exc.do;
end;
/

The output:

some_exc

Reference -http://www.adp-gmbh.ch/ora/plsql/exception.html

ADF & JSF: Some handy code for backing beans

Following are the Some code which is very usefull when working in oracle ADF and JSF.Please go through


// print the roles of the current user
for ( String role : ADFContext.getCurrent().getSecurityContext().getUserRoles() ) {
System.out.println("role "+role);
}




// get the ADF security context and test if the user has the role users 
SecurityContext sec = ADFContext.getCurrent().getSecurityContext();
if ( sec.isUserInRole("users") ) {
}


// is the user valid
public boolean isAuthenticated() {
return ADFContext.getCurrent().getSecurityContext().isAuthenticated();
}



// return the user 
public String getCurrentUser() {
return ADFContext.getCurrent().getSecurityContext().getUserName();
}



// get the binding container 
BindingContainer bindings = BindingContext.getCurrent().getCurrentBindingsEntry();



// get an ADF attributevalue from the ADF page definitions 
AttributeBinding attr = (AttributeBinding)bindings.getControlBinding("test");
attr.setInputValue("test");



// get an Action or MethodAction 
OperationBinding method = bindings.getOperationBinding("methodAction");
method.execute();
List errors = method.getErrors();



method = bindings.getOperationBinding("methodAction");
Map paramsMap = method.getParamsMap();
paramsMap.put("param","value") ;
method.execute();



// Get the data from an ADF tree or table
DCBindingContainer dcBindings = (DCBindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();

FacesCtrlHierBinding treeData = (FacesCtrlHierBinding)bc.getControlBinding("tree");
Row[] rows = treeData.getAllRowsInRange();



// Get a attribute value of the current row of iterator 
DCIteratorBinding iterBind= (DCIteratorBinding)dcBindings.get("testIterator");
String attribute = (String)iterBind.getCurrentRow().getAttribute("field1");



// Get the error 
String error = iterBind.getError().getMessage();



// refresh the iterator 
bindings.refreshControl();
iterBind.executeQuery();
iterBind.refresh(DCIteratorBinding.RANGESIZE_UNLIMITED);



// Get all the rows of a iterator 
Row[] rows = iterBind.getAllRowsInRange();
TestData dataRow = null;
for (Row row : rows) {
dataRow = (TestData)((DCDataRow)row).getDataProvider();
}



// Get the current row of a iterator , a different way 
FacesContext ctx = FacesContext.getCurrentInstance();
ExpressionFactory ef = ctx.getApplication().getExpressionFactory();
ValueExpression ve = ef.createValueExpression(ctx.getELContext(), "#{bindings.testIter.currentRow.dataProvider}", TestHead.class);
TestHead test = (TestHead)ve.getValue(ctx.getELContext());



// Get a session bean 
FacesContext ctx = FacesContext.getCurrentInstance();
ExpressionFactory ef = ctx.getApplication().getExpressionFactory();
ValueExpression ve = ef.createValueExpression(ctx.getELContext(), "#{testSessionBean}", TestSession.class);
TestSession test = (TestSession)ve.getValue(ctx.getELContext());



// main jsf page 
DCBindingContainer dc = (DCBindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();


// taskflow binding 
DCTaskFlowBinding tf = (DCTaskFlowBinding)dc.findExecutableBinding("dynamicRegion1");


// pagedef of a page fragment 
JUFormBinding form = (JUFormBinding) tf.findExecutableBinding("regions_employee_regionPageDef");



// handle to binding container of the region. 
DCBindingContainer dcRegion = form;




// return a methodexpression like a control flow case action or ADF pagedef action 
private MethodExpression getMethodExpression(String name) {
Class [] argtypes = new Class[1];
argtypes[0] = ActionEvent.class;
FacesContext facesCtx = FacesContext.getCurrentInstance();
Application app = facesCtx.getApplication();
ExpressionFactory elFactory = app.getExpressionFactory();
ELContext elContext = facesCtx.getELContext();
return elFactory.createMethodExpression(elContext,name,null,argtypes);
}



// get a value 
private ValueExpression getValueExpression(String name) {
FacesContext facesCtx = FacesContext.getCurrentInstance();
Application app = facesCtx.getApplication();
ExpressionFactory elFactory = app.getExpressionFactory();
ELContext elContext = facesCtx.getELContext();
return elFactory.createValueExpression(elContext, name, Object.class);
}




// an example how to use this 
RichInputText input = new RichInputText();
input.setValueExpression("value",getValueExpression("#{bindings."+item+".inputValue}"));
input.setValueExpression("label",getValueExpression("#{bindings."+item+".hints.label}"));
input.setId(item);
panelForm.getChildren().add(input);





// catch an exception and show it in the jsf page
catch(Exception e) {
FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, e.getMessage(), "");
FacesContext.getCurrentInstance().addMessage(null, msg);
}

FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_WARN, msgHead , msgDetail);
facesContext.addMessage(uiComponent.getClientId(facesContext), msg);




// reset all the child uicomponents 
private void resetValueInputItems(AdfFacesContext adfFacesContext,
UIComponent component){
List items = component.getChildren();
for ( UIComponent item : items ) {

resetValueInputItems(adfFacesContext,item);

if ( item instanceof RichInputText ) {
RichInputText input = (RichInputText)item;
if ( !input.isDisabled() ) {
input.resetValue() ;
adfFacesContext.addPartialTarget(input);
};
} else if ( item instanceof RichInputDate ) {
RichInputDate input = (RichInputDate)item;
if ( !input.isDisabled() ) {
input.resetValue() ;
adfFacesContext.addPartialTarget(input);
};
}
}
}




// redirect to a other url 
ExternalContext ectx = FacesContext.getCurrentInstance().getExternalContext();
HttpServletResponse response = (HttpServletResponse)ectx.getResponse();
String url = ectx.getRequestContextPath()+"/adfAuthentication?logout=true&end_url=/faces/start.jspx";

try {
response.sendRedirect(url);
} catch (Exception ex) {
ex.printStackTrace();
}




// PPR refresh a jsf component 
AdfFacesContext.getCurrentInstance().addPartialTarget(UIComponent);




// find a jsf component 
private UIComponent getUIComponent(String name) {
FacesContext facesCtx = FacesContext.getCurrentInstance();
return facesCtx.getViewRoot().findComponent(name) ;
}




// get the adf bc application module 
private OEServiceImpl getAm(){
FacesContext fc = FacesContext.getCurrentInstance();
Application app = fc.getApplication();
ExpressionFactory elFactory = app.getExpressionFactory();
ELContext elContext = fc.getELContext();
ValueExpression valueExp =
elFactory.createValueExpression(elContext, "#{data.OEServiceDataControl.dataProvider}",
Object.class);
return (OEServiceImpl)valueExp.getValue(elContext);
}




// change the locale 
Locale newLocale = new Locale(this.language);
FacesContext context = FacesContext.getCurrentInstance();
context.getViewRoot().setLocale(newLocale);




// get the stacktrace of a not handled exception 
private ControllerContext cc = ControllerContext.getInstance();

public String getStacktrace() {
if ( cc.getCurrentViewPort().getExceptionData()!=null ) {
StringWriter sw = new StringWriter();
PrintWriter pw = new PrintWriter(sw);
cc.getCurrentViewPort().getExceptionData().printStackTrace(pw);
return sw.toString();
}
return null;
}




// get the selected rows from a table component 
RowKeySet selection = resultTable.getSelectedRowKeys();
Object[] keys = selection.toArray();
List receivers = new ArrayList
(keys.length);
for ( Object key : keys ) {
User user = modelFriends.get((Integer)key);
}




// get selected Rows of a table 2 
for (Object facesRowKey : table.getSelectedRowKeys()) {
table.setRowKey(facesRowKey);
Object o = table.getRowData();
JUCtrlHierNodeBinding rowData = (JUCtrlHierNodeBinding)o;
Row row = rowData.getRow();
Test testRow = (Test)((DCDataRow)row).getDataProvider() ;
}


Read more-http://tamanmohamed.blogspot.com/2011/08/adf-jsf-some-handy-code-for-backing.html

Oracle ADF-How to programmatically set focus on an input component – Techartifact

The af:document component has a property InitialFocusId that allows you to define the Id of an input component that should take focus when a page initially loads. But how do you set the component focus for pages that are already rendered? A use case for this is a partial submit that executes logic on the server, after which the cursor focus in the UI needs to be set to a specific input component of a page or page fragment. The solution to this use case is JavaScript that is generated on the server and executed on the client.
The simplified sample I wrote contains of three input text fields and three command buttons. Initially, using theaf:document InitialFocusId property, the cursor focus is set to the first input text field.
The command button send a partial submit to a managed bean on the server. The managed bean reads the component Id of the input text field to put the focus to from a client attribute that is defined on each command button. Using the MyFaces Trinidad ExtendedRenderKitService class, the managed bean sends a JavaScript call to the client to change the current component focus.
Important: By default, ADF Faces does not render all its user interface components as JavaScript objects. It only renders those components as JavaScript objects that have behavior, like a table that can have its columns moved. Input text components, for example, are rendered in HTML only, which means that the ADF client side JavaScript framework will not find a handle to the component unless you tell ADF Faces to create one. For this, you set theClientComponent property of the input text fields to true. This also is required for the af:document InitialFocusIdproperty to work.
Below is the managed bean code that handles the command button action and that composes and executes the JavaScript to set the client side UI focus.

import javax.faces.component.UIViewRoot;
import javax.faces.context.FacesContext;
import javax.faces.event.ActionEvent;
import oracle.adf.view.rich.component.rich.input.RichInputText;
import oracle.adf.view.rich.component.rich.nav.RichCommandButton;
import org.apache.myfaces.trinidad.render.ExtendedRenderKitService;
import org.apache.myfaces.trinidad.util.Service;

public
class FocusBean {
public FocusBean() {
}

public String onSetFocus(ActionEvent event) {
RichCommandButton rcb = (RichCommandButton)event.getSource();
String focusOn = (String)rcb.getAttributes().get(“focusField”);

FacesContext fctx = FacesContext.getCurrentInstance();
UIViewRoot viewRoot = fctx.getViewRoot();

//search can be improved to include naming containers
RichInputText rit = RichInputText)viewRoot.findComponent(focusOn);

if (rit != null) {
String clientId = rit.getClientId(fctx);
//compose JavaScript to be executed on the client
StringBuilder script = new StringBuilder();
//use client id to ensure component is found if located in
//naming container
script.append(“var textInput = “);
script.append(“AdfPage.PAGE.findComponentByAbsoluteId”);
script.append (“(‘”+clientId+”‘);”);

script.append(“if(textInput != null){“);
script.append(“textInput.focus();”);
script.append(“}”);
//invoke JavaScript
writeJavaScriptToClient(script.toString());
}
}

//generic, reusable helper method to call JavaScript on a client
private void writeJavaScriptToClient(String script) {
FacesContext fctx = FacesContext.getCurrentInstance();
ExtendedRenderKitService erks = null;
erks = Service.getRenderKitService(
fctx, ExtendedRenderKitService.class);
erks.addScript(fctx, script);
}
}

As mentioned, the command buttons in the sample have a client attribute attached that I use to determine which input component should receive focus. The managed bean calls the client attribute here:
RichCommandButton rcb = (RichCommandButton)event.getSource();
String focusOn = (String)rcb.getAttributes().get(“focusField”);
In the page definition, the client attribute is defined as






In your custom implementation of this use case, you for sure will have your own way of determining the next focus target. The server side code however doesn’t change much.

Read More -http://tamanmohamed.blogspot.com/2011/10/how-to-programmatically-set-focus-on.html