On Apr 22, 10:33 am, Joel <johow...@gmail.com> wrote:
> just thinking out loud here... could the desired results be achieved by
> creating a view that's definition selects from an encrypted table function?
>
>
>
> On Wed, Apr 22, 2009 at 11:28 AM, ddf <orat...@msn.com> wrote:
>
> > On Apr 22, 2:20 am, matteo gabella <mgabe...@gmail.com> wrote:
> > > hi all, is it possible to create a view that is querable, but which it
> > > is not possible to see the script?
> > > thank you
> > > matteo
>
> > No. After you grant SELECT on the view in question it becomes
> > available in the ALL_VIEWS data dictionary view, where the source is
> > found.
>
> > David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -
Let's try that:
SQL> CREATE TABLE stocktable (
2 ticker VARCHAR2(4),
3 open_price NUMBER(10),
4 close_price NUMBER(10));
Table created.
SQL>
SQL> INSERT INTO stocktable VALUES ('ORCL', 13, 16);
1 row created.
SQL> INSERT INTO stocktable VALUES ('MSFT', 35, 29);
1 row created.
SQL> INSERT INTO stocktable VALUES ('SUNW', 7, 11);
1 row created.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL> CREATE OR REPLACE TYPE TickerType AS OBJECT (
2 ticker VARCHAR2(4),
3 pricetype VARCHAR2(1),
4 price NUMBER(10));
5 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE TickerTypeSet AS TABLE OF TickerType;
2 /
Type created.
SQL> CREATE OR REPLACE PACKAGE refcur_pkg IS
2
3 TYPE refcur_t IS REF CURSOR RETURN StockTable%ROWTYPE;
4
5 END refcur_pkg;
6 /
Package created.
SQL>
SQL> @stockpivot.plb
SQL> CREATE OR REPLACE FUNCTION stockpivot wrapped
2 a000000
3 b2
4 abcd
5 abcd
6 abcd
7 abcd
8 abcd
9 abcd
10 abcd
11 abcd
12 abcd
13 abcd
14 abcd
15 abcd
16 abcd
17 abcd
18 abcd
19 8
20 1ea 160
21 wTFiB/2ifLFR/
b7+UWMaeO2jCegwgwLM19xqfHSLbrClRDuNUByp45kmmJM7ftUyBG4jjyHN
22 fBQbQU/
oPk2EWpgUSp1Pz1KD269aBCKWwf7rxggezyZu5tvfGlcnxAXRYz3RUWOFcI57XzPo
23 Vw438eCuX7JQj24U/e85VPePCN4y5VeBc2q7CRKAcY4O/
szLWIaQEtSkLR196fWDpqOgS1tE
24 4LYW0yQdzvI5BwCbGhctK9N4tzVAp3wFwBL/zEYMag/3IQndTzT9MeTW4nai98a
+kzBCnnyV
25 ZG81c0kVDRJT7hwjiKVAc9LASIs6XSMr3XGnD5vRatahfYmH2+RVeLLYz7c=
26
27 /
Function created.
SQL>
SQL> create view stock_pivot
2 as
3 SELECT *
4 FROM TABLE(stockpivot(CURSOR(SELECT * FROM StockTable)));
create view stock_pivot
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL>
SQL> grant select on stock_pivot to bong;
Grant succeeded.
SQL>
SQL> connect bong/bing
Connected.
SQL>
SQL> set long 50000
SQL>
SQL> select text
2 from all_views
3 where owner = 'BING';
TEXT
--------------------------------------------------------------------------------
SELECT "TICKER","PRICETYPE","PRICE"
FROM TABLE(stockpivot(CURSOR(SELECT * FROM StockTable)))
SQL>
It doesn't appear to obscure much except the inner workings of the
function, and the function requires a ref cursor to return results so
some sort of query is still visible to someone other than the owner.
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
