Connecting Oracle Express 10g with Oracle Forms 6i

Hi readers...

Recently I was going through my old stuff in Oracle that I was doing before my professional life started with Databases.  I found many of the applications build with Oracle 8i and Oracle Forms 6i (Developer 2000), tried to run them the the problem I was facing was 'no oracle 8i database'...

I thought to connect the same with Oracle Express 10g Database but Forms and Reports gave error.  After some study in database material I found that Oracle Express 10g Database is not compatible with Forms 6i because of its 'Character Set'...

Now the problem was to change the Oracle Express 10g Database Character Set to older version so that forms can be connected...

When Oracle Express 10g Database is installed on SQL command prompt connect with admin database user as

sys/password as sysdba

when connected apply following commands one by one...

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE UTF8;
SHUTDOWN;
STARTUP RESTRICT;
SHUTDOWN;
STARTUP;

Finally you will be able to connect with Forms 6i and run and migrate your applications...

Thanks for reading...

Ask for further help as and when you required...

Play this when get lazy for work... It will change ur mode...

Just wanted to share this with you friends and readers, as when getting bore playing this to cheer me and give some more energy for work...

http://www.google.com/doodles/30th-anniversary-of-pac-man

Auto Spare Parts, Stock Tracking System

Here comes a new database design for Auto Spare Parts Business...

The software comes with following really reliable and effective features...

• Auto Spare Parts Stock Detail
• Car wise Spare Parts Detail
• Spare Parts search system, the address of item
• Part number, name and type wise search
• Car Wise Income Report
• Item Wise Income Report
• Auto Quantity Maintenance after purchase or sale of Items
• Sale Invoice and Customer Record Maintenance

Recovery from new Virus of USB Drive

Yesterday my cousin came to me with a problem that when he opens his USB drive in my computer it shows  nothing but below image:

When he tries to open the shown shortcut it shows nothing and the data is still there...

If you notice the banner of Microsoft Security Essentials, it shows and removes the virus but the files seems to be deleted from the USB Drive...

Simply copy the USB Drive and paste it any where in your system drives...  Better if you create new folder...

Now enable the hidden files and folders from folder options as shown below...

Go to views tab as shows below...

Select Show Hidden Files, Folders and Drives
and click on 'OK'
Now if you go the the folder where you copied the files, under the name of your USB Drive folder...

Open the folder and you will find the following files and folder...

The drive sign which is highlighted, if you open it... Bingo... You will find you files there...

Simply Cut and paste the files from it to your desktop or anywhere you like and format the USB Drive, quick format will also work...

And you are done with....

Paste the files back into your USB Drive and done...

I hope the the posting will be helpful to the readers...

How Triggers work in Oracle...

Lets talk about triggers as I mentioned in my last post that I will come online to write about this feature of PL/SQL is to make you understand that how much easily you can control things in your database...

Okay now lets talk about below trigger that what really its doing...

If you remember about my previous posting and especially about Stock Tracking System... The question is that how it will automatically track and keep record of stock after each and every sale, purchase and wastage if any.  Also sale return and purchase return puts effect on stock...

Lets see below trigger and talk about it...


create or replace trigger price_history
before update of cost_price
on stock
for each row
begin
insert into price_history
values
(:old.stcode,:old.stname,:old.capacity,:old.qty,:old.cost_price,:old.open_price,:old.sal_qty);
end;
/

Don't be confused about this trigger as I was when saw this coding for the first time...

create or replace trigger price_history

it will create trigger and will overwrite the trigger name 'price_history', if one already there...

before update of cost_price

Here we are asking oracle to fire the trigger before changes on cost_price are saved...

on stock

The table name in which changes are being made...

for each row

The trigger will fire for each row, whether you change one record or many...

begin

after this part the trigger body will start...

insert into price_history

It will insert record in table name price_history which is already created in the database...

values
part of command...

(:old.stcode,:old.stname,:old.capacity,:old.qty,:old.cost_price,:old.open_price,:old.sal_qty);
if you note I am using record type as old because I want to record the history of cost_price of items...

end;

Will close the trigger...

Whenever the cost_price of existing item is updated or changed, the trigger will fire and it will add old details of the items with cost_price to table named price_history...

Thank you for your time and next time we will meet for something complex to calculate data...

Placing Buttons in Oracle Forms 6i

Okay apologies for the delay but here I am with method to add buttons to your forms...

Above screen with form field but without buttons on it...

Simply click on new block and create the block but create it manually because you have to enter the buttons in this block.  Better if you name this block as 'BUTTONS'...

Simply click on the name of block and change it to 'BUTTONS' as shown above...

Design button as shown above, from tools menu shown at left of form...

Rename the button as per your requirement...

Okay now if simply selecting the button and pressing 'Ctrl+D' will duplicate the buttons...
Duplicate as many as you require...

After renaming them place them where ever you want on your form...

Right click on button and click on PL/SQL Editor which will give you below screen to enter the codes...

Now please note the coding for different buttons given below...

for INSERT Button:


DECLARE
A NUMBER;
BEGIN
GO_BLOCK('VENDOR');
CLEAR_BLOCK(NO_COMMIT);
SELECT NVL(MAX(VID),0) INTO A FROM TRAINING.VENDOR;
:VENDOR.VID:=(A+1);
GO_ITEM('VENDOR.VNAME');
END;

Let me explain to you the Insert Button coding above.
This is a body of the trigger which will be fired when you will click on the Insert button.  What button will execute is that it will go to the block named 'Vendor' and will clear the block for, if you have inserted anything by mistake or intentionally in the form fields.  Then it will execute the select statement of Oracle Database which will store highest number of Vendor Id into a (which is our variable to store number temporary so that we can perform additional calculations on that number) and then at next level it will put higher number + 1 into vid field automatically.  Every time when you save and make an addition to the record it will increase.  After inserting max number into vid field it will jump to vendor name.

for DELETE button:

GO_BLOCK('VENDOR');

DELETE_RECORD;

for SAVE Button:

COMMIT;

for EXIT Button:

GO_BLOCK('VENDOR');

CLEAR_BLOCK(NO_COMMIT);

EXIT_FORM;

for Query Button:

GO_BLOCK('VENDOR');

EXECUTE_QUERY;

for Clear Button:

GO_BLOCK('VENDOR');

CLEAR_BLOCK(NO_COMMIT);

I hope that you will do practice this method and will make yourself more and more efficient with this.

Please do contact me if you have any problems, and yes the next thing we will talk about is trigger which plays a great and important role in Oracle Database to automatically calculate things and make changes to data when you Insert, Delete or Update the records.

See you soon again for further training...

Billing System for Star Link Graphics & Advertising Solutions

The software is designed for billing of Graphics and Printing work, which automatically keeps track of invoices and vendor balances and the payments that are received and business expenses made.

End of the day it gives reports based on daily, weekly and monthly progress of the printing jobs for owner to know about what is paid and received and what really the owner getting from the business.