procedure based data block [message #316397] |
Thu, 24 April 2008 23:47  |
vaibhavi
Messages: 21 Registered: April 2008
|
Junior Member |
|
|
hi,
I want to create procedure based data block. I want to select data through procedure and display it in form.
When i tried to do it i got the error that procedure should have argument of table or refcursor type.
Can anyone give me example of procedure that can be used to crate data block.
Thanks.
|
|
|
|
Re: procedure based data block [message #316462 is a reply to message #316425] |
Fri, 25 April 2008 03:49   |
vaibhavi
Messages: 21 Registered: April 2008
|
Junior Member |
|
|
hi,
Thanks for reply.
I crated the package and procedure like the above example in oracle sql*plus.
Is it right?
Should i use this in form while creating data block?
Please let me know?
Thanks.
|
|
|
|
Re: procedure based data block [message #316490 is a reply to message #316467] |
Fri, 25 April 2008 06:22   |
vaibhavi
Messages: 21 Registered: April 2008
|
Junior Member |
|
|
hi,
I made the data block on the procedure which is on emp table and i am passing empno and table type parameters same as in above example.
But I did not get that, we have passed the parameters in procedure. How data will be fetched in form? I run the form but it gave me error that "query caused no records to be retrieved.
Should i have to pass the empno and if yes where i have to pass it?
Please let me know.
Thanks.
|
|
|
|
Re: procedure based data block [message #316514 is a reply to message #316505] |
Fri, 25 April 2008 08:10   |
vaibhavi
Messages: 21 Registered: April 2008
|
Junior Member |
|
|
Yes, in property palette
Query Data Source Type is procedure
Query Data source name is employee_pkg.do_query in my case.
In query data source columns and arguments column names are automatically generated.
How the data will be displayed while running form?
|
|
|
|
Re: procedure based data block [message #316744 is a reply to message #316556] |
Mon, 28 April 2008 03:23   |
vaibhavi
Messages: 21 Registered: April 2008
|
Junior Member |
|
|
hi,
Yes, Forms Builder automatically created the QUERY-PROCEDURE block-level trigger.
It has code like
DECLARE
bk_data EMPLOYEE_PKG.EMP_TBL;
BEGIN
EMPLOYEE_PKG.DO_QUERY(bk_data, '');
PLSQL_TABLE.POPULATE_BLOCK(bk_data, 'PROC_BASED_BLOCK');
END
|
|
|
|
|
|
Re: procedure based data block [message #316796 is a reply to message #316792] |
Mon, 28 April 2008 06:38   |
vaibhavi
Messages: 21 Registered: April 2008
|
Junior Member |
|
|
While clicking on display error I got,
FRM-42100 No errors encountered recently.
But it is not showing data.
My employee_pkg specification is
package employee_pkg is
type emp_rec is record
(empno emp.empno%type,
job emp.job%type);
type emp_tbl is table of emp_rec index by binary_integer;
procedure do_query(p_emp_table in out emp_tbl,
p_emp_id in emp.empno%type);
end employee_pkg;
My employee_pkg body is
package body employee_pkg is
procedure do_query(p_emp_table in out emp_tbl,
p_emp_id in emp.empno%type) is
cursor emp_cur(p_id emp.empno%type) is
select empno,job from emp where empno=p_id;
idx number:=1;
begin
for rec in emp_cur(p_emp_id) loop
p_emp_table(idx):=rec;
idx:=idx+1;
end loop;
end;
end employee_pkg;
Let me know if anything wrong in it.
|
|
|
|
Re: procedure based data block [message #334207 is a reply to message #316806] |
Tue, 15 July 2008 16:54   |
cindyconlin
Messages: 3 Registered: July 2008
|
Junior Member |
|
|
Hi,
I'm having a similar problem and am wondering if you ever discovered the answer to this.
I've got a block based on a stored procedure, returning a ref cursor. The stored proc works find when I call it from SQL*Developer, but I receive a "FRM-40301: Query caused no records to be retrieved." when I run it through forms.
I put a bogus column into "Query Data Source Arguments" to force a failure and see if that would help me identify the problem, and it did cause a "FRM-40505: ORACLE error: unable to perform query". But, when I tried to view the error through Forms, I received "FRM-42100: No errors encountered recently".
Just wondering if you were able to get past this, and if so, how.
|
|
|
|
Re: procedure based data block [message #339467 is a reply to message #339142] |
Thu, 07 August 2008 09:58   |
cindyconlin
Messages: 3 Registered: July 2008
|
Junior Member |
|
|
The problem is now resolved. I was passing a literal value as an input paramter to the stored procedure, and had specified the value in single quotes in the Query Data Source Arguments screen. Once I removed the single quotes, then it worked.
Unfortunately, this was really tricky to debug. Using the message() function didn't help me find anything....I just kind of stumbled across the answer. I didn't find a way to get any visibility into the actual code Forms sends to the server when you base a block on a stored procedure.
|
|
|
|
|
|
Re: procedure based data block [message #348531 is a reply to message #316397] |
Wed, 17 September 2008 02:49   |
kalid
Messages: 8 Registered: September 2008
|
Junior Member |
|
|
I have a problem.
I have a data block based on a stored procedure (with parameters). Everything works fine, except this: I have a push button 'X' that change the mode Enter-query / Execute-query.
I am in Enter-query mode, I write something in one item (that is a datatbase item, of course), I press the 'X' button (to change the form in Execute-query mode), tha data is retrieved in the from (grid). But it didn't respect my criteria! The query executed is that in the procedure. The proceudre returns a table of records. I tried with a ref cursor and is the same thing.
So, if I have a data block based on a stored procedure, I can't make any restriction at runtime for retrieving data? (I hope I was clear...)
Thanks.
|
|
|
|
|