ElicПри помощи второй, независимо от её реализации, в принципе нельзя невладельцем извлечь произвольные данные владельца пакета.
Чего-то я не догоняю:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
SQL> CREATE OR REPLACE PACKAGE pp_csv
2 AS
3 PROCEDURE query2sheet(
4 ref_cursor IN OUT SYS_REFCURSOR,
5 sheet IN OUT CLOB,
6 delimeter IN VARCHAR2 DEFAULT ';'
7 );
8 END;
9 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY pp_csv
2 AS
3 PROCEDURE query2sheet(
4 ref_cursor IN OUT SYS_REFCURSOR,
5 sheet IN OUT CLOB,
6 delimeter IN VARCHAR2 DEFAULT ';'
7 )
8 IS
9 BEGIN
10 OPEN ref_cursor FOR sheet;
11 END;
12 END;
13 /
Package body created.
SQL> create user u1 identified by u1
2 /
User created.
SQL> grant create session to u1
2 /
Grant succeeded.
SQL> grant execute on pp_csv to u1
2 /
Grant succeeded.
SQL> connect u1@pdb1sol122/u1
Connected.
SQL> select * from scott.dept
2 /
select * from scott.dept
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> declare
2 v_stmt clob := 'select * from scott.dept';
3 begin
4 scott.pp_csv.query2sheet(:v_cur,v_stmt);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> print v_cur
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
SY.
