Restore Table to specific Point in Time or SCN using FlashBack Table

Hi all,

This week i decided to share some of Admin related tip. We all came across  the situations when accidentally execution of wrong DELETE or UPDATE statement got committed in the database.

Like Developer thinks that he is working on Test Server but accidentally executed following command to update all rows in Production environment.

Update Emp
set Sal = nvl(Sal,0) + ((nvl(Sal,0) * 15) / 100); 

commit; 

This query was to check the affect of increment in salaries by 15% in test environment only for Sales department but it updates all rows and statement is committed into database.

Now what to do in this situation? With Oracle10g and above you don't have to worry about it using Flashback Table we can restore our Table to specific Time or SCN while database is still online.

See What Oracle Docs says about Flashback Table

Oracle Flashback Table provides the DBA the ability to recover a table or set of tables to a specified point in time in the past very quickly, easily, and without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations. Flashback Table restores tables while automatically maintaining associated attributes such as current indexes, triggers and constraints, and not requiring the DBA to find and restore application-specific properties. Using Flashback Table causes the contents of one or more individual tables to revert to their state at some past SCN or time.

Any ways let get into the example

Prerequisites:  From Oracle Documentation


The prerequisites for using the Flashback Table feature on a table are as follows:

• Row movement must be enabled on the table. You can enable row movement with the following SQL statement:
  ALTER TABLE table ENABLE ROW MOVEMENT;
• You must have been granted the FLASHBACK ANY TABLE system privilege or you must have the FLASHBACK object privilege on the table.
  
• You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
  
• Undo information retained in the undo tablespace must go far enough back in time to satisfy the specified target point in time or SCN for the FLASHBACK TABLE operation.
  

Example:

- Create sample table EMP_FLASH_TEST as copy of EMP table in SCOTT Schema

create table scott.Emp_Flash_test 
as 
select * from scott.emp;

- Execute this command to delete all rows from the table (Also Note the Time of the operation)

delete from scott.emp_flash_test
;

commit; 

- Run this query as DBA and note down the SCN number at the time of the operation (This query will provide information about the operations executed on database This feature called Flasback Transaction Query)

select xid, operation, table_name, start_scn
from flashback_transaction_query
where table_name = 'EMP_FLASH_TEST'
and operation = 'DELETE';

Result would be similar to this
Xid      /                        Operation / TableName /                  START_SCN 
0400200052040000    DELETE    EMP_FLASH_TEST    2664690
0400200052040000    DELETE    EMP_FLASH_TEST    2664690
0400200052040000    DELETE    EMP_FLASH_TEST    2664690

- Enable row movement of the table

alter table scott.emp_flash_test enable row movement;

- Execute Flashback Table command to specific timestamp or SCN 

flashback table scott.emp_flash_test to scn 2664690;

-Verify the result now all rows are restored back to the position before the DELETE statement.

select * from scott.emp_flash_test;

Hope it helps

More info about Flashback Table check here

You can follow me on Twitter as well

Have a nice day,
Baig

Storing and Retrieving Images / Word / Excel / PDF and Movies in Oracle Database using Forms10g

Hi all,

I have seen lot of people asking about it at OTN Forums that "How to Store Retrieve Images, Word, Excel, Pdf and Movies into Oracle using Forms".

So here i m providing easiest solution by using WEBUTIL Library.

You can download my Working example

Pre-requisites:
- Webutil setup and configuration (My old post can help u)

-In ORACLE_HOME/forms/server/webutil.cfg file the parameter transfer.database.enabled must be set to TRUE to use this function.

- Create a table called LOBS_TABLE in SCOTT Schema

drop table lobs_table;

create table lobs_table 
(blob_id number primary key,
 char_clob clob default empty_clob(),
 image_blob blob default empty_blob(),
 word_blob blob default empty_blob(),
 pdf_blob blob default empty_blob(),
 Movie_blob blob default empty_blob())
 tablespace users;

Note: In this form we have 2 buttons Browse and View for Each Blob item field the purpose of Browse field is to Open file dialog box to select the file which u want to store in the database and View will retrieve that file from database back to client and open it.

Steps to follow:
- Create a simple form on LOBS_TABLE
Similar to this screen
(for Word, PDF, and Movie item i am not using any special item its just default Image item)

- To Check the progress of Uploading Files Write User-defined Trigger named "CHECK_LOB_PROGRESS" at Block level code similar to this

if webutil_file_transfer.Asynchronous_Upload_Success then 
     message('File uploaded successfully');
     message('File uploaded successfully');
else
     message('Some error in uploading...');
     message('Some error in uploading...');
end if;

- Create WHEN-CREATE-RECORD Trigger at block level to generate Unique BLOB_IDs for Table

:blob_id := to_number(to_char(sysdate,'DDMMHHMISS'));

- Create 2 buttons for each Blob Item "Browse" and "View" as shown in last screen

- On Browse Photos button write this code (This will ask for File Dialog box to choose Image )

declare
    vfilename varchar2(3000);
begin
    vfilename := client_get_file_name('c:\', file_filter => ' Image files (*.jpg)|*.jpg|');
   client_image.read_image_file(vfilename,'jpg','lobs_table.image_blob');    client_image.write_image_file(vfilename,'jpg','lobs_table.image_blob',maximize_compression,ORIGINAL_DEPTH);    
end;

- On "View" button write code similar to this (To Retrieve the file which is stored in the database)
This code will copy the file from DB to Client directory C:\WINDOWS\TEMP and Open a file from there.

declare
    vboolean boolean;
    vfilename varchar2(120) := 'c:\WINDOWS\Temp\img_'||:blob_id||to_char(sysdate,'DDMMRRRR_HH24MISS')||'.jpg';
begin
  vboolean :=
  webutil_file_transfer.DB_To_Client_With_Progress
                     ( vfilename,  --filename
                       'lobs_table', ---table of Blob item
                       'image_blob',  --Blob column name
                       'blob_id = '||:blob_id, ---where clause to retrieve the record
                       'Downloading from Database', --Progress Bar title
                       'Wait to Complete'); --Progress bar subtitle
 client_host('cmd /c start '||vfilename);
end;

- Write on Browse Document button code similar to this

declare
    vfilename varchar2(3000);
    vboolean boolean;
begin
    vfilename := client_get_file_name('c:\',file_filter => 'Document files (*.doc)|*.doc|');
    
    vboolean := webutil_file_transfer.Client_To_DB_With_Progress
                     (   vfilename, 
                         'lobs_table', 
                         'word_blob',
                         'blob_id = '||:blob_id,
                         'Progress',
                         'Uploading File '||vfilename,
                         true,  ---Asynchronous uploading
                         'CHECK_LOB_PROGRESS'); ---User Call back trigger 
                         
end;

Note: With Client_To_DB_With_Progress Procedure a progress bar will appear automatically when file is uploading to or retrieving from Database.

- Write similar code for View Document button as View Image button Only change the extension of the file at the end.

- Repeat the code same as Browse and View Document for Rest of Blobs Item (PDF and Movie)

- Run the form and Create button Press Browse button first after loading of Your file a message will appear File uploaded successfully (Not for Images, Image will appear in Image item by default)

- Save the record

- Press View to View the file stored in database.

Conclusion:
Webutil is very useful library specially for interaction with Client files and directories. By using webutil we can enhance our Forms based applications. By storing files in the database we can ease the administration of important files like Centralized Repository and backup etc etc.

Whats next:
- Change the Image item types for Word, Pdf and movies to some meaningful items and In POST-QUERY also provide some info that Files are already into the database.

Tip: You can use DBMS_LOB.getlength(lob_column_name)  function to get the confirmation that Lobs are stored into the database

You can follow me on Twitter as well

Have a nice day,
Baig

Information Security Awareness

Well found this great map about Information security this all we should know as General knowledge

In a real world what exists around us

Have a nice day,
Baig

How to avoid ORA-02304: invalid object identifier literal

Hi,

Once upon a time i faced an error ORA-02304 while Importing one database schema to another user (Actually i was copying the scheme for some reasons and i had already 1 copy of the same schema in my database).

Problem:
The complete error was

IMP-00003: ORACLE error 2304 encountered

ORA-02304: invalid object identifier literal

IMP-00063: Warning: Skipping table "DEV_SCHEMA"."SUPPLIER_PAYMENT" 
because object type 

 "DEV_SCHEMA"."SUP_PAYMENT_TYPE" cannot be created or has different 
identifier

Reason:
Whenever you create Oracle object types with command for example

create type mytype as object
(ids number,
 names varchar2(60));

Oracle assigns Object ID internally for all objects while i was imported the same Type with Same Object ID was already exists in my database.
like
One column in my table SUPPLIER_PAYMENT contains Nested Table type column and in my database i had already one copy of my schema and NESTED TABLE TYPE with same Object ID (Internally) named SUP_PAYMENT_TYPE.

Solution:
Whenever you create objects with TYPES its a good practice to create all objects types in separate schema so you can share your objects in different schema's objects.

I removed the duplicate schema from the database and re-imported my tables and everything went smoothly.

So, Keep in mind If u going to work with objects in Oracle its better to create them in separate schema.

Have a nice day,
Baig

Get rid of addtional quotes with Quote operator "q" and DBMS_ASSERT.ENQUOTE_LITERAL

Hi,

Today's post is simple but valuable with Oracle 10g and 11g SQL and PL/SQL writing becoming easy and better with 10g's Quote operator "q" (Yes it is q only) you can get rid of concatenating multiple escape characters to make a text string like This is Oracle's World

Quote operator q
Usage:
You can use any single or multi-byte character or Paired with {} <> () or [] Any thing in between your escape character will display as it is.

Syntax:
q' [escape character] Your Text [Your escape character] '


Example:

select 
q'{This is Oracle's World}' Text1,
q'xThis is Oracle's Worldx' Text2,
q'!This is Oracle's World!' Text3,
q'.This is Oracle's World.' Text4
from dual;

DBMS_ASSERT
With Oracle11g Oracle has now DBMS_ASSERT package which makes life easier for DYNAMIC SQL developers. DBMS_ASSERT's procedure  ENQUOTE_LITERAL which takes a string input and adds leading and trailing single quotes to the output string mainly suitable for PL/SQL but u can use it in SQL as well

Example:
In SQL

 select dbms_assert.enquote_literal(ename)
 from scott.emp;

Output would be like
'SMITH'
'ALLEN'
'WARD'

In PL/SQL Dynamic SQL

 create or replace
procedure insert_into_dept(p_deptno number, p_dname varchar2, p_loc varchar2)
is
  vsql_text varchar2(500);
begin
  vsql_text := 'insert into scott.dept
                (deptno,
                 dname, 
                 loc)
                values
                ('||p_deptno||','||
                 dbms_assert.enquote_literal(p_dname)||','||
                 dbms_assert.enquote_literal(p_loc)||')';
  execute immediate vsql_text;
end;

To test this procedure execute following

select * from scott.dept;
exec insert_into_dept(50,'IT','SAN FRANSISCO'); 
select * from scott.dept;

Check the output of scott.dept procedure has insert rows successfully.

Hope it helps

Cheers,
Baig

Mystry of Regular Expressions in Oracle

Hi,

With Database 10g Oracle came up with this new Regular expression support which takes SQL to the new level. i had no background of regular expression so it took me quite a while to digest it so i m sharing it with u.

By using Regular expression functions we can find solutions of many problems like u can replace your old methods of using nesting functions like substr or instr to achieve the result and also use to validate some specific patterns of telephone nos, zip codes, email addresses and so on.

These functions works same as traditional Oracle functions but with additional regular expression support.

There are 4 modes in Regular expressions functions ('x' in 11g)
'c'  Case sensitive matching
'i'  Insensitive case matching
'm' Treat source string as multi-line activating Anchor chars

'n' Allow the period (.) to match any newline character
'x'  Ignores whitespace characters. By default, whitespace characters match themselves.

Oracle 10g has four regular expressions

- REGEXP_LIKE (source, regexp, modes) (This is Condition function can only be used in Where clause)
- REGEXP_SUBSTR(source, regexp, position, occurrence, modes)
- REGEXP_INSTR (source, regexp, position, occurrence, return_option, modes)
- REGEXP_REPLACE (source, regexp, replacement, position, occurrence, modes)

In Oracle 11g we have 1 more addition

- REGEXP_COUNT (source, regexp, position, occurrence, modes)

Today i m covering REGEXP_LIKE

How it works
- Insert the sample data into test table as well as we will use SCOTT Schema for some examples.
Note: There are many ways to achieve the same results i m providing examples as per my understanding.

Great tool for Remote Access and Support

Hi,
Yesterday i found a great magical tool for Desktop Sharing and good thing is that its personal use is free.

Check out what they say

TeamViewer - the All-In-One Solution for
Remote Access and Support over the Internet

TeamViewer connects to any PC or server around the world within a few seconds. You can remote control your partner's PC as if you were sitting right in front of it. Find out why more than 60 million users trust TeamViewer!

-All u have to do is to download Team viewer 5 from Team Viewer
-Ask for Dynamic ID and Password from your partner (Appearing on the left side of the screen).
-Enter that ID and password on your right side screen section ID

Thats all a new window will appear and u can remote control that machine from any where in the world. NOT ONLY U CAN ACCESS THAT MACHINE BUT ALSO U CAN VIDEO CHAT OR CONFERENCE CALL WITH IT

More u can find on their website Team Viewer

Take care,
Baig

How to add Java bean by writing your own Java code in Oracle forms

Hello everybody,

Today i m going to show u how to add Java bean (Bean area) in Oracle forms 10g and if u want to write some bean for Oracle forms then following are the guidelines

In this Example  (Download zip contains all files)
I will write a very very simple Bean In which i will set its value to System date Time and return it (working as global variable).
I used Oracle JDeveloper Studio 11.1.1.2.0.
 
Things to Remember:
- Whenever u write bean for Forms your class should extends VBean class which is available in frmall.jar $ORACLE_HOME/forms/java directory
- Copy frmall.jar to /lib directory in Jdeveloper installation directory.

Steps:
-Add frmall.jar in Jdeveloper Libraries so u can extend VBean class.

- Write bean code in Jdeveloper (In my example i have created SETMYTIME and GETMYTIME to set and get the value of MyDate string)

-Create a deployment profile JAR type in Properties on the project

- Deploy it using JAR file to $ORACLE_HOME/forms/java or ur application server path.

- Provide ur JAR file name in configuration section archive=frmall.jar,FormProperties.jar in

$ORACLE_HOME/forms/server/formsweb.cfg file
Note: use archive_jinit if u r using Jinitiator plugin

- Create Bean Area in Forms.
- Set its Implementation class property with the fully classified name and path of your bean i.e oracle.forms.custombean.TimeBean in my example.

- You can use SET_CUSTOM_ITEM_PROPERTY procedure to set the Custom events properties to the bean

Set_Custom_Property('BeanArea', RecordNo, PropertyName,value);

- Use GET_CUSTOM_ITEM_PROPERTY to get the value of that property.

Result := Get_Custom_Property( 'BeanArea', recordNo , PropertyName );

-Run the form Press Set Time to bean Button and then Press Get Time from Bean button Result item will display the time when u have pressed the Button :)

Thanks Mr.Francois Degrelle for adding this

You can avoid this Design time message by adding the full classname to your FORMS_BUILDER_CLASSPATH variable

Make sure the Jar file is signed simple way to sign by using sign_webutil.bat

"C:\DevSuiteHome_1\forms\webutil>sign_webutil.bat C:\DevSuiteHome_1\form\java\FormProperties.jar"


This example is very simple but i hope it would help somebody to understand the basics about Writing and using Java Beans for Oracle forms.

Next time i will try to come with some advance example Till then .......


Cheers,
Baig