?
Solved

Change package specification without overwriting its contents

Posted on 2010-01-13
8
Medium Priority
?
534 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
[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
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

770 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