SYAMS-Info: Maret 2013

Oracle Function PLSQL: Conversion date to number (hour,min)

Oracle Function : Conversion date to number (hour,min)

Use this function to get conversion date variable to number(hour,min) :

The function would generate date variable, (date1-date2) becomes number (hour,min)

FUNCTION GET_DURATION(d1 number) RETURN VARCHAR2 IS
--# d1 = tanggal awal
--# d2 = tanggal akhir
tmpvar VARCHAR2(100);
durdays NUMBER(20, 10); -- days between two dates
durhrs BINARY_INTEGER; -- completed hours
durmin BINARY_INTEGER; -- completed minutes
dursec BINARY_INTEGER; -- completed second
durhari PLS_INTEGER;
jhrs number;
jmin number;
thrs number;
BEGIN
durdays := d1;

durhari := TRUNC(d1);

durhrs := MOD(TRUNC(24 * durdays), 24);
durmin := MOD(TRUNC(durdays * 1440), 60);
dursec := MOD(TRUNC(durdays * 86400), 60);

jhrs :=(to_number(durhari)*24)+to_number(durhrs||'.'||lpad(durmin,2,'0'));
thrs :=jhrs;
RETURN(thrs);
END get_duration;

How to use :

select GET_DURATION(date_variable) from dual;

example :

select GET_DURATION(date1-date2) from dual;

select GET_DURATION((date1-date2)-(date3-date4)) from dual;

Oracle Designer 6i: How install SQLLDR on win xp

A few weeks ago, i was looking for some articles that discuss "How to install SQLLDR on windows xp?" but i did not get satisfactory results.

After studying some of the structures that exist within the application Designer 6i, I did a little improvisation.

Oracle Designer 6i: How install SQLLDR on win xp?

1.

Download SQLLDR package by this link below.
http://www.4shared.com/zip/YEkcRoB3/sqlldr.html?

2.

Exact the file and it contains 2 files "des_817" and "des_817.reg"

3.

Copy folder des_817 onto "C:\des_817"

4.

Run des_817.reg

5.

Set up the network connection by "regedit"
HKEY_LOCAL_MACHINE >> SOFTWARE >> ORACLE >> HOME2

TNS_ADMIN = "your networking path"

mine

TNS_ADMIN = C:\orades\net80\admin

How to use :

1.

Open command prompt (Run >> cmd)

2.

Type this script :

C:\des_817\bin\sqlldr userid=usr/pass@sid control=ctlfile

Oracle Form 6i: How to put database data into List Item(Combo Box)?

Last days around, i just tried to make some List Item(ComboBox) that filed from database selected. . .
It's pretty good and easy enough.

1.

Create Program Unit :

PROCEDURE CREATE_Filenames_RG IS
-- Andrew Fraser v2.2 27th May 2010
-- Populate dynamic lookup

   it_id1 Item := Find_Item('block.my_list_item');

   group1_id RecordGroup;

   GRP_status NUMBER;

   V_Space varchar2(10) := ''''||'0'||'''';

BEGIN

  group1_id := Find_Group('FILENAMES_RG');

  IF NOT Id_Null(group1_id) THEN
   delete_group(group1_id);
  END IF;
  group1_id := Create_Group_From_Query('FILENAMES_RG',
   'SELECT field FROM table ORDER BY field') ;

   Grp_status := Populate_Group('FILENAMES_RG');
   IF Grp_status = 0 THEN
If Not Id_Null(it_id1) THEN
If Get_Item_Property(it_id1,Item_Type) = 'LIST' Then
Clear_List(it_id1);
Populate_List(it_id1,'FILENAMES_RG');
   END IF;
END IF;
END IF;

End;

2.

Create/edit triggers to call this:

On form startup:

When-New-Form-Instance on the form as a whole:

CREATE_Filenames_RG;

3.

And/or on mouse click if you want the database table requeried every single time the user clicks on the dropdown:

When-Mouse-Click on the new item itself:

Clear_List('CAS_UPLOAD');
Clear_Item;
CREATE_Filenames_RG;

easy right??

#source : http://andrewfraserdba.com/2010/05/27/oracle-forms-dropdown-list-item-values-populated-from-database-table/