Home » Developer & Programmer » Forms » procedure based data block (oracle 10g)
procedure based data block [message #316397] Thu, 24 April 2008 23:47 Go to next message
vaibhavi
Messages: 21
Registered: April 2008
Junior Member
hi, Razz

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 #316425 is a reply to message #316397] Fri, 25 April 2008 02:04 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's an example of a packaged procedure:
CREATE OR REPLACE PACKAGE Pkg_View
IS
   TYPE ocit_record_type IS RECORD (
      mm_id           TOB_MM.mm_id%TYPE,
      oci_id          TOB_O.oci_id%TYPE,
      datum           DATE
   );

   TYPE idxby_ocit_type IS TABLE OF ocit_record_type
      INDEX BY BINARY_INTEGER;

   idxby_ocit_table   idxby_ocit_type;

   PROCEDURE obview (
      par_table       IN OUT   idxby_ocit_type,
      par_mm_id       IN       TOB_MM.mm_id%TYPE,
   );
END Pkg_View;

"par_table IN OUT idxby_ocit_type" is what Oracle is complaining about in your code.
Re: procedure based data block [message #316462 is a reply to message #316425] Fri, 25 April 2008 03:49 Go to previous messageGo to next message
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 #316467 is a reply to message #316462] Fri, 25 April 2008 04:22 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So far, so good.

Now open Forms Builder and create data block based on this procedure.
Re: procedure based data block [message #316490 is a reply to message #316467] Fri, 25 April 2008 06:22 Go to previous messageGo to next message
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 #316505 is a reply to message #316490] Fri, 25 April 2008 07:12 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Data block property palette should look like this:

./fa/4191/0/

Did you create "Query Data Source Columns"? In there, you should put all columns that are making up a procedure result (i.e. fields on the canvas).

"Query Data Source Arguments" are parameters used in the procedure (which would be "par_table" and "par_mm_id" in my example).
Re: procedure based data block [message #316514 is a reply to message #316505] Fri, 25 April 2008 08:10 Go to previous messageGo to next message
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 #316556 is a reply to message #316514] Fri, 25 April 2008 12:41 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did Forms Builder automatically create the QUERY-PROCEDURE block-level trigger? How does it look like? (be careful - don't edit it!).
Re: procedure based data block [message #316744 is a reply to message #316556] Mon, 28 April 2008 03:23 Go to previous messageGo to next message
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 #316758 is a reply to message #316744] Mon, 28 April 2008 04:20 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK; so, what happens when you execute a query?
Re: procedure based data block [message #316759 is a reply to message #316758] Mon, 28 April 2008 04:23 Go to previous messageGo to next message
vaibhavi
Messages: 21
Registered: April 2008
Junior Member
It is giving error FRM-40301.
Query caused no records to be retrieved.
Re: procedure based data block [message #316792 is a reply to message #316759] Mon, 28 April 2008 06:23 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Go to Help menu and select "Display Error"; copy-paste what's in there so that we could see what happened.
Re: procedure based data block [message #316796 is a reply to message #316792] Mon, 28 April 2008 06:38 Go to previous messageGo to next message
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 #316806 is a reply to message #316796] Mon, 28 April 2008 07:13 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, learn how to format your code and use of [code] tags which will preserve formatting (it can be found in the OraFAQ Forum Guide).

I've slightly modified your code in order to check what you've done:
SQL> create or replace package body employee_pkg is
  2    procedure do_query(p_emp_table in out emp_tbl,
  3                       p_emp_id in emp.empno%type)
  4    is
  5      cursor emp_cur(p_id emp.empno%type) is
  6        select empno,job from emp where empno=p_id;
  7      idx number:=1;
  8    begin
  9
 10      p_emp_table.delete;
 11
 12      for rec in emp_cur(p_emp_id) loop
 13        p_emp_table(idx):=rec;
 14        idx := idx + 1;
 15      end loop;
 16
 17        IF p_emp_table.last > 0 THEN
 18           FOR i IN 1 .. p_emp_table.LAST
 19           LOOP
 20              DBMS_OUTPUT.put_line (p_emp_table (i).empno || ' ' ||
 21                                    p_emp_table (i).job);
 22           END LOOP;
 23       END IF;
 24    end;
 25  end employee_pkg;
 26  /

Package body created.
Let's see what has happened:
SQL> set serveroutput on
SQL>
SQL> DECLARE
  2    bk_data employee_pkg.emp_tbl;
  3  BEGIN
  4    employee_pkg.do_query(bk_data, 7782);
  5  END;
  6  /
7782 MANAGER

PL/SQL procedure successfully completed.

SQL>

So, it seems that package is OK.

Could you post the FMB file so that we could see what's been done there?
Re: procedure based data block [message #334207 is a reply to message #316806] Tue, 15 July 2008 16:54 Go to previous messageGo to next message
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 #339142 is a reply to message #334207] Wed, 06 August 2008 21:26 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you solved your problem? Have you placed 'message(); pause;' pairs through your code so you know exactly which statement is failing?

David
Re: procedure based data block [message #339467 is a reply to message #339142] Thu, 07 August 2008 09:58 Go to previous messageGo to next message
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 #339678 is a reply to message #339467] Fri, 08 August 2008 02:16 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Then perhaps you should have displayed the literals that you were trying to pass.

David
Re: procedure based data block [message #339797 is a reply to message #339678] Fri, 08 August 2008 09:15 Go to previous messageGo to next message
cindyconlin
Messages: 3
Registered: July 2008
Junior Member
In which trigger would I have put the message statements to display the literals that Forms was sending to the stored procedure?
Re: procedure based data block [message #341208 is a reply to message #339797] Sun, 17 August 2008 21:01 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Create some items in your control block, display them temporarily on the canvas, and place the data into these items.

David
Re: procedure based data block [message #348531 is a reply to message #316397] Wed, 17 September 2008 02:49 Go to previous messageGo to next message
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.
Re: procedure based data block [message #348786 is a reply to message #348531] Wed, 17 September 2008 23:35 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Google 'oracle forms "from clause query"'.

David
Re: procedure based data block [message #348788 is a reply to message #348531] Wed, 17 September 2008 23:37 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Also, please do not hijack other threads.

David
Previous Topic: moves between two blocks
Next Topic: ORA-04067 when calling fmx
Goto Forum:
  


Current Time: Mon Dec 05 05:15:07 CST 2016

Total time taken to generate the page: 0.15590 seconds