executing package on another schema

[suggs]

I have compiled and run a pl/sql package on one schema's data then when I try to execute the same package from the same schema on another schema's data I get the error.

1st schema claudedemo
2nd schema xcs


SQL> exec archive_database.select_data('claudedemo');

PL/SQL procedure successfully completed.

SQL> exec archive_database.select_data('xcs');
BEGIN archive_database.select_data('xcs'); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SYS_SQL", line 824
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "CLAUDEDEMO.ARCHIVE_DATABASE", line 63
ORA-06512: at "CLAUDEDEMO.ARCHIVE_DATABASE", line 30
ORA-06512: at line 1

code:
CREATE OR REPLACE PACKAGE BODY archive_database AS

CURSOR get_tables(p_owner VARCHAR2) RETURN dba_tables%ROWTYPE IS
SELECT * FROM dba_tables WHERE owner = p_owner;

CURSOR get_columns(p_table VARCHAR2) RETURN dba_tab_columns%ROWTYPE IS
SELECT * FROM dba_tab_columns WHERE table_name = p_table ORDER BY column_name;

PROCEDURE select_data (v_owner VARCHAR2) IS

fHandle UTL_FILE.FILE_TYPE;
tab_record dba_tables%ROWTYPE;
col_record dba_tab_columns%ROWTYPE;
ltable dba_tab_columns.table_name%TYPE;
lowner dba_tables.owner%TYPE;
l_rows number;


BEGIN
fHandle:=UTL_FILE.FOPEN('F:\oracle\orasvr\admin\CLADTEST\arch\','archivedata.txt','w');
UTL_FILE.PUT_LINE(fHandle,'File Begin<000123>');

lowner := UPPER(v_owner);
OPEN archive_database.get_tables(lowner);
LOOP
FETCH archive_database.get_tables INTO tab_record;
EXIT WHEN archive_database.get_tables%NOTFOUND;
ltable := tab_record.table_name;
-- DBMS_OUTPUT.PUT_LINE(tab_record.table_name);
l_rows := dump_csv( 'select * from ' || tab_record.table_name, ',', 'F:\oracle\orasvr\admin\CLDTEST\arch\', 'test.dat', tab_record.owner, tab_record.table_name, fhandle);


END LOOP;

CLOSE archive_database.get_tables;
UTL_FILE.PUT_LINE(fHandle,'End File<000123>');
UTL_FILE.FCLOSE(fHandle);

END select_data;

function dump_csv( p_query in varchar2,
p_separator in varchar2 default ',',
p_dir in varchar2,
p_filename in varchar2,
p_owner in dba_tables.owner%TYPE,
p_tablename in dba_tab_columns.table_name%TYPE,
-- p_owner in varchar2,
-- p_tablename in varchar2,
fhandle in UTL_FILE.FILE_TYPE)
return number
is

l_theCursor integer default dbms_sql.open_cursor;
l_columns dbms_sql.desc_tab;
l_columnValue varchar2(4000);
l_status integer;
l_colCnt integer default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;
begin
--fHandle := utl_file.fopen( p_dir, p_filename, 'w' );

dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );

for i in 1 .. 255 loop
begin
dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
l_colCnt := i;
exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
end loop;

dbms_sql.define_column( l_theCursor, 1, l_columnValue, 4000 );

l_status := dbms_sql.execute(l_theCursor);

loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
l_separator := '';

UTL_FILE.PUT(fHandle, '<schema>:' || p_owner || ':<table>:' || p_tablename || ':');

for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( fHandle, l_separator || l_columnValue );
l_separator := p_separator;
end loop;

UTL_FILE.PUT_LINE(fHandle,';');
l_cnt := l_cnt+1;
end loop;
dbms_sql.close_cursor(l_theCursor);


return l_cnt;
end dump_csv;
END archive_database;
/

 

[sem]

You must have explicit, not via role, grants on objetcs refered from stored procedures.