OOP Concepts in PLSQL with Record type

 CREATE OR REPLACE TYPE HTML_OBJ_Record is object  (Body_String Clob)

CREATE OR REPLACE TYPE HTML_OBJ AS OBJECT (

   HTML_Record  HTML_OBJ_Record,

   MEMBER PROCEDURE init( SELF IN OUT NOCOPY HTML_OBJ)

  )

CREATE OR REPLACE TYPE BODY HTML_OBJ AS

  

  MEMBER PROCEDURE init (SELF IN OUT NOCOPY HTML_OBJ) IS

  BEGIN

         SELF.HTML_Record:=new HTML_OBJ_Record(null);

         SELF.HTML_Record.body_string:='Hello';

  END;

END;

declare

html HTML_OBJ;

begin

    html := new HTML_OBJ(null);

    DBMS_OUTPUT.put_line('=> '||html.HTML_Record.body_string);

    html.init();

    DBMS_OUTPUT.put_line('=> '||html.HTML_Record.body_string);  

  end;

  

Create DB Link Example using TNS

CREATE DATABASE LINK DBLink_Name

    CONNECT TO UserName IDENTIFIED BY Password

    USING '(DESCRIPTION=

                (ADDRESS=(PROTOCOL=TCP)(HOST=DBname)(PORT=portNumber))

                (CONNECT_DATA=(SERVICE_NAME=SID))

            )';

Get Number of Rows returned by ResultSet in Java

A simple getRowCount method can look like this :

private int getRowCount(ResultSet resultSet) {
    if (resultSet == null) {
        return 0;
    }

    try {
        resultSet.last();
        return resultSet.getRow();
    } catch (SQLException exp) {
        exp.printStackTrace();
    } finally {
        try {
            resultSet.beforeFirst();
        } catch (SQLException exp) {
            exp.printStackTrace();
        }
    }

    return 0;
}

Just to be aware that this method will need a scroll sensitive resultSet, so while creating the connection you have to specify the scroll option. Default is FORWARD and using this method will throw you exception.

//step3 create the statement object  
Statement stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);  

How to move an undecorated JFrame in Java

How to move an undecorated JFrame in Java 

The first step is to create your project by clicking file at the top of your project and then click new project, you can name your project whatever you want. Create you form by right clicking the Source Packages and by selecting New JFrame Form and then drag panel component in your form.

then you will need to create a two event for your panel the Mouse Dragged and Mouse Pressed, copy paste the code below:

[java]

int xMouse;

int yMouse;

private void framedragMouseDragged(java.awt.event.MouseEvent evt) {

int x = evt.getXOnScreen();

int y = evt.getYOnScreen();

this.setLocation(x – xMouse , y – yMouse);

}

private void framedragMousePressed(java.awt.event.MouseEvent evt) {

xMouse  = evt.getX();

yMouse = evt.getY();

}

Upload-File-to-Application-Server-Oracle-OAF

This library is the step of our journey to build java based utilities, Use this Utilities and report if found any Bug.

Source is available here 

GitHub Source

Setup for Installation

Install OAF and configure its envirnoment as per your Oracle E-Buisness Suite.

Read Documentations: Documents.

Open This project in Oracle JDeveloper

OAF Current Row Values

Add Current Row Values in Event Parameters.

Parameters: Open dialog and add parameters by choosing your own Parameter name and its value from current row.

${oa.FileSetupVO1.SrlFileSetupId}

FileSetupVO1 is my view name

SrlFileSetupId is my field from view

FND_PROFILE AND FND_GLOBAL

FND_PROFILE values: 

fnd_profile.value('PROFILEOPTION');
fnd_profile.value'MFG_ORGANIZATION_ID');
fnd_profile.value('ORG_ID');
fnd_profile.value('LOGIN_ID');
fnd_profile.value('USER_ID');
fnd_profile.value('USERNAME');
fnd_profile.value('CONCURRENT_REQUEST_ID');
fnd_profile.value('GL_SET_OF_BKS_ID');
fnd_profile.value('SO_ORGANIZATION_ID');
fnd_profile.value('APPL_SHRT_NAME');
fnd_profile.value('RESP_NAME');
fnd_profile.value('RESP_ID');

FND_GLOBAL values: 

FND_GLOBAL.USER_ID;
FND_GLOBAL.APPS_INTIALIZE;
FND_GLOBAL.LOGIN_ID;
FND_GLOBAL.CONC_LOGIN_ID;
FND_GLOBAL.PROG_APPL_ID;
FND_GLOBAL.CONC_PROGRAM_ID;
FND_GLOBAL.CONC_REQUEST_ID;

How to get Current Row in OAF

We can Get current Row Using findRowByRef method of ApplicationModule.


Here Example Code.


Code of my AM
UploadFileAMImpl am = (UploadFileAMImpl)pageContext.getApplicationModule(webBean);

Code of RowImp
FileSetupVORowImpl setupRow = (FileSetupVORowImpl) am.findRowByRef(pageContext.getParameter(OAWebBeanConstants.EVENT_SOURCE_ROW_REFERENCE)) ; 



findRowByRef is standard method that will return Current Rowimplement
.
Note: Your Action Type must be FireAction. findRowByRef  will return null for firePartialAction.

How to get next value of Sequence dynamically in Oracle

we can execute sequence and get its value using Execute Immediate.
here is the Example Code;

CREATE SEQUENCE mySEQ START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 1000000 NOCYCLE NOCACHE ORDER

declare

  SequenceValue number;

begin

    EXECUTE IMMEDIATE 'select mySEQ.nextval  from dual' INTO SequenceValue;

    ---Now you can Use SequenceValue anywhere

end;

/

drop sequence mySEQ 

/

Rownum in SQL

The Oracle/PLSQL ROWNUM function returns a number that represents the order that a row is selected by Oracle from a table or joined tables.

Example

select * from all_objects where rownum < 2

BIP 11g: Template Builder replaces with

XML-22008: (Error) Namespace prefix ‘ref’ used but not declared

By Default MSWord user form field help text as <?ref:xdo00003?> and when we configrute this rtf file in Oracle EBS. so it generate error or Dont show proper format. So need to change  this text <?ref:xdo00003?> to it's data tag.

Solution:

choose tab "Build" and select radio button "Backward compatible" then re-create fields.

after this setting ms word will add <?ColumnTag?> instead of <?ref:xdo000x?>.

Compute and Set Interactive Grid Column Value Oracle Apex

Set / Get Interactive Grid Column Value Master Detail Form Oracle Apex 1) Assign Static Id to Column (that will be update by command) and use it's id in following commands. in my example P4_ITEM_VALUE is Static Id of Item Value Column.


2) Write Dynamic action and call this JavaScript apex.item( "P4_ITEM_VALUE").setValue(apex.item('P4_RATE').getValue()*apex.item('P4_QUANTITY').getValue());

Set / Get Interactive Grid Column Value Master Detail Form Oracle Apex

1) Assign Static Id to Column (that will be update by command) and use it's id  in following commands.

in my example P4_ITEM_VALUE is Static Id of Item Value Column. 

2) Write Dynamic action and call this JavaScript

var salary = apex.item( "P4_ITEM_VALUE"); 

salary.setValue(100);

or 

apex.item( "P4_ITEM_VALUE").setValue(10);

Oracle DDL with EXECUTE IMMEDIATE -- Drop Multiple Object at once

If You want to Drop Multiple Object at once use this script. Please using

This script check cursor query and take update of your database

/* Formatted on 3/31/2020 1:40:01 am (QP5 v5.139.911.3011) */

DECLARE

   CURSOR OBJECTLIST

   IS

      SELECT    'Drop '

             || OBJECT_TYPE

             || '  '

             || OWNER

             || '.'

             || OBJECT_NAME

             || ''

                DROPCOMMAND

        FROM ALL_OBJECTS

       WHERE OWNER = 'APPS' AND OBJECT_NAME LIKE 'EBA%';

BEGIN

   FOR LDX IN OBJECTLIST

   LOOP

      DBMS_OUTPUT.PUT_LINE (TO_CHAR (LDX.DROPCOMMAND));

      BEGIN

         EXECUTE IMMEDIATE LDX.DROPCOMMAND;

         DBMS_OUTPUT.PUT_LINE ('Object has been dropped.');

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.

            PUT_LINE (

                  SQLERRM

               || ' While performing this Command,'

               || TO_CHAR (LDX.DROPCOMMAND));

      END;

   END LOOP;

END;

select 'Drop '||OBJECT_TYPE||'  '||OWNER||'.'||OBJECT_NAME||';'

from all_objects

where owner='APPS'

AND OBJECT_NAME LIKE 'EBA%'