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%'

Format date and time in a Windows batch script

@ECHO OFF
: Sets the proper date and time stamp with 24Hr Time for log file naming
: convention

SET HOUR=%time:~0,2%
SET dtStamp9=%date:~-4%%date:~4,2%%date:~7,2%_0%time:~1,1%%time:~3,2%%time:~6,2% 
SET dtStamp24=%date:~-4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%

if "%HOUR:~0,1%" == " " (SET dtStamp=%dtStamp9%) else (SET dtStamp=%dtStamp24%)

ECHO %dtStamp%

PAUSE

System Date & Time in Windos OS Command

echo %date%
Tue 02/11/2020

echo %time%
10:39:09.74

echo %date%  %time%
Tue 02/11/2020 10:39:09.74

index Table Type in PLSQL

DECLARE

  TYPE TABLENAMETYPE IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;

  L_TABLENAMETYPE TABLENAMETYPE;

BEGIN

  --YOU CAN USE THIS L_TABLENAMETYPE TYPE HERE

  L_TABLENAMETYPE(1) := 'AB';

  L_TABLENAMETYPE(2) := 'ABC';

  L_TABLENAMETYPE(3) := 'ABD';

  L_TABLENAMETYPE(4) := 'ABE';

  FOR LDX IN 1 .. L_TABLENAMETYPE.COUNT

 

   LOOP

 

    DBMS_OUTPUT.PUT_LINE(L_TABLENAMETYPE(LDX));

 

  END LOOP;

  ---DELETE ALL VALUES

  L_TABLENAMETYPE.DELETE;

END;

--RESULT

AB

ABC

ABD

ABE

---USE TABLE TYPE WITH BULK COLLECT INTO

DECLARE

CURSOR C IS

  SELECT * FROM EMP;

TYPE ANY_TABLE_TYPE IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;

EMP_REC ANY_TABLE_TYPE;

BEGIN

  OPEN C;

  FETCH C BULK COLLECT

 

    INTO EMP_REC;

  CLOSE C;

  FORALL I IN EMP_REC.FIRST .. EMP_REC.LAST

 

    UPDATE EMP

   

       SET COMM = EMP_REC(I).SAL * 10 / 100

   

     WHERE EMPNO = EMP_REC(I).EMPNO;

  COMMIT;

END;

Perform Dynamic DLL in Oracle

EXECUTE IMMEDIATE statement can be used to execute dynamic SQL statements.

Syntax: EXECUTE IMMEDIATE 'User_Code';

--

-- Create/Drop Table using this PLSQL Script or Procedure

-- DLL Script

-- by mfaisal 02-Jan-2020

Declare

---Create

begin

  execute immediate 'CREATE TABLE X_DDL_ON_APPS (APPS_ID NUMBER)';

end;

Declare

--Drop with Oracle EBS Package

begin

  system.ad_apps_private.do_apps_ddl(schema_name => 'APPS',

                                     ddl_text => 'DROP TABLE X_DDL_ON_APPS ');

end;

Backup of all Oracle Database objects on Server with Procedure

----Backup With Procedure

---Create Directory

CREATE OR REPLACE DIRECTORY PS_DIR AS '/u01/oracle/PROD/fs2/EBSapps/appl/au/12.0.0/forms/BackUp';

---Create Procedure

  

   CREATE OR REPLACE PROCEDURE    EXPORT_DDL

AS

    V_DDL_CLOB  CLOB;

    VPATH VARCHAR2(255);

BEGIN

    FOR C IN (SELECT OBJECT_NAME, OBJECT_TYPE

             FROM USER_OBJECTS

             WHERE OBJECT_TYPE IN ('TABLE','VIEW','FUNCTION','PROCEDURE','PACKAGE','PACKAGE_BODY')

             and OBJECT_NAME like '%TGC%')

    LOOP

        V_DDL_CLOB := DBMS_METADATA.GET_DDL(C.OBJECT_TYPE, C.OBJECT_NAME);

        DBMS_XSLPROCESSOR.CLOB2FILE(V_DDL_CLOB, 'PS_DIR', C.OBJECT_TYPE || '_' || C.OBJECT_NAME||'.SQL');

    END LOOP;

END;

--- Call procedure

Exec EXPORT_DDL;

OAF Page to Upload Files into Server from local Machine

1. Create a New Workspace and Project

File > New > General > Workspace Configured for Oracle Applications

File Name – PrajkumarFileUploadDemo

Automatically a new OA Project will also be created

Project Name -- FileUploadDemo

Default Package -- prajkumar.oracle.apps.fnd.fileuploaddemo

2. Create a New Application Module (AM)

Right Click on FileUploadDemo > New > ADF Business Components > Application Module

Name -- FileUploadAM

Package -- prajkumar.oracle.apps.fnd.fileuploaddemo.server

Check Application Module Class: FileUploadAMImpl Generate JavaFile(s)

3. Create a New Page

Right click on FileUploadDemo > New > Web Tier > OA Components > Page

Name -- FileUploadPG

Package -- prajkumar.oracle.apps.fnd.fileuploaddemo.webui

4. Select the FileUploadPG and go to the strcuture pane where a default region has been created

5. Select region1 and set the following properties --

   

Attribute	Property
ID	PageLayoutRN
AM Definition	prajkumar.oracle.apps.fnd.fileuploaddemo.server.FileUploadAM
Window Title	Uploading File into Server from Local Machine Demo Window
Title	Uploading File into Server from Local Machine Demo

   

6. Create messageComponentLayout Region Under Page Layout Region

Right click PageLayoutRN > New > Region

Attribute	Property
ID	MainRN
Item Style	messageComponentLayout

7. Create a New Item messageFileUpload Bean under MainRN

Right click on MainRN > New > messageFileUpload

Set Following Properties for New Item --

Attribute	Property
ID	MessageFileUpload
Item Style	messageFileUpload

8. Create a New Item Submit Button Bean under MainRN

Right click on MainRN > New > messageLayout

Set Following Properties for messageLayout --

Attribute	Property
ID	ButtonLayout

Right Click on ButtonLayout > New > Item

Attribute	Property
ID	Submit
Item Style	submitButton
Attribute Set	/oracle/apps/fnd/attributesets/Buttons/Go

9. Create Controller for page FileUploadPG

Right Click on PageLayoutRN > Set New Controller

Package Name: prajkumar.oracle.apps.fnd.fileuploaddemo.webui

Class Name: FileUploadCO

Write Following Code in FileUploadCO processFormRequest

import oracle.cabo.ui.data.DataObject;
import java.io.FileOutputStream;
import java.io.InputStream;
import oracle.jbo.domain.BlobDomain;
import java.io.File;
import oracle.apps.fnd.framework.OAException;

public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{ super.processFormRequest(pageContext, webBean);  

 if(pageContext.getParameter("Submit")!=null)
 {
  upLoadFile(pageContext,webBean);    
 }
}

CODE #1 -- If Page has not deployed at instance, testing at Local Machine, use following Code

public void upLoadFile(OAPageContext pageContext,OAWebBean webBean)
{ String filePath = "D:\\PRajkumar";
 System.out.println("Default File Path---->"+filePath);

 String fileUrl = null;
 try
 {
  DataObject fileUploadData =  pageContext.getNamedDataObject("MessageFileUpload");

//FileUploading is my MessageFileUpload Bean Id
  if(fileUploadData!=null)
  {
   String uFileName = (String)fileUploadData.selectValue(null, "UPLOAD_FILE_NAME"); 
   String contentType = (String) fileUploadData.selectValue(null, "UPLOAD_FILE_MIME_TYPE");  
   System.out.println("User File Name---->"+uFileName);

   FileOutputStream output = null;
   InputStream input = null;

   BlobDomain uploadedByteStream = (BlobDomain)fileUploadData.selectValue(null, uFileName);
   System.out.println("uploadedByteStream---->"+uploadedByteStream);
                          
   File file = new File("D:\\PRajkumar", uFileName);  
   System.out.println("File output---->"+file);

   output = new FileOutputStream(file);

   System.out.println("output----->"+output);
   input = uploadedByteStream.getInputStream();

   System.out.println("input---->"+input);
   byte abyte0[] = new byte[0x19000];
   int i;
    
   while((i = input.read(abyte0)) > 0)
   output.write(abyte0, 0, i);

   output.close();
   input.close();
  }
 }
 catch(Exception ex)
 {
  throw new OAException(ex.getMessage(), OAException.ERROR);
 }    
}

CODE #2 -- If Page has been Deployed at Instance, Use Following Code 

public void upLoadFile(OAPageContext pageContext,OAWebBean webBean)
{ String filePath = "/u01/app/apnac03r12/PRajkumar/";
 System.out.println("Default File Path---->"+filePath);

 String fileUrl = null;
 try
 {
  DataObject fileUploadData =  pageContext.getNamedDataObject("MessageFileUpload");

//FileUploading is my MessageFileUpload Bean Id
  if(fileUploadData!=null)
  {
   String uFileName = (String)fileUploadData.selectValue(null, "UPLOAD_FILE_NAME");  
   String contentType = (String) fileUploadData.selectValue(null, "UPLOAD_FILE_MIME_TYPE");  
   System.out.println("User File Name---->"+uFileName);

   FileOutputStream output = null;
   InputStream input = null;

   BlobDomain uploadedByteStream = (BlobDomain)fileUploadData.selectValue(null, uFileName);
   System.out.println("uploadedByteStream---->"+uploadedByteStream);
                          
   File file = new File("/u01/app/apnac03r12/PRajkumar", uFileName);  
   System.out.println("File output---->"+file);

   output = new FileOutputStream(file);

   System.out.println("output----->"+output);
   input = uploadedByteStream.getInputStream();

   System.out.println("input---->"+input);
   byte abyte0[] = new byte[0x19000];
   int i;
    
   while((i = input.read(abyte0)) > 0)
   output.write(abyte0, 0, i);

   output.close();
   input.close();
  }
 }
 catch(Exception ex)
 {
  throw new OAException(ex.getMessage(), OAException.ERROR);
 }    
}

10. Congratulation you have successfully finished. Run Your page and Test Your Work

 Page has deployed and Used CODE #2

Before Upload the File

After Upload the File

   

 Page has not been deployed and Used CODE #1

Before Upload the File

After Upload the File