Calling new form from the current oracle form.

Calling another form by pressing button in main form:-

Create a button in the current form.On its when-button-pressed trigger write the below code to call another form.
One can pass parameter  also from one form to another.

declare
the_param      varchar2(10);
   pl_id          paramlist;
   the_code       varchar2(10);
 

begin

  :System.Message_Level := 25;
  POST;  
  :System.Message_Level :=0;
  The_Param:='pram1';
  pl_id := Get_Parameter_List(The_Param);
  if not Id_Null(pl_id) then
     Destroy_Parameter_List( pl_id );
  end if;
  pl_id := create_parameter_list(the_param);


      add_parameter(pl_id,'P_QUERY',text_parameter,:PARAMETER.P_QUERY);
 
call_form('new_form_name',hide,no_replace,no_query_only,pl_id);


end;

also we can open the new form in query only mode by replacing "no_query_only" value to "query_only".

How to select last two rows of an Oracle table?

select * from (
select ROW_NUMBER()
   OVER (PARTITION BY employee_name ORDER BY emp_code desc) rnum,emp_code from your_table where employee_name='Test'
)
where rnum<=2

How to initialize a date field to system date in oracle forms?

do you want initialize a date in text item?

if so,

Set initial value $$dbdate$$

or 

Set initial value to $$date$$

Convert Arabic Hijri date to Gregorian date using nls_calendar!!

--p_hijri_date is varchar2 field
select TO_CHAR(TO_DATE(p_hijri_date,
                          'dd/mm/yyyy',
                          'nls_calendar=''English Hijrah'''),
                  'dd-mm-yyyy',
                  'nls_calendar=''Gregorian''')
from dual;


select TO_CHAR(TO_DATE('06/02/1433',
                          'dd/mm/yyyy',
                          'nls_calendar=''English Hijrah'''),
                  'dd-mm-yyyy',
                  'nls_calendar=''Gregorian''')
from dual;

output- 01-01-2012

How To Create a Text File report from oracle Forms/Reports

Creating a text file and save it on server through Oracle forms:-


declare

 file_path varchar2(2000);
 V_file_str varchar2(2000);
 cursor rec1 is
 SELECT  emp_name,emp_code
  FROM employee_master;
 first_flag char :='F';
BEGIN
file_path:='D:\ORACLE-PROJECTS\files1\'||to_char(sysdate,'dd_mm_yyyy_hh-mi-ss')||'.TXT';

v_file_str:='LIST OF EMPLOYEES'||TO_CHAR(SYSDATE,'DDMMYY')||CHR(10);
SaveTextToFile(v_file_str, file_path, 'w');
             for rec in REC1          
             loop
                           v_file_str:='01'||REC.EMP_CODE||REC.EMP_NAME;
              SaveTextToFile(v_file_str, file_path, 'a');
             end loop;
ENd;

Note:-where savetexttofile is a procedure:-

PROCEDURE SaveTextToFile(p_Text in varchar2, p_FilePath in varchar2, p_mode in varchar2) IS
 v_TextFile text_io.file_type;
 e_FileOpenError exception;
BEGIN
 begin
  v_TextFile := text_io.fopen(p_FilePath, p_mode);
 exception
  when others then
    LAST_ERROR := 'Cannot open file';
   raise e_FileOpenError;
 end;

 text_io.put(v_TextFile, p_Text);

 text_io.fclose(v_TextFile); 
END;
---------------------------


creating a text file through report:-

define a key in cgicmd file

textreport: userid=system/sys@test destype=file desname='c:\test_report.txt' desformat=delimited

create a report and give the desired formatting,

In the form use the following syntax while calling the report:-

rep_url:=/*server path*/||'/reports/rwservlet?textreport&module='|report_name;
             
  WEB.SHOW_DOCUMENT(rep_url,'_blank');

Then the text file will be save in the c: directory.

Procedure To Run Report From Oracle 10g Forms:-

The following procedure shows how to print report from oracle form.

Web.Show_Document() Built-in to call Oracle Reports on the Web is an alternative to the Run_Report_Object() Built-in.

Web.Show_Document(URL, Target);

1. url – The URL is passed as a string in a variable, or as a combination of both. If the target Web page is located on the same server that runs Forms Services,relative addressing could be used.

2.target  – Definition of the target where the addressed Web page should be displayed. Values must be single-quoted. Possible target values are ‘_blank’ to show the Reports output in an extra browser window, ‘_self’ to replace the Forms

application with the Reports output, ‘<frame name>’ to load the Reports output into a named frame of the multi frame HTML page.

A complete syntax example to run Reports from a browser looks like this:-

http://<server>:<port>/reports/rwservlet? keyname&report=<report>.rdf&desformat=[htmlcss|pdf|xml|delimited|]&destype=cache&paramform=[no|yes]

Report – the name of the Reports module to execute

desformat – the output format of the returned Reports result set. Desformat can be

htmlcss, html, pdf, xml, rtf and delimited. For Reports run from Forms pdf and

htmlcss are the most commonly used options

destype – determines where the Reports output gets written to. “Cache” specifies

that the Reports output gets streamed to the requesting browser. ‘

paramform – determines if Reports should display a HTML parameter form before

executing the request. The parameter form can be used for the user to further filter

the expected Reports result set. Valid values are ‘yes’ and ‘no’.

To reduce the length of the Reports request URL, you can create a key entry in the

Reports cgicmd.dat configuration file to store command line parameters that don’t

change from one Report to the other. In this case the first argument in a Reports

Web request, right after the question mark, must be the key name2.

Keyname refers to a command line listed under a unique header (the key name) in the cgicmd.dat file. Note that this works differently for JSP files, which use the keyword/value pair cmdkey=value to specify key names for command lines that are stored in the cgicmd.dat file.

DECLARE

REP_NAME varchar2 (2000); 

rep_url varchar2 (4000);

BEGIN

                                                               

                                REP_NAME:='d:\ PROJECTS\ code\'||'MYFIRSTREPORT.rdf';                                       

                                rep_url:='http://localhost:8890'||'/reports/rwservlet?employee&module='||REP_NAME                          

                                ||'&p_emp_code='||:BLOCK_NAME.EMP_CODE;

                                                                                                                                                                                                                               

                                WEB.SHOW_DOCUMENT(rep_url,'_blank');

END;

How a deadlock situation occur in Oracle database?

Create a procedure and call a sub procedure within it which is a pragma Autonomous_transaction. Don't Commit or rollback the autonomous procedure.This will creat a deadlock situation.

The reason for this is if you declare any procedure as pragma autonomous_transaction then whatever changes are committed are within the scope of this procedure. If you don't commit the insert statement within this procedure it creats lock.