Contains information about Teradata,BTEQ,FASTLOAD,FASTEXPORT, Oracle Pl/SQL,forms, JAVA open source related topics and discussions
Oracle forms and reports
  Oracle database 

23 May 2009

Oracle Terminal Reference Forms 6i


I found that there was no documentation available properly about using the 

Oracle terminal tool for Oracle forms 6i.


I have summarized the posts present in different web sites and forums together .

for using Oracle terminal.


Using the User Defined Key 0-9 

In order to create a key binding for a key trigger, perform these actions:

  • In Form Builder, create your function key trigger.
  • Using Oracle Terminal, open the appropriate Form Builder resource file (for MS Windows open FMRUSW.RES --> Normally present under the folder Oracle-Forms-Home\Forms60 folder).
  • Choose Functions/Edit Key to invoke the Key Binding Editor.
  • In the Key Binding Editor, double-click on windows-sqlforms to invoke Key Binding definition window.
  • Select the Insert Row button and then enter the action that corresponds to your function key trigger. An example of the chart for MS Windows follows:
       – Key-Fn Trigger - KEY-F3
       – Microsoft Action - User defined Key 3
       – Microsoft Code - 85
  • Specify the key binding for your trigger, then select OK.
  • Select the Product Actions Editor.
  • Double-click on the sqlforms category.
  • Select the Insert Row button, enter the Action, Code and Description for your trigger, then select OK.
  • Dismiss the Product Action and Key Binding Editors that invokes a help system by selecting OK for each, and then choose File/Save.
  • Choose Functions/Generate to generate your modifications and create a new resource file that incorporates your changes. Save the new resource file.    
//To create a KEY-F3 trigger, enter into Action Field in Key Bind 
//Definition window  User Defined Key F3  
//Specify a key binding for your trigger, and then select OK  
  Action Binding User Defined Key F3  Control+F2  
//Enter the following to Microsoft Windows  Action Code 
 Description User Defined Key F3 85 [Trigger description] 
Use Accelerators For Commonly Used Functions (Menu Items)

Accelerators are keyboard shortcuts for frequently performed actions (for example, Ctrl+P for print, Esc for escape or cancel). When possible, it is a good (but not mandatory) practice to provide keyboard accelerators. A good rule to use is to always set a keyboard accelerator for menu items that appear visually on a tool bar. Keyboard shortcuts allow users to bypass opening the menu by using a specific combination of keystrokes that perform the same function as a corresponding menu item.

Developer/2000 provides for five logical accelerator keys, [Accelerator 1] through [Accelerator 5] that are set up with Oracle Terminal. To assign a key, find the menu item in the Object Navigator and display its property sheet. In the Property Palette, under the Functional node, type the name of the logical accelerator key you want to assign to the item in the Keyboard Accelerator property field. For example, enter Accelerator 1.

In the Oracle Terminal, associate this logical accelerator key, for example, to a key sequence such as Ctrl+F to open the File menu item. Instead of pressing Alt+F, then S, to activate menu item File-Save, a user can just press Ctrl+S to execute the same function. Now the user is able to activate a menu item without going through the menu.

This graphic is a screen capture of the Oracle Forms Builder Property Palette.  This screen capture displays a list of Oracle Form Properties along with their editable associated property fields.  The middle of the screen displays the Keyboard Accelerator property, with Accelerator 1 typed in the column to the right, which is the Keyboard Accelerator property field.  The Keyboard Accelerator property has 5 attributes associated with it.  The attributes are visible in menu, visible in horizontal menu toolbar, visible in vertical menu toolbar, icon in menu, and icon filename.  In the property field, yes has been selected for the visible in menu, and no is selected for the other attributes.

Use Access Keys to Select or Execute an Item (Mainly for Buttons or Textitems)
This technique applies to the buttons, radio buttons, menus and check boxes. It specifies the character that will be used as an access key, allowing the user to select or execute an item by pressing a key combination, such as Alt+C. In a Label Property on Property Palette place the name of the menu item with an ampersand after the first letter: for example “A&ction”. The access key is displayed with an underscore in the item label. Buttons with the iconic property set to “Yes” cannot have access keys.

Push buttons, checkboxes, tabs, menu items and radio buttons should have an access key unless:

  • They are keyboard-navigable (an access key is still desirable in this case).
  • An excessive number exist such that deriving a unique letter would be difficult (in which case the ones with no access key must be navigable).
  • They are not absolutely critical to the functionality of the product.
  • For Checkboxes and Radio Buttons: if they are part of a multi-row block and use the Prompt, not the Label attribute, they cannot render an access key.
List of Valid Action Codes which you can use in Oracle Terminal 
Here is a list of the numeric "action codes" that correspond to various product actions that are available in Oracle Forms. This list might not be complete nor current in respect to the latest version of Oracle Forms available, but it should be useful as a reference.   
    

1  Next Field      
2  Previous Field      
3  Clear Field      
4  Left Arrow      
5  Right Arrow      
6  Up Arrow      
7  Down Arrow      
8  Goto Beginning Of Line      
9  Goto End Of Line     
10  Goto Extreme Vertical Up     
11  Goto Extreme Vertical Down     
12  Scroll Up     
13  Scroll Down     
14  Scroll Left     
15  Scroll Right     
16  Delete Character To Left     
17  Delete Char On/Right     
18  Delete Line     
18  Terminate Input On Form     
19  Move Cursor Left     
20  Move Cursor Right     
21  Scroll-Up     
22  Invoke Editor For Field     
23  Delete Character     
23  Insert Line     
24  Search     
25  Toggle Insert/Replace Mode     
26  Select Item     
27  Return     
28  Invoke Menu     
29  List Of Values     
30  Invoke Context-Sensitive Help     
31  Redraw Screen     
35  Show Keys     
36  Accept     
61  Next Key     
62  Clear Record     
63  Delete Record     
64  Duplicate Record     
65  Insert Record     
66  Next Set     
67  Next Record     
68  Previous Record     
69  Clear Block     
70  Ask Block     
71  Next Block     
72  Previous Block     
73  Duplicate Field     
74  Clear Form     
75  Enter     
76  Enter Query     
77  Execute Query     
78  Error     
79  Print     
80  Clear Query     
81  Update Record     
82  User Defined Key 0     
83  User Defined Key 1     
84  User Defined Key 2     
85  User Defined Key 3     
86  User Defined Key 4     
87  User Defined Key 5     
88  User Defined Key 6     
89  User Defined Key 7     
90  User Defined Key 8     
91  User Defined Key 9     
92  Clear Eol  
11004  Return To Application Menu  
11005  Previous Menu  
11006  Return To Main Menu  
11007  Enter > 1 Os Commands  
11008  Enter 1 Os Command  
11009  Show Background Menu  
11010  Background Menu Option 1  
11011  Background Menu Option 2  
11012  Background Menu Option 3  
11013  Background Menu Option 4  
11014  Background Menu Option 5  
11015  Background Menu Option 6  
11016  Background Menu Option 7  
11017  Background Menu Option 8  
11018  Background Menu Option 9  
11019  Background Menu Option 10  
11020  Re-Enter Application Parameters  
11021  Re-Enter Menu Parameters  
11022  Accelerator Key 1  
11023  Accelerator Key 2  
11024  Accelerator Key 3  
11025  Accelerator Key 4  
11026  Accelerator Key 5  
11027  Accelerator Key 6  
11028  Accelerator Key 7  
11029  Accelerator Key 8  
11030  Accelerator Key 9  
11031  Accelerator Key 10

    Kindly revert for any clarifications


19 May 2009

Need to Upgrade Oracle applications

I found a interesting article from Binarysematics which clearly explains what
are the advantages in migrating Oracle Forms application and
Why the people are not doing so?

Kindly check the following link



                                                           

16 May 2009

Calling Java procedures from ORACLE PL/SQL

Why we need to call JAVA procedures in PL/SQL?  

JAVA one of the oldest and type safe language ever seen.It was first introduced into the IT world when C++ was not able to call remote procedures.

Almost there are thousands and thousands of logic from Hello world to complex logic fo aircrafts written in JAVA.So it is important to reuse the logic than rewriting from scratch in PL/SQL.

I have summarized the steps below to call a JAVA method from ORACLE 

Note
   Only STATIC methods can only be called  from PL/SQL as it as Modular language

Steps
1. Create a JAVA class to be loaded into Oracle database.

     Iam using our very old hello world program.Instead of printing Hello World to console, it would return the value to the user.

public class Hello 
{  
public static String world()  
{  
return "Hello world";  
}


2. Compile the JAVA file using JAVAC
     Note
           Kindly add ORACLE_HOME/JDK/BIN in your PATH variable
Microsoft Windows XP [Version 5.1.2600] 
(C) Copyright 1985-2001 Microsoft Corp.  
C:\Documents and Settings\Administrator>javac H:\JDEV\Hello.java  
     


3. Load the JAVA files into Oracle database          
Note
   You should have added the path of ORACLE_HOME\JDK in the classpath or
it would show the driver not found error is thrown
C:\Documents and Settings\Administrator>loadjava -user store/store@shanthaguru H:\jdev\Hello.class  
C:\Documents and Settings\Administrator>



Note
     After loading it into database , you can verify it using SQL*plus
   or SQL Developer
  
SQL * PLUS
SQL*Plus: Release 10.1.0.2.0 - Production on Sun May 17 13:07:14 2009  
Copyright (c) 1982, 2004, Oracle. All rights reserved.  
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 
Production With the Partitioning, OLAP and Data Mining options  
SQL> select object_name from user_objects where object_type = 'JAVA CLASS';  
OBJECT_NAME 
-------------------------------------------------------------------------------- 
Hello



SQL DEVELOPER
    Open the JAVA node under the corresponding schema and see the loaded
JAVA Classes


   
4. Creating a PL/SQL procedure for calling the loaded the JAVA method   
SQL> CREATE OR REPLACE FUNCTION helloworld RETURN VARCHAR2 AS  
2 LANGUAGE JAVA NAME 'Hello.world () return java.lang.String';  
3 / Function created.




Note
   The return type should be JAVA specfic , note this in the above code.


Now try call the PL/SQL function to call the JAVA method

SQL> select helloworld from dual;  
HELLOWORLD 
-------------------------------------------------------------------- 
Hello world

14 May 2009

Oracle and Sun

Hi,
   Atlast Oracle and Sun merges and it would be a great break through.
Kindly check the Following link for more details



12 May 2009

Oracle Forms vs .NET

Hi,

There would some dilemmas when selecting the IDE and development tool when 
constructing your windows or web application.

I think this is an intresting thread which has hot conversation in favour of both Oracle forms and
.NET. 

Kindly click the following link

                                        

07 May 2009

Oracle Forms connection to MSACCESS


Hi,
 In This Post we would Look into how to Connect Oracle Forms to MSACESS and combining the feature of Oracle forms and MSACCESS.

Steps
1. Create a MDB file with a username and password
     I think everyone knbows to open access and create an MDB file.

2. Run the following scripts
     \Forms_home\OCA60\SQL\ACCESS  --> *ACCDMBLD.SQL* (Used to create EMP and DEPT table)
       \Forms_home\OCA60\SQL                    --> *DEMODATA.SQL* (Insert demo data into the tables)

3. Create a ODBC data source in the data sources management tab in windows
    1. Open the Data source managment tab
        Control Panel --> Administrative tools ---> Data Sources (ODBC) 
        
     
    2.  Click the Add button,*Create new datasource* screen opens
       
         

    3. Select Microsoft Access Driver(*.mdb) and click Finish.
     
    4. ODBC Microsoft access screen opens, give a name for the data source
        and click the OK button
            
      
    
    5.  Click  the selet button and select the corresponding MDB file
         and click the OK button

          

    6. The datasource would have been created and check the initial screen
      
        
4. Open the Forms Builder and Connect to MSACCESS     
    In connect field type  username/password@ODBC:DATA_SOURCE_NAME

5. Create a Database Block using the database lock wizard and set the primary property of the EMPNO    column to       'Yes' before running the form.You would be able to do all the insert, update, Delete , Query     Operation from         that form.

    

Connecting Oracle to MSACCESS

Hi,

This post summarizes the most important and intresting feature of Oracle which I have tried.

Normally for data entry operators, we would enter the data for internal application in small database like MSACCESS,.... 

but the query feature of Oracle is easy, so if you want to maintain your datasource as MSACCESS and if you want to do any queries using Oracle SQL, this helps you.

Steps
1. Create a MDB file with a username and password
     I think everyone knbows to open access and create an MDB file.

2. Run the following scripts
     \Forms_home\OCA60\SQL\ACCESS  --> *ACCDMBLD.SQL* (Used to create EMP and DEPT table)
       \Forms_home\OCA60\SQL                    --> *DEMODATA.SQL* (Insert demo data into the tables)

3. Create a ODBC data source in the data sources management tab in windows
    1. Open the Data source managment tab
        Control Panel --> Administrative tools ---> Data Sources (ODBC) 
        
     
    2.  Click the Add button,*Create new datasource* screen opens
       
         

    3. Select Microsoft Access Driver(*.mdb) and click Finish.
     
    4. ODBC Microsoft access screen opens, give a name for the data source
        and click the OK button
            
      
    
    5.  Click  the selet button and select the corresponding MDB file
         and click the OK button

          

    6. The datasource would have been created and check the initial screen
      
        
 

4. Open the SQLPLUS.exe under \Forms_HOME\BIN
    
    

5. Connect with the username and password as follows
    username/password@ODBC:DATA_SOURCE_NAME
    
    
      
      
6. Select the data from tables in MSACCESS as we do it in Oracle.
     
    Select * from EMP;

    
    
    
     
 

     

     

05 May 2009

Reading and Writing CLOB objects

Hi,
   In this post , we can look into how to read and write CLOB objects.

Why we need to use CLOB objects?
 The only downside of using VARCHAR2 is that it is limited to 4000 characters, 
so if we are creating large queries or if we you store resume or text files in database, then
we can use this CLOb, where we can store upto 2GB .

Steps
1. Creating a table containing a CLOB column
    
SQL> create table lob_contain
2 (id number,
3 lob_ptr clob default empty_clob());
Table created.
    
2. Create a procedcure to write a text to the CLOB column



Declare

lob_locator CLOB;

text varchar2(200) := 'Resume NAme - G.Subramanian KAushik City - Chennai
Location - triplicane Occupation - Software engineer Hobbies - Reading astrology
books,nemerology, Playing video games';

amount number;

offset integer;

Begin

-- Setting the amount to be written
amount := length(text);

-- Lock the record in the table lob_contain to get a expicit lock over the record
Select lob_ptr into lob_locator from lob_contain where id = 1 FOR UPDATE;

--Setting the Offset
offset := DBMS_LOB.GETLENGTH(lob_locator)+2;

--Write the text to the CLOB table
DBMS_LOB.WRITE(lob_locator,amount,offset,text);
COMMIT;
END;    
 
3. Select the inserted value as a VARCHAR2 text.
    

   SQL>  select * from lob_contain;

   ID                                 LOB_PTR
-------      ---------------------------------------------------------
   1            Resume NAme - G.Subramanian KAushik City - Chennai 
                 Location - triplicane

    

01 May 2009

Comparison of different SQL implementations

Hi,
     A complete description of the different implementation of the SQL standards, have been clearly explained in the following link.

This would really helps the developers, if they are migrating the application from one database to another.





Generating unique identifiers

Hi,
     In web applications it is required to use unique id in certain columns in the tables, so that it does not getsrepeated. It is possible to generate unique identifiers in PL/SQL using a built in named SYS_GUID.

This builtin generates a 32 chracter length identifier, which can be used as a primary key in a column.




SQL> begin
2 dbms_output.put_line('sys_guid');
3 dbms_output.put_line('--------');
4 dbms_output.put_line(sys_guid);
5 end;
6 /
sys_guid
--------
459C2423AA744A618EACC336A8808284

PL/SQL procedure successfully completed