problem in form v4.5

[shafiqm]

Hi All,
I know that it is very simple in form to call any stored procedure. I created a package with lots of procedures in oracle7 for example mypackage, now i want to call one of the procedure in form (v4.5), lets suppose mypackage.myproc_1. But when i call the stored procedure through form, it gives me the PL/SQL 303 (qualifier <qualifer name> must be defined) error message. calling of same procedure is working fine in SQL*Plus. Is this a form version problem? If anyone of you come accross this situation, please let me know how to resolve it.

Thanks in advance

 

[lewisp]

First of all, are you connecting to the same user in forms as you are in sqlplus?

Second, are any parameters in the procedure defaulted? What datatypes of parameters are you passing in/out?

 

[shafiqm]

(1)Yes, I connected to the same user.
(2)I have not defined any default value and i am using
%type with parameters to get the data type from database.

 

[lewisp]

Whats the exact version number you are running? I have 4.5.7.0.10 and it seems to work fine based on your descriptions of the procedure.

Have you considered upgrading to Forms 6 or 6i?

 

[sem]

There may be a number of issues:
1. You can not access packaged variables from Forms
2. You can not access pl/sql v2+ types, e.g. binary_integer, pl/sql tables etc.

Regards, Dima

 

[shafiqm]

I like to add some more information about this problem for you to find out a better solution.

When i change either package name or procedure name then form accept it and reads it, does not give any error message.
when i dont use the %type to any of these parameter, it works but sooner i use the %type it gives me error message. See the following example:
(1)It works and form does not complain.
procedure myproc(col1 in mytable.col1%type,
col2 in mytable.col2%type,
col3 in varchar2)-- i did not use %type
(2)It does not work, and form does not read the package declaration from database.
procedure myproc(col1 in mytable.col1%type,
col2 in mytable.col2%type,
col3 in mytable.col3%type) -- i used %type
(3)Here i changed the name of procedure, now it works.
procedure myproc2(col1 in mytable.col1%type,
col2 in mytable.col2%type,
col3 in mytable.col3%type)

I did not understand what is wrong?.
My problem is that:
(1)I am not authorized person to upgrade the version of form. company wants to stick with this version because of some reason.
(2)I cannot change the package and procedure names.
(3)I cannot use fixed data type for any parameter , like (col1 in varchar2). I have to use (col1 in mytable.col1%type).

Hope u will understand my problem and keep giving me feed back.

Thanks in advance.

Regards

 

[lewisp]

Do you get this problem in the form builder or at runtime?

Have you tried dropping and re-creating the procedure in the database?

Have you tried disconnecting and reconnecting in your forms builder before recompiling?

It might be something to do with database peocedure timestamps, but I've not come across this one before in 4.5. Clutching at straws, what is the REMOTE_DEPENDENCIES set to on your database?

 

[shafiqm]

(1) I get this problem at compile time.
(2) Yes i tried so many times, dropping and re-creating this package.
(3) Also so many times I re-connecting to form desinger and then tried to re-compile it

But i am still getting error message

My database REMOTE_DEPENDENCIES_MODE is set to TIMESTAMP.

 

[lewisp]

I've just done a search on Oracle Metalink. They say the following in one of their bulletins:

[tt]If you call a stored database subprogram from Oracle Forms, the only supported datatypes for any parameters or return values of the subprogram are:
VARCHAR2 - maximum VARCHAR2(2000)
NUMBER
DATE
BOOLEAN

Parameters that are defined using the %TYPE attribute are also unsupported, even though their expansion may refer to one of the supported datatypes above.[/tt]

Elsewhere thay also say

[tt]There are 13 objects that need to be on the server and VALID for it to create a stub.[/tt] They use stubs to work aorund the incopmatabilities between PL/SQL v1 in Forms 4.5 and PL/SQL v2 on the database.

[tt]You can check the status of these objects by running the following script from SQLPLUS while connected as SYS:

COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A12
SELECT object_name, object_type, status
FROM all_objects
WHERE object_name IN
('PSTUBT', 'PSTUBTBL', 'PIDL', 'DIUTIL','DIANA', 'STANDARD', 'DBMS_STANDARD')
ORDER BY 1,2;

The correct result of this query is as follows:
OBJECT_NAME OBJECT_TYPE STATUS
----------------------- --------------- -------
DBMS_STANDARD PACKAGE VALID
DBMS_STANDARD PACKAGE BODY VALID
DBMS_STANDARD SYNONYM VALID
DIANA PACKAGE VALID
DIANA PACKAGE BODY VALID
DIUTIL PACKAGE VALID
DIUTIL PACKAGE BODY VALID
PIDL PACKAGE VALID
PIDL PACKAGE BODY VALID
PSTUBT PROCEDURE VALID
PSTUBTBL TABLE VALID
STANDARD PACKAGE VALID
STANDARD PACKAGE BODY VALID
[/tt]

Does that help or make it worse?

 

[shafiqm]

Thanke a lot 'lewisp' helping me to resolve this problem. My one coleage has solved it.

 

[lewisp]

Can you tell us what the solution is please?

 

[shafiqm]

Sure i would like to share the solution of this problem.
What i was doing, everytime i drop and re-create the package in SQL*Plus (sequel). But one of my colleaque said try to drop the this package from 'form designer'.
I go to object navigator,
click on 'database' node,
select the user, i cnnected to.
click on <username> node.
select the 'stored program units'
select the required package that was giving me errors.
drop it from 'form designer'
re-create package in SQL*Plus.

Then i re-compile the form, amazingly it worked.

Regards,