select rights for package

[VBmim]

Hello,

I am fairly new to pl/sql programming and I have a problem...

I have a simple procedure which I want to create. It would have to go through all the check constraints and rename them to something more readable than SYS_00999;

I have already come this far:

create or replace procedure RENAME_CHECKS is
begin
   execute immediate 'create global temporary table TMP on commit delete rows as  select t.* from sys.dba_cons_columns t where owner = ''OWNER'' and constraint_name in (select constraint_name from SYS.all_constraints where owner = ''OWNER'' and constraint_type = ''C'')';
  
   
   execute immediate 'drop table TMP';   


end RENAME_CHECKS;

The problem is that when I want to execute this procedure I get the error 'table or view does not exist'. The oracle user which creates the procedure has a 'select any table' grant and I also get the results I want when I run the query on itself.

What am I doing wrong here?

Greetz,

OraMim

 

[LKBrwnDBA]

You cannot create and use a table within the same procedure.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[VBmim]

I do not understand what you are trying to tell me...

I am trying to get my check constraint information in a temporary table, which later I will run through to find appropriate names and then rename the constraints as such...

If this is not the way to do it, could you advise me another better way?

Greets,

OraMim

 

[LKBrwnDBA]

Create the temporary table (outside the procedure).

On the other hand, use a 'cursor' and loop through it:

...
Create Or Replace Procedure RENAME_CHECKS Is
-- Etc --
  Cursor C_Csr Is
    Select T.* From Sys.Dba_Cons_Columns T 
     Where Owner = 'OWNER' 
       And Constraint_Name In (
           Select Constraint_Name From Sys.All_Constraints 
            Where Owner = 'OWNER' And Constraint_Type = 'C';
Begin
  For C In C_Csr
  Loop
    -- Rename the constraint here --
  End Loop;
End RENAME_CHECKS;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[VBmim]

Hello,

I tried applying your example but I got a compiler error saying that the table or view does not exist.

create or replace procedure RENAME_CHECKS is
cursor C_Csr is select * from dba_cons_columns  where owner = 'OWNER';
i number;
begin
For C In C_Csr Loop
    -- Rename the constraint here --
    i := i + 1;
  end Loop; 
end RENAME_CHECKS;

I have tried both 'sys.dba_cons_columns' as 'dba_cons_columns' and they give both the same results. I have also checked if there is a public synonym for 'sys.dba_cons_columns' and there is!

I can also hardly believe that temp tables have to be created outside the procedure to be used. What is the use of having temp tables then?
The reason why I wanted to do it with temporary tables is that I have to loop through my results again to make sure the name I provide for my check constraint is unique (and also less than 30 characters). I have a total of +2000 check constraints to rename and if I provide a standard naming of 'C' || table_name_short || column_name_short I end up with double names.

Greetz,

OraMim

 

[Thargy]

If you need to go backwards and forwards through your data, you should use a collection, not a cursor for loop. Something like

FUNCTION analyser_trigger_names RETURN user_trigger_type
IS


TYPE user_trigger_type IS TABLE OF user_triggers.trigger_name%TYPE;
analyser_triggers user_trigger_type;

BEGIN

SELECT trigger_name
  BULK COLLECT INTO analyser_triggers
  FROM user_triggers
 WHERE SUBSTR(trigger_name,1,7) = 'TBI_AI_'
 ORDER BY trigger_name;

RETURN analyser_triggers;

EXCEPTION

WHEN OTHERS
     THEN RAISE;

END analyser_trigger_names;

The collection in this case is called analyser_triggers and can be moved through in either direction.

Regards

T

 

[ChrisHunt]

I think you may need to explicitly grant select access on sys.dba_cons_columns to your user, rather than relying on access you get through a role. Oracle can be picky like that.

To avoid duplicate names, add an ORDER BY to your cursor to sort it by table and column name. Then you can add a sequence number to the end of each name.

Why would you want to do this, by the way?

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[LKBrwnDBA]

1) Chris is correct.

2)

I can also hardly believe that temp tables have to be created outside the procedure to be used. What is the use of having temp tables then?

Unlike WinDoze SQL and similar sql products where temp tables are created on the fly, Oracle temporary tables have a more "permanent" definition and hold session-private data that exists only for the duration of a transaction or session.





----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[VBmim]

Hello everyone,

Thanks for all the responses...

Granting select rights on this particular view solved the 'table not found' error. The particular user I am using had a 'select any table' gant, I thought that the view dba_cons_columns would have been included...

Thargtheslayer: can you put more than one column in this collection? In my result set I need to have check name, table name and column name.

Chris: Ordering by table name and column name is indeed one step in the process, but because I have to truncate the column and table name in order to make a valid check constraint name (less than 30 characters), check constraints on two different tables end up having the same name...

The problem I have is that in order to create our database at a customer site we make use of a dump file (containing the structure of our database). It happens that this dump file is imported more than once, and all automatically named objects (like my check constraints) are recreated with another automatic SYS_xxx name. This results in duplicate check constraints. If the constraints have names, this mistake can not happen.

Greetz,

OraMim

 

[VBmim]

Owkay... I have come so far:

create or replace procedure transics.RENAME_CHECKS is

type constraint_rec is record
(
r_constraint_name  dba_cons_columns.constraint_name%TYPE,
r_table_name       dba_cons_columns.table_name%TYPE,
r_column_name      dba_cons_columns.column_name%TYPE,
r_new_constraint_name varchar2(30)
);

Type constraint_rec_tab IS TABLE OF constraint_rec;
   
begin


select constraint_name, table_name, column_name bulk collect into constraint_rec_tab
from dba_cons_colums where t.owner = 'OWNER' and t.constraint_name in (select 
       constraint_name from all_constraints where owner = 'OWNER' and constraint_type = 'C') 
       order by table_name, column_name;
      

end RENAME_CHECKS;

So when I declare a cursor with this statement I have no problems, but when I want to use the same query to fill the collection, it throws yet another "table or view does not exist" error...

 

[VBmim]

Never mind! I just realized I made a stupid typo in my query...