forms development

[zapata]

Ok, this forum i was told is great because quick responses.
Please I need u'r help.
I am creating forms and I have a field called phone_number.
What I want to do is have the phone form populate phone_number fields 3 times and caller's firs_name(caller1), madain_name(caller2), nick_name(caller3) on forms.

example:
caller_name Phone_number
caller1 phone_fax
caller2 phone_work
caller3 phone_home

This is like stored procedure where you can say
create or replace phoneType(
phone_work emp.phone_number%type,
phone_home emp.phone_number%type,
phone_fax emp.phone_number%type,
caller1 emp.caller_name%type,
caller2 emp.caller_name%type,
caller3 emp.caller_name%type

Any help will real nice!
peace!!
zapata

 

[sem]

So what is your specific problem? Just create the form, block and items you need. Then create some button and/or menu item calling your procedure.

 

[zapata]

sem, thank you very much for responding.
Here is what my problem is.
I have 6 tables to select 38 fields from on one block.
29 of those fields come from one table.
Now I need to add the remaining 9 fields on the same block and some of those fields like I stated above have multiple values for one field like:

Caller_Name Phone
----------------------
caller_name1 phone1
caller_name2 phone2
caller_name3 phone3

How can I get all fields from different tables and stored on one block?
I tried master-detail and on the property pallete on COPY VALUE FROM: I selected table1.id. It is not displaying the result from the rest of the other tables.
I hope my problem is explained properly. If not please ask me to explain further.

 

[sem]

If your tables are involved in one-to-many relation with your 29-field table at one-end, you may create a block based on this table and a number of blocks, based on related tables. Pleas do not ommit "add relation" step when creating additional blocks or add this relations to the "base" block manually later. If you're not planning to edit data in related tables you may create one block for "base" table and a set of non base table items, populated during post-query processing. You may use TLists to display a number of related records. The main disadvantage of these techniques is that you have to create your own "query by example" procedure for non-base-table items or allow quereing base table items only.

 

[zapata]

thanks sem!
I wrote a stored procedure and the trigger to call each non-base table but I am running into "bad bind variable' error.
I am pasting the stored proc and the trigger to call.
Please see if there is something you can do to help me.
CREATE OR REPLACE PROCEDURE OTHER_NAMES (
IN_DOCTOR_CODE IN DOCF.DOCCODE%TYPE,
CALNAM OUT DOCF.LNAME%TYPE,
REQNAM OUT DOCF.LNAME%TYPE,
RESNAM OUT DOCF.LNAME%TYPE) IS
cursor a1 is
select Lname
from docf
where doccode = IN_DOCTOR_CODE;
a1_rec a1%ROWTYPE;
begin
-- open the doctor cursor
open a1;
fetch a1 into a1_rec;
Calnam := a1_rec.LName;
fetch a1 into a1_rec;
Reqnam := a1_rec.LName;
fetch a1 into a1_rec;
Resnam := a1_rec.LName;
end;

*****************************
test script
******************************
DECLARE
NAME VARCHAR2(35);
BEGIN
-- Call the Forms Procedure to get the
-- count of others in the DOCF.
OTHER_NAMESDOCF.DOCCODE, NAME,NULL,NULL);
-- Assign the namet to the field on
-- the DOCF block.
OCF.CALNAM := NAME;
END;

 

[zapata]

sem,
please be patient with my threads on this issue.
According to response you gave(great by the way).
I have some difficulty putting it all together for following reasons.

If a caller calls looking for a certain information, an operator will do a search to see if that info is available. If it is not, it will be inserted and saved for future calls.
The problem then arises because my boss wants me to design the form in such a way that even though items will be pulled from 7 different tables, all items must be on one block.
So if I make some of them non-database items, no inserts will be allowed and they have to be on one block.
Do you or anyone know of a workaround?
This has been beating on me now for a long time.
Please anyone who can help, please help. sorry for the long thread.

 

[sem]

First of all about bind variable: you can not pass literals (null) for out parameters, you have to provide some place to store the result. So your procedure may be as follows
DECLARE
NAME VARCHAR2(35);

-- DATATYPES ARE JUST ASUMPTION
REQ VARCHAR2(35);
RES VARCHAR2(35);
BEGIN
-- Call the Forms Procedure to get the
-- count of others in the DOCF.
OTHER_NAMESDOCF.DOCCODE, NAME,REQ,RES);
-- Assign the namet to the field on
-- the DOCF block.
OCF.CALNAM := NAME;
END;

Then, your procedure OTHER_NAMES is a bit suspicious. The idea is that Oracle does not store tables as lists, so your sequencial fetches may return data in arbitrary order.

Does your boss really want your items to be in one block? Maybe the same window/canvas satisfy him? It will look like a single block if you'll remove block decorations and draw it manually. But if one block is the requirement you have to create you own insert procedures . You may also create a view and instead-of truggers to insert/update data in base/lookup tables.

 

[zapata]

thanks for your continued help, sem!
well, your idea of having one window is actually what I am trying to accomplish but still the way the original design was done, you have an item lets call it person_id as a database item.
then you have person_name as a non-database item.
so the idea is to populate person_name based on a join of person_id from one table and another id from another table.
in some cases, the id from where a value will be derived to populate the non-database item is singularly from another table.
but my bigger problem now is you raised the issue that my procedure is a bit 'suspicious'.
based on the procedure that i posted, can you modify it for me?
thanks again(from the heart)

 

[sem]

Unfortunately no. I do not know your docf table structure. According to your procedure I can not even imagine how to get doctor's properties by doctor's code correctly. If you have additional field, containig information on type of property stored in Lname - lucky you are. If you or table's creator supposed that the lines would be retreived in the same order they were inserted - this is a great mistake. Oracle stores data in free blocks regardless on their physical position. If you use parallel query option the rows may be inserted COMPLETELY INDEPENDANTLY. If you do not use - Oracle gets fre block from some list filled from both newly allocated space and freed by updates/deletes, possibly made by other processes. So try to base your procedure on this asumption.
Good luck.

 

[zapata]

sem, you have been a professional and great help.
thank you for your time and helpful hints and tips!