APORACLE

Format number upto a dynamically passed precision

2015-09-25T15:30:00.003+05:30

Requirement :- To format a column value up-to a given precision.
Solution :- There are two parameters P_NUMBER & P_PRECISION. We can simply use any column name in place of these two parameters.

SELECT DECODE (INSTR (:P_NUMBER, '.', -1),

0, :P_NUMBER,

SUBSTR (:P_NUMBER,

DECODE (:P_PRECISION,

0, INSTR (:P_NUMBER, '.', -1) - 1,

INSTR (:P_NUMBER, '.', -1)

)

|| DECODE (INSTR (:P_NUMBER, '.', -1),

0, RPAD (DECODE (:P_PRECISION, 0, NULL, '.'),

:P_PRECISION + 1,

'0'

RPAD (SUBSTR (:P_NUMBER, INSTR (:P_NUMBER, '.', -1) + 1),

:P_PRECISION,

'0'

)

) NUM

FROM DUAL;

DEFAULT values for table columns in Oracle Database 12c Release 1 (12.1)

2015-09-22T12:58:00.000+05:30

DEFAULT values using sequences

Finally oracle has made it possible to specify the CURRVAL and NEXTVAL sequence pseudo columns as the default values for a column. You should also consider using Identity columns for this purpose.

In the below example we can see the effect of specifying a sequence as the default value for a column. The default value is only used when the column is not referenced by the insert.

CREATE SEQUENCE DEFAULT_SEQ;

CREATE TABLE TEST_DEFAULT_TBL (

ID NUMBER DEFAULT DEFAULT_SEQ.NEXTVAL,

DESCRIPTION VARCHAR2 (250)

);

INSERT INTO TEST_DEFAULT_TBL (DESCRIPTION) VALUES ('DESCRIPTION ONLY');

INSERT INTO TEST_DEFAULT_TBL (ID, DESCRIPTION) VALUES (100, 'ID=100 WITH DESCRIPTION');

INSERT INTO TEST_DEFAULT_TBL (ID, DESCRIPTION) VALUES (NULL, 'ID=NULL WITH DESCRIPTION');

The best part of the fact that we can use both the NEXTVAL and CURRVAL pseudo columns gives us the ability to auto-populate master-detail relationships, as shown below.

CREATE SEQUENCE T1_MASTER_SEQ;

CREATE SEQUENCE T2_DETAIL_SEQ;

CREATE TABLE MASTER_TBL (

ID NUMBER DEFAULT T1_MASTER_SEQ.NEXTVAL,

DESCRIPTION VARCHAR2 (250)

);

CREATE TABLE DETAIL_TBL (

ID NUMBER DEFAULT T2_DETAIL_SEQ.NEXTVAL,

MASTER_ID NUMBER DEFAULT T1_MASTER_SEQ.CURRVAL,

DESCRIPTION VARCHAR2 (250)

);

Calling PL/SQL Functions and Procedures in OAF

2015-09-16T15:15:00.001+05:30

In general, to invoke a stored procedure from within a controller (testCO.class), you need to:

1. Create a JDBC Callable Statement with the PL/SQL block containing the stored procedure invocation

2. Bind IN & OUT variables.

3. Execute the statement.

4. Retrieve the values of any OUT parameters.

5. Close the statement.

The following code snippet example shows how to create and use a CallableStatement in an OAF.

import java.sql.CallableStatement;

import oracle.apps.fnd.framework.OAException;

import oracle.apps.fnd.framework.server.OADBTransaction;

import java.sql.Types;

import oracle.jdbc.driver.OracleCallableStatement;

OADBTransaction trx = am.getOADBTransaction();

String imstsflag = "F";

int XucollectionId =

Integer.parseInt(pageContext.getSessionValue("UCollectionId").toString());

try {

String s =

" BEGIN XXOLNG_EAM_QLTY_PKG.VALIDATE_IM_STATUS(:1,:2); END;";

CallableStatement cstmt = trx.createCallableStatement(s, 1);

cstmt.clearParameters();

cstmt.setInt(1, XucollectionId);

cstmt.registerOutParameter(2, 12);

cstmt.execute();

imstsflag = cstmt.getString(2);

cstmt.close();

} catch (Exception e) {

pageContext.writeDiagnostics(this,

"AP>Exception on pkg-->" + e.getMessage(),

1);

e.printStackTrace();

}