Sponsored

Special Tips for Working with Multi-form Applications in Oracle D2k / Oracle Forms

The following is a list of pointers to have at your fingertips when developing multi-form applications. These are not hard and fast rules, but rules of thumb that, when followed, make the resulting application error-proof. Some of the pointers are based on the techniques in the pre ceding sections.
  • Use CALL_FORM to run forms in modal mode. The calling form remains inactive till the called form is exited.
  • Use NEW_FORM to close the calling form, and then invoke the called form.
  • Use OPEN_FORM for interform navigation or interform sessions. Multiple instances of the called form can be opened in this case.
  • Share data forward across forms by means of parameters and parameter lists, and reserve global variables to share data from the called to the calling forms.
  • When using timers, create timers in each form independently, and delete the respective timer when exiting the particular form. Doing so maintains used timers and discards unused ones. Also, this reduces unnecessary faults such as network errors, which might otherwise crop up, especially when running applications on the Web.
  • When opening multiple instances of the same form in OPEN_FORM configuration, especially in an OPEN_FORM chain, ensure correct navigation from subform to its parent form. This can be done in the WHEN-WINDOW-ACTIVATED trigger of the calling form using GO_FORM(form_id).
  • Invoke the child forms after saving pending database changes in the calling forms. Particular requirements due to the user or to the demands of the application are the only exclusions to this rule.
  • Do not exit parent forms before exiting child forms, unless otherwise specifically required. This avoids a random disorder of active forms left open. Also, it maintains an application's logical functional order.
  • Writing a generic procedure for simulating a CLOSE ALL FORMS provides extra flexibility to close a multi-form application in one shot.
  • Be careful about the data mode parameter of CALL_FORM, NEW_FORM, and OPEN_FORM. Use GLOBAL_SCOPE record groups as an alternative. This enables the sharing of global data among all forms with ease. For example, using a global scope record group can avoid the use of too many global variables.

Myths About the Form Filename, Form Module Name, and Form ID in Oracle D2k / Oracle Forms

Any Oracle Form that is stored as an operating system file has three values associated with it: the form filename, the form module name, and the form ID. The third value is invisible to the developer and can be obtained only by using Forms'supplied built-ins for that purpose. The first two seem to be closely related, but an in-depth analysis reveals that they are two entirely different values. This section exposes the theory hidden behind these three variables and presents tips to read and write their values.

The form filename is the actual operating system filename representing the binary .FMX. It can be obtained by using GET_APPLICATION_PROPERTY(CURRENT_FORM) or GET_FORM_PROPERTY(FILE_NAME).
The form module name is the name of the form module (which appears as the very first name under the Forms node in the Object Navigator), irrespective of whether the form is stored in the file system or database. This is obtained by GET_APPLICATION_PROPERTY(CURRENT_FORM_NAME) and GET_FORM_PROPERTY(FORM_NAME). The form system variables :SYSTEM.CURRENT_FORM and :SYSTEM.LAST_FORM also give the value of the form module name.

The form ID is an internal ID assigned to each form presently open (active or inactive) in a run-form session. This is of the composite data type FORMMODULE and can be obtained by using the FIND_FORM function. The parameter to be passed to this function can be one of the values specified in the preceding paragraph:

DECLARE

  form_id FORMMODULE;

BEGIN

  form_id := FIND_FORM(NAME_IN('SYSTEM.CURRENT_FORM'));

  form_id := FIND_FORM(GET_APPLICATION_PROPERTY(CURRENT_FORM_NAME)

END;

Also, the parameters for GO_FORM in an OPEN_FORM configuration can be either the form module name or form_id. I recommend using form_id because referencing by internal IDs saves an extra evaluation of the form_id each time the form is referenced.

Starting and Shutting Down a Database with RMAN in Oracle

Problem
You need to start and shut down the Oracle database from the RMAN client during a backup
and recovery–related task.
Solution
You can both shut down and start up a database using the equivalent of the usual SQL*Plus
startup and shutdown commands from the RMAN client. The following sections show how to
issue the startup and shutdown commands from RMAN.
Starting a Database
You can use the startup command with several options. Here’s an example that shows how the
database is opened using the startup command:
RMAN> startup
RMAN enables you to do more with the nomount option, however. In the following example,
you can see how you can go through all the steps of opening a database—starting the
instance, restoring the control file, mounting the control files, recovering the database, and,
finally, opening the database. The example shows how to restore the control file while connected
to the recovery catalog. After restoring the control file, the database is mounted with
the alter database mount command. Next you see the recover command, which is mandatory
after restoring a control file. Finally, the database is opened with the open resetlogs
option:

RMAN> connect target /
RMAN> connect catalog rman/rman@catdb
RMAN> startup nomount;
RMAN> restore controlfile;
RMAN> alter database mount;
RMAN> recover database;
RMAN> alter database open resetlogs;


The nomount option also comes in handy when you lose your spfile or are forced to start
the instance without a spfile (and any init.ora file). You can then use the nomount option to
start up the database with a dummy parameter file. For example:
set DBID 1296234570;
startup force nomount; # RMAN will start the instance with a dummy parameter file
Once RMAN starts the database with the dummy parameter file, you can restore the
actual spfile from the autobackup:
restore spfile from autobackup; # restore a server parameter file
startup force; # restart instance with the new server parameter file
After restoring the spfile, you can start the database using that spfile.
You can also use the dba option with the shutdown command to restrict access only to
those users who’ve been granted the restricted session privilege. Here’s how to do that:

RMAN> startup dba pfile=/tmp/initprod1.ora;

The database is now open, but only users with the restricted session privilege will be
able to connect. Typically, DBAs give the restricted session privilege only to each other. It
gives you a way to do work in the database while ensuring that no business users are connected.
Shutting Down a Database
Issue the shutdown command to close down the database and stop the instance. All the standard
SQL*Plus options you can use with the shutdown command—normal, immediate, abort,
and transactional—have the same effect and meaning when used from within RMAN. Here’s
an example:

RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> backup database;
RMAN> alter database open;


This example shuts down the database, kicking off any current users as soon as their currently
executing SQL statements finish. The database is then backed up and reopened for use.

Returning Nonexistent Rows in Oracle SQL

Problem
You have gaps in the sequence numbers used for a table’s primary key, and you want to identify the
intermediate sequence numbers.
Solution
Although primary keys are generally (and preferably) invisible to the end user, there are times you may
want to reuse sequence numbers where the number of digits in the sequence number must be kept as
low as possible to satisfy the requirements of downstream systems or processes. For example, the
company baseball team uses the number in EMPLOYEE_ID column as the number on the back of the
uniform jersey. The manager wants to reuse old jersey numbers if possible, since the number on the
back of the jersey is limited to three digits.
To find the employee numbers that were skipped or are currently not in use, you can use a query
with two embedded subqueries as follows:

with all_used_emp_ids as
(select level poss_emp_id from (select max(employee_id) max_emp_num
from employees)
connect by level <= max_emp_num)

select poss_emp_id
from all_used_emp_ids
where poss_emp_id not in (select employee_id from employees)
order by poss_emp_id
;

POSS_EMP_ID
----------------------
1
2
3
4
5
. . .
99
179
183
101 rows selected
The query retrieves any unused employee numbers up to the highest existing employee number
currently in the EMPLOYEES table.

ADD_GROUP_ROW built-in Oracle D2k / Oracle Forms

Description
Adds a row to the given record group.
Syntax
PROCEDURE ADD_GROUP_ROW
(recordgroup_id RecordGroup,
row_number NUMBER);
PROCEDURE ADD_GROUP_ROW
(recordgroup_name VARCHAR2,
row_number NUMBER);
Built-in Type unrestricted procedure
Enter Query Mode yes
Parameters
recordgroup_id The unique ID that Form Builder assigns when it creates the group. The
data type of the ID is RecordGroup.
recordgroup_name The name you gave to the record group when creating it. The data type of
the name is VARCHAR2.
row_number A whole number that specifies a row in the group. If you add a row to any
but the last position in a group, all rows below that are logically
renumbered. To add a row to the end of a group, use the
END_OF_GROUP constant.
Error Conditions:
Form Builder returns a runtime error given either of the following conditions:
·  If you enter the name of a non-existent record group.
·  If you supply a row number that is out of range or is invalid (for example, an alphabetic character).
ADD_GROUP_ROW restrictions
·  A group can consist of 0 or more rows.
·  You can add rows to a group only after it has been created and columns have been added.
·  If you specify a row number greater than the number of rows already in the group (or a negative
number), the row is inserted at the end of the group.
·  You cannot add rows to a static group without a query.
ADD_GROUP_ROW examples
/*
** Built-in: ADD_GROUP_ROW
** Example: Add ten rows to a new record group and populate.
*/
PROCEDURE Populate_My_Group IS
rg_name VARCHAR2(20) := ’My_Group’;
rg_col1 VARCHAR2(20) := rg_name||’.NumCol’;
rg_col2 VARCHAR2(20) := rg_name||’.CharCol’;
rg_id RecordGroup;
gc_id GroupColumn;
in_words VARCHAR2(15);
BEGIN
/*
** Check to see if Record Group already exists
*/
rg_id := Find_Group( rg_name );
/*
** If it does, then clear all the rows from the group and
** populate ten rows with the numbers from 1..10 along
** with the equivalent number in words.
**
** Row# NumCol CharCol
** ---- ------ -------
** 1 1 one
** 2 2 two
** : : :
** 10 10 ten
*/
IF NOT Id_Null(rg_id) THEN
Delete_Group_Row( rg_id, ALL_ROWS );
FOR i IN 1..10 LOOP
/*
** Add the i-th Row to the end (bottom) of the
** record group, and set the values of the two cells
*/
in_words := TO_CHAR(TO_DATE(i,’YYYY’),’year’);
Add_Group_Row( rg_id, END_OF_GROUP );
Set_Group_Number_Cell( rg_col1, i, i);
Set_Group_Char_Cell( rg_col2, i, in_words);
END LOOP;
END IF;
END;

Calling / Running a report in Oracle forms 10g / 11g

Below is the procedure to call a report in Oracle Forms 10g/11g  by passing report file name with full path and report format like 'PDF', 'RTF' etc.

Procedure call_report (preport In Varchar2, pformat In Varchar2)
Is
      pi_id                paramlist;
      repno                Number;
      al_id                alert;
      al_report            Number;
      User                 Varchar2 (25);
      pass                 Varchar2 (25);
      today                Date;
      store_var            Varchar2 (150);
      --OC
      vc_reportserverjob   Varchar2 (100);
      report_job_id        Varchar2 (100);
      repid                report_object;
      v_rep_status         Varchar2 (100);
   Begin
      pi_id := Get_parameter_list ('rep_param');

      If Not Id_null (pi_id)
      Then
         Destroy_parameter_list (pi_id);
      End If;

      pi_id := Create_parameter_list ('rep_param');

      Add_parameter (pi_id,
                     'PARAMFORM',
                     TEXT_PARAMETER,
                     'no');
      --- report object
      --- the below report object 'cproreport' must be created in Report object navigator.

      repid := Find_report_object ('cproreport');

      Set_report_object_property (repid, report_filename, Rtrim(:parameter.report_path)||preport);
      Set_report_object_property (repid, report_server, :parameter.r_server);
      Set_report_object_property (repid, report_execution_mode, RUNTIME);
      Set_report_object_property (repid, report_comm_mode, SYNCHRONOUS);
      Set_report_object_property (repid, report_destype, cache);
      Set_report_object_property (repid, report_desformat, pformat);

      vc_reportserverjob := Run_report_object (repid, pi_id);
      report_job_id :=
         Substr (vc_reportserverjob,
                 Length (:parameter.r_server) + 2,
                 Length (vc_reportserverjob));
      v_rep_status := Report_object_status (vc_reportserverjob);

      If v_rep_status = 'FINISHED'
      Then
         web.show_document (
               'http://'
            || :parameter.host
            || ':'
            || :parameter.port
            || '/reports/rwservlet/getjobid'
            || report_job_id
            || '?server='
            || :parameter.r_server,
            '_blank'
         );
      Else
         Message ('error when running report' || v_rep_status);
      End If;
   End;

LOGON_SCREEN built-in Oracle D2k / Oracle Forms

Description
Displays the default Form Builder logon screen and requests a valid username and password. Most
commonly, you will include this built-in subprogram in an On-Logon trigger to connect to a non-
ORACLE data source.
Syntax
PROCEDURE LOGON_SCREEN;
Built-in Type unrestricted procedure
Enter Query Mode yes
Parameters
none
LOGON_SCREEN restrictions
·  You must issue a call to the LOGON built-in to create the connection to your data source.
LOGON_SCREEN examples
/*
** Built-in: LOGON_SCREEN
** Example: Use the default Form Builder logon screen to
prompt
** for username and password before logging on to
** the database. This uses the ’Get_Connect_Info’
** procedure from the GET_APPLICATION_PROPERTY
** example.
*/
DECLARE
un VARCHAR2(80);
pw VARCHAR2(80);
cn VARCHAR2(80);
BEGIN
/*
** Bring up the logon screen
*/
Logon_Screen;
/*
** Get the username, password and
** connect string.
*/
Get_Connect_Info( un, pw, cn );
/*
** Log the user onto the database
*/
IF cn IS NOT NULL THEN
LOGON(un,pw||’@’||cn);
ELSE
LOGON(un,pw);
END IF;END;

GET_REPORT_OBJECT_PROPERTY built-in Oracle D2k / Oracle Forms

Description
Programmatically obtain a property of a report object.
Syntax
FUNCTION GET_REPORT_OBJECT_PROPERTY
(report_id REPORT_OBJECT,
property NUMBER
);
FUNCTION GET_REPORT_OBJECT_PROPERTY
(report_name VARCHAR2,
property NUMBER
);
Built-in Type unrestricted procedure
Enter Query Mode yes
Parameters
report_id Specifies the unique ID of the report. You can get the report ID for a
particular report using FIND_REPORT_OBJECT .
report_name Specifies the unique name of the report.
property One of the following constants:
REPORT_EXECUTION_MODE: Returns a string value of the report
execution mode, either BATCH or RUNTIME
REPORT_COMM_MODE: Returns a string value of the report
communication mode, either SYNCHRONOUS or ASYNCHRONOUS
REPORT_DESTYPE: Returns a string value of the report destination type,
either PREVIEW, FILE, PRINTER, MAIL, CACHE or SCREEN
REPORT_FILENAME: Returns a string value of the report filename
REPORT_SOURCE_BLOCK: Returns a string value of the report source
block name
REPORT_QUERY_NAME: Returns a string value of the report query
name
REPORT_DESNAME: Returns a string value of the report destination
name
REPORT_DESFORMAT: Returns a string value of the report destination
format
REPORT_SERVER: Returns a string value of the report server name
REPORT_OTHER: Returns a string value of the other user-specified report
properties

Usage Notes

·  GET_REPORT_OBJECT_PROPERTY returns a string value for all properties. In contrast,
SET_REPORT_OBJECT_PROPERTY sets properties using constant or string values. The value
type depends on the particular property being set.

GET_REPORT_OBJECT_PROPERTY examples

DECLARE
repid REPORT_OBJECT;
report_prop VARCHAR2(20);
BEGIN
repid := find_report_object(’report4’);
report_prop := get_report_object_property(repid,
REPORT_EXECUTION_MODE);
message(’REPORT EXECUTION MODE PROPERTY IS ’ || report_prop);
report_prop := get_report_object_property(repid,
REPORT_COMM_MODE);
message(’REPORT COMM_MODE PROPERTY IS ’ || report_prop);
report_prop := get_report_object_property(repid,
REPORT_DESTYPE);
message(’REPORT DESTYPE PROPERTY IS ’ || report_prop);
report_prop := get_report_object_property(repid,
REPORT_FILENAME);
message(’REPORT_FILENAME PROPERTY IS ’ || report_prop);
END;

GET_LIST_ELEMENT_COUNT built-in Oracle D2k / Oracle Forms

Description
Returns the total number of list item elements in a list, including elements with NULL values.
Syntax
FUNCTION GET_LIST_ELEMENT_COUNT
(list_id Item);
FUNCTION GET_LIST_ELEMENT_COUNT
(list_name VARCHAR2);
Built-in Type unrestricted function
Returns VARCHAR2
Enter Query Mode yes
Parameters
list_id Specifies the unique ID that Form Builder assigns when it creates the list
item. Use the FIND_ITEM built-in to return the ID to an appropriately
typed variable. The data type of the ID is ITEM.
list_name The name you gave to the list item when you created it. The data type of
the name is VARCHAR2.
GET_LIST_ELEMENT_COUNT examples
/*
** Built-in: GET_LIST_ELEMENT_COUNT
** Example: Add an element to the list item. Before adding
** the element, verify that the element is not in
** the current list.
*/
DECLARE
total_list_count NUMBER(2);
loop_index_var NUMBER(2) := 1;
list_element VARCHAR2(50);
list_element_value VARCHAR2(50);
list_element_to_add VARCHAR2(50);
list_value_to_add VARCHAR2(50);
element_match VARCHAR2(5) := ’TRUE’;
value_match VARCHAR2(5) := ’TRUE’;
BEGIN
/*
** Determine the total number of list elements.
*/
total_list_count := Get_List_Element_Count(list_id);
/*
** Compare the current list item elements with the element that
** will be added.
*/
LOOP
list_element := Get_List_Element_Value(list_id,
loop_index_var);
loop_index_var := loop_index_var + 1;
IF list_element_to_add = list_element THEN
element_match := ’FALSE’;
END IF;
EXIT WHEN list_element = list_element_to_add OR
loop_index_var = total_list_count;
END LOOP;
/*
** Compare the current list item values with the value that
** will be added.
*/
loop_index_var := 1;
LOOP
list_element_value:= Get_List_Element_Value(list_id,
loop_index_var);
loop_index_var := loop_index_var + 1;
IF list_value_to_add = list_element_value THEN
value_match := ’FALSE’;
END IF;
EXIT WHEN list_element_value = list_value_to_add OR
loop_index_var = total_list_count;
END LOOP;
/*
** Add the element and value if it is not in the current list
*/
IF element_match AND value_match = ’TRUE’ THEN
Add_List_Element(list_id, list_name, list_element_to_add,
list_value_to_add);
END IF
END;

Determining the First Date or Day in a Month in Oracle

Problem
You need to dynamically determine the first date in a given month at runtime.Solution
Oracle’s TRUNC function can be used on dates to perform start-of-period calculations, such as the first
day of the month.

select trunc(sysdate,'mm')
from dual;


TRUNC(SYS
---------
01-MAY-09

Our recipe was written on May 21st. Any arbitrary date, datetime, or timestamp expression can be
used.

Testing for the Existence of Data with Oracle SQL

Problem
You would like to compare the data in two related tables, to show where matching data exists, and to
also show where matching data doesn’t exist.
Solution
Oracle supports the EXISTS and NOT EXISTS predicates, allowing you to correlate the data in one table or
expression with matching or missing data in another table or expression. We’ll use the hypothetical
situation of needing to find which departments currently have managers. Phrasing this in a way that best
illustrates the EXISTS solution, the next SQL statement finds all departments where a manager is known
to exist.

select department_name
from hr.departments d
where exists
(select e.employee_id
from hr.employees e
where d.manager_id = e.employee_id);


The complement, testing for non-existence, is shown in the next statement. We ask to find all
departments in HR.DEPARTMENTS with a manager that does not exist in the data held in HR.EMPLOYEES.

select department_name
from hr.departments d
where not exists
(select e.employee_id
from hr.employees e
where d.manager_id = e.employee_id);


QUERY_PARAMETER built-in Oracle D2k / Oracle Forms 6i / 10g

Description
Displays the Query Parameter dialog showing the current values of the specified substitution parameters.
End users can set the value of any parameter you include in the list.
The Query Parameter dialog is modal, and control does not return to the calling trigger or procedure until
the end user either accepts or cancels the dialog. This means that any PL/SQL statements that follow the
call to QUERY_PARAMETER are not executed until the Query Parameter dialog is dismissed.
Syntax
PROCEDURE QUERY_PARAMETER
(parameter_string VARCHAR2);
Built-in Type unrestricted procedure
Parameters
parameter_string Specifies a string of substitution parameters for a menu item. The syntax
for specifying the parameter_string parameter requires the ampersand
&parm_name. Substitution parameters are referenced in PL/SQL code
with the colon syntax ":param_name" used for all bind variables).
QUERY_PARAMETER examples
/*
** Built-in: QUERY_PARAMETER
** Example: Prompt for several menu parameters
** programmatically, validating their contents.
*/
PROCEDURE Update_Warehouse IS
validation_Err BOOLEAN;
BEGIN
WHILE TRUE LOOP
Query_Parameter(’&p1 &q2 &z6’);
/*
** If the user did not Cancel the box the Menu_Success
** function will return boolean TRUE.
*/
IF Menu_Success THEN
IF TO_NUMBER( :q2 ) NOT BETWEEN 100 AND 5000 THEN
Message(’Qty must be in the range 100..5000’);
Bell;
Validation_Err := TRUE;
END IF;
/*
** Start a sub-block so we can catch a Value_Error
** exception in a local handler
*/
BEGIN
IF TO_DATE( :z6 ) < SYSDATE THEN
Message(’Target Date must name a day in the future.’);
Bell;
Validation_Err := TRUE;
END IF;
EXCEPTION
WHEN VALUE_ERROR THEN
Message(’Target Date must be of the form DD-MON-YY’);
Bell;
Validation_Err := TRUE;
END;
/*
** If we get here, all parameters were valid so do the
** Update Statement.
*/
IF NOT Validation_Err THEN
UPDATE WAREHOUSE
SET QTY_TO_ORDER = QTY_TO_ORDER*0.18
WHERE TARGET_DATE = TO_DATE(:z6)
AND QTY_ON_HAND > TO_NUMBER(:q2)
AND COST_CODE LIKE :p1||’%’;
END IF;
ELSE
/*
** If Menu_Success is boolean false, then return back
** from the procedure since user cancelled the dialog
*/
RETURN;
END IF;
END LOOP;
END;

Advertise with lowest / cheapest price on the web

Advertise on the highest advertise blog site. Take the direct publisher contact benefit no commission to agents, affiliates, cpc etc. On lowest price.
Show your image banner advertisement with the following sizes:

300 x 250

326 x 286

728 x 60

468 x 60

300 x 600

160 x 600

Pick any of the above sizes for $100 per month only. This offer will end on 24th December 2012. So hurry take the advantage of this X-Mas Season.

Just e-mail us at foxinfotech@yahoo.com


Do you want to have a blog site but you don't have time or knowledge don't worry we will do it for you click this link Blog Site Development with SEO

Tags:
Online advertising
Like google adwords
advertising cheapest
advertising lowest price
advertising on internet

CREATE_RECORD built-in in Oracle D2k / Oracle Forms 6i / 10g

Description
Creates a new record in the current block after the current record. Form Builder then navigates to the
new record.
Syntax
PROCEDURE CREATE_RECORD;
Built-in Type restricted procedure
Enter Query Mode no
Parameters
none
CREATE_RECORD examples
/*
** Built-in: CREATE_RECORD
** Example: Populate new records in a block based on return
** values from a query
*/
PROCEDURE Populate_Rows_Into_Block( projid NUMBER) IS
CURSOR tempcur( cp_projid NUMBER ) IS
SELECT milestone_name, due_date
FROM milestone
WHERE project_id = cp_projid
ORDER BY due_date;
BEGIN
/* Add these records to the bottom of the block */
Last_Record;
/* Loop thru the records in the cursor */
FOR rec IN tempcur( projid ) LOOP
/*
** Create an empty record and set the current row’s
** Milestone_Name and Due_Date items.
*/
Create_Record;
: Milestone.Milestone_Name := rec.milestone_name;
: Milestone.Due_Date := rec.due_date;
END LOOP;
First_Record;
END;

SHOW_EDITOR built-in Oracle D2k / Oracle Forms 6i

Description
Displays the given editor at the given coordinates and passes a string to the editor, or retrieves an
existing string from the editor. If no coordinates are supplied, the editor is displayed in the default
position specified for the editor at design time.
Syntax
SHOW_EDITOR
(editor_id Editor,
message_in VARCHAR2,
message_out VARCHAR2,
result BOOLKAN);
SHOW_EDITOR
(editor_id Editor,
message_in VARCHAR2,
x NUMBER,
y NUMBER,
message_out VARCHAR2,
result BOOLEAN);
SHOW_EDITOR
(editor_name VARCHAR2,
message_in VARCHAR2,
message_out VARCHAR2,
result BOOLEAN);
SHOW_EDITOR
(editor_name VARCHAR2,
message_in VARCHAR2,
x NUMBER,
y NUMBER,
message_out VARCHAR2,
result BOOLEAN);
Built-in Type unrestricted procedure that returns two OUT parameters (result and message_out)
Enter Query Mode yes

SHOW_EDITOR examples
/*
** Built-in: SHOW_EDITOR
** Example: Accept input from the operator in a user-defined
** editor. Use the system editor if the user has
** checked the "System_Editor" menu item under the
** "Preferences" menu in our custom menu module.
*/
DECLARE
ed_id Editor;
mi_id MenuItem;
ed_name VARCHAR2(40);
val VARCHAR2(32000);
ed_ok BOOLEAN;
BEGIN
mi_id := Find_Menu_Item(’PREFERENCES.SYSTEM_EDITOR’);
IF Get_Menu_Item_Property(mi_id,CHECKED) = ’TRUE’ THEN
ed_name := ’system_editor’;
ELSE
ed_name := ’my_editor1’;
END IF;
ed_id := Find_Editor( ed_name );
/*
** Show the appropriate editor at position (10,14) on the
** screen. Pass the contents of the :emp.comments item
** into the editor and reassign the edited contents if
** ’ed_ok’ returns boolean TRUE.
*/
val := :emp.comments;
Show_Editor( ed_id, val, 10,14, val, ed_ok);
IF ed_ok THEN
:emp.comments := val;
END IF;
END;

Show_Editor In Oracle Forms

Reviewed by Pauline on

Mar 11

Rating:
5

Horsey attachable bicycle ornament / accessory

 Who doesn't like to accessorized their bicycle? We all do.Our idea of accessorizing our bike may not be as mind numbingly ridiculous as this so-called horse contraption, but accessorize our bike we do. Shortlisted for several design awards, Horsey is the brainchild of a designer from Korea, meant for bicycle riders who want to imagine themselves as equesttrians. In the designer's own words, "Horsey is an attachable bicycle ornament / accessory which makes one's bike Horsey! through this 'Horsey' project.

A different / accessorized / attachable cycle.


Finding the Difference Between Two Dates or Date Parts in Oracle (SQL)

Problem
You want to calculate the elapsed time between two dates or datetime values.
Solution
Oracle supports intuitive date and datetime arithmetic using normal operators like the addition and
subtraction symbols. Subtracting two dates to find the difference between them works, but produces
results that aren’t necessarily formatted for easy understanding. For instance, we can use the next SQL
statement to find the employment duration for employees in the HR.EMPLOYEES table, but then have to
wrestle with part-days represented as fractional numbers.


select employee_id, first_name, last_name, sysdate - hire_date Length_of_employment
from hr.employees;


EMPLOYEE_ID FIRST_NAME LAST_NAME LENGTH_OF_EMPLOYMENT
----------- ---------- --------- --------------------
198 Donald OConnell 3636.5341
199 Douglas Grant 3430.5341
200 Jennifer Whalen 7931.5341
201 Michael Hartstein 4856.5341
202 Pat Fay 4309.5341

If you’re calculating date and time differences, you usually want to be precise, down to the second
or even smaller time unit. If you’re calculating employee duration for a human-readable report, you
probably want values in years or years and months. There’s little need to be accurate to the fraction of a
second in this kind of report. The next SQL statement achieves both precise seconds and humanreadable
years and months as output.


select employee_id, first_name, last_name,
(sysdate - hire_date)*86400 Emp_Length_Seconds,
extract(year from (sysdate - hire_date) year to month) || ' years, ' ||
extract(month from (sysdate - hire_date) year to month) || ' months. '
Emp_Length_Readable
from hr.employees;


EMPLOYEE_ID FIRST_NAME LAST_NAME EMP_LENGTH_SECONDS EMP_LENGTH_READABLE
----------- ---------- --------- ------------------ ------------------
198 Donald OConnell 314198410 9 years, 11 months.
199 Douglas Grant 296400010 9 years, 5 months.
200 Jennifer Whalen 685286410 21 years, 9 months.
201 Michael Hartstein 419606410 13 years, 4 months.
202 Pat Fay 372345610 11 years, 10 months.

Nintendo Wii U goes on sale in U.S. with apps and no TV

    Smaller Text Larger Text Text Size
    Print
    E-mail
    Reprints

By Cliff Edwards and Michelle Kaske, Published: November 19

Nov. 19 (Bloomberg) -- Nintendo Co., working to prove it can still succeed by marrying its hardware to exclusive software, began selling the Wii U console amid tight supplies and delays in implementing a new TV-viewing service.

The first new video-game console for U.S. homes since 2006, the Wii U initially won’t offer the Nintendo TVii service that the Kyoto, Japan-based company has touted as a centerpiece of its capabilities. The feature will be available sometime in December, the company said on Nov. 16, without being specific.

“The value of Wii U goes well beyond day one,” Reggie Fils-Aime, Nintendo’s North America president, said in a statement. “Nintendo will be enhancing the Wii U experience with continuous updates and new services for Wii U owners.”

POST built-in in Oracle D2k Forms

Description
Writes data in the form to the database, but does not perform a database commit. Form Builder first
validates the form. If there are changes to post to the database, for each block in the form Form Builder
writes deletes, inserts, and updates to the database.
Any data that you post to the database is committed to the database by the next COMMIT_FORM that
executes during the current Runform session. Alternatively, this data can be rolled back by the next
CLEAR_FORM.
Syntax
PROCEDURE POST;
Built-in Type restricted procedure
Enter Query Mode no
Parameters
none
Usage Notes
If this form was called via OPEN_FORM with the NO_SESSION parameter specified, then the POST
will validate and write the data both in this form and in the calling form.
POST examples
/*
** Built-in: POST and EXIT_FORM
** Example: Leave the called form, without rolling back the
** posted changes so they may be posted and
** committed by the calling form as part of the
** same transaction.
*/
BEGIN
Post;
/*
** Form_Status should be ’QUERY’ if all records were
** successfully posted.
*/
IF :System.Form_Status <> ’QUERY’ THEN
Message(’An error prevented the system from posting
changes’);
RAISE Form_trigger_Failure;
END IF;
/*
** By default, Exit_Form asks to commit and performs a
** rollback to savepoint. We’ve already posted, so we do
** not need to commit, and we don’t want the posted changes
** to be rolled back.
*/
Exit_Form(NO_COMMIT, NO_ROLLBACK);
END;

PLAY_SOUND built-in in Oracle D2k Forms

Description
Plays the sound object in the specified sound item.
Syntax
PLAY_SOUND(item_id ITEM);
PLAY_SOUND(item_name VARCHAR2);
Built-in Type restricted
Enter Query Mode No
Parameters:
item_id The unique ID Form Builder gave the sound item when you created it.
item_name The name you gave the sound item when you created it.
PLAY_SOUND examples
/* Example 1: This procedure call (attached to a menu item)
** plays a sound object from the specified sound item:
*/
GO_ITEM(’about.abc_inc’);
PLAY_SOUND(’about.abc_inc’);
/* Example 2: These procedure calls (attached to a
** When-Button-Pressed trigger) read a sound object from the
** file system and play it. Note: since an item must have focus
** in order to play a sound, the trigger code includes a call
** to the built-in procedure GO_ITEM:
*/
BEGIN
IF :clerks.last_name EQ ’BARNES’ THEN
GO_ITEM(’orders.filled_by’);
READ_SOUND_FILE(’t:\orders\clerk\barnes.wav’,
’wave’,
’orders.filled_by’);
PLAY_SOUND(’orders.filled_by’);
END IF;
END;

NAME_IN built-in in Oracle D2k Forms

Description
Returns the value of the indicated variable.
The returned value is in the form of a character string. However, you can use NAME_IN to return
numbers and dates as character strings and then convert those strings to the appropriate data types. You
can use the returned value as you would use any value within an executable statement.
If you nest the NAME_IN function, Form Builder evaluates the individual NAME_IN functions from the
innermost one to the outermost one.
Syntax
FUNCTION NAME_IN
(variable_name VARCHAR2);
Built-in Type unrestricted function
Returns VARCHAR2
Enter Query Mode yes
Parameters
variable_name Specifies a valid variable or text item. The data type of the name is
VARCHAR2.
Usage Notes
If the returned value is a date string, NAME_IN will use the format mask specified in the
BUILTIN_DATE_FORMAT property. If the DATE_FORMAT_COMPATIBILITY_MODE property
is set to 4.5 the default American format is used to format the returned string.
NAME_IN examples
/*
** Built-in: NAME_IN
** Example: Simple implementation of a Last-In-First-Out
** stack mechanism using Global variables.
** For each named stack, a global variable
** GLOBAL.<stackname>_PTR points to the largest
** element on the stack. PUSH increments this
** value as new elements are added. Values
** PUSH’ed on or POP’ed off the named stack are
** actually stored in GLOBAL variables of a
** conveniently formed name: GLOBAL.<stackname>nnn
** where ’nnn’ is the number of the element on the
** stack.
**
** Usage:
** Push(’MYSTACKNAME’, ’1’);
** Push(’MYSTACKNAME’, ’2’);
**
** str_var := Pop(’MYSTACKNAME’); -- Gets ’2’
** str_var := Pop(’MYSTACKNAME’); -- Gets ’1’
** str_var := Pop(’MYSTACKNAME’); -- Gets ’EOS’
**
*/
PROCEDURE Push ( the_stackname VARCHAR2,
the_value VARCHAR2 ) IS
ptr_name VARCHAR2(40); -- This stack’s pointer name
prefix VARCHAR2(40); -- Common prefix for storage vars
elt_name VARCHAR2(40); -- Name of storage element
new_idx VARCHAR2(4) ; -- New stack pointer value
BEGIN
/*
** For any named stack that we reference, the global
** variables used for storing the stack’s values and the
** stack’s pointer all begin with a common prefix:
** GLOBAL.<stackname>
*/
prefix := ’GLOBAL.’ || the_stackname;
/*
** This named stack’s pointer resides in
** GLOBAL.<stackname>_PTR Remember that this is the *name*
** of the pointer.
*/
ptr_name := prefix || ’_PTR’;
/*
** Initialize the stack pointer with a default value of
** zero if the stack pointer did not exist previously, ie
** the GLOBAL.<stackname>_PTR had yet to be created.
*/
Default_Value( ’0’, ptr_name );
/*
** Since we’re PUSH’ing a new element on the stack,
** increment the stack pointer to reflect this new
** element’s position. Remember that GLOBAL variables are
** always of type VARCHAR2, so we must convert them TO_NUMBER
** before any calculations.
*/
new_idx := TO_CHAR( TO_NUMBER( Name_In( ptr_name ) ) + 1 ) ;
Copy( new_idx , ptr_name );
/*
** Determine the name of the global variable which will
** store the value passed in, GLOBAL.<stackname><new_idx>.
** This is simply the prefix concatenated to the new index
** number we just calculated above.
*/
elt_name := prefix||new_idx;
Copy( the_value , elt_name );
END;
FUNCTION Pop ( the_stackname VARCHAR2)
RETURN VARCHAR2 IS
ptr_name VARCHAR2(40); -- This stack’s pointer name
prefix VARCHAR2(40); -- Common prefix for storage vars
elt_name VARCHAR2(40); -- Name of storage element
new_idx VARCHAR2(4) ; -- New stack pointer value
cur_idx VARCHAR2(4) ; -- Current stack pointer value
the_val VARCHAR2(255);
EMPTY_STACK CONSTANT VARCHAR2(3) := ’EOS’;
NO_SUCH_STACK CONSTANT VARCHAR2(3) := ’NSS’;
BEGIN
/*
** For any named stack that we reference, the global
** variables used for storing the stack’s values and the
** stack’s pointer all begin with a common prefix:
** GLOBAL.<stackname>
*/
prefix := ’GLOBAL.’ || the_stackname;
/*
** This named stack’s pointer resides in
** GLOBAL.<stackname>_PTR Remember that this is the *name*
** of the pointer.
*/
ptr_name := prefix || ’_PTR’;
/*
** Force a default value of NULL so we can test if the
** pointer exists (as a global variable). If it does not
** exist, we can test in a moment for the NULL, and avoid
** the typical error due to referencing non-existent
** global variables.
*/
Default_Value( NULL, ptr_name );
/*
** If the *value* contained in the pointer is NULL, then
** the pointer must not have existed prior to the
** Default_Value statement above. Return the constant
** NO_SUCH_STACK in this case and erase the global
** variable that the Default_Value implicitly created.
*/
IF Name_In( ptr_name ) IS NULL THEN
the_val := NO_SUCH_STACK;
Erase( ptr_name );
/*
** Otherwise, the named stack already exists. Get the
** index of the largest stack element from this stack’s
** pointer.
*/
ELSE
cur_idx := Name_In( ptr_name ) ;
/*
** If the index is zero, then the named stack is already
** empty, so return the constant EMPTY_STACK, and leave
** the stack’s pointer around for later use, ie don’t
** ERASE it.
**
** Note that a stack can only be empty if some values
** have been PUSH’ed and then all values subsequently
** POP’ed. If no values were ever PUSH’ed on this named
** stack, then no associated stack pointer would have
** been created, and we would flag that error with the
** NO_SUCH_STACK case above.
*/
IF cur_idx = ’0’ THEN
the_val := EMPTY_STACK;
/*
** If the index is non-zero, then:
** (1) Determine the name of the global variable in
** which the value to be POP’ed is stored,
** GLOBAL.<stackname><cur_idx>
** (2) Get the value of the (cur_idx)-th element to
** return
** (3) Decrement the stack pointer
** (4) Erase the global variable which was used for
** value storage
*/
ELSE
elt_name:= prefix || cur_idx;
the_val := Name_In( elt_name );
new_idx := TO_CHAR( TO_NUMBER( Name_In(ptr_name) ) - 1 ) ;
Copy( new_idx , ptr_name );
Erase( elt_name );
END IF;
END IF;
RETURN the_val;
END;

  Doodle for Google / Doodle4Google 2012 winner

 Arun Kumar Yadav, Kendriya Vidyalaya Chandigarh

India - A prism of multiplicity

India has diverse cultures, religions, languages, customs and traditions. This diversity can be witnessed in enthusiasm for sports; unique folk culture; extraordinary remarkable handicrafts; wide range of flora and fauna; agricultural practices with worldwide farming output; unparalleled spices and cuisines... Such colossal diversities represent Indias oneness.
We have this thing called a logo which you see each time you visit www.google.co.in. We love it and hate the idea of it becoming boring. So, we often do fun things with our logo and call it a doodle. To keep the fun going, we like to invite bright young minds to come and share their imagination and creativity by designing their own doodle, and that’s when you doodle4google.Last year we asked young doodlers across India to describe, what they think India's biggest contribution to the world has been.

The doodle theme for 2012...

This year we are looking for doodles which show India’s “Unity in Diversity”. Bring alive the things you think are uniquely different and diverse, yet unite India and make it into a doodle.


Converting digits/numbers in words for INR currency (Indian Rupees) in Oracle PL/SQL

Below are the two functions for converting numbers/digits to Indian Rupees words. The main function is Rupees function.
Usage:

Select Rupees(99999.99) from dual;

or

Set serveroutput on;
Declare
  vstring varchar2(200);
Begin
   vstring := Rupees(9546);
  dbms_output.put_line(vstring);
end;

It is valid for maximum 99,999.00, you can further modify.

CREATE OR REPLACE FUNCTION number_to_word (pnum in number) RETURN varchar2 IS
rvalue varchar2(50);
BEGIN
  if pnum = 1 then    
      rvalue := 'One';
  elsif pnum = 2 then
      rvalue := 'Two';
  elsif pnum = 3 then
      rvalue := 'Three';
  elsif pnum = 4 then
      rvalue := 'Four';
  elsif pnum = 5 then
      rvalue := 'Five';
  elsif pnum = 6 then
      rvalue := 'Six';
  elsif pnum = 7 then
      rvalue := 'Seven';
  elsif pnum = 8 then
      rvalue := 'Eight';
  elsif pnum = 9 then
      rvalue := 'Nine';
  elsif pnum = 10 then
      rvalue := 'Ten';
  elsif pnum = 11 then
      rvalue := 'Eleven';
  elsif pnum = 12 then
      Rvalue := 'Twelve';
  elsif pnum = 13 then
      rvalue := 'Thirteen';
  elsif pnum = 14 then
      rvalue := 'Fourteen';
  elsif pnum = 15 then
      rvalue := 'Fifteen';
  elsif pnum = 16 then
      rvalue := 'Sixteen';
  elsif pnum = 17 then
      rvalue := 'Seventeen';
  elsif pnum = 18 then
      rvalue := 'Eighteen';
  elsif pnum = 19 then
      rvalue := 'Nineteen';
  elsif pnum = 20 then
      rvalue := 'Twenty';
  elsif pnum = 30 then
      rvalue := 'Thirty';
  elsif pnum = 40 then
      rvalue := 'Forty';
  elsif pnum = 50 then
      rvalue := 'Fifty';
  elsif pnum = 60 then
      rvalue := 'Sixty';
  elsif pnum = 70 then
      rvalue := 'Seventy';
  elsif pnum = 80 then
      rvalue := 'Eighty';
  elsif pnum = 90 then
      rvalue := 'Ninety';
  else
      rvalue := '';
  end if;
  return(rvalue);
END;
/

CREATE OR REPLACE FUNCTION Rupees (pn IN NUMBER)
   RETURN VARCHAR2
IS
   vt     VARCHAR2 (200);
   ntv    VARCHAR2 (10);
   vlen   NUMBER (2);
BEGIN
   vt := 'Rupees ';
   ntv := RTRIM (LTRIM (TO_CHAR (pn)));
   vlen := LENGTH (ntv);

   IF vlen > 0 AND vlen < 2
   THEN                                                        -- single digit
      vt := vt || number_to_word (pn);
   ELSIF vlen > 1 AND vlen < 3
   THEN                                                           -- two digit
      IF pn < 21
      THEN
         vt := vt || number_to_word (pn);
      ELSE
         vt :=
            vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 1, 1) || '0'));
         vt := vt || ' ';
         vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 2, 1)));
      END IF;
   -- two digit handled.
   ELSIF vlen > 2 AND vlen < 4
   THEN                                                         -- three digit
      vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 1, 1)));
      vt := vt || ' Hundred ';

      IF TO_NUMBER (SUBSTR (ntv, 2, 2)) > 0
      THEN
         vt := vt || 'and ';
      END IF;

      IF TO_NUMBER (SUBSTR (ntv, 2, 2)) > 20
      THEN
         vt :=
            vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 2, 1) || '0'));

         IF TO_NUMBER (SUBSTR (ntv, 2, 1)) > 0
         THEN
            vt := vt || ' ';
         END IF;

         vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 3, 1)));
      ELSE
         vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 2, 2)));

         IF TO_NUMBER (SUBSTR (ntv, 2, 1)) > 0
         THEN
            vt := vt || ' ';
         END IF;
      END IF;
   -- three digit handled
   ELSIF vlen > 3 AND vlen < 5
   THEN                                                            -- thousand
      vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 1, 1)));
      vt := vt || ' Thousand ';

      IF TO_NUMBER (SUBSTR (ntv, 2, 3)) > 0
      THEN
         IF TO_NUMBER (SUBSTR (ntv, 2, 1)) > 0
         THEN
            vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 2, 1)));
            vt := vt || ' Hundred ';
         END IF;

         IF TO_NUMBER (SUBSTR (ntv, 3, 2)) > 0
         THEN
            vt := vt || 'and ';
         END IF;

         IF TO_NUMBER (SUBSTR (ntv, 3, 2)) > 20
         THEN
            -- 9999
            vt :=
               vt
               || number_to_word (TO_NUMBER (SUBSTR (ntv, 3, 1) || '0'));

            IF TO_NUMBER (SUBSTR (ntv, 3, 1)) > 0
            THEN
               vt := vt || ' ';
            END IF;

            vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 4, 1)));
         ELSE
            vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 3, 2)));

            IF TO_NUMBER (SUBSTR (ntv, 3, 1)) > 0
            THEN
               vt := vt || ' ';
            END IF;
         END IF;
      END IF;
      -- thousand handled now 99 thousand.
      ELSIF vlen > 4 AND vlen < 6
   THEN                                                           
   -- thousand
   if to_number(substr(ntv,1,2)) <= 20 then
      vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 1, 2)));
      vt := vt || ' Thousand ';
      else
      vt := vt || number_to_word(to_number(substr(ntv,1,1)||'0'));
      vt := vt || ' '|| number_to_word(to_number(substr(ntv,2,1)));
      vt := vt || ' Thousand ';
   end if;

      IF TO_NUMBER (SUBSTR (ntv, 3, 3)) > 0
      THEN
         IF TO_NUMBER (SUBSTR (ntv, 3, 1)) > 0
         THEN
            vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 3, 1)));
            vt := vt || ' Hundred ';
         END IF;

         IF TO_NUMBER (SUBSTR (ntv, 4, 2)) > 0
         THEN
            vt := vt || 'and ';
         END IF;

         IF TO_NUMBER (SUBSTR (ntv, 4, 2)) > 20
         THEN
            -- 99999
            vt :=
               vt
               || number_to_word (TO_NUMBER (SUBSTR (ntv, 4, 1) || '0'));

            IF TO_NUMBER (SUBSTR (ntv, 4, 1)) > 0
            THEN
               vt := vt || ' ';
            END IF;

            vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 5, 1)));
         ELSE
            vt := vt || number_to_word (TO_NUMBER (SUBSTR (ntv, 4, 2)));

            IF TO_NUMBER (SUBSTR (ntv, 4, 1)) > 0
            THEN
               vt := vt || ' ';
            END IF;
         END IF;
      END IF;
   END IF;

   IF LENGTH (vt) > 7
   THEN
      vt := RTRIM (vt) || ' only.';
   ELSE
      vt := '';
   END IF;

   RETURN (vt);
END;
/

Traditional Searching, Extracting, and Replacing String in Oracle PL/SQL

Frequently, you’ll find yourself wanting to search a string for a bit of text. Starting with
Oracle Database 10g, you can use regular expressions for these textual manipulations;
see the section later in this chapter on regular expressions for the full details. If you’re
not yet using Oracle Database 10g or later, you can use an approach that is backwardcompatible
to older database versions. The INSTR function returns the character position
of a substring within a larger string. The following code finds the locations of all
the commas in a list of names:
 
DECLARE
names VARCHAR2(60) := 'Anna,Matt,Joe,Nathan,Andrew,Aaron,Jeff';
comma_location NUMBER := 0;
BEGIN
LOOP
comma_location := INSTR(names,',',comma_location+1);
EXIT WHEN comma_location = 0;
DBMS_OUTPUT.PUT_LINE(comma_location);
END LOOP;
END;

The output is:

5
10
14
21
28
34

The first argument to INSTR is the string to search. The second is the substring to look
for, in this case a comma. The third argument specifies the character position at which
to begin looking. After each comma is found, the loop begins looking again one character
further down the string. When no match is found, INSTR returns zero, and the
loop ends.

Having found the location of some text in a string, a natural next step is to extract it. I
don’t care about those commas. Let’s extract the names instead. For that, I’ll use the
SUBSTR function:

DECLARE
names VARCHAR2(60) := 'Anna,Matt,Joe,Nathan,Andrew,Aaron,Jeff';
names_adjusted VARCHAR2(61);
comma_location NUMBER := 0;
prev_location NUMBER := 0;
BEGIN
--Stick a comma after the final name
names_adjusted := names || ',';
LOOP
comma_location := INSTR(names_adjusted,',',comma_location+1);
EXIT WHEN comma_location = 0;
DBMS_OUTPUT.PUT_LINE(
SUBSTR(names_adjusted,
prev_location+1,
comma_location-prev_location-1));
prev_location := comma_location;
END LOOP;
END;

The list of names that I get is:

Anna
Matt
Joe
Nathan
Andrew
Aaron
Jeff

The keys to the preceding bit of code are twofold. First, a comma is appended to the
end of the string to make the loop’s logic easier to write. Every name in names_adjusted
is followed by a comma. That simplifies life. Then, each time the loop iterates to
DBMS_OUTPUT.PUT_LINE, the two variables named prev_location and
comma_location point to the character positions on either side of the name to print.

It’s then just a matter of some simple math and the SUBSTR function. Three arguments
are passed:
names_adjusted
The string from which to extract a name.
prev_location+1
The character position of the first letter in the name. Remember that prev_location
will point to just before the name to display, usually to a comma preceding the
name. That’s why I add 1 to the value.
comma_location-prev_location-1
The number of characters to extract. I subtract the extra 1 to avoid displaying the
trailing comma.

All this searching and extracting is fairly tedious. Sometimes I can reduce the complexity
of my code by cleverly using some of the built-in functions. Let’s try the
REPLACE function to swap those commas with newlines:

DECLARE
names VARCHAR2(60) := 'Anna,Matt,Joe,Nathan,Andrew,Aaron,Jeff';
BEGIN
DBMS_OUTPUT.PUT_LINE(
REPLACE(names, ',', chr(10))
);
END;


And the output is (!):

Anna
Matt
Joe
Nathan
Andrew
Aaron
Jeff

By using REPLACE I was able to avoid all that looping. I got the same results with code
that is more simple and elegant. Of course, you won’t always be able to avoid loop
processing by using REPLACE, but it’s good to know about alternative algorithms.
With programming, there are always several ways to get the results you want!

This blog site is the most advertised site. Site is very well organized with advertisement and its content. This site is representing top brands like Amazon.com, Flipcart.com, famous dating sites, games, new offers, new search engines and many more to come like Yatra.com, Makemytrip.com and bharatmatrimony.com etc.

This blog site itself gaining popularity and getting famous blog site.

Getting a popular blog site. http://foxinfotech.blogspot.in


Do you want to have a blog site but you don't have time to develop ori lack of knowledge then don't worry we will do it for you click this link for more information http://foxinfotech.blogspot.in/2012/12/blog-site-development-blogsite.html

Copying Rows from One Table to Another in Oracle Sql

Problem
You want to copy information from one table to another.
Solution
Use the INSERT statement with the SELECT option to copy data from one table to another. Suppose you
have a table of candidates applying for jobs at your company, with many of the same details as the
HR.EMPLOYEES table. This INSERT statement will insert into the HR.EMPLOYEES table based on a SELECT
statement on the CANDIDATES table.

insert into hr.employees
(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, 􀂫
commission_pct, manager_id, department_id)
select 210, first_name, last_name, email, phone_number, sysdate, 'IT_PROG', 3500, 􀂫
NULL, 103, 60
from hr.candidates
where first_name = 'Susan'
and last_name = 'Jones';


Traversing Hierarchical Data from Top to Bottom in Oracle Sql with connect by

Problem
You need to generate reports from tables with hierarchical data, traversing the hierarchy from top to
bottom and clearly denoting the level of each row in the hierarchy.
Solution
Use the CONNECT BY clause to specify a hierarchical query, the PRIOR operator to define the linking
condition between the parent nodes, and the combination of the LEVEL pseudo-column and LPAD to
provide a visual aid in the report. In the following example, the management team wants a report that
displays the management structure of the company, clearly showing subordinates within each
higher-level manager:
 
select employee_id, level,
lpad(' ',(level-1)*3) || last_name || ', ' || first_name full_name
from employees
start with manager_id is null
connect by manager_id = prior employee_id
;


EMPLOYEE_ID LEVEL FULL_NAME
---------------------- ------------------- -----------------------------
100 1 King, Steven
101 2 Kochhar, Neena
108 3 Greenberg, Nancy
109 4 Faviet, Daniel
110 4 Chen, John
111 4 Sciarra, Ismael
112 4 Urman, Jose Manuel
113 4 Popp, Luis
200 3 Whalen, Jennifer
203 3 Mavris, Susan
204 3 Baer, Hermann
205 3 Higgins, Shelley
206 4 Gietz, William
102 2 De Haan, Lex
103 3 Hunold, Alexander
104 4 Ernst, Bruce
105 4 Austin, David
106 4 Pataballa, Valli
107 4 Lorentz, Diana
...
201 2 Hartstein, Michael
202 3 Fay, Pat
107 rows selected

The query indents the results proportionally to the employee’s position in the company
management structure.

Performing Case-Insensitive Queries in Oracle

Problem
You have loaded a significant amount of data that is in mixed case. You want to query as if it were all
lowercase (or all uppercase).
Solution 
If you are using Oracle Database 10g or higher, you can use the ALTER SESSION statement to enable
case-insensitive searching with the >, <, and = comparison operators. To enable case-insensitive
searching, alter the NLS_SORT and NLS_COMP parameters:

alter session set nls_sort=binary_ci;
alter session set nls_comp=linguistic;


These settings allow case-insensitive searches. You can run the following query to select all values of
John, regardless of case:

select * from parties where first_name = 'john';

FIRST_NAME
------------------------------
John
JOHN
john

Assigning Ranking Values to Rows in a Query Result in SQL (Oracle)

Problem
The results from a query need to be allocated an ordinal number representing their positions in the
result. You do not want to have to insert and track these numbers in the source data.
Solution
Oracle provides the RANK analytic function to generate a ranking number for rows in a result set. RANK is
applied as a normal OLAP-style function to a column or derived expression. For the purposes of this
recipe, we’ll assume that the business would like to rank employees by salary, from highest-paid down.
The following SELECT statement uses the rank function to assign these values.

select employee_id, salary, rank() over (order by salary desc) as Salary_Rank
from hr.employees;


Our query produces results from the highest earner at 24000 per month, right down to the employee
in 107th place earning 2100 per month, as these abridged results show.

EMPLOYEE_ID SALARY SALARY_RANK
----------- ---------- -----------
100 24000 1
101 17000 2
102 17000 2
145 14000 4
146 13500 5
201 13000 6
205 12000 7
108 12000 7
147 12000 7

132 2100 107
107 rows selected.

Calculating Totals and Subtotals in Oracle SQL

Problem
You need to calculate totals and subtotals in a variety of environments, using the lowest common
denominator of SQL. For instance, you need to count the number of people in each department, as well
as a grand total, in a way that can run across a variety of editions of Oracle without change.
Solution
In situations where you feel you can’t use analytic functions like ROLLUP and CUBE, or are restricted by
licensing or other factors, you can use traditional aggregation and grouping techniques in separate SQL
statements, and combine the results with a UNION to fold all the logic into a single statement. This SELECT
combines counts of employees by department in one query, with the count of all employees in
another query.
 

select nvl(to_char(department_id),'-') as "DEPT.", count(*) as "EMP_COUNT"
from hr.employees
group by department_id
union
select 'All Depts.', count(*)
from hr.employees;


The recipe results appear as follows, with abridged output to save space.

DEPT. EMP_COUNT
----------- ----------
- 1
10 1
100 6
110 2

90 3
All Depts. 107
13 rows selected.

DISPLAY_ITEM built-in in Oracle D2k Forms

Description
Maintained for backward compatibility only. For new applications, you should use the
SET_ITEM_INSTANCE_PROPERTY built-in. DISPLAY_ITEM modifies an item’s appearance by
assigning a specified display attribute to the item. DISPLAY_ITEM has the side-effect of also changing
the appearance of any items that mirror the changed instance. SET_ITEM_INSTANCE_PROPERTY
does not change mirror items.
You can reference any item in the current form.
Any change made by a DISPLAY_ITEM built-in is effective until:
·  the same item instance is referenced by another DISPLAY_ITEM built-in, or
·  the same item instance is referenced by the SET_ITEM_INSTANCE_PROPERTY built-in (with
VISUAL_ATTRIBUTE property), or
·  the instance of the item is removed (e.g., through a CLEAR_RECORD or a query), or
·  you modify a record (whose status is NEW), navigate out of the record, then re-enter the record, or
·  the current form is exited
Syntax
PROCEDURE DISPLAY_ITEM
(item_id ITEM,
attribute VARCHAR2);
PROCEDURE DISPLAY_ITEM
(item_name VARCHAR2,
attribute VARCHAR2);
Built-in Type unrestricted procedure
Enter Query Mode yes
Parameters
item_id Specifies the unique ID that Form Builder assigns to the item when it
creates the item. The data type of the ID is ITEM.
item_name Specifies the VARCHAR2 string you gave to the item when you created it.
attribute Specifies a named visual attribute that should exist. You can also specify a
valid attribute from your Oracle*Terminal resource file. Form Builder will
search for named visual attribute first. Note: You can specify Normal as
a method for applying the default attributes to an item, but only if your
form does not contain a visual attribute or logical (character mode or
otherwise) called Normal. You can also specify NULL as a method for
returning an item to its initial visual attributes (default, custom, or named).
DISPLAY_ITEM examples
/*
** Built-in: DISPLAY_ITEM

** Example: Change the visual attribute of each item in the
** current record.
*/
DECLARE
cur_itm VARCHAR2(80);
cur_block VARCHAR2(80) := :System.Cursor_Block;
BEGIN
cur_itm := Get_Block_Property( cur_block, FIRST_ITEM );
WHILE ( cur_itm IS NOT NULL ) LOOP
cur_itm := cur_block||’.’||cur_itm;
Display_Item( cur_itm, ’My_Favorite_Named_Attribute’);
cur_itm := Get_Item_Property( cur_itm, NEXTITEM );
END LOOP;
END;

CHECKBOX_CHECKED built-in in Oracle D2k Forms

Description
A call to the CHECKBOX_CHECKED function returns a BOOLEAN value indicating the state of the
given check box. If the item is not a check box, Form Builder returns the following error:
FRM-41038: Item <item_name> is not a check box.
Syntax
FUNCTION CHECKBOX_CHECKED
(item_id ITEM);
FUNCTION CHECKBOX_CHECKED
(item_name VARCHAR2);
Built-in Type unrestricted function
Returns BOOLEAN
Enter Query Mode yes
A call to GET_ITEM_PROPERTY(item_name, ITEM_TYPE) can be used to verify the item type
before calling CHECKBOX_CHECKED.
To set the value of a check box programmatically, assign a valid value to the check box using standard
bind variable syntax.
Parameters
item_id Specifies the unique ID that Form Builder assigns to the item when it
creates it. The data type of the ID is ITEM.
item_name Specifies the string you defined as the name of the item at design time. The
data type of the name is VARCHAR2.
CHECKBOX_CHECKED restrictions
The CHECKBOX_CHECKED built-in returns a BOOLEAN value regarding the state of the given check
box. It does not return the actual value of the check box nor does it return the value you might have
indicated for the Mapping of Other Values property.
CHECKBOX_CHECKED examples
/*
** Built-in: CHECKBOX_CHECKED
** Example: Sets the query case-sensitivity of the item
** whose name is passed as an argument, depending
** on an indicator checkbox item.
*/
PROCEDURE Set_Case_Sensitivity( it_name VARCHAR2) IS
indicator_name VARCHAR2(80) := ’control.case_indicator’;
it_id Item;
BEGIN
it_id := Find_Item(it_name);
IF Checkbox_Checked(indicator_name) THEN
/*
** Set the item whose name was passed in to query case-
** sensitively (i.e., Case Insensitive is False)
*/
Set_Item_Property(it_id, CASE_INSENSITIVE_QUERY,
PROPERTY_FALSE );
ELSE
/*
** Set the item whose name was passed in to query case-
** insensitively (ie Case Insensitive True)
*/
Set_Item_Property(it_id,CASE_INSENSITIVE_QUERY,PROPERTY_TRUE);
END IF;
END;

SYSTEM.TAB_PREVIOUS_PAGE system variable in Oracle D2k Forms

Syntax
SYSTEM.TAB_PREVIOUS_PAGE
Description
The system variable SYSTEM.TAB_PREVIOUS_PAGE specifies the name of the tab page from which
navigation occurred. Use it inside a When-Tab-Page-Changed trigger.
SYSTEM.TAB_PREVIOUS_PAGE examples
/* Use system variable SYSTEM.TAB_PREVIOUS_PAGE inside a
** When-Tab-Page-Changed trigger to change the label of the
** tab page to initial-cap after an end user navigates out
** of the tab page:
*/
Example

DECLARE
tp_nm VARCHAR2(30);
tp_id TAB_PAGE;
tp_lbl VARCHAR2(30);
BEGIN
tp_nm := :SYSTEM.TAB_PREVIOUS_PAGE;
tp_id := FIND_TAB_PAGE(tp_nm);
tp_lbl := GET_TAB_PAGE_PROPERTY(tp_id, label);
IF tp_nm LIKE ’ORD%’ THEN
SET_TAB_PAGE_PROPERTY(tp_id, label, ’Orders’);
END IF;
END;


Restoring and Recovering Your Database in Oracle with RMAN

Problem
You’ve experienced a failure and want to use RMAN to restore and recover your database. You
have a current and good backup in the default location, and all needed control files, archived
redo log files, and online redo log files are available.
Solution
Connect to RMAN, and use the following commands to restore and recover your database. In
this recipe you’ll perform the following steps:
1. Connect to the target database.
2. Mount the database.
3. Restore the database.
4. Recover the database.
5. Open the database.

To keep this example as simple as possible, we’ll show how to restore and recover the
entire database.

RMAN> connect target /
RMAN> startup mount;
RMAN> restore database;

You’ll see several lines of output as RMAN tells you what it is restoring. It should look
something like the following:
Starting restore at 19-OCT-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORA01\BRDSTN\SYSTEM01.DBF
restoring datafile 00002 to C:\ORA01\BRDSTN\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORA01\BRDSTN\SYSAUX01.DBF
restoring datafile 00004 to C:\ORA01\BRDSTN\USERS01.DBF
Next recover your database as follows:

RMAN> recover database;
You should see a message similar to this:
Starting recover at 19-OCT-06
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:07
Finished recover at 19-OCT-06
You can now open your database for use with the alter database open command:

RMAN> alter database open;
database opened

Writing Regular RMAN Backups to the Flash Recovery Area in Oracle 11g

Problem
Now that you have configured a flash recovery area, you want RMAN to use it when creating
disk-based backups.
Solution
You can easily make RMAN store backups in the flash recovery area. Here are the steps to follow:
1. Start RMAN:
$ rman
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Sep 29 01:20:19 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN>

2. Connect to the target database:
RMAN> connect target /
connected to target database: PRODB2 (DBID=524826567)

3. Now, initiate a backup without specifying a format option:

RMAN> backup database;
Starting backup at 09-OCT-06
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=134 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/home/oracle/oradata/PRODB2/SYSTEM.dbf
input datafile fno=00003 name=/home/oracle/oradata/PRODB2/SYSAUX.dbf
input datafile fno=00005 name=/home/oracle/oradata/PRODB2/EXAMPLE.dbf
input datafile fno=00002 name=/home/oracle/oradata/PRODB2/UNDOTBS1.dbf
input datafile fno=00004 name=/home/oracle/oradata/PRODB2/USERS.dbf
channel ORA_DISK_1: starting piece 1 at 09-OCT-06
channel ORA_DISK_1: finished piece 1 at 09-OCT-06
piece handle=/home/oracle/flasharea/PRODB2/backupset/2006_10_09/➥
o1_mf_nnndf_TAG20061009T200113_2lorpcgq_.bkp tag=TAG➥
20061009T200113 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:57
Finished backup at 09-OCT-06
Starting Control File Autobackup at 09-OCT-06
piece handle=/home/oracle/flasharea/PRODB2/autobackup/2006_10_09/➥
o1_mf_n_603403392_2lort11n_.bkp comment=NONE
Finished Control File Autobackup at 09-OCT-06
RMAN>

Note the command in step 3 carefully; you issued just the backup database command.
You specified nothing else—no channel creation, no format, nothing. Since you have defined
a flash recovery area, the backups go in there by default. Of course, you can issue a format
command and use channels to redirect the backup to a different location, but the flash recovery
area provides greater control if you choose to place the backups there.

Creating the Flash Recovery Area in Oracle 11g

Problem
You want to create the flash recovery area for your database.
Solution
Before creating the flash recovery area, you should decide the following:
• Where you want the FRA to be created
• How much space should be allocated to the FRA
Having the answers to these questions in mind, you can then use the following process to
create the flash recovery area:
1. Disable the parameters log_archive_dest and log_archive_duplex_dest, if they are set
in the database. You can do that by issuing the following commands:

alter system set log_archive_duplex_dest = '';
alter system set log_archive_dest = '';

2. Log on as a user with the sysdba role (such as the user sys) in preparation to create the
flash recovery area:

sqlplus / as sysdba (if logged in as the Oracle software owner)
sqlplus sys/<PasswordOfUserSys> as sysdba

3. Issue the following commands to size and create the flash recovery area:

alter system set db_recovery_size = 4G;
alter system set db_recovery_dest = '/home/oracle/flasharea';

The sequence of these commands is important; you have to issue them in that order,
not the reverse. However, do replace the size and path name with the values you have
chosen for your system.
That’s it; the flash recovery area is ready for operation.

V. Kapoor

{picture#https://3.bp.blogspot.com/-q3wCssWUHo8/ViDFfCpPEuI/AAAAAAAAD0M/MHPvgnl3T3U/s1600/authpic.JPG} Vinish Kapoor is a Software Consultant, currently working in a reputed IT company in Noida, India and doing blogging on Oracle Forms, SQL and PLSQL topics. He is an author of an eBook Oracle Forms Recipes, which is available on Google play. {facebook#https://www.facebook.com/foxinfotech2014} {twitter#https://twitter.com/foxinfotech} {google#https://plus.google.com/u/0/+VKapoor/posts}
Fox Infotech. Powered by Blogger.