[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

package compilation error

Posted on 2011-10-14
7
Medium Priority
?
343 Views
Last Modified: 2012-05-12
Hello All,

I am trying to create a package with function and somehow its says compilation error

create or replace
PACKAGE CONVPKG AS

TYPE MY_TYPE IS RECORD (
    column1 NVARCHAR2(1),
    column2    VARCHAR2(1)
  );


FUNCTION loadP RETURN MY_TYPE;

END CONVPKG;

create or replace
PACKAGE BODY CONVPKG AS

ent MY_TYPE := null;

FUNCTION loadP RETURN MY_TYPE
IS
OutParam MY_TYPE;
BEGIN

IF ent IS NULL THEN
OutParam.column1 := 'b';
Outparam.column2 := 'B';
ent := outparam;

END IF;


RETURN ent;
END;

END CONVPKG;


0
Comment
Question by:dojjol
  • 4
  • 3
7 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36969100
You cannot do this.  You need to check each column in the record for null.

Per the docs:
http://download.oracle.com/docs/cd/B13789_01/appdev.101/b10807/05_colls.htm#i20716

Comparing Records

Records cannot be tested for nullity, or compared for equality, or inequality.

If you want to make such comparisons, write your own function that accepts two records as parameters and does the appropriate checks or comparisons on the corresponding fields.

0
 

Author Comment

by:dojjol
ID: 36970298
Thanks for the help.\
I used the boolean to flag it out.

but now if run select CONVPKG.loadP () from dual, it says invalid datatype.

I tried commenting out ent, and it worked, but my function is to populate ent, not really sure Why it says invalid datatype
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36970323
I don't understand the boolean flag comment.

If you are returning a boolean from the function, that is the cause of the invalid data type.  booleans can only exist inside pl/sql.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:dojjol
ID: 36970353
By boolean I mean

I changed

IF ent IS NULL THEN
OutParam.column1 := 'b';
Outparam.column2 := 'B';
ent := outparam;

END IF;

to

IF ent_cached == false THEN
OutParam.column1 := 'b';
Outparam.column2 := 'B';
ent := outparam;
ent_cached  := true;
END IF;


and was able to compile it.

But now when I run it says invalid datatype and I already declared ent as record in package.

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36970382
So you are trying to return ent outside of the package as the result of a sqlplus select?

Try:
select select CONVPKG.loadP().column1 from dual;

I've not done a lot with user-defined data types/objects.  You might need to declare the type outside the package to do this.  

0
 

Author Comment

by:dojjol
ID: 36970507
I actually removed the type from loadP and it has no return type now

and wrote seperate function to return varchar2

FUNCTION getP RETURN VARCHAR2
AS
BEGIN
RETURN ent.column1;
END;

but after executing loadP, I do
select CONVPKG.getEnt from dual;

and return me null and expected result should be 'b'.
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 36970831
>>and return me null and expected result should be 'b'.

Is this because of the issue from your new question?  your boolean is set to 'false' therefor the if is never entered.

If so, you need to accept the answer to this one and continue in the other.  These are quickly becoming the same question and there is a maximum of 500 points for a single question.

This question as asked was about the compilation error.  The answer to this one was: you cannot do this posted in http:#a36969100.

0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question