COde for Report calling from Oracle d2k

declare
p paramlist;
appid pls_integer;
curtimer timer;
begin
p := create_parameter_list('abc');
add_parameter(p,'dno',text_parameter,:block2.deptno);
add_parameter(p,'DESTYPE',TEXT_PARAMETER,'file');
ADD_PARAMETER(P,'DESNAME',TEXT_PARAMETER,'c:\'||:block2.deptno||'.PDF');
ADD_PARAMETER(P,'DESFORMAT',TEXT_PARAMETER,'pdf');
ADD_PARAMETER(P,'PARAMFORM',TEXT_PARAMETER,'NO');

run_product(REPORTS,'c:\6i\emp.rdf',asynchronous,runtime,filesystem,p);
-- host('cmd /C START C:\'||:block2.deptno||'.pdf',NO_SCREEN );
host('cmd /C START iexplore C:\'||:block2.deptno||'.pdf',NO_SCREEN);


/*host('C:\Program Files\Internet Explorer\iexplore.exe');
appid := dde.app_begin(' C:\ankur2.pdf',DDE.APP_MODE_MINIMIZED); */


destroy_parameter_list(p);


end;

Diffrence between two date, Pl-sql program result in Years - Month-format

Helo frnds

I am uploading this code.
work in pl-sql
DECLARE
DATE1 DATE;
DATE2 DATE;
V_NO NUMBER;
V_YEAR NUMBER;
V_MONTH NUMBER;
BEGIN
DATE1 := TO_DATE('&DATE1');
DATE2 := TO_DATE('&DATE2');
V_NO := DATE1 - DATE2;
IF (v_NO<0) THEN v_NO := V_NO * (-1);
END IF;
V_YEAR := TRUNC(V_NO/365);
V_NO := MOD(V_NO , 365);
V_MONTH := TRUNC(v_NO/30);
V_NO := MOD(V_NO,30);
DBMS_OUTPUT.PUT_LINE(V_YEAR ||' YEARS ' || V_MONTH ||' MONTH ' || V_NO || ' DAYS');
END;
/

Alert in Oracle D2k

Alert :-

1. This is used to display message to the user to confirm any action.
2. use show_alert to show the alert at run time.
3. the above function returns the following values (number()
88 alert_button1
89 alert_button2
90 alert_button3
4. u can change the following attribute of an alert at run time
(title,message,label of button)

Steps for creating a alert.
1. create an alert in object navigator through + command
2. make changed in the property of alert as per requirement.
3. now show the alert at run time using show_alert.


Code for ‘Key-delrec’ trigger at form level ( name of alert is alert_confirm)

Declare
Alt_ret_val numbers;
Begin
Set_alert_property ( ‘alert_confirm’, TITLE,’Confirming Delete Action’);
Set_alert_property ( ‘alert_confirm’,Alert_message_text,’Sure to delete this record ?? ‘);
Set_alert_button_property(‘alert_confirm’,alert_button,label,’plz delete’);
Alert_ret_val := show_alert(‘alert_confirm’);
If alert_ret_val := alert_button1 then delete_record;
Else
Message(‘you cance the operation’);
Message(‘’);
End if;
End;

Note :-
Max you can have 3 button and min you can have 1 button in an alert.
You can not change no of button at run time.

Code for delte record button is

Do_key(‘delete_record’);

It will make a call to key dec-rec

Trigger in D2kTrigger in D2kTrigger in D2k

Trigger in D2k

1. Trigger in form builder are available at three level ie item level, Block level and form level.
2. The area of application of trigger is dependent on the level where it has been defined.
3. If the same trigger has been defined at more than one level than the execution of trigger depends on the value of the property of trigger i.e. ‘ execution hierarchy’.
Override
1. It will override the entire higher level trigger having same name.
before
1. Lower level will be executed before its immediate parent level
After
1. lower level trigger will be executed after its immediate parent.

The lower level trigger will be always available but reverse is not true.

you can help for more these two pic following link

Trigger in form builder is divided into five parts.

1. Pre
2. post
3. when
4. on
5. key

Pre – Trigger. This trigger is executed before the current event.
Some of the pre trigger event are
1. pre-commit
2. pre-form
3. pre-logon
4. pre-text-item
5. pre-block

Pre-Query:-
1. This trigger is executed before any query is send to the database for execution.
2. it is used to validate the user input before sending the user-input to db to remove unnecessary presence on DB.

Code for Pre Query.


If :emp.deptno not in (10,20,30) then message (:emp.deptno ’ does not exict’);
Message(‘’);
Raise form_trigger_failure;
Length(:emp.empno) <4>

Post – trigger :->
1. These trigger are executed after the corresponding event.
2. some of the post trigger
• post-logon
• post-block
• post-query
• post-insert/update/delete
• post-text-item

Post-query:- this trigger is executed after the execution of query in the block
3. pre-query is executed only once and post-query is executed that many no. of times records returned by the query submitted to DB.


Code for Post –Query Emp block

Declare
V_groeg_sal number;
V_grade char(1);
Begin
V_groeg_sal := :emp.T_gross_sal;
If v_gross_sal >= 50000 and v_gross_sal <= 7000 then v_grade := ‘a’; Else v_gross_sal >= 3000 and v_gross_sal <>
1. this is executed one the cursor/control goes from the item.
2. use this trigger to populate any value like grade on the basis of gross sal(sal + comm.)


When trigger ->

1. These trigger get executed when the corresponding event happens.
2. some of the when –trigger
when –button-pressed
when-validate-item
when-validate-record
when-new-item-instance
when-new-record-instance
when-new-block-instance
when-create-record


when-validate-item :-

it is executed when the control comes out of any item after making changes in th e value that item.


When-validate-record :- executed when the control goes out of the current record after making changes in the record.

When –create-record

This trigger is executed when a new record is created in the block.
This is the only trigger in form builder when the forms does not ask you to save changes if we have allocated some value to my field of the block.

Code for when –create-record at emp block

:emp.sal := 200;
:emp.comm := 100;

Code for when-validate-item of Sal field
If :emp.sal<2000> 2000’);
Raise form_trigger_failure;
End if;

Code at blk level
When-validate-record of emp block

If :emp.sal<:emp.comm Then Message(‘plz enter sal>comm’);
Message(‘’);
Raise form_trigger_failure;
Else if :emp.name is null or length(:emp.ename)<4> these trigger executed during corresponding event.

Some of the trigger are.

On-error
On-message
On-logon
On-insertupdatedelete

On-error:-> in this trigger is executed when any errors happens at run time which is a system defined error.
Some of the keyword are

Error-code
Error-type
Error-text

On-message

This is executed when any message comes at run time from form builder.

Keywords

Message-code
Message-type
Message-text
Code for on-error at form level

If error_code = 50016
Then message(‘plz enter only number’);
Message(‘’);
Else
Message(Error_code - ‘ error_type ’ ‘ error_text);
Message(‘’)


Key trigger :-> these trigger are executed whenever a corr. Runtime key is used

Key Trigger Bultin
F7 Key-enter Enter_query
F8 Key-executed Execute_query
F9 Key-listval List-values
F10 Key-commit Commit
F6 Key-crerec Create_record
Shift+f6 Key-delrec Delete_record

1. we can use key trigger to override the default functionality of run time keys.
2. we can use do_key (‘buil-in’) to call the corr/key trigger.
3. for example do_key(‘commit’) will made a call to key-commit trigger.
4. we can also use execute-trigger (‘any trigger name’) to execute the code of any trigger

execute_trigger(‘key-nxtrec’);

Code :-> for key-nexrec

If :system.last_record = ‘true’ then
Message (‘ you are already at last record’);
Message(‘’);
Raise form_trigger_failure;
Else
Next_record;
End if;

Code for next_record button

Button
D0-key(‘next_record’);
Execute_trigger(‘key-nxtrec’);



Code for key-up

If :system.cursor_record = ‘1’
Then message(‘ you are already at first record’);
Message(‘’);
Raise form_trigger_failure;
Else
Previous_record;
End if;