Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Acceding to All_source with a StoredProcedure

480094Jan 3 2006 — edited Jan 3 2006
Hallo 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 31 2006
Added on Jan 3 2006
3 comments
410 views