Acceding to All_source with a StoredProcedure
480094Jan 3 2006 — edited Jan 3 2006Hallo everybody!
That is my problem:
I've the table SOURCE_HIST in the schema arc01a
create table SOURCE_HIST
(
CHANGE_DATE DATE not null,
NAME VARCHAR2(30) not null,
TYPE VARCHAR2(12) not null,
LINE NUMBER not null,
TEXT VARCHAR2(4000)
);
alter table SOURCE_HIST
add constraint PK_SH primary key (CHANGE_DATE, NAME, TYPE, LINE);
I want to use this table to versioning pl/sql code during the development.
I've the procedure sp_insert_sourcehist in the schema arc01a to insert record in to source_hist:
create or replace procedure sp_insert_sourcehist(tip in varchar2, nome in varchar2, ow in varchar2) is
begin
if tip in ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TYPE', 'TYPE BODY')
then
INSERT INTO SOURCE_HIST
SELECT sysdate, name,type,line,text
FROM all_SOURCE
WHERE TYPE = tip AND NAME = nome and OWNER=ow;
end if;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
end sp_insert_sourcehist;
I'm using the procedure and it's OK!
Now I'm in the schema arc01_usr: arc01_usr can read and write in source_hist and can execute sp_insert_sourcehist. There are also synonims for arc01a.source_hist and arc01a.sp_insert_sourcehist (create synonym source_hist for arc01a.source_hist;create synonym sp_insert_sourcehist for arc01a.sp_insert_sourcehist). If I use this procedure for inserting code ownered by arc01_usr, it doesn't insert any row; if I use it for inserting code owned by arc01a it inserts the right rows!!!
What can I do to use this stored procedure without copying it in all schemas?
Thanks for your attention,
Sonia