1. Function is mainly used in the case where it must return a value. Where as a procedure may or may not return a value or may return more than one value using the OUT parameter. 2. Function can be called from SQL statements where as procedure can not be called from the sql statements
3. Functions are normally used for computations where as procedures are normally used for executing business logic.
4. You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query.
5. Function returns 1 value only. Procedure can return multiple values (max 1024).
6.Stored Procedure: supports deferred name resolution. Example while writing a stored procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is allowed only in during creation but runtime throws error Function wont support deferred name resolution.
7.Stored procedure returns always integer value by default zero. where as function return type could be scalar or table or table values
8. Stored procedure is precompiled execution plan where as functions are not. 9.A procedure may modify an object where a function can only return a value The RETURN statement immediately completes the execution of a subprogram and returns control to the caller.
How will you delete duplicating rows from a base table? delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name); ordelete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from table_name tb where ta.dv=tb.dv);
What is difference between SUBSTR and INSTR? SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4) output BCDEINSTR provides character position in which a pattern is found in a string. eg INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-') Find out nth highest salary from emp table SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal); select sal,UPPER( (to_char(to_date(sal,'j'), 'jsp')))from emp;
--------- -----------------------------------------------------800 eight hundred1600 one thousand six hundred1250 one thousand two hundred fiftyIf you want to add some text like, Rs. Three Thousand only.SQL> select sal "Salary ",(' Rs. ' (to_char(to_date(sal,'j'), 'Jsp')) ' only.'))"Sal in Words" from emp
select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);Output:-135Even number of records:select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)Output:-246
Clustering is a method of storing tables that are intimately related and often joined together into the same area on disk. For example, instead of the BOOKSHELF table being in one section of the disk and the BOOKSHELF_AUTHOR table being somewhere else, their rows could be interleaved together in a single area, called a cluster.
The SELECT ... FOR UPDATE statement identifies the rows that will be updated or deleted then locks each row in the result set. This is useful when you want to base an update on the existing values in a row. In that case you must make sure the row is not changed by another user before the update.
FOR UPDATE clause in the cursor query is used to lock the affected rows when the cursor is opened. Because the Oracle Server releases locks at the end of the transaction you should not commit across fetches from an explicit cursor if FOR UPDATE is used.
What WHERE CURRENT OF clause does in a cursor? LOOPSELECT num_credits INTO v_numcredits FROM classesWHERE dept=123 and course=101;UPDATE studentsSET current_credits=current_credits+v_numcreditsWHERE CURRENT OF X;END LOOPCOMMIT;END;
Can cursor variables be stored in PL/SQL tables.If yes how. If not why? No, a cursor variable points a row which cannot be stored in a two-dimensional PL/SQL table.
No you can not store the cursor variable into pl/sql table
but yes the out put or cursor variable can be stored how:
set serveroutput on
declaretype v_table is table of emp%rowtype index by binary_integer;my_table v_table;i number;cursor c1 is select * from emp;begini := 1;open c1;loopfetch c1 into my_table(i);exit when c1%notfound;i := i + 1;end loop;for r in 1.. my_table.countloopdbms_output.put_line(my_table(r).empno);end loop;end;
When referencing the current row from an explicit cursor we use 'WHERE CURRENT OF'. it allows us to apply updates and deletes to the row currently been addressed.
NOTE: The cursor must have been declared with the FOR UPDATE clause so that the rows are locked on OPEN.
IN : lets you pass values to the subprogram being calledOUT: lets you return values to the caller of subprogramINOUT: lets you to pass values to the subprogram and return values to the caller of the subprogram.
What are ORACLE PRECOMPILERS? Using ORACLE PRECOMPILERS, SQL statements and PL/SQL blocks can be contained inside 3GL programs written in C,C++,COBOL,PASCAL, FORTRAN,PL/1 AND ADA.The Precompilers are known as Pro*C,Pro*Cobol,...This form of PL/SQL is known as embedded pl/sql,the language in which pl/sql is embedded is known as the host language. The prcompiler translates the embedded SQL and pl/sql ststements into calls to the precompiler runtime library.The output must be compiled and linked with this library to creater an executable.
Difference between database triggers and form triggers? Data base trigger(DBT) fires when a DML operation is performed on a data base table. Form trigger(FT) Fires when user presses a key or navigates between fields on the screenCan be row level or statement level No distinction between row level and statement level.Can manipulate data stored in Oracle tables via SQL Can manipulate data in Oracle tables as well as variables in forms.Can be fired from any session executing the triggering DML statements. Can be fired only from the form that define the trigger.Can cause other database triggers to fire.Can cause other database triggers to fire, but not other form triggers.
What is an UTL_FILE.What are different procedures and functions associated with it? UTL_FILE is a package that adds the ability to read and write to operating system files. Procedures associated with it are FCLOSE, FCLOSE_ALL and 5 procedures to output data to a file PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH.PUT, FFLUSH.PUT_LINE,FFLUSH.NEW_LINE. Functions associated with it are FOPEN, ISOPEN.
Differentiate between TRUNCATE and DELETE TRUNCATE deletes much faster than DELETETRUNCATE DELETE It is a DDL statement It is a DML statement It is a one way trip,cannot ROLLBACK One can Rollback Doesn't have selective features (where clause) Has Doesn't fire database triggers Does It requires disabling of referential constraints. Does not require
Difference between Optimizer=ALL_ROWS and Optimizer=CHOOSE Choose - Choose either Rule based or Cost based depend on theavailability of statistics. If statistics is available on the table it usesCBO and if not it uses RBO.
ALL_ROWS - Choose based optimization, statistics is needed. U couldpass as hint in ur query.
But in the majority of cases with optimizer_mode=choose ( and with a goodstatistics ) the CBO will be able to find a good execution plan foryoursqueries . Difference between HASH JOIN And MERGE JOIN Hash joing is implemented in CBO and comparatively faster. Merge joing is comparatively slow because both the row sources are to be sorted before merging. Difference Between Hash Join & Merge Join Merge Join :
Oracle performs a join between two sets of row data using the mergejoin algorithm. The inputs are two separate sets of row data. Output isthe results of the join. Oracle reads rows from both inputs in analternating fashion and merges together matching rows in order togenerate output. The two inputs are sorted on join column.
Hash Join :
Oracle performs a join between two sets of row data using hash joinalgorithm. Input and Output same as Merge Join. Oracle reads all rowsfrom the second input and builds a hash structure (like has table injava), before reading each row from the first input one at a time. Foreach row from the first input, the hash structure is probed and matchingrows generate output.
Can data be inserted/deleted/updated from CLOB,BLOB,NCLOB columns.If not why?
RE: Suppose there are two fields in table(Employee) say Name and Salary and there are in total 100 recor...-------------------------------------------------------------------------------- Question : Suppose there are two fields in table(Employee) say Name and Salary and there are in total 100 records in table. Now my query gives the name of an employee whose salary is 10th among 100 salaries. this query should be solve in SQL not in PL/SQL ?
ans: the command will be:
select name salary from employee where rowid ( select Max(rowid) from employee where rownum< 10);
where we use nested tables in our real time
Friday, November 6, 2009
Tuesday, August 4, 2009
Zen of Time Management
Time is synonymous to Life, isn’t it? If you say, you have spent X years for something, means you have devoted X years of your life to that thing!
It is an irreplaceable resource. Effectual use of Time will increase your efficiency.
OK, I know all this… but how… how would I use my time effectively? What is it that I can follow to commit to it? I am an average person… so I can implement it only if the system is simple.
Here is the answer… the few simple steps that will help you save your time the way you dreamt.
Daily Touchstones:
Monitor your time on daily basis. Keep track of the actual flow of your days for 15 days. Categorize time spent activities by the activity type.
Observe where your time is utilized by viewing Time Record Summary and Daily Time Record.
Setup your ideal daily routine and establish the habit of daily priority setting.
Identify the time-stealers and adopt Anti-time-stealing techniques to deal effectively with the time-stealers
Anti-time-stealing techniques:
Break down the projects into smaller chunk of tasks. Learn how to eat the elephant.… one piece at a time.
Set priorities to stay focused. Set goals to accomplish no more than 3 of your most important tasks (MITs) – these tasks should be the biggest rocks, if climbed successfully; lead you to nearer to your set goal.
Remind everyone involved of the priorities – this will align everyone.
Learn to say “No”: Don’t say “Yes” to everything and everyone that come to you.
Delegate whatever you can
Identify common pattern of tasks. Organize such tasks to be done together. It should be part of your permanent daily schedule.
Talk about time saving ideas with others… surprisingly you will find a few more ideas than you already know.
Make “Beating the Deadlines” a top-priority. Just Meeting the deadline should not be acceptable!
Organize yourself to accomplish a thing from start to end. Don’t fragment your time.
Reserve a day of quiet time for yourself in a week. Use “Do not disturb” sign for uninterrupted work.
Make effective use of secretaries or receptionist to screen out time stealers.
Consolidate email, chat or telephone time. Set fixed time for answering the calls or answering your emails. Your Chat window should not enjoy “Available” status for all the time.
Eat that Frog! – don’t put off unpleasant tasks.
Use your idle time to read through Blogs or listen to inspiring music.
Eat light; stay tight. Eliminate alcohol from your weekdays.
Understand that work from home is no brainier.
Learn to tolerate ‘Excellence’ everyday, procrastinate perfection for the next day.
Use your prime time for the MITs.
Make a habit of keeping a capture tool to capture your great ideas and to save them for later use.
Follow the 80/20 rule. Plan for 20 percent of your time so that you can act effectively for the rest of 80% — Gone are the days of first acting and then thinking.
Audit your Time Record – Criticize yourself mercilessly.
Focus on “What” not “How” Things should be got done; doesn’t matter how.
It is an irreplaceable resource. Effectual use of Time will increase your efficiency.
OK, I know all this… but how… how would I use my time effectively? What is it that I can follow to commit to it? I am an average person… so I can implement it only if the system is simple.
Here is the answer… the few simple steps that will help you save your time the way you dreamt.
Daily Touchstones:
Monitor your time on daily basis. Keep track of the actual flow of your days for 15 days. Categorize time spent activities by the activity type.
Observe where your time is utilized by viewing Time Record Summary and Daily Time Record.
Setup your ideal daily routine and establish the habit of daily priority setting.
Identify the time-stealers and adopt Anti-time-stealing techniques to deal effectively with the time-stealers
Anti-time-stealing techniques:
Break down the projects into smaller chunk of tasks. Learn how to eat the elephant.… one piece at a time.
Set priorities to stay focused. Set goals to accomplish no more than 3 of your most important tasks (MITs) – these tasks should be the biggest rocks, if climbed successfully; lead you to nearer to your set goal.
Remind everyone involved of the priorities – this will align everyone.
Learn to say “No”: Don’t say “Yes” to everything and everyone that come to you.
Delegate whatever you can
Identify common pattern of tasks. Organize such tasks to be done together. It should be part of your permanent daily schedule.
Talk about time saving ideas with others… surprisingly you will find a few more ideas than you already know.
Make “Beating the Deadlines” a top-priority. Just Meeting the deadline should not be acceptable!
Organize yourself to accomplish a thing from start to end. Don’t fragment your time.
Reserve a day of quiet time for yourself in a week. Use “Do not disturb” sign for uninterrupted work.
Make effective use of secretaries or receptionist to screen out time stealers.
Consolidate email, chat or telephone time. Set fixed time for answering the calls or answering your emails. Your Chat window should not enjoy “Available” status for all the time.
Eat that Frog! – don’t put off unpleasant tasks.
Use your idle time to read through Blogs or listen to inspiring music.
Eat light; stay tight. Eliminate alcohol from your weekdays.
Understand that work from home is no brainier.
Learn to tolerate ‘Excellence’ everyday, procrastinate perfection for the next day.
Use your prime time for the MITs.
Make a habit of keeping a capture tool to capture your great ideas and to save them for later use.
Follow the 80/20 rule. Plan for 20 percent of your time so that you can act effectively for the rest of 80% — Gone are the days of first acting and then thinking.
Audit your Time Record – Criticize yourself mercilessly.
Focus on “What” not “How” Things should be got done; doesn’t matter how.
Tuesday, February 3, 2009
Hard Work ....
How to gain encouragement to go on, even when hard work doesn't give satisfactory results ?
at times it happens..that u r wrking hard and really hard towards some goal..but each time u stumble.. u try harder and stumble still.. each fall makes one frustrated and exhausted and also hopeless.. in such a situation, how shud a person gain motivation and strength to go on with positivity ??thank you !
---------------------------------------
1 Listen to a good rock song.
2 Solutions are based on problem identification and your post is abstract. If you listed what specifically you are finding a discouraging process it would be easier to provide concrete advise. Sometimes trying and falling just means that it's time to give up and move on - that door is not open for you now and maybe never will be. Other times, discouraging ups and downs are just a natural process of getting better at something and eventually succeeding. Only you can know whether to keep on or change direction. If you want some advice on that, I'd describe the issue that your dealing with specifically and then you will likely get some specific help.
3 Dont let anything beat you,Stop, say to yourself, this isnt going to beat me. Take a big breath, focus on the results and go and shine!!!!
4 People with hardwork giving satisfactory results but if there is an encouragement people can even give better or even can give best results
5 My remembering that the more number of times you fall the more stronger you become. And it requires great great strength to try again even after many falls. We are not weak cowards to give up, we are like lions, we get up and try again no matter how many times fate hits us. We show the fate our face again without running away.
at times it happens..that u r wrking hard and really hard towards some goal..but each time u stumble.. u try harder and stumble still.. each fall makes one frustrated and exhausted and also hopeless.. in such a situation, how shud a person gain motivation and strength to go on with positivity ??thank you !
---------------------------------------
1 Listen to a good rock song.
2 Solutions are based on problem identification and your post is abstract. If you listed what specifically you are finding a discouraging process it would be easier to provide concrete advise. Sometimes trying and falling just means that it's time to give up and move on - that door is not open for you now and maybe never will be. Other times, discouraging ups and downs are just a natural process of getting better at something and eventually succeeding. Only you can know whether to keep on or change direction. If you want some advice on that, I'd describe the issue that your dealing with specifically and then you will likely get some specific help.
3 Dont let anything beat you,Stop, say to yourself, this isnt going to beat me. Take a big breath, focus on the results and go and shine!!!!
4 People with hardwork giving satisfactory results but if there is an encouragement people can even give better or even can give best results
5 My remembering that the more number of times you fall the more stronger you become. And it requires great great strength to try again even after many falls. We are not weak cowards to give up, we are like lions, we get up and try again no matter how many times fate hits us. We show the fate our face again without running away.
Monday, February 2, 2009
GROUING OF TOTAL
I am joining some table to display the data as shown aboveselect COUNTRY,STATE,TIMING,sum(AMOUNT) from table1,table2where table1.country=table2.country group by COUNTRY,STATE,TIMING;COUNTRY STATE TIMING AMOUNT====================================AUSTRULIA REGION1 MORNING 20AUSTRULIA REGION1 EVENING 30AUSTRULIA REGION1 NIGHT 12AUSTRULIA REGION2 MORNING 17AUSTRULIA REGION2 EVENING 13AUSTRULIA REGION2 NIGHT 20actually i want data like thisAUSTRULIA REGION1 MORNING 20AUSTRULIA REGION1 EVENING 30AUSTRULIA REGION1 NIGHT 12AUSTRULIA REGION2 MORNING 17AUSTRULIA REGION2 EVENING 13AUSTRULIA REGION2 NIGHT 20AUSTRULIA TOTAL MORNING 37AUSTRULIA TOTAL EVENING 43AUSTRULIA TOTAL NIGHT 32lets say I don’t have region2 Then also I want to display data likeAUSTRULIA REGION1 MORNING 20AUSTRULIA REGION1 EVENING 30AUSTRULIA REGION1 NIGHT 12AUSTRULIA TOTAL MORNING 20AUSTRULIA TOTAL EVENING 30AUSTRULIA TOTAL NIGHT 12
How to do so?Thanks
-------------------------------------------------------------------------------------------------
http://www.psoug.org/reference/rollup.html
SELECT COUNTRY,
STATE,
TIMING,
SUM(AMOUNT)
FROM table1,
table2
WHERE table1.country = table2.country
GROUP BY COUNTRY,
STATE,
TIMING
UNION
SELECT COUNTRY,
'TOTAL',
TIMING,
SUM(AMOUNT)
FROM table1,
table2
WHERE table1.country = table2.country
GROUP BY COUNTRY,
TIMING;
SCOTT@soti_10> with Test_Data as (
2 select 'AUSTRULIA' as country, 'REGION1' as state, 'MORNING' as timing, 20 as amount from dual union all
3 select 'AUSTRULIA' as country, 'REGION1' as state, 'EVENING' as timing, 30 as amount from dual union all
4 select 'AUSTRULIA' as country, 'REGION1' as state, 'NIGHT' as timing, 12 as amount from dual union all
5 select 'AUSTRULIA' as country, 'REGION2' as state, 'MORNING' as timing, 17 as amount from dual union all
6 select 'AUSTRULIA' as country, 'REGION2' as state, 'EVENING' as timing, 13 as amount from dual union all
7 select 'AUSTRULIA' as country, 'REGION2' as state, 'NIGHT' as timing, 20 as amount from dual
8 )
9 select country,
10 nvl(state, 'TOTAL') as state,
11 timing,
12 sum(amount) as amount
13 from Test_Data
14 group by country, timing, rollup(state)
15 order by state,
16 case when timing = 'MORNING' then 1
17 when timing = 'EVENING' then 2
18 when timing = 'NIGHT' then 3
19 end
20 ;
COUNTRY STATE TIMING AMOUNT
--------- ------- ------- ----------
AUSTRULIA REGION1 MORNING 20
AUSTRULIA REGION1 EVENING 30
AUSTRULIA REGION1 NIGHT 12
AUSTRULIA REGION2 MORNING 17
AUSTRULIA REGION2 EVENING 13
AUSTRULIA REGION2 NIGHT 20
AUSTRULIA TOTAL MORNING 37
AUSTRULIA TOTAL EVENING 43
AUSTRULIA TOTAL NIGHT 32
9 rows selected.
How to do so?Thanks
-------------------------------------------------------------------------------------------------
http://www.psoug.org/reference/rollup.html
SELECT COUNTRY,
STATE,
TIMING,
SUM(AMOUNT)
FROM table1,
table2
WHERE table1.country = table2.country
GROUP BY COUNTRY,
STATE,
TIMING
UNION
SELECT COUNTRY,
'TOTAL',
TIMING,
SUM(AMOUNT)
FROM table1,
table2
WHERE table1.country = table2.country
GROUP BY COUNTRY,
TIMING;
SCOTT@soti_10> with Test_Data as (
2 select 'AUSTRULIA' as country, 'REGION1' as state, 'MORNING' as timing, 20 as amount from dual union all
3 select 'AUSTRULIA' as country, 'REGION1' as state, 'EVENING' as timing, 30 as amount from dual union all
4 select 'AUSTRULIA' as country, 'REGION1' as state, 'NIGHT' as timing, 12 as amount from dual union all
5 select 'AUSTRULIA' as country, 'REGION2' as state, 'MORNING' as timing, 17 as amount from dual union all
6 select 'AUSTRULIA' as country, 'REGION2' as state, 'EVENING' as timing, 13 as amount from dual union all
7 select 'AUSTRULIA' as country, 'REGION2' as state, 'NIGHT' as timing, 20 as amount from dual
8 )
9 select country,
10 nvl(state, 'TOTAL') as state,
11 timing,
12 sum(amount) as amount
13 from Test_Data
14 group by country, timing, rollup(state)
15 order by state,
16 case when timing = 'MORNING' then 1
17 when timing = 'EVENING' then 2
18 when timing = 'NIGHT' then 3
19 end
20 ;
COUNTRY STATE TIMING AMOUNT
--------- ------- ------- ----------
AUSTRULIA REGION1 MORNING 20
AUSTRULIA REGION1 EVENING 30
AUSTRULIA REGION1 NIGHT 12
AUSTRULIA REGION2 MORNING 17
AUSTRULIA REGION2 EVENING 13
AUSTRULIA REGION2 NIGHT 20
AUSTRULIA TOTAL MORNING 37
AUSTRULIA TOTAL EVENING 43
AUSTRULIA TOTAL NIGHT 32
9 rows selected.
Monday, January 19, 2009
D2K Export into Excel Using
Export into Excel Using Report
Free Demp Excel Export
http://www.free-dev.com/create_xls4emp.htm
How to save a query result and export it to, say excell?
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:728625409049http://matzberger.de/oracle/spreadsheet-en.htmlhttp://wiki.oracle.com/thread/1227569/Output+to+a+csv+file?t=anon
http://radio.weblogs.com/0137094/2006/10/26.htmlhttp://spendolini.blogspot.com/2006/04/custom-export-to-csv.htmlhttp://sanjeev-oracle-world.blogspot.com/2007/06/create-excel-workbook-by-plsql-code.htmlhttp://www.rci-informatique.fr/rxls (french)
How to save a query result and export it to, say excell?
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:68212348056
Free Demp Excel Export
http://www.free-dev.com/create_xls4emp.htm
How to save a query result and export it to, say excell?
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:728625409049http://matzberger.de/oracle/spreadsheet-en.htmlhttp://wiki.oracle.com/thread/1227569/Output+to+a+csv+file?t=anon
http://radio.weblogs.com/0137094/2006/10/26.htmlhttp://spendolini.blogspot.com/2006/04/custom-export-to-csv.htmlhttp://sanjeev-oracle-world.blogspot.com/2007/06/create-excel-workbook-by-plsql-code.htmlhttp://www.rci-informatique.fr/rxls (french)
How to save a query result and export it to, say excell?
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:68212348056
Saturday, January 17, 2009
MASTER FORM
Hi Friends
1 The enabled and visible property of an item cannot be disabled at runtime if the cursor is on that item
Ture or False
2 A window is connect to block
Ture or False
3 The pre-Form trigger fire befor the when-new-form-instance trigger
Ture or False
4 When-validation-item cannot have navigational code like next_item,next_block etc
Ture or False
5 By default, the type of canvas is 'Context'
Ture or False
6 A control block is created by using Block wizard
Ture or False
7 New_record procedure create a new record at runtime
Ture or False
8 Execute_query retriever rows from the table and dispalys fist record on the form
Ture or False
9 MessageBox() is used to display message on the stauts bar
Ture or False
10 System.Form_status system variable are
QUERY, NEW, CHANGED
11 The window should be open maximized by setting
In Form Level-when-new-form-instance
SET_WINDOW_PROPERTY(FORMS_MDI_WINDOW,WINDOW_STATE,MAXIMIZE);
SET_WINDOW_PROPERTY('WINDOW1',WINDOW_STATE,MAXIMIZE);
12 window property can set at runtime by using (set_window_property)
1 The enabled and visible property of an item cannot be disabled at runtime if the cursor is on that item
Ture or False
2 A window is connect to block
Ture or False
3 The pre-Form trigger fire befor the when-new-form-instance trigger
Ture or False
4 When-validation-item cannot have navigational code like next_item,next_block etc
Ture or False
5 By default, the type of canvas is 'Context'
Ture or False
6 A control block is created by using Block wizard
Ture or False
7 New_record procedure create a new record at runtime
Ture or False
8 Execute_query retriever rows from the table and dispalys fist record on the form
Ture or False
9 MessageBox() is used to display message on the stauts bar
Ture or False
10 System.Form_status system variable are
QUERY, NEW, CHANGED
11 The window should be open maximized by setting
In Form Level-when-new-form-instance
SET_WINDOW_PROPERTY(FORMS_MDI_WINDOW,WINDOW_STATE,MAXIMIZE);
SET_WINDOW_PROPERTY('WINDOW1',WINDOW_STATE,MAXIMIZE);
12 window property can set at runtime by using (set_window_property)
D2K QUESTION
Hi friends this are some Q and A of the D2k
If you know the A of this Q post the currect Ans...
1 BLOCK ARE logical containers that have no physical representations
True or False
2 A single block can be connected to multiple database object like a table, a view
True or False
3 When a frame is associated with a block, the items in the block are automatically
arranged within the frame
True or False
4 Scroll bar for a block be attached to a block only at the time of block creating
True or False
5 A window is connected to a block
True or False
6 Evert form contains at lest one block,on window,one canvas and one or more items
True or False
7 GUI stands for Graphic User Interface
8 The Data Block items can be used only to show information that must be fetched or assigned prgmm.
9 When a new from is created ____________ by default
Canvas , Items, Window
If you know the A of this Q post the currect Ans...
1 BLOCK ARE logical containers that have no physical representations
True or False
2 A single block can be connected to multiple database object like a table, a view
True or False
3 When a frame is associated with a block, the items in the block are automatically
arranged within the frame
True or False
4 Scroll bar for a block be attached to a block only at the time of block creating
True or False
5 A window is connected to a block
True or False
6 Evert form contains at lest one block,on window,one canvas and one or more items
True or False
7 GUI stands for Graphic User Interface
8 The Data Block items can be used only to show information that must be fetched or assigned prgmm.
9 When a new from is created ____________ by default
Canvas , Items, Window
Calling a form and passing a context
Hi, this task seems to come up very frequently, so i thought i might be a good idea to put all that information in a thread so that it can be refered when the next request comes in.
Calling a form and passing a context
One common task in a forms-application is call one form form another one and give some context-information to that new form when calling it. In some cases you also want to get a kind of "return-value" when the called form is finished to that you can react on it. There are several solutions for this task i will describe here:
Test-case
For the examples i take a simple application consisting of two forms-modules, both based on the EMPLOYEES-tables form the HR-demo-schema. The first form, i call it OVERVIEW is a tabular form where you can query data from the employees-table, but cannot change anything. Beneath the table there are two buttons, one called "Edit" and one called "New". Both will call the second form i call EDIT_EMP which can either be used to edit an existing record form the EMPLOYEES-table, or to create a new employee. What action should be possible in the second form is given as context from the OVERVIEW-form.In detail, - when pressing the Edit-button i want to give the EMPLOYEE_ID from the selected record to the EDIT_EMP, along with the information to edit that record- when pressing the New-button i want to call EDIT_EMP with a blank record which enables the user to enter data for a new employee.In the examples i will deal only with CALL_FORM (for first). There are some issues with OPEN_FORM which are similar (or equal) but also some which may differ.
Use parameters to pass a context
One approach of passing a context from one OVERVIEW to EDIT_EMP is to use Parameters.To do that create two parameters in the Object-Navigator in EDIT_EMP:-P_EMPLOYEE_ID with datatype Number-P_MODE with datatype CharTo give a context from OVERVIEW to EDIT_EMP you now have to create a parameter-list, fill in the appropiate parameters and values and pass the parameters when calling the EDIT_EMP.Here the example-code for the WHEN-BUTTON-PRESSED-trigger on the Edit-button in OVERVIEW:
DECLARE
pl PARAMLIST:=GET_PARAMETER_LIST('PL_EDIT_EMP');
BEGIN
-- Delete list if existing
IF NOT ID_NULL(pl) THEN
DESTROY_PARAMETER_LIST(pl);
END IF;
-- Create new
pl:=CREATE_PARAMETER_LIST('PL_EDIT_EMP');
-- Add parameter for Mode
ADD_PARAMETER(pl, 'P_MODE', TEXT_PARAMETER, 'EDIT');
-- Add parameter for EMPLOYEE_ID
ADD_PARAMETER(pl, 'P_EMPLOYEE_ID', TEXT_PARAMETER, :EMP.EMPLOYEE_ID);
-- Now call the second form, giving the parameter-list
CALL_FORM('EDIT_EMP',NO_HIDE,DO_REPLACE,NO_QUERY_ONLY,pl);
END;
And now in EDIT_EMP you have to "take over" the parameters and react on them. A good trigger to do so is the WHEN-NEW-FORM-INSTANCE-trigger. Heres the code that will react on the Edit-Mode and query the appropiate record.
IF :PARAMETER.P_MODE='EDIT' THEN
-- go to the employee-block
GO_BLOCK('EMP');
-- execute the query (the block has a WHERE-condition using EMPLOYEE_ID=:PARAMETER.P_EMPLOYEE_ID)
EXECUTE_QUERY;
-- Restrict createing new records
SET_BLOCK_PROPERTY('EMP', INSERT_ALLOWED, PROPERTY_FALSE);
END IF;
Important here is the WHERE-condition which is set at the EMP-block to restrict the query to the parameter-value.Thats it for the parameter-approach.Pro's: -You can see in the Module-definition of the called form which parameters it accepts.-The values of the parameters are specific to the called "instance" of the form, so if one form is called more than one time, each form can have it's own valuesCon's:Parameters are one-way, means you can pass parameters when from the calling form to the called form, but you cannot return values back to the called form.
Using globals to pass a context
With this approach you make use of the Global-variable-concept in forms to pass a context. Globals are not defined at design-time, but implicitly at runtime when you first assign a value to them or use the Built-In DEFAULT_VALUE.Lets do the same thing as before using Globals:Heres the WHEN-BUTTON-PRESSED-trigger on the Edit-Button:
-- Define global for Mode
:GLOBAL.MODE:='EDIT';
-- Define global for Employee-Id
:GLOBAL.EMPLOYEE_ID:=:EMP.EMPLOYEE_ID;
-- Now call the second form
CALL_FORM('EDIT_EMP',NO_HIDE,DO_REPLACE,NO_QUERY_ONLY);
And also, you take over the context in the WHEN-NEW-FORM-INSTANCE-trigger of EDIT_EMP:
-- Make sure the Gloabls exist
DEFAULT_VALUE(NULL, 'GLOBAL.MODE');
DEFAULT_VALUE(NULL, 'GLOBAL.EMPLOYEE_ID');
IF :GLOBAL.MODE='EDIT' THEN
-- go to the employee-block
GO_BLOCK('EMP');
-- execute the query (the block has a WHERE-condition using EMPLOYEE_ID=:GLOBAL.EMPLOYEE_ID)
EXECUTE_QUERY;
-- Restrict creating new records
SET_BLOCK_PROPERTY('EMP', INSERT_ALLOWED, PROPERTY_FALSE);
END IF;
Again, the WHERE-condition at the block EMP is important to be set.So far, the Global-approach is quite similar to the parameter-approach.Pro's: -Globals can be seen by every module in a forms-session, so you can also use them to return a value from the called form back to the calling formCon's:-Globals are not defined declarative so you have to look into the code-Globals can be seen by every module in a forms-session, so if one form is called more than one time, the second call will overwrite the globals from the first call.
Returning values from a called form
As returning a value from a clalled dialog is a common requirement, lets enhance the example and return a flag from the EDIT_EMP to indicate if the user has changed the edited employee, so that we can do a requery in that case to reflect the changes in the overview.First, we implement the WHEN-BUTTON-PRESSED-trigger on the save-button in EDIT_EMP:
IF :SYSTEM.FORM_STATUS='CHANGED' THEN
-- save changes, if needed
DO_KEY('COMMIT_FORM');
-- set "saved"-flag
:GLOBAL.USER_HAS_SAVED:='TRUE';
-- exit
EXIT_FORM;
END IF;
And also the WHEN-BUTTOn-PRESSED-trigger on the cancel-button:
-- reset saved-flag
:GLOBAL.USER_HAS_SAVED:='FALSE';
-- exit without saving
EXIT_FORM(NO_VALIDATE);
Last we have to adjust the Edit-button code to react on the return-value
-- Define global for Mode
:GLOBAL.MODE:='EDIT';
-- Define global for Employee-Id
:GLOBAL.EMPLOYEE_ID:=:EMP.EMPLOYEE_ID;
-- Now call the second form
CALL_FORM('EDIT_EMP1',NO_HIDE,DO_REPLACE,NO_QUERY_ONLY);
-- Make sure the global exists
DEFAULT_VALUE('FALSE', 'GLOBAL.USER_HAS_SAVED');
-- Check retur value
IF :GLOBAL.USER_HAS_SAVED='TRUE' THEN
-- Requery data
GO_BLOCK('EMP');
EXECUTE_QUERY;
END IF;
Encapsulating the logic
Both approaches have pros and cons and both have in common the same problems when a form is called from more than one other form:-The names of the parameters/globals must match on both sides.-The passed values must match on both sides-When enhancing the called form its sometimes hard to find all calling forms to adjust the callSo i took the following approach to "hide the complexity" and make it quite straight-forward to do the communication:-Create a pll to contain all communication logic.-For each form that can be called, create a package in it-Inside each package, create the following functions/procedure- a procedure to call the form, having all the parameters needed to call as IN-parameters, or- a function to call the form, having all the parameters needed to call as IN-parameters and returning the result- a procedure for retrieving the callers parameters from the package to the called form.- a procedure to set the return-value from the called form into the package.- All communication with the called form in done exclusively using this packageIn case there are different modes or variants to call a form, there may also be different "calling"-procedures/functions.This approach has some (in my eyec big) advantages:-The way the communication between caller and calling-form is implemented is hidden and doesn't really matter to the caller-All usage of Global-names/parameternames is only done in this package, so the chance of having typos in it is quite small-Parameters are given with its "real" datatype and can even be checked before calling the form.-If the signature to call a form must change the signature of the package has to be changed, and with a recompile it's quite eays to find modules using the changed module, for they will no longer compileSo, lets change the example to match this approach:First, heres the code for the package:
PACKAGE PK_EDIT_EMP IS
-- Call the dialog to edit the employee with the given ID
-- function return TRUE if user changed the Employee, else FALSE
FUNCTION FK_EDIT_EMPLOYEE(i_nEmployeeId IN NUMBER)
RETURN BOOLEAN;
-- retrieve the callersparameters
-- o_vcMode will be EDIT for editing NEW for creating a new Employee
-- o_nEmployeeId will give the Id of the employee to be queried when o_vcMode is EDIT
PROCEDURE PR_GET_PARAMETERS(o_vcMode OUT VARCHAR2,
o_nEmployeeId OUT NUMBER);
-- Sets the return-value
PROCEDURE PR_SET_RETURN(i_bSaved IN BOOLEAN);
END;
PACKAGE BODY PK_EDIT_EMP IS
VCC_GLOBAL_MODE VARCHAR2(30):='GLOBAL.EDIT_EMP_MODE';
VCC_GLOBAL_EMPID VARCHAR2(30):='GLOBAL.EDIT_EMP_EMPID';
VCC_GLOBAL_RETURN VARCHAR2(30):='GLOBAL.EDIT_EMP_RETURN';
FUNCTION FK_EDIT_EMPLOYEE(i_nEmployeeId IN NUMBER)
RETURN BOOLEAN IS
BEGIN
-- Set the globals
COPY('FALSE', VCC_GLOBAL_RETURN);
COPY('EDIT', VCC_GLOBAL_MODE);
COPY(TO_CHAR(i_nEmployeeId), VCC_GLOBAL_EMPID);
-- call the form
CALL_FORM('EDIT_EMP1',NO_HIDE,DO_REPLACE,NO_QUERY_ONLY);
-- return result
RETURN (NAME_IN(VCC_GLOBAL_RETURN)='TRUE');
END;
-- ----------------------------------------------------------------
PROCEDURE PR_GET_PARAMETERS(o_vcMode OUT VARCHAR2,
o_nEmployeeId OUT NUMBER) IS
BEGIN
-- transfer globals to out-params
o_vcMode:=NAME_IN(VCC_GLOBAL_MODE);
o_nEmployeeId:=TO_NUMBER(NAME_IN(VCC_GLOBAL_EMPID));
END;
-- ----------------------------------------------------------------
PROCEDURE PR_SET_RETURN(i_bSaved IN BOOLEAN) IS
BEGIN
-- map boolean to Char-result
IF i_bSaved THEN
COPY('TRUE', VCC_GLOBAL_RETURN);
ELSE
COPY('FALSE', VCC_GLOBAL_RETURN);
END IF;
END;
END;
Now the WHEN-BUTTON-PRESSED-trigger in OVERVIEW:
IF PK_EDIT_EMP.FK_EDIT_EMPLOYEE(:EMP.EMPLOYEE_ID) THEN
-- Requery data
GO_BLOCK('EMP');
EXECUTE_QUERY;
END IF;
The WHEN-NEW-FORM-INSTANCE-trigger in EDIT_EMP:
DECLARE
vcMode VARCHAR2(4);
nEmployeeId NUMBER;
BEGIN
PK_EDIT_EMP.PR_GET_PARAMETERS(vcMode, nEmployeeId);
-- to avoid using the globals directly in the form, created a item in y control-block
-- and restrict the query on that
:BL_CONTROL.IT_EMPLOYEE_ID:=nEmployeeId;
-- go to the employee-block
GO_BLOCK('EMP');
-- execute the query (the block has a WHERE-condition using EMPLOYEE_ID=:BL_CONTROL.IT_EMPLOYEE_ID
EXECUTE_QUERY;
-- Restrict createing new records
SET_BLOCK_PROPERTY('EMP', INSERT_ALLOWED, PROPERTY_FALSE);
END;
Note that for avoiding to "break" the encapsulation i created a control-block with an item to store the employee_id in.And last, the two button "Save"
IF :SYSTEM.FORM_STATUS='CHANGED' THEN
DO_KEY('COMMIT_FORM');
PK_EDIT_EMP.PR_SET_RETURN(TRUE);
EXIT_FORM;
END IF;
and "Cancel"
PK_EDIT_EMP.PR_SET_RETURN(FALSE);
EXIT_FORM(NO_VALIDATE);
Calling a form and passing a context
One common task in a forms-application is call one form form another one and give some context-information to that new form when calling it. In some cases you also want to get a kind of "return-value" when the called form is finished to that you can react on it. There are several solutions for this task i will describe here:
Test-case
For the examples i take a simple application consisting of two forms-modules, both based on the EMPLOYEES-tables form the HR-demo-schema. The first form, i call it OVERVIEW is a tabular form where you can query data from the employees-table, but cannot change anything. Beneath the table there are two buttons, one called "Edit" and one called "New". Both will call the second form i call EDIT_EMP which can either be used to edit an existing record form the EMPLOYEES-table, or to create a new employee. What action should be possible in the second form is given as context from the OVERVIEW-form.In detail, - when pressing the Edit-button i want to give the EMPLOYEE_ID from the selected record to the EDIT_EMP, along with the information to edit that record- when pressing the New-button i want to call EDIT_EMP with a blank record which enables the user to enter data for a new employee.In the examples i will deal only with CALL_FORM (for first). There are some issues with OPEN_FORM which are similar (or equal) but also some which may differ.
Use parameters to pass a context
One approach of passing a context from one OVERVIEW to EDIT_EMP is to use Parameters.To do that create two parameters in the Object-Navigator in EDIT_EMP:-P_EMPLOYEE_ID with datatype Number-P_MODE with datatype CharTo give a context from OVERVIEW to EDIT_EMP you now have to create a parameter-list, fill in the appropiate parameters and values and pass the parameters when calling the EDIT_EMP.Here the example-code for the WHEN-BUTTON-PRESSED-trigger on the Edit-button in OVERVIEW:
DECLARE
pl PARAMLIST:=GET_PARAMETER_LIST('PL_EDIT_EMP');
BEGIN
-- Delete list if existing
IF NOT ID_NULL(pl) THEN
DESTROY_PARAMETER_LIST(pl);
END IF;
-- Create new
pl:=CREATE_PARAMETER_LIST('PL_EDIT_EMP');
-- Add parameter for Mode
ADD_PARAMETER(pl, 'P_MODE', TEXT_PARAMETER, 'EDIT');
-- Add parameter for EMPLOYEE_ID
ADD_PARAMETER(pl, 'P_EMPLOYEE_ID', TEXT_PARAMETER, :EMP.EMPLOYEE_ID);
-- Now call the second form, giving the parameter-list
CALL_FORM('EDIT_EMP',NO_HIDE,DO_REPLACE,NO_QUERY_ONLY,pl);
END;
And now in EDIT_EMP you have to "take over" the parameters and react on them. A good trigger to do so is the WHEN-NEW-FORM-INSTANCE-trigger. Heres the code that will react on the Edit-Mode and query the appropiate record.
IF :PARAMETER.P_MODE='EDIT' THEN
-- go to the employee-block
GO_BLOCK('EMP');
-- execute the query (the block has a WHERE-condition using EMPLOYEE_ID=:PARAMETER.P_EMPLOYEE_ID)
EXECUTE_QUERY;
-- Restrict createing new records
SET_BLOCK_PROPERTY('EMP', INSERT_ALLOWED, PROPERTY_FALSE);
END IF;
Important here is the WHERE-condition which is set at the EMP-block to restrict the query to the parameter-value.Thats it for the parameter-approach.Pro's: -You can see in the Module-definition of the called form which parameters it accepts.-The values of the parameters are specific to the called "instance" of the form, so if one form is called more than one time, each form can have it's own valuesCon's:Parameters are one-way, means you can pass parameters when from the calling form to the called form, but you cannot return values back to the called form.
Using globals to pass a context
With this approach you make use of the Global-variable-concept in forms to pass a context. Globals are not defined at design-time, but implicitly at runtime when you first assign a value to them or use the Built-In DEFAULT_VALUE.Lets do the same thing as before using Globals:Heres the WHEN-BUTTON-PRESSED-trigger on the Edit-Button:
-- Define global for Mode
:GLOBAL.MODE:='EDIT';
-- Define global for Employee-Id
:GLOBAL.EMPLOYEE_ID:=:EMP.EMPLOYEE_ID;
-- Now call the second form
CALL_FORM('EDIT_EMP',NO_HIDE,DO_REPLACE,NO_QUERY_ONLY);
And also, you take over the context in the WHEN-NEW-FORM-INSTANCE-trigger of EDIT_EMP:
-- Make sure the Gloabls exist
DEFAULT_VALUE(NULL, 'GLOBAL.MODE');
DEFAULT_VALUE(NULL, 'GLOBAL.EMPLOYEE_ID');
IF :GLOBAL.MODE='EDIT' THEN
-- go to the employee-block
GO_BLOCK('EMP');
-- execute the query (the block has a WHERE-condition using EMPLOYEE_ID=:GLOBAL.EMPLOYEE_ID)
EXECUTE_QUERY;
-- Restrict creating new records
SET_BLOCK_PROPERTY('EMP', INSERT_ALLOWED, PROPERTY_FALSE);
END IF;
Again, the WHERE-condition at the block EMP is important to be set.So far, the Global-approach is quite similar to the parameter-approach.Pro's: -Globals can be seen by every module in a forms-session, so you can also use them to return a value from the called form back to the calling formCon's:-Globals are not defined declarative so you have to look into the code-Globals can be seen by every module in a forms-session, so if one form is called more than one time, the second call will overwrite the globals from the first call.
Returning values from a called form
As returning a value from a clalled dialog is a common requirement, lets enhance the example and return a flag from the EDIT_EMP to indicate if the user has changed the edited employee, so that we can do a requery in that case to reflect the changes in the overview.First, we implement the WHEN-BUTTON-PRESSED-trigger on the save-button in EDIT_EMP:
IF :SYSTEM.FORM_STATUS='CHANGED' THEN
-- save changes, if needed
DO_KEY('COMMIT_FORM');
-- set "saved"-flag
:GLOBAL.USER_HAS_SAVED:='TRUE';
-- exit
EXIT_FORM;
END IF;
And also the WHEN-BUTTOn-PRESSED-trigger on the cancel-button:
-- reset saved-flag
:GLOBAL.USER_HAS_SAVED:='FALSE';
-- exit without saving
EXIT_FORM(NO_VALIDATE);
Last we have to adjust the Edit-button code to react on the return-value
-- Define global for Mode
:GLOBAL.MODE:='EDIT';
-- Define global for Employee-Id
:GLOBAL.EMPLOYEE_ID:=:EMP.EMPLOYEE_ID;
-- Now call the second form
CALL_FORM('EDIT_EMP1',NO_HIDE,DO_REPLACE,NO_QUERY_ONLY);
-- Make sure the global exists
DEFAULT_VALUE('FALSE', 'GLOBAL.USER_HAS_SAVED');
-- Check retur value
IF :GLOBAL.USER_HAS_SAVED='TRUE' THEN
-- Requery data
GO_BLOCK('EMP');
EXECUTE_QUERY;
END IF;
Encapsulating the logic
Both approaches have pros and cons and both have in common the same problems when a form is called from more than one other form:-The names of the parameters/globals must match on both sides.-The passed values must match on both sides-When enhancing the called form its sometimes hard to find all calling forms to adjust the callSo i took the following approach to "hide the complexity" and make it quite straight-forward to do the communication:-Create a pll to contain all communication logic.-For each form that can be called, create a package in it-Inside each package, create the following functions/procedure- a procedure to call the form, having all the parameters needed to call as IN-parameters, or- a function to call the form, having all the parameters needed to call as IN-parameters and returning the result- a procedure for retrieving the callers parameters from the package to the called form.- a procedure to set the return-value from the called form into the package.- All communication with the called form in done exclusively using this packageIn case there are different modes or variants to call a form, there may also be different "calling"-procedures/functions.This approach has some (in my eyec big) advantages:-The way the communication between caller and calling-form is implemented is hidden and doesn't really matter to the caller-All usage of Global-names/parameternames is only done in this package, so the chance of having typos in it is quite small-Parameters are given with its "real" datatype and can even be checked before calling the form.-If the signature to call a form must change the signature of the package has to be changed, and with a recompile it's quite eays to find modules using the changed module, for they will no longer compileSo, lets change the example to match this approach:First, heres the code for the package:
PACKAGE PK_EDIT_EMP IS
-- Call the dialog to edit the employee with the given ID
-- function return TRUE if user changed the Employee, else FALSE
FUNCTION FK_EDIT_EMPLOYEE(i_nEmployeeId IN NUMBER)
RETURN BOOLEAN;
-- retrieve the callersparameters
-- o_vcMode will be EDIT for editing NEW for creating a new Employee
-- o_nEmployeeId will give the Id of the employee to be queried when o_vcMode is EDIT
PROCEDURE PR_GET_PARAMETERS(o_vcMode OUT VARCHAR2,
o_nEmployeeId OUT NUMBER);
-- Sets the return-value
PROCEDURE PR_SET_RETURN(i_bSaved IN BOOLEAN);
END;
PACKAGE BODY PK_EDIT_EMP IS
VCC_GLOBAL_MODE VARCHAR2(30):='GLOBAL.EDIT_EMP_MODE';
VCC_GLOBAL_EMPID VARCHAR2(30):='GLOBAL.EDIT_EMP_EMPID';
VCC_GLOBAL_RETURN VARCHAR2(30):='GLOBAL.EDIT_EMP_RETURN';
FUNCTION FK_EDIT_EMPLOYEE(i_nEmployeeId IN NUMBER)
RETURN BOOLEAN IS
BEGIN
-- Set the globals
COPY('FALSE', VCC_GLOBAL_RETURN);
COPY('EDIT', VCC_GLOBAL_MODE);
COPY(TO_CHAR(i_nEmployeeId), VCC_GLOBAL_EMPID);
-- call the form
CALL_FORM('EDIT_EMP1',NO_HIDE,DO_REPLACE,NO_QUERY_ONLY);
-- return result
RETURN (NAME_IN(VCC_GLOBAL_RETURN)='TRUE');
END;
-- ----------------------------------------------------------------
PROCEDURE PR_GET_PARAMETERS(o_vcMode OUT VARCHAR2,
o_nEmployeeId OUT NUMBER) IS
BEGIN
-- transfer globals to out-params
o_vcMode:=NAME_IN(VCC_GLOBAL_MODE);
o_nEmployeeId:=TO_NUMBER(NAME_IN(VCC_GLOBAL_EMPID));
END;
-- ----------------------------------------------------------------
PROCEDURE PR_SET_RETURN(i_bSaved IN BOOLEAN) IS
BEGIN
-- map boolean to Char-result
IF i_bSaved THEN
COPY('TRUE', VCC_GLOBAL_RETURN);
ELSE
COPY('FALSE', VCC_GLOBAL_RETURN);
END IF;
END;
END;
Now the WHEN-BUTTON-PRESSED-trigger in OVERVIEW:
IF PK_EDIT_EMP.FK_EDIT_EMPLOYEE(:EMP.EMPLOYEE_ID) THEN
-- Requery data
GO_BLOCK('EMP');
EXECUTE_QUERY;
END IF;
The WHEN-NEW-FORM-INSTANCE-trigger in EDIT_EMP:
DECLARE
vcMode VARCHAR2(4);
nEmployeeId NUMBER;
BEGIN
PK_EDIT_EMP.PR_GET_PARAMETERS(vcMode, nEmployeeId);
-- to avoid using the globals directly in the form, created a item in y control-block
-- and restrict the query on that
:BL_CONTROL.IT_EMPLOYEE_ID:=nEmployeeId;
-- go to the employee-block
GO_BLOCK('EMP');
-- execute the query (the block has a WHERE-condition using EMPLOYEE_ID=:BL_CONTROL.IT_EMPLOYEE_ID
EXECUTE_QUERY;
-- Restrict createing new records
SET_BLOCK_PROPERTY('EMP', INSERT_ALLOWED, PROPERTY_FALSE);
END;
Note that for avoiding to "break" the encapsulation i created a control-block with an item to store the employee_id in.And last, the two button "Save"
IF :SYSTEM.FORM_STATUS='CHANGED' THEN
DO_KEY('COMMIT_FORM');
PK_EDIT_EMP.PR_SET_RETURN(TRUE);
EXIT_FORM;
END IF;
and "Cancel"
PK_EDIT_EMP.PR_SET_RETURN(FALSE);
EXIT_FORM(NO_VALIDATE);
Subscribe to:
Posts (Atom)