Category: Oracle

IFS is an ERP system built by the Swedish software firm Industrial and Financial Systems AB. It exposes most of its functionality for automation, customization, etc. Below is an example of how we can use a quick script (to be executed by tools such as SQL*Plus) to automate the batch creation of detail lines in a Customer Order. Many of the other exposed APIs offered by IFS can be called in a similar manner.

declare
	info_	varchar2(4000) := '';
	attr_	varchar2(4000) := '';
	objid_	varchar2(2000) := '';
	objversion_	varchar2(2000) := '';

	cursor recs_ is
	select order_no, cust_id, part_no, qty, unit_cost 
	from my_tmp_worklist_tab;
begin
	for rec_ in recs_ loop
		Client_Sys.Clear_Attr(attr_);
		info_ := '';
		objid_ := '';
		objversion_ := '';

		-- Add attributes to the Customer Order detail line
		Client_Sys.Add_To_Attr('ORDER_NO', rec_.order_no, attr_);
		Client_Sys.Add_To_Attr('DELIVER_TO_CUSTOMER_NO', rec_.cust_id, attr_);
		Client_Sys.Add_To_Attr('CATALOG_NO', rec_.part_no, attr_);
		Client_Sys.Add_To_Attr('PART_NO', rec_.part_no, attr_);
		Client_Sys.Add_To_Attr('BUY_QTY_DUE', rec_.qty, attr_);
		Client_Sys.Add_To_Attr('DESIRED_QTY', rec_.qty, attr_);
		Client_Sys.Add_To_Attr('REVISED_QTY_DUE', rec_.qty, attr_);
		Client_Sys.Add_To_Attr('COST', rec_.unit_cost, attr_);
		Client_Sys.Add_To_Attr('PLANNED_DELIVERY_DATE', sysdate+7, attr_);
		-- ... There may be other fields you may wish to populate...

		-- Example of how to update an already-established attribute
		if (rec_.unit_cost = 0) then
			Client_Sys.Set_Item_Value('COST', '0.01', attr_);
		end if;
		
		begin
			Customer_Order_line_API.New__(info_, objid_, objversion_, attr_, 'DO');
		exception
			when other then
			info_ := sqlerrm;
			dbms_output.put_line('Cust Order ' || rec_.order_no || ' error: ' || info_);
		end;

		commit;    
   end loop;   
end;

We can use an Oracle PL/SQL function to retrieve a list of values from the database. In this article, we will look at two sample methods, one of which returns a character string (ie. varchar2) while the other returns a record set (ie. sys_refcursor). Our application will determine which method is best in our implementation.

In the first example below, we use a for-loop to iterate through a select statement. In each iteration, we concatenate the new data into a varchar2 variable. Finally, at the end of the function, we return the said variable.

create or replace function get_city_list(in_country_ in varchar2)
return varchar2
as
ret_ varchar2(1000);
begin
	for rec in(select city from cities where country=in_country_) loop
		ret_ := ret_ || rec.city || ',';
	end loop;
	return ret_;
end get_city_list;

Alternatively, we can have the function return us a record set. To do so, we declare a sys_refcursor variable, and then in the body of the function we assign a SQL statement for it.

create or replace function get_city_list(in_country_ in varchar2)
return sys_refcursor
as
	ret_ sys_refcursor
begin
	open ret_ for select city from cities where country=in_country_;
	return ret_;
end get_city_list;

For this particular example, let us assume that the information being between application takes on the form of two fields, row_id and row_status. Given that, we will create this following object type, which will be used by the queue shortly.

create or replace type obj_stuff as object (
	row_id	number,
	row_status	varchar2(10)
);

With the object type created, we will first create a queue table based on this object type, followed by a queue.

declare
begin
	dbms_aqadm.create_queue_table (
		queue_table	=>	'myschema.stuff_queue_table',
		queue_payload_type	=>	'myschema.obj_stuff'
	);
	dbms_aqadm.create_queue (
		queue_name	=>	'myschema.stuff_queue',
		queue_table	=>	'myschema.stuff_queue_table'
	);
end;

With the script above, we now have the queue table and the queue set up. To start the queue, use the .start_queue script, as below.

declare
begin
	dbms_aqadm.start_queue (
		queue_name	=>	'myschema.stuff_queue',
		enqueue	=>	true
	);
end;

Let’s say you have a sequence object created called myschema.seq_mysequence which has the next value of 3000 and it normally increments 1 at a time, and let’s say that you need to reserve all numbers between 3000 and 4000 for a special purpose, thus you need to manually advance the sequence from 3000 to 4000. This is how you can increment the sequence 1000:

-- First, alter the object so the next increment will jump 1000 instead of just 1.
alter sequence myschema.seq_mysequence increment by 1000;

-- Run a select to actually increment it by 1000
select myschema.seq_mysequence.nextval from dual;

-- Alter the object back to incrementing only 1 at a time
alter sequence myschema.seq_mysequence increment by 1;

Because the ampersand symbol, “&”, denotes the start of a variable, you might be caught off guard when you are trying to use it as a literal value. For example:

SQL> select 'I like fish & chips' as x from dual;

Enter value for chips: old   1: select 'I like fish & chips' as x from dual
new   1: select 'I like fish ' as x from dual

X
------------
I like fish

1 row selected.

Of course, this is not what what you intended; you meant for the literal string to appear as “I like fish & chips”. There are two ways for you to get around this problem. The first method is to make sure the ampersand symbol is at the end of a string, thus requiring you to break up your sentence in one of the two following ways.

SQL> select 'I like fish &' || ' chips' as x from dual;

X
-------------------
I like fish & chips

1 row selected.

SQL> select concat('I like fish &',' chips') as x from dual;

X
-------------------
I like fish & chips

1 row selected.

The second method is to escape the ampersand symbol in the following manner.

SQL> set escape on;
SQL> select 'I like fish & chips' as x from dual;

X
-------------------
I like fish & chips

1 row selected.

Today, I was working with a code base still in development, but really wanted to use it against the latest production data. To achieve this, I used Oracle 11g’s Recovery Manager, “RMAN”, to duplicate the latest content of my production database onto a development Oracle server that I have set up to work with my development code. Let us assume that my production database name was “prodDb” residing on server “prodServer”, the database I was creating is “devDb” residing on “devServer”.

First, I needed to create some directories on devServer.

mkdir -p /u01/app/oracle/admin/devDb/adump
mkdir -p /u01/app/oracle/flash_recovery_area/devDb
mkdir -p /u01/oradata/devDb

Then, on devServer, I used the scp command to copy the prodServer password file to devServer.

scp prodServer:/u01/app/oracle/product/ver_no/db_1/dbs/orapwproddb $ORACLE_HOME/dbs/.

Next, I created a dummy init file so we can mount this empty devDb database.

echo 'DB_NAME=devDb' > $ORACLE_HOME/dbs/initdevDb.ora
echo 'control_files=(/u01/oradata/devDb/control01.ctl,/u01/oradata/devDb/control02.ctl,/u01/oradata/devDb/control03.ctl)' >> $ORACLE_HOME/dbs/initdevDb.ora

I mounted devDb, then restarted the listener.

(From terminal prompt, log in to SQL*Plus as sysdba)
sqlplus /nolog
conn / as sysdba

(SQL*Plus command to mount database)
startup nomount pfile=$ORACLE_HOME/dbs/initdevDb.ora;
exit;

(Back in terminal prompt, restart the listener)
lsnrctl stop
lsnrctl start

My work on the devServer was then done. I moved on to the prodServer machine; from the terminal prompt of the prodServer, I started RMAN.

(From terminal prompt, run "rman")
rman

(In RMAN prompt, connect to prodDb as target, and to devDb as the auxiliary)
connect target sys@prodDb
connect auxiliary sys@devDb

(In RMAN, run the following script; you may wish to modify to suit your needs, of course)
run {
   duplicate target database to devDb from active database
   spfile
           parameter_value_convert 'prodDb','devDb'
           set db_unique_name='devDb'
           set service_names='devDb.mydomain.com'
           set log_file_name_convert='prodDb','devDb'
           set log_archive_config=''
           set fal_client=''
           set fal_server=''
           set log_archive_dest_1=''
           set memory_max_target='1200m'
           set memory_target='1200m'
           set sga_max_size='800m'
           set sga_target='800m'
           set db_recovery_file_dest_size = '20G'
           db_file_name_convert 'prodDb','devDb'
        ;
     }

This morning, a coworker came to my office with an awkward smile on his face. “Oops”, he started, “I ran a bad query and I updated a bunch of records incorrectly.” He thought he had messed up big time, but actually, I was able to recover data for him quite easily, utilizing our Oracle 11g database’s flashback feature (note: this method applies to Oracle 10g as well).

I asked him two questions: What was the update SQL statement that he ran, and when did he run it? For our example, let’s say the SQL statement was as follows, and he ran it at 10am.

-- Bad SQL statement executed by user
update myTable
set field1 = field2, field2 = null
where field1 is null;

As you can see, since he is moving field2 into field1, and previously other records had already gone through this process, he could no longer tell which ones were modified by him at 10am and which ones were done previously. Since it only had been a few minutes since he committed the bad update statement, I knew I had plenty of time to recover the data. Actually, I did not need much time at all. This is what I did to identify the records he modified:

create table myTable_comparison
as
select before.pkey_field, before.field1, after.field1
from myTable after, 
myTable as of timestamp to_date('6/1/2009 09:59:00','mm/dd/yyyy hh24:mi:ss') before
where before.pkey_field=after.pkey_field
and before.field1 <> after.field1;

Note in the above example, I used the additional clause … as of timestamp [insert a date value here] to pull out a view of what the data looked like today at 9:59am, a moment before my coworker updated the data. This creates the table myTable_comparison that contains all rows which had a different before and after field1 value, using myTable’s primary key column as the unique identifier to help my coworker review what had been changed. He confirmed the records in this table are exactly what he wanted to revert, so I rewrote the select statement into an update statement to revert the data back to the previous state.

In this case, my coworker mistakenly updated values. What if he had mistakenly deleted records instead? To recover, we just need to find the records that was present previously, but no longer present anymore by using an outer join between the before and after tables.

select before.pkey_field, before.field1, after.field1
from myTable after, 
myTable as of timestamp to_date('6/1/2009 09:59:00','mm/dd/yyyy hh24:mi:ss') before
where before.pkey_field=after.pkey_field
and before.pkey_field=after.pkey_field(+) and after.pkey_field is null;