Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> View using WITH and a FUNCTION
DanS
post Sep 7 2017, 08:10 AM
Post #1


Newbie
*

Group: Members
Posts: 4
Joined: 7-September 17
Member No.: 52,153



DB Info: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit

I am trying to create a view using a WITH clause and a FUNCTION.
The query itself works (when it is not obfuscated for the web).
But when I try to create a view, I receive:
ORA-06552 PL/SQL : ORA-01031: insufficient privileges

CREATE OR REPLACE VIEW some_schema.my_view AS
WITH FUNCTION get_data RETURN VARCHAR2 IS
v_column_data VARCHAR2(10);
BEGIN
SELECT column_data
INTO v_column_data
FROM some_schema.some_table
WHERE PK_COLUMN = PK_INFO;
--
RETURN v_column_data ;
END;
---------------------------
SELECT 'column_data is: '||get_data
FROM dual

I've tried a simplier view where the function simply returns sysdate, and that works fine.
This example works:

CREATE OR REPLACE VIEW schema.my_view AS
WITH FUNCTION get_date RETURN VARCHAR2 IS
d_date DATE;
BEGIN
SELECT SYSDATE
INTO d_Date
FROM dual;
--
RETURN d_Date;
END;
---------------------------
SELECT 'Today is: '||get_date today_is
FROM dual


SELECT * FROM schema.my_view;
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Sep 7 2017, 02:44 PM
Post #2


Advanced Member
***

Group: Members
Posts: 77
Joined: 6-June 16
From: India
Member No.: 51,370



Hello DanS,

How are you executing the VIEW creation statement? It fails in almost all IDEs but when you execute the same script in SQL*PLUS, it works fine.

Try executing your script in SQL*PLUS and then let us know. I tried and it works fine in SQL*PLUS and fails in SQL DEVELOPER 4.1.3 version.


Thank you,
Boobal Ganesan


--------------------
Check out my new Advanced PL/SQL book here - http://www.rampant-books.com/book_1701_pls...initive_ref.htm
Go to the top of the page
 
+Quote Post
DanS
post Sep 7 2017, 03:35 PM
Post #3


Newbie
*

Group: Members
Posts: 4
Joined: 7-September 17
Member No.: 52,153



Ok, here's another test...
If you create this in your schema, it works great. (Replace "schema." with NULL).

However, if you run all this in another schema (Replace "schema." with an actual schema other than your own), it fails with ORA-01031: Insuffucuent Privileges

I suspect its because the FUNCTION statement is creating the Function in your schema and the view in the one specified by 'schema'


CODE
----------------------------
-- Create a dummy table
----------------------------
CREATE TABLE schema.test_table (column_data VARCHAR2(10));
INSERT INTO  schema.test_Table (column_data) VALUES ('Dan');
COMMIT;
-------------------------
-- Create the view
-------------------------
CREATE OR REPLACE VIEW  schema.my_test_view AS
WITH FUNCTION get_data(i_column_data schema.test_Table.column_data%TYPE) RETURN schema.test_Table.column_data%TYPE IS
v_column_data schema.test_Table.column_data%TYPE;
BEGIN
  BEGIN
    SELECT column_data
      INTO v_column_data
      FROM  schema.test_Table
     WHERE column_data = i_column_data;
    -------------------
  EXCEPTION
    WHEN no_data_found THEN
     v_column_data := NULL;
  END;
  RETURN v_column_data;
END;
------------------------------------------------
SELECT 'Here is the data: '||get_data(column_data) Info FROM  schema.test_table

--------------------------------
-- Select from the new view
--------------------------------
SELECT * FROM  schema.my_test_view;
------------------
-- Clean up
------------------
DROP VIEW  schema.my_test_view;
DROP TABLE  schema.test_Table;
Go to the top of the page
 
+Quote Post
DanS
post Sep 8 2017, 08:33 AM
Post #4


Newbie
*

Group: Members
Posts: 4
Joined: 7-September 17
Member No.: 52,153



If I grant select on schema.table_name to public, I am able to create the view in a schema other than my own.
It's some sort of permission issue.
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Sep 9 2017, 01:31 AM
Post #5


Advanced Member
***

Group: Members
Posts: 77
Joined: 6-June 16
From: India
Member No.: 51,370



Hello DanS,

QUOTE (DanS @ Sep 8 2017, 02:05 AM) *
I suspect its because the FUNCTION statement is creating the Function in your schema and the view in the one specified by 'schema'


You don't have to suspect it as that's how Oracle works smile.gif

Also, there is no function being created in the current schema as quoted by you above, it is just executed once similar to a SELECT statement. When you try to create the view over the WITH clause function for a different schema, it expects all the objects' privilege to be available to create it as a valid object. If the privilege is not available, Oracle doesnt stop us from creating the view, but the object is created invalid. This we can fix later by issuing the proper privileges and then recompiling the object.


Thank you,
Boobal Ganesan


--------------------
Check out my new Advanced PL/SQL book here - http://www.rampant-books.com/book_1701_pls...initive_ref.htm
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Sep 9 2017, 01:33 AM
Post #6


Advanced Member
***

Group: Members
Posts: 77
Joined: 6-June 16
From: India
Member No.: 51,370



Hello DanS,

QUOTE (DanS @ Sep 8 2017, 07:03 PM) *
If I grant select on schema.table_name to public, I am able to create the view in a schema other than my own.
It's some sort of permission issue.


Yes it is. When we want to refer an object outside our current schema, we need the appropriate privilege granted upon us.


Thank you,
Boobal Ganesan


--------------------
Check out my new Advanced PL/SQL book here - http://www.rampant-books.com/book_1701_pls...initive_ref.htm
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd September 2017 - 05:28 PM