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!

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

user10757003
Yes, you can do this. you need to create empty batch files with the name in the correct format (as with the normal flat file batch process). Generally i expect you would know the location and Category to be used in creating the empty file name so these could be hard-coded. the issue is the Period to be used, and there are various options. If you are using the 'Global' POV setting then you could use the global period setting, which would have to updated each period by the administrator. Another option might be to maintain a seperate file with the current period held in it. Another option might be to utilise a filed on the locaiton which again would need maintaining. Another option might be that the period is held in the SQL table holding the data you are importing so you could do a read of the table first to get the period
1 - 1
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,526 views