| How to create datablock based on procedure [message #152358] |
Thu, 22 December 2005 12:57  |
sikorsky05
Messages: 40
Registered: October 2005
|
Member |
|
|
I have this procedure below . After creating it I went into Form builder and choose Data block wizard and they choose Create block from stored procedure... when I entered get_rowcnt and hit refresh didn't see anything.. What do I need to modify in order for this to work .
CREATE OR REPLACE procedure get_rowcnt
IS
v_cnt number;
cursor c1 is
select table_name from count;
v_str varchar2(1000);
begin
for rc in c1 loop
v_str := 'SELECT count(*) from ' || rc.table_name || ' where model = ''UH60M'' and group_Code = ''P3I'' and archive_number = 0';
return;
execute immediate v_str into v_cnt;
dbms_output.put_line('Number of rows in table ' || rc.table_name || ' = ' || v_cnt);
end loop;
end get_rowcnt;
/
|
|
|
|
|
|
| Re: How to create datablock based on procedure [message #153261 is a reply to message #152358] |
Fri, 30 December 2005 09:23  |
RJ.Zijlstra
Messages: 104
Registered: December 2005
Location: Netherlands - IJmuiden
|
Senior Member |
|
|
Hello Sikorski,
What you want can be done, but absolutely NOT in the way you tried.
If you want to base a form on a procedure, you must not base it on a procedure but on a package (nice how Oracle termonology works he?)
Normally Forms will do a lot for you if you use the wizard. Now remember this: everything you want the Form to do, MUST be coded by you. So, normally it is a pita. But to give you some idea how to do this:
CREATE OR REPLACE PACKAGE TEST.BO_JNL010F
IS
Type type_par is Record(
JNL_ID B_Journaal.ID%type,
Boeknr B_Boeknr.boeknr%type,
HoofdRek B_Transactions.naam%type,
SubRek B_Sub_BTR.naam%type,
Leverancier Leveranciers.naam%type,
Debet B_Journaal.debet%type,
Credit B_Journaal.credit%type,
Factuur_Datum date,
Betaal_Datum date
);
Type type_Tab_Type_Par is table of type_par index by binary_integer;
--
procedure sql_query(P_Table IN OUT type_Tab_Type_Par);
procedure sql_delete(P_Table IN OUT type_Tab_Type_Par);
procedure sql_update(P_Table IN OUT type_Tab_Type_Par);
procedure sql_insert(P_Table IN OUT type_Tab_Type_Par);
procedure sql_lock(P_Table IN OUT type_Tab_Type_Par);
--
procedure Set_Record(P_JNL IN OUT Type_Par);
--
END BO_JNL010F;
Define a recordtype ( here type_par ). This record will contain every item on your form. ( Normally these are the columns of the table(s) you work with). You can make the items anything you like, AS LONG as they are in your form.
Then create Type type_Tab_Type_Par is table of type_par index by binary_integer. So a type of table index by binary_integer of the records you defined.
Then you got to tell Forms what to do if Forms:
commit / deletes / queries /updates/ inserts or locks.
You MUST create ALL of these procedures, even if you only put 'NULL' in it.
You create the package specification, make for now an empty body and ONLY NOW you are ready to use Forms.
Use the wizard and point to this package and follow the steps.
--
Then create in the package body the things Forms must do when it inserts the fields from your Form.
The NICE thing is you can insert into 10 tables at once, write a log file etc etc AS LONG as you define these actions in your insert procedure.
The BAD thing is, that you must code EVERYTHING. ( I assure you, you will obtain a whole new set of 'nice' words...)
Updating etc the same.
Well, I think I gave you enough to think about for now.
Almost forget to tell you the nicest thing:
If you are connected to Forms and connected to your procedure editor (sql*plus/ toad etc) and you recompile after a change, then if you compile Forms, your system freezes... You MUST disconnect/reconnect Forms after a recompile.
Happy New Year
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
