Skip to Main Content
Forums

Oracle Forms

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-6550 - Usually a PL/SQL compilation error.

abladbSep 7 2010 — edited Sep 8 2010
Hi there,

I'm comming crazy guys. I need help. I am making a call to a DB Procedure (which exists; its correct written) from a block properties, so when I execute the query from the application, it returns the data from a cursor into the form. That should be it, but an ORA-6550 pops-up with no data return at the Execute Query Trigger, at Form Level. This is it:

BLOCK'S PROPERTIES:
- The block is a DB block.
- Query Data Source Type: Procedure
- Query Data Source Name: PackageName.ID_QUERY
- Query Data Source Columns: 9 columns, put in the same order than as called from the DB Procedure (which has a cursor on it as I will show); I declare the Type of each column with its length as at the DB Table is. NO SPACES after the name of each Column Name!!! After the last columns, I have checked there wasn't spaces either in the next place to put another Column.
- Query Data Source Arguments: 2 argument names: a cursor (type: RefCursor, Type Name: PackageName.B_CURSOR, Mode: in out) and the parameter the user inserts to make the query (Type: Number, Mode: In; Value: :block.itemName). At the place of the third argument, nothing, no spaces.

DB Procedure:

PROCEDURE ID_QUERY (CUR IN OUT B_CURSOR, PARAM IN NUMBER) IS
BEGIN

OPEN CUR FOR
SELECT A.NUM_RET,
B.NOM_NOM, B.NOM_APE1, B.NOM_APE2,
A.COD_DES,
A.COD_ULT,
C.TIP_NIV,
A.NUM_TIME
FROM AP_1 A, AP_2 B, AP_3 C
WHERE A.COD_AFE = B.COD_PER
AND A.COD_ULT = C.COD_SIT
AND A.NUM_RET = PARAM;

EXCEPTION
WHEN OTHERS THEN
OPEN CUR FOR
SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
FROM AP_RET A
WHERE A.NUM_RET = PARAM;

END ID_QUERY;

I have tryed the Query. It works!!!

The thing is SYMPLE!!! But i have the error ORA-6550, and... I don't get it!!!

I need some help. Somedy please tell me something to try.

What I've tried until now is:
- To change the Procedure's name, at DB and at Block's Properties Query Data Source Name.
- To create a new Block, with all this stuff again.
- To erase possible spaces left at the Column Names.
- To put the same order the DB Cursor Query Columns has at Block's Query Data Source Columns.
- I have checked all the Columns Types, and All the Columns Lengths types.

Please, tell me something to try. I've been 3h now with this error...

Thank you!!!

Comments

François Degrelle
Do you mean ORA-06550 ?
Where is defined the B_CURSOR cursor ?

Francois
abladb
Yes, sorry, ORA-06550.

The Cursor is declared at the Definition DB Package like this:

-- Cursor Definition
TYPE B_CURSOR IS REF CURSOR;

-- Selector Block Procedure
PROCEDURE ID_QUERY (CUR IN OUT B_CURSOR,
PARAM IN NUMBER);
François Degrelle
You need to use a strongly defined REF CURSOR
  -- Record de type EMP --
  TYPE emp_rec IS RECORD(
    empno	emp.empno%TYPE,
    ename	emp.ename%TYPE,
    job		emp.job%TYPE,
    sal		emp.sal%TYPE,
    comm	emp.comm%TYPE);

  -- REF CURSOR --
  TYPE emp_cursor IS REF CURSOR RETURN emp_rec;
  ...
Francois
abladb
I'll try and I will tell you the results.

Thank you for the help!!

:)
abladb
I still have the ORA-06550 pop-up error.

:(

Any other suggestion to try?

:)

Edited by: abladb on Sep 7, 2010 3:15 PM
François Degrelle
What line ?
abladb
I can't tell you. The application uses Company's Standards so the error comes out with the following text:

"STD0004 - The System Message ORA-06550 doesn't exist in the DataBase" which announces me the form I am making has the ORA-06550 error.

Edited by: abladb on Sep 7, 2010 3:44 PM

Edited by: abladb on Sep 7, 2010 3:45 PM
François Degrelle
If you don't know the line number, how can you develop and debug properly ?
We can spend days on this until all errors are solved.

Francois
abladb
Using the head, asking people around, searching on the Internet... and... finally, asking you guys.

That's how.

:(

Thank you for your help anyways.

;)
CraigB
Antonio,
If I understand your post correctly, you are referencing the B_CURSOR type in the Procedure ID_QUERY. The B_CURSOR type exists in a seperate database package. If this is correct, the error you are encountering in your ID_QUERY procedure is caused by the way you reference the B_CURSOR type. Since this type exists in a seperate DB Package, you have to reference the type using the Package name. Referencing B_CURSOR as you are would require the type to exist as its own object in the database. Change your procedure definition as follows and let us know if this works: 
/* Remove the << >> and just use the name of the database package. */
PROCEDURE ID_QUERY (CUR IN OUT <<DB_PKG_NAME_HERE>>.B_CURSOR, PARAM IN NUMBER) IS
BEGIN
The other option would be to move the definition of your B_CURSOR type out of your database package. 
CREATE OR REPLACE TYPE b_cursor AS OBJECT (
    /* Type attributes/definition here */
);
Hope this helps,
Craig B-)

If someone's response is helpful or correct, please mark it accordingly.
796349
Hello!
I have a problems, i need learn oracle form and reports but i don't know!!!

please help me to get the tools and book online
where i can download the tool for oracle form??? what are the tools for it??

in wait your help!!!
abladb
Sorry if took me so long to answer.

Hi Craig, you understood correctly. However, I defined that correctly too as I said in the first post:

- Query Data Source Arguments: 2 argument names: a cursor (type: RefCursor, Type Name: PackageName.B_CURSOR, Mode: in out) and the parameter the user inserts to make the query (Type: Number, Mode: In; Value: :block.itemName)

I'll try to explain again: A have a form with a database block which is the one the final user executes. That block calls through its Properties, to a procedure to have all the data. The way I call the procedure is from the Query Data Source Name Property (at the Block's Properties), as PackageName.ProcedureName.

- All the parameters of the Procedure are in the Query Data Source Columns Property.
- And all the columns I've got with data after the procedure has run are in the Query Data Source Arguments Property.

It should be right, but an Ora-06550 error comes up.

Hey guys!!! I promess to continue with this tomorrow. Here, in Spain, its 7PM so I have to leave.

Thank you for your time, and... if anyone wants to propose anything to help me, be sure tomorrow in the morning I'll be here trying it.

Thank you.

:)

Edited by: abladb on Sep 7, 2010 6:11 PM
Andreas Weiden
Welcome to the forum.

The first thing you should do is to read the forum etiquette and open up your own thread whenever you have a question. Don't hijack other ones threads.

About software, check the forms-page here http://www.oracle.com/technetwork/developer-tools/forms/overview/index-098877.html

To start with Forms, you need an oracle database (XE-edition is enough for the beginning) and the developer suite (either version 10g or 11g, 10g is easier to install).
CraigB
Oh I see. You've based your datablock on a procedure. There are two basic methods for doing this. First is using a Ref_Cursor as you are and the second is using a PL/SQL Table of Records. Now that I fully understand, I have another question. Did you use the data block wizard to create the block or are you manually setting a block up to use your procedure?

As I recall, the first time I based a block on a procedure, I tried to do everything manually and had problems. As soon as I used the wizard everything work perfect. If this is what you are doing I strongly suggest you use the datablock wizard. Take a look at My Oracle Support document ID: 66887.1 *(Basing a Block on a Stored Procedure - Sample Code)*. If you don't have access to My Oracle Support, then check out Oracle Forms 10g release 2: Demos, Tips and Techniques and scroll down to section "2.3.2 Block based on stored procedures". You can also look at Forms: Procedure Based Block for a nice demo.

Hope this helps,
Craig B-)

If someone's response is helpful or correct, please mark it accordingly.

Edited by: CraigB on Sep 7, 2010 1:30 PM
1 person found this helpful
abladb
Good Morning Craig,

Thank you for answering again!!!

I'll try what you say, and I'll answer back with the results.

Thank you!!!

PS: Actually, I think I started the block Manually. (!!!!)

;)

Thank you for the links!!!
abladb
Ok. That was it!!! To make it with the wizzard!!! However, now I have another problem related to it. When executing the query, it doesn't return anything having the error Query caused no records to be retrieved. (FRM-40301), but it should return one register.

I have tried to put the Param data (a number directly) at Query Data Source Arguments like:
- Argument name: PARAM
- Type: NUMBER
- Mode: IN
- Value: 1000000096

instead of
- Argument name: PARAM
- Type: NUMBER
- Mode: IN
- Value: :BlockName.ItemName

and it neither works!!!

Then, I've tried to put the Param data at the procedure (using Toad), and it works. Returns one register.

So, what now? Anything else to try??

Editing:
I'm realizing that when I create a Store Procedure using the Wizzard, the wizzard ask me to insert the procedure name I will use. When ''refreshing'' for comming up the colums to choose, a Forms Error comes up: FRM-10095: Assertion failed in iewbdbc_oracle_to_id, at C:\forms\101220\src\ie\iewbdb.c:724 twice, but after pressing OK, the columns come up, so I can choose the ones I need (all of them) and continue.

I have pressed the Help button on this pop-up, and this is what it shows:
- Cause:  An internal inconsistency was detected.
- Action:  Contact an Oracle support representative, and proceed with caution.

Could this be the answer of my new problem?

Edited by: abladb on Sep 8, 2010 12:00 PM
abladb
Finally!!! I've got it!!!

One of the columns I had was a TimeStamp Type, but forms do not recognize it, so it transformed it into Varchar2, and that was the final problem.

I've got it!!!

Thank you all for the help!!!

:D
abladb
Special thanks to Craig, and to everyone who tried to help me.

Read thread (2 pages) to have the answer.

^^
CraigB
Glad I could help! Thanks for posting the final solution so others can benefit from your experience!

Craig...
1 - 19
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 6 2010
Added on Sep 7 2010
#error, #forms, #moved-from-devops
19 comments
23,354 views