We help IT Professionals succeed at work.

PL SQL  Record Collection Issue

Seaghost
Seaghost asked
on
351 Views
Last Modified: 2012-02-20
I need assistance with being able to use a record that I have declared in a package specification in a standalone pl sql function that is currently resides under the same schema as the Package. In my function creation process, I want to use the record type that I have declared as the RETURN data type. When I attempt to do this and try and compile my function code, I receive a message indicating that my record must be declared and/or the expression is incomplete or malformed. Any assistance with this is appreciated to the extreme. This is currently on the Oracle 10G platform.
Comment
Watch Question

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
please post your code or at least the relevant portions

Author

Commented:
CREATE OR REPLACE FUNCTION APP_TEST_SCHEMA.F_GET_QTY_PTS(param1 NUMBER, param2 VARCHAR2, param3 VARCHAR2) RETURN PKG_QTY_SCORE_RESULTS.T_QTY_POINTS

IS

Q_PTS T_QTY_POINTS;

BEING
........SOME CODE .......
RETURN Q_PTS;
END F_GET_QTY_PTS;
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
your function must declare it's q_pts variable through the package


Q_PTS PKG_QTY_SCORE_RESULTS.T_QTY_POINTS;

Author

Commented:
Ok, I made the correction and that worked just fine. However; when I call the procedure via

select F_GET_QTY_PTS(param1 NUMBER, param2 VARCHAR2, param3 VARCHAR2) from dual;

it tells me invalid datatype - ORA-00902

Commented:
You do not have to specify datatype while calling

select F_GET_QTY_PTS(param1 , param2 , param3 ) from dual;

Open in new window

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
pl/sql types aren't the same thing as sql object types

if you want to use it in sql,  then try this...

create or replace type T_QTY_POINTS as object(
field1 number,
field2 date,
field3 varchar2(20)
);

change the fields and types to be whatever you used in the pl/sql record, except you can't use %TYPE referencing,  you'll have to be explicit.


then,  remove the package record definition and remove
and change your function to return this type (i.e. no package dereference) and change the variable back to what you had before.

Author

Commented:
When I am calling the function, I am just passing the parameters that correlate with the datatypes in the function.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
the parameters aren't the problem,  it's the return type

your sql statement doesn't know what PKG_QTY_SCORE_RESULTS.T_QTY_POINTS  is.

sql doesn't understand pl/sql records.

you must create an object type for it to be used in sql

Author

Commented:
Ok, let me give that a try. I will post back my findings. Thank You.

Author

Commented:
Ok, it seemed to have worked just fine. I do have just no more question though. Is there way an easy way to use the max function against a nested table or another type of pl sql collection?
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.