Friday 26 September 2014

we can easily transfer data from one block to another block in oracle forms using checkbox with image.


check the code below

>> transfer data from one block to another block only selected data
begin

go_block('fromcustomer_block');
 first_record;

  loop
    go_block('tocustomer_block');
    if :fromcustomer_block.ck_from = 'Y' then
     :tocustomer_block.to_cust_name := :fromcustomer_block.from_cust_name;
:tocustomer_block.to_cust_code := :fromcustomer_block.from_cust_code;
 :tocustomer_block.ck_to :='Y';
 end if;
     next_record;

     go_block('fromcustomer_block');
      exit when :system.last_record = 'TRUE';
      next_record;    
  end loop;
  first_record;
  go_block('tocustomer_block');
  first_record;
  
  end;

<< removing data from another block only selected data

begin
    go_block('tocustomer_block');
            first_record;
LOOP
if :tocustomer_block.ck_to = 'Y'   then
clear_record;
end if;
EXIT WHEN :SYSTEM.LAST_recORD='TRUE';
Next_Record;
END LOOP;

  end;

Update Code:

begin
if :SALESEXECUTIVE_BLOCK.to_se_code is null then 
     libpkg_general.msgbox('please select the sales exicutive to update', 'Sales');
     return;
        end if;
go_block('tocustomer_block');
if :tocustomer_block.to_cust_code is null then 
libpkg_general.msgbox('Please Tranfer the Customer first', 'Sales');
return;
end if;
if :SALESEXECUTIVE_BLOCK.ck_toselect = 'Y' and :tocustomer_block.to_cust_code is not            null  and   :tocustomer_block.ck_to = 'Y' then
first_record;
loop
if :tocustomer_block.ck_to = 'Y' then
update customer set sales_person2 = :SALESEXECUTIVE_BLOCK.to_se_code where 
code = :tocustomer_block.to_cust_code;
EXIT WHEN :SYSTEM.LAST_recORD='TRUE';
end if;
Next_Record;
END LOOP;
else
libpkg_general.msgbox('Atleast select one ccustomer', 'Sales');
return;
end if;
commit;
              libpkg_general.msgbox('Updaed Successfully', 'Sales');
end;


Just place a text in a report and write this code in pl/sql editor and you can call another report in a main report (calling one report to another report)

function U_sub1FormatTrigger return boolean is
begin
    SRW.SET_HYPERLINK('http://000.000.00.00:0000/reports/rwservlet?LWF+report=LWFEmpCasSubscription+DESTYPE=CACHE+DESFORMAT=pdf+SERVER=repserv'||
    '+P_COMPANY_CODE='||''''||:P_COMPANY_CODE||''''||
    '+P_MEMCOMPANY='||''''||:membercompanycode||''''||
    '+P_CATEGORY='||''''||:cf_category||''''||
  '+P_EMP='||''''||:cf_pemp||''''||
  '+P_TODT='||''''||:P_TODT||'''');

end;

Friday 19 September 2014

You can fill the data dynamically in detail block in oracle forms just try this code hope it will helpfull for you

PROCEDURE fromcustomer IS
  cursor c is
  select code,name from customer where 
  sales_person2 = :SALESEXECUTIVE_BLOCK.from_se_code;
begin
go_block('fromcustomer_block');
clear_block(no_validate);
for rec in c loop

:fromcustomer_block.from_cust_code := rec.code;
:fromcustomer_block.from_cust_name := rec.name;
:fromcustomer_block.ck_from := 'Y';
 
  NEXT_RECORD;
end loop;
first_record;

END;

Thursday 18 September 2014

One of the most common interview questions that is being asked is explain Equi Join, Natural Join and Cross Join in sql. I will break the question in different meaningful easy to understand concepts

Inner Join

This is the most used join in the SQL. this join returns only those records/rows that match/exists in both the database tables.

Inner Join Example

  1. SELECT * FROM tblEmp JOIN tblDept
  2. ON tblEmp.DeptID = tblDept.DeptID;
Inner Join Result
tblEmp.Name
tblEmp.DeptID
tblDept.Name
tblDept.DeptID
Ram
1
HR
1
Raju
2
IT
2
Soya
2
IT
2
Sam
3
ADMIN
3
In the join condition, you can also use other operators like <,>,<>.

Equi Join

Equi join is a special type of join in which we use only equality operator. Hence, when you make a query for join using equality operator then that join query comes under Equi join.

Equi Join Example

  1. SELECT * FROM tblEmp JOIN tblDept
  2. ON tblEmp.DeptID = tblDept.DeptID;
  3. --Using Clause is not supported by SQL Server
  4. --Oracle and MySQL Query
  5. SELECT * FROM tblEmp INNER JOIN tblDept USING(DeptID)
Equi Join Result
tblEmp.Name
tblEmp.DeptID
tblDept.Name
tblDept.DeptID
Ram
1
HR
1
Raju
2
IT
2
Soya
2
IT
2
Sam
3
ADMIN
3

Note

  1. Inner join can have equality (=) and other operators (like <,>,<>) in the join condition.
  2. Equi join only have equality (=) operator in the join condition.
  3. Equi join can be an Inner join, Left Outer join, Right Outer join
  4. The USING clause is not supported by SQL Server and Sybase. This clause is supported by Oracle and MySQL.

Natural Join

Natural join is a type of equi join which occurs implicitly by comparing all the same names columns in both tables. The join result have only one column for each pair of equally named columns.

Natural Join Example

  1. --Run in Oracle and MySQL
  2. SELECT * FROM tblEmp NATURAL JOIN tblDept
Natural Join Result
DeptID
tblEmp.Name
tblDept.Name
1
Ram
HR
2
Raju
IT
2
Soya
IT
3
Sam
ADMIN
In the above join result we have only one column "DeptID" for each pair of equally named columns.

Note

  1. In Natural join, you can't see what columns from both the tables will be used in the join. In Natural join, you might not get the desired result what you are expecting.
  2. Natural join clause is not supported by SQL Server, it is supported by Oracle and MySQL.

Wednesday 10 September 2014

Check this it will help full for you

How To Remove the 'Do you want to save changes?



A.) This note documents how to change the 'Do you want to save the changes you
have made?' alert that is displayed when exiting an application when there is
uncommited changes on the form.

By default, when exiting a form, Forms checks the status of the form, and
if there are uncommited changes, will display the alert asking if the user
wants to commit the data. Then, depending on the choice the user makes, forms
will commit or rollback the changes and exit the form, or do nothing if cancel
is chosen.

In order to customise this behaviour it is necessary to perform this
functionality yourself. The following steps are necessary to implement the
customised box:-

1: When exiting form, Check the form status.

2: If the status is changed, then display the customised alert.

3. Depending upon the user input either:-
a: commit the changes and exit the form.
b: Exit the form without saving the changes.
c: Do nothing if the user has canceled exiting the form.

The Code
--------

Create an alert ASK-COMMIT with the following attributes:-

Message: 'There are unsaved changes. Save them?'
button1: 'YES'
button2: 'CANCEL'
button3: 'NO'
Alert Style: 'CAUTION'

Then, in a KEY-EXIT trigger at form level, enter the following code:-
---------------------------------------------------------------------------

declare
button_val number;
begin
-- Perform validation at item level to update :SYSTEM.FORM_STATUS.
validate(ITEM_SCOPE);
-- Check the record status
if(:SYSTEM.FORM_STATUS = 'CHANGED') then
button_val := show_alert('ask_commit');
if button_val = ALERT_BUTTON1 then
-- If user chooses to commit changes...
commit_form;
exit_form;
elsif button_val = ALERT_BUTTON2 then
-- Do nothing if user cancels...
null;
elsif button_val = ALERT_BUTTON3 then
-- Exit without committing...
Exit_form(No_commit);
end if;
else
-- There have been no changes, just exit...
exit_form;
end if;
end;

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

Notes
-----

The validate(ITEM_SCOPE) line at the start is a useful bit of code that
updates the :SYSTEM.FORM_STATUS, :SYSTEM.BLOCK_STATUS or :SYSTEM.RECORD_STATUS.

In the example code above, if a user has entered some data and then tried to
exit without navigating out of the field, The form status will still be
'QUERY'. The validate built-in ensures the form status will be 'CHANGED'.

This code is generic and can be used in other places such as before issuing a
CALL_FORM in a WHEN-BUTTON-PRESSED trigger in order to get a user to
commit/rollback changes before navigating to a new form.
----------------------------------------------------------------------------


B.) HOW TO SUPPRESS "DO YOU WANT TO SAVE CHANGES" PROMPT IN MASTER-DETAIL?


Problem Description:
====================

A form is run that has a master and detail block. The user may navigate to
the detail block and make a change and then navigate back to the master block
and try to go to the next master record.

Oracle Forms 4.x or higher recognizes that the detail block has been changed and prompts
the user:

Do you want to save the changes you have made?

SQL*Forms 3.0 recognizes that the detail block has been changed and prompts
the user:

Do you want to commit the changes you have made?


Problem Explanation:
====================

How can you suppress this message/prompt? The user wants to automatically go
down to the next master record regardless of any changes made in the detail
block.



Solution Description:
=====================

Oracle Forms 4.x or higher:
---------------------------

Modify the CLEAR_ALL_MASTER_DETAILS procedure body. Change the existing
CLEAR_BLOCK(ASK_COMMIT) to CLEAR_BLOCK(DO_COMMIT).

SQL*Forms 3.0:
--------------
Modify the procedure CLEAR_DETAILS. Change the existing CLEAR_BLOCK(OPT) to
CLEAR_BLOCK(DO_COMMIT).

**Note: This is the quicker way to change the code. Trigger KEY-DELREC calls
clear_masterblockname_details(FALSE, NO_COMMIT) which in turn executes
clear_details('detailblockname', 'masterblockname', opt). If you modify the
clear_details procedure, it will always do clear_block(do_commit) since it is
hardcoded. The better way to change the functionality would be to change all
the trigger text to do clear_masterblockname_details(TRUE, NO_COMMIT) instead
of (TRUE, ASK_COMMIT) in triggers key-clrblk, key-clrrec, key-crerec,
key-down, key-entqry, key-exeqry, key-nxtrec, key-nxtset, key-prevrec,
key-scrdown, key-scrup and key-up. If you modify the procedure clear_details
to do clear_block(no_commit) then you just need to change the code in the
procedure.

Solution Explanation:
=====================

When you create a master-detail relationship form, certain triggers and
procedures are automatically created for you. To change the default
functionality, you need to change the clear_block statement.

The default parameter for CLEAR_BLOCK is ASK_COMMIT.

The options for clear_block are:

o ask_commit - Oracle Forms prompts the operator to commit the
changes during clear_block processing.
o do_commit - Oracle Forms validates the changes, performs a commit,
and flushes the current block without prompting the
operator.
o no_commit - Oracle Forms validates the changes and flushes the
current block without performing a commit or prompting
the operator.
o no_validate - Oracle Forms flushes the current block without
validating the changes, committing the changes, or
prompting the operator.


C.)HOW TO GET FROM NORMAL TO EXECUTE QUERY MODE WITHOUT BEING PROMPTED BY FORMS?

Problem Description:
====================

You are receving the following message from Oracle Forms Runtime when you try
to get into Enter Query mode or use Execute Query:

'Do you want to commit the changes you have made?'


Problem Explanation:
====================

How do I get into Query mode from normal processing mode to perform an Enter
Query or Execute Query without being prompted with this message? In normal
processing mode you can insert and update data in a block.

Solution Description:
====================

Use the Oracle Forms CLEAR_BLOCK built-in with commit mode DO_COMMIT,
NO_COMMIT or NO_VALIDATE before calling ENTER_QUERY or EXECUTE_QUERY.

For Example:
------------

You want to put the user in Enter Query mode or Execute a Query by pressing a
button. You have two buttons on your form: ENTER_QUERY_BUTTON and
EXEC_QUERY_BUTTON.

Create the following When-Button-Pressed triggers:

Item Level When-Button-Pressed trigger on ENTER_QUERY_BUTTON:
BEGIN
CLEAR_BLOCK(NO_COMMIT);
ENTER_QUERY;
END;

If you wish to save a value in one of the items in the block to include it as
query criterion for the EXECUTE_QUERY, also create a Pre-Query trigger.

Item Level When-Button-Pressed trigger on EXEC_QUERY_BUTTON:
BEGIN
/* Save the form item value in a global variable to use in */
/* Pre-Query trigger. */
:GLOBAL.item_name := :block.fieldname;

CLEAR_BLOCK(NO_COMMIT);
EXECUTE_QUERY;
END;

Block Level Pre-Query trigger:
BEGIN
:block.fieldname := :GLOBAL.item_name;
END;


Make sure that the Mouse Navigate property = FALSE for both buttons, otherwise
FRM-40202 will occur when the button is pressed.


Solution Explanation:
====================

Specifying NO_COMMIT or NO_VALIDATE with the CLEAR_BLOCK built-in causes
Oracle Forms to flush the current block without performing a commit
or prompting the operator.

Please be aware that any changes made to the data in the block will be cleared
and NOT SAVED to the database.

Tuesday 9 September 2014

Copyright © ORACLE-FORU - SQL, PL/SQL and ORACLE D2K(FORMS AND REPORTS) collections | Powered by Blogger
Design by N.Design Studio | Blogger Theme by NewBloggerThemes.com