How to find a table being refferenced ?

You want to know does a TABLE referenced as foreign key in any other table ? Let execute the following code and find out the real truth.

select a.owner,
       a.table_name,
       a.constraint_name,
       a.status
    from   all_constraints a
    where  a.constraint_type = 'R'
    and exists(
              select 1
              from   all_constraints
              where  constraint_name=a.r_constraint_name
              and constraint_type in ('P', 'U')
              and table_name = :OBJECT_NAME        --------name of the table from which you are not able to deleting
              AND OWNER = :OBJECT_OWNER)      -------- name of the schema user name
              ORDER BY TABLE_NAME, CONSTRAINT_NAME

Hopes this helps.... and leave you comments 

How to get year,month and date from two date ?

You are in trouble getting with year, month and date ? Don't worry, just run the following script with values and you will get that.

SELECT    TRUNC (MONTHS_BETWEEN (:END_DATE, :START_DATE) / 12)  YEARS,
  MOD (TRUNC (MONTHS_BETWEEN (:END_DATE, :START_DATE)), 12)  MONTHS,
 TO_DATE (:END_DATE)- ADD_MONTHS (:START_DATE,TRUNC (MONTHS_BETWEEN (:END_DATE, :START_DATE)) ) DAYS

FROM DUAL;

Hopes this helps..

Oracle Forms 10g Browser Crashes

I'm able to open and compile forms in Developer Suit. However, when I try to execute a 10g form, I don't get very far. The browser that gets launched from Forms Designer throws an error and closes right away (after prompting to send error report). I tried IE7, IE8, and Firefox with the same result. 


What can i do ?
Do you want to get rid from this problem ? Just follow the following guideline.

1. Open Internet Explorer 8
2. Go to Tools, Internet Options
3. Click on the Advanced tab.
4. Scroll down into the Security section and find “Enable memory protection to help mitigate online attacks”.
5. Uncheck “Enable memory protection to help mitigate online attacks”.
6. Click Ok and Ok again.
7. Reboot your computer.

and also replace the JVM.DLL(2.2Mb) in the <Program Files>\Oracle\JInitiator 1.3.1.22\bin\hotspot folder.

You can download. JVM.DLL 

Hopes this helps.

ma as'salama

Oracle Searching at Forms, With Individual LOV

Do you want to search in every field in form ? Don't want to memorize each filed value ? Want to select the value from LOV (List of Values) ?

Ok. I will discuss, how can you do this.
For example, We have three filed named, department_id, department_name,location. We want to search in form in Enter Query mode. Instead of writing the value, we want to select it from LOV.

First, you need to create LOV for each and every filed for our form  filed and assign to the item.
Write Key-Listval trigger for each item and write the following code

if :system.mode='ENTER-QUERY' THEN
LIST_VALUES;
end if;

This will prevent the item to show LOV in any other mode and show only Enter-Query mode.

Now click on Enter-Query, Show your LOV(forms 6i= F9, 10g=Ctrl+L),select value and hit Execute-Query.

You are done.

If you don't like this try another way...Oracle Searching at Forms, Dynamic Way

Ma as'salama

How to Read Image File at Oracle Forms 10g ?

I heard and answered lot's of question. How i read image file from file system to Database in oracle forms 10g ?
Here is the solution.
First of all you have to configure webutil correctly. You need to configure formsweb.cfg, webutil.cfg
Attach the webutil.pll library at your forms. Make object group and subclass of webutil.pll
Now add a puss button to your form and write the following code at When-Button-Pressed trigger
--------------------------------------------

declare
 vfilename varchar2(300);
begin
 vfilename := client_get_file_name(file_filter => 'jpg(*.jpg,*.gif)|*.*');
 if vfilename is not null then
 client_image.read_image_file(vfilename,substr( vfilename,instr( vfilename, -1)),'COMPANY_INFO_MST.CIM_LOGO');
 client_image.write_image_file(vfilename,'','COMPANY_INFO_MST.CIM_LOGO'); 
 end if;
 
end;

-----------------------------------

set the following property of the image item.

image format: TIFF
sizing style: Adjust

You are done...  

Oracle Searching at Forms, Dynamic Way

We have 20 or more or less then 20 filed in our form. User like to search in the form based on all fields or frequently uses field. If user doesn't want to use "Query Mode" of Forms. How can we achieve this ?

1. Do we create a form with text field for referencing database filed ?

2. Write a complex Query using like operator or lexical parameter ? Or any other way ?

My choice is LOV (List of Values) and a Puss Button.

Create a LOV selecting column as you need. LOV return item should be one and better to be ID column. Don't assign it to any column.
Now at your search button create two trigger.

1. When-Button-Pressed and write something like to post the block state to enter query.

-------------------------------------------------------------------------------------------

BEGIN
 IF :SYSTEM.MODE = 'NORMAL' THEN 
  GO_BLOCK('ITEM_MST');
  CLEAR_BLOCK(NO_COMMIT);
  ENTER_QUERY;
 ELSIF :SYSTEM.MODE = 'ENTER-QUERY' THEN
  ENTER_QUERY;
 ELSIF :SYSTEM.MODE = 'QUERY' THEN 
  GO_BLOCK('ITEM_MST');
  CLEAR_BLOCK(NO_COMMIT);
  ENTER_QUERY;
 END IF;
 EXCEPTION 
  WHEN OTHERS THEN NULL;
END;

-----------------------------------------------------------------------------------------------------

2. When-Mouse-Click and call the LOV and execute query.

------------------------------------------------------------------------------------------------------

BEGIN 
 IF :SYSTEM.MODE = 'ENTER-QUERY' THEN 
  IF SHOW_LOV('CATEGORY_SEARCH') THEN 
   EXECUTE_QUERY;
  END IF;
 END IF;
 IF :System.Record_Status = 'NEW' THEN 
      Exit_Form(No_Validate); 
  END IF; 
END;

-------------------------------------------------------------------------------

You have done. 

Need more clarification ? Post your comments. :)