Solved

User defined records and how to declare in spec as well as body

Posted on 2013-01-16
9
417 Views
Last Modified: 2013-01-16
If I have a record declared in the body of my package for the purposes of passing records instead of variables as parameters, must I also declare the record in the spec. I seem to be getting an error that makes me believe that I need to declare it in the spec as well as the body.

The error was PLS-00201. params_rec must be declared

my record in the body is this:
TYPE PARAMS_REC IS RECORD(
                             P_SCHEMA VARCHAR2(4000)
                            ,P_TABLENAME VARCHAR2(4000)
                            ,P_QUERY VARCHAR2(4000)
                            ,P_LOGGING_Y_N VARCHAR2(1)
                            ,P_COMPRESS_Y_N VARCHAR2(1)
                            ,P_TABLESPACE VARCHAR2(4000));

Open in new window


must it also go in the spec? Things don't need to be declared in the spec unless I want them public right?

This is from the SPEC and is the procedure I want to pass the record variables to:
PROCEDURE DROP_TABLES
              (
                            /*P_SCHEMA VARCHAR2
                           ,P_TABLENAME VARCHAR2*/
                          P_PARAMS_REC PARAMS_REC; --THIS ISN'T WORKING

              ); --DROPS PRE-EXISTING TABLES

Open in new window

0
Comment
Question by:Mark_Co
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 38784168
since the procedure and it's params_rec parameter are defined in the spec,  the record type itself must also be defined in the spec.  

the procedure's declaration is what is requiring it to be public.



Without the type being in the spec,  the procedure declaration doesn't make sense.
What is "params_rec" within the scope of the spec, if it's hidden in the body?
0
 

Author Comment

by:Mark_Co
ID: 38784176
"since the procedure and it's params_rec parameter are defined in the spec" did you mean body?
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 38784189
no I meant spec

>>> This is from the SPEC and is the procedure I want to pass the record variables to:


You declared the procedure in the SPEC, the procedure requires "params_rec", so in order for that declaration to be valid, everything in the declaration must be known.


Once the record type is declared in the spec, take it out of the body
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:Mark_Co
ID: 38784206
OH i think i see what you mean. So i remove it from the body altogether, and just declare it in the spec only?
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 38784208
Another way to look at it.


PROCEDURE my_proc(p_my_parameter IN my_secret_type);



can you invoke this procedure?

No,  you don't know what my_secret_type  is.
You can't declare a variable to be of that type because it's a secret, hidden in the body.

That's effectively what your current spec is saying.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 38784212
>>> OH i think i see what you mean. So i remove it from the body altogether, and just declare it in the spec only?

yes
0
 

Author Comment

by:Mark_Co
ID: 38784215
...and if I chose to not declare the procedure at all in the spec, then I could leave the record defined in the body and I should be ok?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 38784244
yes, as long as you don't have any other public references to it, it can stay hidden in the body
0
 

Author Closing Comment

by:Mark_Co
ID: 38784264
Great! I appreciate you helping me understand that
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Fill Null values 5 55
oracle collections 2 42
oracle forms question 22 61
Creating a are-you-sure condition prior to shutdown: what do you think of... 14 61
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

734 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