Monday, November 22, 2010

Extract filename from a Directory path in plsql

When I was searching for a Oracle API to list the file names from a folder/directory, I found the link
www.chrispoole.co.uk/tips/plsqltip2.htm

I just tried to use the script and provided the test results in this post. I did this because it seems to be a worthy script.


Test Enviroment: 
Test OS: Windows
Test DB: Oracle 10g
Tool USed: SQL Developer

SCRIPT:
SET SERVEROUTPUT ON;
DECLARE
pattern VARCHAR2(1024) := 'C:\tmp\*.*';
ns VARCHAR2(1024);
BEGIN
SYS.DBMS_OUTPUT.ENABLE(1000000);
SYS.DBMS_BACKUP_RESTORE.searchFiles(pattern, ns);
FOR each_file IN (SELECT FNAME_KRBMSFT AS name FROM X$KRBMSFT) LOOP
DBMS_OUTPUT.PUT_LINE(each_file.name);
END LOOP;
END;
/

Directory


Test Results:



I welcome my blog viewers to rpovide their valuable comments to improve this script 

Monday, November 22, 2010 by Team search · 0

Thursday, November 11, 2010

Different Types of BPEL Processes


There are 3 different types of processes or services

  1. A synchronous process
  2. An asynchronous process
  3. An empty process

In Synchronous services, the client invoking a synchronous process will wait for the response to the request.
The client will not process further until it receives response from the invoked synchronous process.



In Oracle BPEL Process Designer, when a project is created with the “Synchronous BPEL Process” template, the initial BPEL process flow contains

  • A client PartnerLink representing the client who initiates this BPEL process as a service. This is shown as the client in the diagram.
  • A receive activity, called receiveInput, to receive a request message from the client.
  • A reply activity, called replyOutput, for the BPEL process to return a synchronous response message to the client.


In asynchronous services, the client invokes the service but does not wait for the response. The client can continue with some other processing and later, when it does receive the response, it resumes whatever processing initiated the service request.              



In Oracle BPEL Process Designer, when a project is created with the “Asynchronous BPEL Process” template, the initial BPEL process flow contains
  • A client PartnerLink representing the client who initiates this BPEL process as a service. This is shown as the client in the diagram.
  • A receive activity, called receiveInput, to receive a request message from the client.
  • A invoke activity, called callbackClient, for the BPEL process to return asynchronous response message to the client.

Empty Service is useful to design a process flow as a client to initiate other services.


In Oracle BPEL Process Designer, when a project is created with the “Empty BPEL Process” template, the initial BPEL process flow does not contain any activities as shown above.

Thursday, November 11, 2010 by Team search · 0

BPEL Adapters


What are Adapters?
Adapters are used for efficient integration with back-end applications to optimize the business process. Adapters support a robust, light-weight, highly scalable, and standards-based integration framework, which enables disparate applications to communicate with each other. They are used to integrate packaged applications, legacy applications, databases, and Web services. Using adapters, the user can ensure interoperability by integrating applications that are heterogeneous, provided by different vendors, based on different technologies, and run on different platforms.­­­

The following types of technology adapters are available in BPEL.
·         File/FTP adapter
·         Database adapter
·         Oracle Apps adapter
·         AQ adapter
·         MQ adapter
·         JMS adapter

File/FTP Adapter

The file/FTP adapters enable to configure the BPEL process to interact with local and remote file system directories. The file and FTP adapters can read and write the following file formats and use the adapter translator component at both design time and run time
·         XML (both XSD- and DTD-based)
·         Delimited
·         Fixed positional
·         Binary data
·         COBOL Copybook data
It can also treat file contents as an opaque object.
For inbound file, the file and FTP adapters perform the following operations
·         Poll the file system looking for matches
·         Read and translate the file contents based on the translation logic defined at design time
·         Publish the same as an XML message
For outbound files sent from Oracle BPEL Process Manager, the file and FTP adapters perform the following operations
·         Receive messages from BPEL
·         Format the XML contents as specified at design time
·         Produce output files
For inbound file and FTP adapters have parameters for the inbound directory where the input file appears, the file naming pattern and the frequency with which to poll the directory.

Database Adapter

The database adapter enables a BPEL process to communicate with Oracle databases or third-party databases through JDBC.

The following operations are supported in the Database adapter

  • Call a Stored procedure or a function
  • Perform a table operation which can be one of the following
Ø  Insert Only
Ø  Update Only
Ø  Insert or Update
Ø  Update
Ø  Select
  • Poll for new or changed records in a table
  • Execute Custom SQL

Before connecting to a database through the database adapter, the database connection must be created and configured for that particular database using the Database Connection Wizard.

Oracle Applications Adapter  

The Oracle Apps adapter enables to interact with various interfaces of an Oracle Apps instance that is hosted in a given database connection.

It offers a rich set of object invocation which includes concurrent programs, XML gateway, EDI sources, business events apart form tables, views and APIs which can be invoked even through the database adapter.

It offers support for open interface imports, and other such things that can be done in the Oracle Apps.


It provides a flexible mechanism for bidirectional, asynchronous communication between two applications that uses Queues as a medium to pass on information to each other.

The enqueue operation is outbound while the dequeue operation is inbound thus the AQ adapter is both the producer and consumer of the AQ messages.

Specify the operation whether it is an Enqueue or Dequeue and the queue name from which the message is to be enqueued or dequeued

by Team search · 0

JPR and Build.xml file contains what?

JPR

This is called as project file. This acts as a parent node in the System or Application Navigator for all the files within the project. All relevant project information is stored in this file. This file gets created when a BPEL project is created with default project properties or gets updated when alter those settings for an existing project.
JPR file loads all the required BPEL project related files into JDeveloper. There is no need to edit a project file directly; this automatically gets updated whenever a change is made in the properties by wizard.

Build.xml file

This is an apache ant file used for compiling and deploying process.
This file contains reference to key files ant-orabpel.xml and build.properties file to bind properties value at the time of building a jar file.
                                  
ant-orabpel.xml: is a BPEL server file residing in the server. This file contains server information like server host name, port number, server credentials, default domain name, etc.

build.properties: is a project specific properties file and located with in the project folder. Properties specific to the BPEL process including custom properties can be specified in this file.

by Team search · 0

Various files are created in a BPEL Project

Below are the list of file types i know,

build.xml                             -              Deployment parameter file
.jpr                                         -              JDeveloper project file
.xsd                                        -              XML Schema Definition
.wsdl                                     -              Web Services Description Language File
bpel.xml                              -              Business process data file
graphics.xml                       -              Graphics data file
build.properties               -              Setup file to substitute the parameters
.bpel                                      -              Process Flow
.xsl                                         -              XML transformation style sheet (using XSLT)
.schema                               -              Oracle database schema property file (especially created for Apps or DB adapters)
.table                                    -              Schema file for database objects (especially created for Apps or DB adapters)                                       

by Team search · 0

What is BPEL Suitcase?



BPEL Suitcase is a JAR file that gets created when a BPEL process is compiled. The BPEL process archive and its components are compiled and packaged into it. Target middle tier server name, domain and revision number are can be referred by the option _bpelc_ in custom build.xml. . This JAR file can be directly deployed into middle tier process manager using BPEL console.
This JAR file includes the following files
  • .bpel file implementation of the process
  • .wsdl file
  • bpel.xml deployment descriptor file
  • Any other local resource that is required, such as XML schemas, Java classes or libraries, and so on.

    by Team search · 0

    Monday, November 1, 2010

    API to Update Oracle Applications Password (fnd_user_pkg.updateuser)

    Below script can be used to update the Oracle apps password via API fnd_user_pkg.updateuser

     Script:

    BEGIN
    FND_USER_PKG.UPDATEUSER(x_user_name            => 'teamsearch'
                           ,x_owner                => 'SEED'
                           ,x_unencrypted_password => 'oracle@123'
                           ,x_password_date        => SYSDATE + 500);

    END;

    Monday, November 1, 2010 by Team search · 1

    Disclaimer

    The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.