Oracle Forms 4.5.7 ROWTYPE

I have created a function that return a ROWTYPE in the Oracle Database.
However, my Oracle Forms version 4.5.7 seems does not support that, or I should not implement like this? are there any other solutions?

The code below is the function I have created in the database.
 
CREATE OR REPLACE
FUNCTION SP_PRODUCT_fn_GET_REC (
        pt_prod_code IN      PRODUCTS.PROD_CODE%TYPE
	) 
RETURN PRODUCTS%ROWTYPE
IS
   r_ProdRec   PRODUCTS%ROWTYPE;
   
    CURSOR c_Prod IS 
	SELECT  *
	FROM	PRODUCTS
	WHERE 	PROD_CODE = pt_prod_code;
BEGIN
    OPEN c_Prod;
    FETCH c_Prod INTO r_ProdRec;
    CLOSE c_Prod;
    RETURN r_ProdRec;

END SP_PRODUCT_fn_GET_REC;
/

Open in new window


The code below is the partial code in the Oracle Forms.
 
DECLARE
pt_prod_rec   PRODUCTS%ROWTYPE;

BEGIN
    pt_prod_rec := SP_PRODUCT_fn_GET_REC('12345678');

END;

Open in new window

Billy MaVice PresidentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Naveen KumarProduction Manager / Application Support ManagerCommented:
why not test this in forms 6i ?  As you said, this could be a limitation in forms 4.5.x

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Naveen KumarProduction Manager / Application Support ManagerCommented:
Can you try this to see if this works. I am using a procedure and an OUT variable to get that values back to the calling block.

CREATE OR REPLACE procedure SP_PRODUCT_fn_GET_REC (
        pt_prod_code IN      PRODUCTS.PROD_CODE%TYPE,
      retvar OUT PRODUCTS%ROWTYPE
      )
IS
   r_ProdRec   PRODUCTS%ROWTYPE;
   
    CURSOR c_Prod IS
      SELECT  *
      FROM      PRODUCTS
      WHERE       PROD_CODE = pt_prod_code;
BEGIN
    OPEN c_Prod;
    FETCH c_Prod INTO retvar;
    CLOSE c_Prod;

END SP_PRODUCT_fn_GET_REC;
/


DECLARE
pt_prod_rec   PRODUCTS%ROWTYPE;
BEGIN
    SP_PRODUCT_fn_GET_REC('12345678',pt_prod_rec);
-- you can use pt_prod_rec.<<column_name>> to use the returned values
END;
/
Billy MaVice PresidentAuthor Commented:
nav_kum_v, I can't use 6i, becoz this is using in the company...
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Naveen KumarProduction Manager / Application Support ManagerCommented:
Fine. did you try the procedure code i had given ?
Billy MaVice PresidentAuthor Commented:
yes tired, but doesn't work too...............
what is the workaround in old form?
Naveen KumarProduction Manager / Application Support ManagerCommented:
yes tired, but doesn't work too............... ----> What is the error you are getting ? Give me the error / screenshot . I think that should work fine.

What i gave is like a workaround only right ?
Billy MaVice PresidentAuthor Commented:
some parts were hided.
form.jpg
Naveen KumarProduction Manager / Application Support ManagerCommented:
you have done a mistake ... you are calling the procedure incorrectly. It should be called as i had given in my previous update " SP_PRODUCT_fn_GET_REC('12345678',pt_prod_rec); "

not like as you doing it with the below because a procedure cannot return anything right ?

GET_RESULT := SP_PRODUCT_fn_GET_REC('        ',pt_prod_rec);

Test it out again by calling properly.

Thanks
Billy MaVice PresidentAuthor Commented:
form
Naveen KumarProduction Manager / Application Support ManagerCommented:
I think this procedure is not yet created in your database.

CREATE OR REPLACE procedure SP_PRODUCT_pr_GET_REC (
        pt_prod_code IN      PRODUCTS.PROD_CODE%TYPE,
      retvar OUT PRODUCTS%ROWTYPE
      )
IS
   r_ProdRec   PRODUCTS%ROWTYPE;
   
    CURSOR c_Prod IS
      SELECT  *
      FROM      PRODUCTS
      WHERE       PROD_CODE = pt_prod_code;
BEGIN
    OPEN c_Prod;
    FETCH c_Prod INTO retvar;
    CLOSE c_Prod;

END SP_PRODUCT_pr_GET_REC;
/

Also once it is created, check in DBA_OBJECTS for the valid status.

select *
from dba_objects
where object_name ='SP_PRODUCT_PR_GET_REC';

Thanks
Billy MaVice PresidentAuthor Commented:
Naveen KumarProduction Manager / Application Support ManagerCommented:
What is the data type of the PROD_CODE column in the products table ?

Also check in which user the procedure was created ? Is that the same user to which the forms is connecting ? Can you please verify ?

Thanks
Billy MaVice PresidentAuthor Commented:
PROD_CODE  VARCHAR2(18)

Both PROCEDURE created and CONNECTION OF FORM are the same user.
So I just change the OUT parameter from ROWTYPE to VARCHAR2, to test it is ok.

The problem is why ROWTYPE passing with cause error when complie the form.
Naveen KumarProduction Manager / Application Support ManagerCommented:
Ok. Then i think it is boiling down to the same limitation of the Forms 4.5.x.

1) Why not just do a direct select from the table instead of calling a procedure here because all you need is the record values right ?

2) if you think you need only procedure for some reason, then i can only think of having that many OUT variables as that many columns but this does not sound a good idea to me.

Thanks

Naveen KumarProduction Manager / Application Support ManagerCommented:
for the 1) option, why not just this select instead of procedure call...

   SELECT  *
         into prod_rec
      FROM      PRODUCTS
      WHERE       PROD_CODE = pt_prod_code;
Billy MaVice PresidentAuthor Commented:
haha, becoz my colleague want to get the entire row rather than having lots of OUT variables
Naveen KumarProduction Manager / Application Support ManagerCommented:
what about option 1) with the select alone.

I understand option 2 is not that great idea to have them.
flow01IT-specialistCommented:
It's the Oracle forms 4.5 version that doesn't allow the rowtype.  I wrote (I think 1996) wrapper procedures to transform the rowtype-variable in all necessary OUT-variables) .
In later versions of Oracle forms rowtypes can be used as return variable.
Billy MaVice PresidentAuthor Commented:
Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.