2011/07/04

ODC: How to scan and commit files to UCM

Oracle offers in its Enterprise Content Management (ECM) suite solutions for the capture and document recognition. Oracle Document Capture (ODC) 10g is a desktop application designed to capture documents for, later, recognize some areas and metadata and index them against a target repository which can be a file system, a database, Content Server UCM, etc.

Oracle Document Capture incorporates other features such as indexing, recognition and import of documents in a customized and programmable batch processes. It also includes the Oracle Distributed Document Capture (ODDC) technology, an application based on a web interface to scan and index documents in a distributed environment.

You can follow a guide to install and configure the software here: http://studentsatoracle.blogspot.com/2010/11/odc-install-and-configure-oracle.html

In the viewlet below, you can follow a tutorial to prepare the software to scan invoices (in this example) and index it in a Oracle UCM Content Server. The viewlet shows all the needed steps to be able to scan and index and how to capture metadata automatically using OCR zones.

Documentation:
Oracle's oficial documentation for ECM 10g
Installation guide for ODC


Downloads:
Oracle ODC and ODDC


Viewlet:
(Duration: 8 min.)


2011/05/17

Webcenter: Build and use Taskflows from Data Controls

In Enterprise 2.0 applications is important to have a flexible and customizable screens that make easy the navigation and the interaction for users. So with Oracle Webcenter Suite 11g you are able to create new Business Mashups easily and will help you to show graphical information, interact with a database, ...

In the example below, we are adding the two kinds of mashups to a Webcenter Spaces page. The first one is a SQL Data Control that will fetch data from a database table and the second one is a webservice deployed on the Weblogic application server that will insert data from the Webcenter page to the same database table.

Note: You can follow this guide to deploy a new Webservice

Once the mashups are added, the video is showing how to test it and checking it by connecting to the database through sqlplus.

2011/05/02

jDeveloper: Create and deploy a Java Webservice

This tutorial shows how to create a simple Java Webservice using the Oracle jDeveloper 11g IDE. The attached code for the example connects to a database through JDBC and executes a SQL statement to insert a new row in a specific table.

Once the application has been configured, it will be deployed and tested in a Oracle Weblogic Server. We will execute the webservice through a web browser and check it by connecting to the database with a sqlplus console.

Step-by-step:
1) Create a generic java application and add the webservice component

2) Create a new java class and add some methods to publish. Here you have an example:
NOTE: Remember to adapt this code to your requirements.

public void InsertValue(String user, String value){
try{
//Example from http://studentsatoracle.blogspot.com
Connection con=null;
Class.forName("oracle.jdbc.driver.OracleDriver");

//Configure the jdbc connection: jdbc:oracle:thin:@host:port:SID,username,passwd
con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:owcdb01", "DEV_WEBCENTER","welcome1");
Statement s = con.createStatement();
//Configure the SQL query as you want, the table must exists
s.execute("INSERT INTO DEV_WEBCENTER.uservalues (ID,USERNAME,INSERTDATE,VALUE) VALUES (DEV_WEBCENTER.uservalues_seq.nextval,'"+user+"',To_char(SYSDATE),"+value+")");
s.close();
con.close();
}catch(Exception e){
System.out.println("ERROR: "+e.getMessage());
}
}


3) Configure the webservice and publish the desired methods

4) Deploy the application. You can follow this guide to create a deployment profile: http://studentsatoracle.blogspot.com/2011/02/jdeveloper-how-to-deploy-application-to.html

5) Open a Weblogic Server console and test the application

Documentation:
Oracle's official documentation for jDeveloper 11g

Downloads:
Oracle jDeveloper 11g

Viewlet:
(Duration: 3 min.)

2011/03/28

Combine Partitioning, Advanced Compression and TotalRecall features in Oracle Database 11g

Using Partitioning together, Advanced Compression and TotalRecall features can reduce the workload and, thus, work more efficiently.

Partitioning provides several ways of partitioning a table into multiple subtables and define multiple criteria partition from a Partition Key.

Advanced Compression provides multiple compression features, including OLTP compression. This feature is widely explained and tested in this same blog. Follow this link if you want to explore it deeper: http://studentsatoracle.blogspot.com/2010/12/advanced-compression-in-oracle-database_03.html.

Last, but no least, TotalRecall creates a flashback file to record activity on the table, so the user can see the value that had a record of that table on a certain day and time. In addition, the user can define the time of data retention and the quota of disk space used for it.

We'll test a "worst-case" scenario using all three features on a test table. We will create a table partitioned in two subtables, each one of them having a different compression type. Also, we'll set a flashback file so we can query the values of the table for a year.

But, we'll just focus on the most efficient way of combining the three features, we won't check any metrics like execution times or compression rate.

First we must do some thinking. Partitions of a table must be defined at the create table command while compression can be either defined at the creation of the table or later with the alter table command. We can alter a partitioned table, but due to limitations of partitioning, we can't alter the compression type of a subtable. So, we'll have to define the partitions and the compression type when we create the table.

So, let's create our test table:


create table atest ( c1 number(5), c2 varchar2(5), c3 varchar2(5), primary key (c1) validate)
partition by range(c1) (
partition atest_p1 values less than(6) compress,
partition atest_p2 values less than(maxvalue) compress for oltp
);


With this setting, we split the newly created table test in two subtables and the criteria for the partition is the range of values at the column c1. Any item with c1 lower than 6 will go to the subtable atest_p1, while the rest will go to atest_p2. In addition, we define the compression type for the subtables. Subtable atest_p1 has a basic compression, while atest_p2 has OLTP compression.

Then we fill it a little with some silly loop:


insert into atest
select rownum, (rownum*2)+2, (rownum*3)+3
from dual connect by rownum <= 10;


Here comes the funny part. Once partitions, compression types and data are set, it's time to test the TotalRecall feature. First of all, we will need to create a new Tablespace: 


create tablespace flasharch;



Now, we'll create and set the flashback file:



create flashback archive atest_fla tablespace flasharch quota 1M retention 1 year;


Once we create the flashback, we have to alter our test table to "link" them:


alter table atest flashback archive atest_fla;


And that's all there is to it. Now, let's give it a shot.

Make a simple update, changing the values inside our test table:


update atest set c2=c2+c3+12;


Then, execute a query refering to a moment before the update. To make things more difficult, let's ask only for the values of subtable atest_p2 (change the value inside the brackets for the time before the update if testing):


select * from atest partition (atest_p2) as of timestamp to_timestamp('DD-MMM-YY hh.mm.ss');


Keep in mind that once we alter the table to the flashback, we won't be able to perform some operations on that table, mostly alter table. We'll have to drop the flashback if we want to perform major changes to the "flashbacked" table. So, while this method gives all the benefits of the three features, also has the disadvantage of making the table somewhat static and unalterable. This means there must be some previous planning, because dismantling the settings may end in loss of information. Knowing all that, the advantages of using various powerful features integrated in the database are obvious.

Thanks for reading and feel free to leave any comments or questions.

2011/03/16

Setting listener.ora and tnsnames.ora on Amazon EC2

Every time you start your Amazon EC2 instance, your public and private DNS change. This is a problem because we have to modify the files listener.ora and tnsnames.ora many times. If not, your listener (among other things) will not work.

The following bash script creates the two files with the private ip that you pass as argument and starts the listener. The old files are renamed in the same folder (just in case you want to recover them).

Check that the LISTENER_DIR variable is correct in your system.

#!/bin/bash

EXPECTED_ARGS=1
LISTENER_DIR=/u01/app/oracle/product/11.2.0/db_1/network/admin

if [ $# -ne $EXPECTED_ARGS ]
then
echo "Usage: `basename $0` {private_ip}"
exit 0
fi

cp ${LISTENER_DIR}/listener.ora ${LISTENER_DIR}/listener.ora.old

echo "LISTENER =" > ${LISTENER_DIR}/listener.ora
echo " (DESCRIPTION_LIST =" >> ${LISTENER_DIR}/listener.ora
echo " (DESCRIPTION =" >> ${LISTENER_DIR}/listener.ora
echo " (ADDRESS = (PROTOCOL = TCP)(HOST = "${1}")(PORT = 1521))" >> ${LISTENER_DIR}/listener.ora
echo " )" >> ${LISTENER_DIR}/listener.ora
echo " )" >> ${LISTENER_DIR}/listener.ora

lsnrctl start

cp ${LISTENER_DIR}/tnsnames.ora ${LISTENER_DIR}/tnsnames.ora.old

echo "ORCL =" > ${LISTENER_DIR}/tnsnames.ora
echo " (DESCRIPTION =" >> ${LISTENER_DIR}/tnsnames.ora
echo " (ADDRESS = (PROTOCOL = TCP)(HOST = "${1}")(PORT = 1521))" >> ${LISTENER_DIR}/tnsnames.ora
echo " (CONNECT_DATA =" >> ${LISTENER_DIR}/tnsnames.ora
echo " (SERVER = DEDICATED)" >> ${LISTENER_DIR}/tnsnames.ora
echo " (SERVICE_NAME = orcl)" >> ${LISTENER_DIR}/tnsnames.ora
echo " )" >> ${LISTENER_DIR}/tnsnames.ora
echo " )" >> ${LISTENER_DIR}/tnsnames.ora

2011/02/18

Oracle Webcenter 11g Developer's Tutorial

Oracle Fusion Middleware has released a new tutorial for the new version of Oracle Webcenter (11.1.1.4) which introduce users to the Webcenter Portal Framework for developers.

The tutorial covers the main features:
1) Create a web application through jDeveloper 11g
2) Modify Page Templates and Skins
3) Changing the look & feel of the application
4) Consume content from an external repository (Oracle UCM)
5) Pages permissions and runtime content editing with Oracle Composer

Tips and troubleshoot:
jDeveloper: How to deploy an application to an external WebLogic Server
jDeveloper: Connecting UCM as a repository
Setting permissions between Oracle UCM and Webcenter Spaces

Downloads:
Tutorial Content Materials

Tutorial:
Go Green, Eat Fresh

2011/02/14

Setting permissions between Oracle UCM and Webcenter Spaces

Web applications usually consume dynamic content from the Content Server to enrich the website. In a Webcenter application, the task flow Content Presenter allows your web application to display documents from Oracle UCM (or other repositories) using the advantages that UCM provides: document conversions, workflow revisions or security layer.

This last one is a important point to take care because the web application's security permissions will be based on the configuration between the Content Server and Webcenter. For this reason, a summary of how to check and troubleshoot your configuration is explained in the section below:

In the example, you are going to grant write permission to user Bob who is a contributor in the web application and has the role PersonalSpacesRole. This role is defined for users who will interact with the Content Server. You are going to grant permissions to any folder created with security group "public".

You can follow the official documentation to create these users, roles and security groups: http://download.oracle.com/docs/cd/E17904_01/webcenter.1111/e12405/wcadm_documents.htm#CIHFHAAD

Step-by-step:

1) Login to the Content Server as an administrator user
2) In the administration tab, open Admin Applets
3) Click on User Admin
4) Click on Security menu and select Permissions by Role option
5) Select the webcenter user role (in the example PersonalSpacesRole) and select the security group you want to grant permissions (in the example Public)
6) Click on Edit Permissions
button and update the values.
7) Now, all the users with the PersonalSpacesRole should be able to edit documents in any Public folder











Documentation:

Oracle's official documentation for UCM 11g
Oracle's official documentation for Webcenter Spaces

Downloads:
Oracle UCM 11g
Oracle Webcenter 11g

jDeveloper: Connecting UCM as a repository

During the development of a web application is important to provide contents to enrich the portal. Using Oracle jDeveloper 11g you can easily create a connection to any content repository which will allow you to navigate it and contribute the application with information stored in it.

In this example, you are going to create a connection with Oracle UCM as a main repository and the video will show how to troubleshoot some connection errors by checking the connection parameters like the RIDC socket type, hostname or listening port...

Documentation:

Oracle's official documentation for jDeveloper 11g

Downloads:
Oracle jDeveloper 11g

Viewlet:
(Duration: 2 min.)

2011/02/10

jDeveloper: How to deploy an application to an external WebLogic Server

Oracle jDeveloper has an integrated WebLogic Server to test and debug applications locally, but you can also deploy it to an external server. To do this, you must create a connection with a WebLogic domain and a deployment profile as the viewlet shows below.

Documentation:

Oracle's official documentation for jDeveloper 11g

Downloads:
Oracle jDeveloper 11g

Viewlet:
(Duration: 2 min.)

2011/02/09

UCM: Managing video conversions and filters with FlipFactory

As you know, Oracle Universal Content Management (UCM) allows you to store video files and transform them into other video file with different format and resolution. Through the FlipFactory admin console, a 3rd party conversion module, you can configure new conversion types where you can choose the output extension, resolution,... or apply filters like text overlay, saturation, noise reduction...

At the end of this post, you can follow a viewlet explaining the needed steps to configure the Content Server to provide a new conversion type, in this example, a new conversion with .avi output file with a watermark filter.

Documentation:

Oracle's official documentation for UCM 11g

Downloads:
Oracle ECM suite 11g

Viewlet:
(Duration: 4 min.)

2011/02/04

How to delete files older than X days in Windows Batch

In this entry I will explain how to delete files in a Windows environment depending on its age.

This task is rather easy in Unix but in Windows (DOS) it gets quite more complicated.

Now imagine you have some folders storing all the backups the system does, and now you would like to clean those folders so they just have the last ones.

I searched a lot and seems that with normal Batch is almost impossible to do it, and after trying like 3 or 4 different options I found this one to be the best overall.

First off, as I already said, with normal batch this task is so complicated, that is why we will use and extra tool called Robocopy. This tool is included in the Windows Server 2003 Resource Kit Tools which you can download from here:

http://download.microsoft.com/download/8/e/c/8ec3a7d8-05b4-440a-a71e-ca3ee25fe057/rktools.exe

Robocopy is a powerful copy command with lots of useful options.

Then idea is quite simple:

- We will create a temporal folder.
- We will move the files we want to delete to that folder.
- Finally we will delete the folder created with the files in it.

Now on to the code.

I will just put the code commenting each line, instead of writing what it does in a not-so-easy-to-understand paragraph.

* * * * *

@ECHO OFF
ECHO # Starting...
ECHO.

REM Setting the log file name
SET day=%date:~0,2%
SET mon=%date:~3,2%
SET year=%date:~6,4%
SET logfile_name=clearing_log_%day%-%mon%-%year%

REM Writing the first line in the log including the time
SET time=%time:~0,5%
ECHO # Clearing.bat launched at %time% > %logfile_name%.txt
ECHO. >> %logfile_name%.txt

REM Program Start

REM variables - here just write the paths of the folders you want to clear
SET folder_1_path="C:\Documents and Settings\user\Desktop\Folder_1"
SET folder_2_path="C:\Documents and Settings\user\Desktop\Folder_2"

REM this next variable will be the path of the temporal folder (you can write whatever path you want as it will be deleted)
SET path_temp_folder="C:\Documents and Settings\user\Desktop\Justin Bieber"

REM first we will check if the folders specified in the path variables exists
IF EXIST %folder_1_path% (
GOTO folder_1_exists
) ELSE (
ECHO [ERROR] The folder 1 does not exist.
ECHO [ERROR] The folder 1 does not exist. >> %logfile_name%.txt
GOTO final
)

:folder_1_exists
REM now we check if the second folder exists
IF EXIST %folder_2_path% (
GOTO folder_2_exists
) ELSE (
ECHO [ERROR] The folder 2 does not exist.
ECHO [ERROR] The folder 2 does not exist. >> %logfile_name%.txt
GOTO final
)

:folder_2_exists
REM now we will delete the files in the first folder

REM first we create the temporal folder
MKDIR %path_temp_folder%

REM now we move the files older than X (in this case 7) days to that folder
ROBOCOPY %folder_1_path% %path_temp_folder% /move /minage:7 >nul
REM ROBOCOPY syntax = source destination [file [file]…] [options]

REM and finally as stated we delete the temporal folder
RMDIR %path_temp_folder% /s /q

ECHO Folder 1 cleaned.
ECHO Folder 1 cleaned. >> %logfile_name%.txt

GOTO clear_1_done

:clear_1_done
REM now we will delete the files in the second folder

REM first we create the temporal folder
MKDIR %path_temp_folder%

REM now we move the files older than X (in this second case 3) days to that folder
ROBOCOPY %folder_2_path% %path_temp_folder% /move /minage:3 >nul
REM ROBOCOPY syntax = source destination [file [file]…] [options]

REM and finally as stated we delete the temporal folder
RMDIR %path_temp_folder% /s /q

ECHO Folder 2 cleaned.
ECHO Folder 2 cleaned. >> %logfile_name%.txt

GOTO final

:final
ECHO.
ECHO # Done.
ECHO. >> %logfile_name%.txt
ECHO # Done. >> %logfile_name%.txt
ECHO # Log file = %logfile_name%.txt
ECHO # Press any key to exit.
PAUSE>NUL

* * * * *

That's it.
Just create and new .bat file, copy the code above, modify it (paths, older days...etc) to match your goals and voilà ;)

Hope it helps.

2011/01/18

How to get traces in apex automatically (in all the pages)

We know that adding ?p_trace=YES to the APEX URL generates SQL Trace information for the page for the current session.. This is very useful when the problem is located in few places. However, sometimes the performance problem seems to be random, it happens in different website pages or it may be difficult to reproduce. For these cases, the above solution is not valid.

What we need is to generate traces automatically for the entire website (like adding ?p_trace=YES in all the url pages but automatically).

Our solution is to create a new process (on load - before header) in the home of our website. This process executes:
execute immediate 'alter session set sql_trace=true';
To create the process the steps are:
  1. In apex, go to the Edit page of the page that will execute the process
  2. In the Processes section, click the Create new Process icon.
  3. Select PL/SQL and click next
  4. Type the name of the process and select "On Load - Before Header" in the Point LOV and click next.
  5. Type: execute immediate 'alter session set sql_trace=true';
  6. Click Create Process
Once it is done, every time the user loads a web page the trace is generated automatically. Not only in the home but in all the pages that the user visits. When the problem is reproduced we are sure that the trace has been generated in the directory pointed by the user_demp_dest parameter. Remember to delete the process when you solve the problem.

We need to grant to the parsing schema of our application the privilege to alter the session with the following command:

grant alter session to parsing_schema

You can find the parsing schema your application is using in apex:

Home > Application Builder > Application number > Shared Components > Edit Security Attributes