Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Change package specification without overwriting its contents

Posted on 2010-01-13
8
Medium Priority
?
535 Views
Last Modified: 2013-12-18
Hello dear experts, the question is quite difficult, please read the following:

I have a package spec:
CREATE OR REPLACE PACKAGE declarations IS

   rec_activity_current  dw_fact_activity%rowtype;
   rec_activity_before   dw_fact_activity%rowtype;
   rec_provision_current dw_fact_provision%rowtype;
   rec_provision_before  dw_fact_provision%rowtype;

END;

When I upgrade my application running scripts through SQLPlus , I add a new table (dw_fact_access ) to my schema. Also I need to change " declarations " package. I need to add a new rowtype declaration of a new table I've added. The package declarations eventually should look like:

CREATE OR REPLACE PACKAGE declarations IS

   rec_activity_current  dw_fact_activity%rowtype;
   rec_activity_before   dw_fact_activity%rowtype;
   rec_provision_current dw_fact_provision%rowtype;
   rec_provision_before  dw_fact_provision%rowtype;

   rec_provision_current dw_fact_access%rowtype;
   rec_provision_before  dw_fact_access%rowtype;

END;

But I can't just overwrite the package spec using CREATE OR REPLACE !!!!
I don't know what upgrades I did in the past, I mean what table I have already created.
I need to change existing package spec, adding new rows
rec_provision_current dw_fact_access%rowtype;
rec_provision_before  dw_fact_access%rowtype;
into the package spec.

Is there any technique to implement it ???
How can I do this in windows and Unix ???

Thank you !


0
Comment
Question by:OraDeveloper
  • 3
  • 3
  • 2
8 Comments
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 26301973
When you say "I can't just overwrite the package spec using CREATE OR REPLACE " do you mean it isn't working and you get an error?  If so, what is the error message.

The create statements you have shown is simply run from sqlplus or some other tool (such as Toad).  It makes no difference whether the database is on unix or windows!
0
 
LVL 14

Expert Comment

by:shru_0409
ID: 26302100
definetly it will give the error b'coz same record set  u can not create

change the record set name like

rec_activity_current  dw_fact_activity%rowtype;
   rec_activity_before   dw_fact_activity%rowtype;
   rec_provision_current dw_fact_provision%rowtype;
   rec_provision_before  dw_fact_provision%rowtype;

   rec_provision_current_new dw_fact_access%rowtype;
   rec_provision_before_new   dw_fact_access%rowtype;


try it
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 26302129
Okay, I think I know what you are referring to.  You want to create rec_provision_current and rec_provision_before using a different rowtype.

You can't create a record/variable with the same name more than once, which is why you're getting the error.

If your original package is declared as follows:

CREATE OR REPLACE PACKAGE declarations IS

   rec_activity_current  dw_fact_activity%rowtype;
   rec_activity_before   dw_fact_activity%rowtype;
   rec_provision_current dw_fact_provision%rowtype;
   rec_provision_before  dw_fact_provision%rowtype;

END;

Why can't you simply re-create it as this:

CREATE OR REPLACE PACKAGE declarations IS

   rec_activity_current  dw_fact_activity%rowtype;
   rec_activity_before   dw_fact_activity%rowtype;
   rec_provision_current dw_fact_access%rowtype;
   rec_provision_before  dw_fact_access%rowtype;

END;
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 14

Expert Comment

by:shru_0409
ID: 26302251
r u check the original package is declaration??

CREATE OR REPLACE PACKAGE declarations IS

   rec_activity_current  dw_fact_activity%rowtype;
   rec_activity_before   dw_fact_activity%rowtype;
   rec_provision_current dw_fact_provision%rowtype; --- check record field  
   rec_provision_before  dw_fact_provision%rowtype; --- check record field  

   rec_provision_current dw_fact_access%rowtype; --- check record field  same
   rec_provision_before  dw_fact_access%rowtype; -- check record field  

END;

and author is going to changed the record type table not a record field which is keeping same ....
it will give the error
PLS-00410: duplicate fields in RECORD,TABLE or argument list are not permitted

0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 26302334
The following will compile on my 10g database:

CREATE OR REPLACE PACKAGE declarations IS

   rec_activity_current  file_control%rowtype;
   rec_activity_before   file_control%rowtype;
   rec_provision_current file_types%rowtype; --- check record field  
   rec_provision_before  file_types%rowtype; --- check record field  

   rec_provision_current frequencies%rowtype; --- check record field  same
   rec_provision_before  frequencies%rowtype; -- check record field  

END;

Note: I had to change the table names to reflect tables on my database.

But what does the rec_provision_current and rec_provision_before look like?  They are likely to reflect the last declaration against the frequencies table, not the first one against file_types.  If so, what is the point of declaring these record structures twice as I don't understand how you could use them both?

If you are wanting to create current and before records for each table then surely, what you should be doing is:

CREATE OR REPLACE PACKAGE declarations IS

   rec_activity_current  dw_fact_activity%rowtype;
   rec_activity_before   dw_fact_activity%rowtype;
   rec_provision_current dw_fact_provision%rowtype;
   rec_provision_before  dw_fact_provision%rowtype;

   rec_access_current dw_fact_access%rowtype;
   rec_access_before  dw_fact_access%rowtype;

END;
0
 

Author Comment

by:OraDeveloper
ID: 26302411
Oh, Sorry people !!!

I did a mistake in record name !!!!

Sorry again, a new package should look like:

CREATE OR REPLACE PACKAGE declarations IS

   rec_activity_current  dw_fact_activity%rowtype;
   rec_activity_before   dw_fact_activity%rowtype;
   rec_provision_current dw_fact_provision%rowtype;
   rec_provision_before  dw_fact_provision%rowtype;

   rec_access_current dw_fact_access%rowtype;
   rec_access_before  dw_fact_access%rowtype;

END;

I can't just REPLACE the existing package, because I don't know at the time of upgrade what was the previous upgrades and what records were declared in the past, for example the existing package could be:

CREATE OR REPLACE PACKAGE declarations IS

   rec_activity_current  dw_fact_activity%rowtype;
   rec_activity_before   dw_fact_activity%rowtype;
   rec_provision_current dw_fact_provision%rowtype;
   rec_provision_before  dw_fact_provision%rowtype;
   rec_message_current dw_fact_message%rowtype;
   rec_message_before  dw_fact_message%rowtype;

END;

OR

CREATE OR REPLACE PACKAGE declarations IS

   rec_activity_current  dw_fact_activity%rowtype;
   rec_activity_before   dw_fact_activity%rowtype;
   rec_provision_current dw_fact_provision%rowtype;
   rec_provision_before  dw_fact_provision%rowtype;
   rec_profile_current dw_fact_profile%rowtype;
   rec_profile_before  dw_fact_profile%rowtype;

END;

Thanks



0
 
LVL 14

Expert Comment

by:shru_0409
ID: 26302434
Milleniumaire..... u r right...
I m checking the record type....
0
 

Accepted Solution

by:
OraDeveloper earned 0 total points
ID: 26302978
Hi, I solved the problem building the package dynamically, basing a decision on existence of procedures that use ROWTYPE variables that I needed to insert:

DECLARE

  v_sql    VARCHAR2(2000);
  v_module VARCHAR2(100);

  CURSOR cur IS
    SELECT object_name
    FROM user_objects
    WHERE object_type = 'PROCEDURE'
    AND object_name LIKE 'CALC_%_ACC';

  rec cur%rowtype;

BEGIN

  v_sql := 'CREATE OR REPLACE PACKAGE declarations IS' || CHR(10);
 
  FOR rec IN cur LOOP
   
    v_module := REPLACE(REPLACE(rec.object_name,'CALC_'),'_ACC');
    v_sql := v_sql || '  rec_' || v_module || '_current  dw_fact_' || v_module || '%rowtype;' || CHR(10);
 
  END LOOP;
 
  v_sql := v_sql || 'END;';

EXECUTE IMMEDIATE v_sql;

END;
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

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 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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

571 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