Skip to Main Content
  • Questions
  • PL SQL Functions that work with Read-Only Access

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Ken.

Asked: November 30, 2016 - 3:54 pm UTC

Last updated: December 01, 2016 - 9:50 am UTC

Version: 4.2.0.16.260

Viewed 1000+ times

You Asked

Tom!

I've enjoyed your point of view and the clarity of your interactions for a couple of years now.

I’ve been using SQL Developer off and on for years but I just recently started running into more complicated needs and I’m hoping you can point me in the right direction.

My PL SQL knowledge is below average and I’m looking for a place to learn more but I’m in need of training in the real world, on live databases where I’ve only been given read-only access. Until now simple functions like “With” & “Case” have provided my users what they need but I’d like to know what other options are available to a restricted application developer that isn’t in the IT clan?

and Chris said...

If you're using 12c then you can define functions using the with clause:

with function inc(p int) return int as 
begin
  return p + 1;
end;
  select rownum, inc(rownum) from dual 
  connect by level <= 5;

ROWNUM  INC(ROWNUM)  
1       2            
2       3            
3       4            
4       5            
5       6 


And you can run anonymous PL/SQL blocks:

declare
  ...
begin
  ...
end;
/


But unless you've got the "create procedure" privilege (unlikely for a "read-only" user) you won't be able to create your own PL/SQL packages/procedures. You're best off speaking with IT to see what they can give you...

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Read-Only access and PL/SQL

Ken, November 30, 2016 - 5:38 pm UTC

Excellent answer! I assumed the need to ask for more access but really like the idea of using a "With" clause to instantiate a function; just wish we were on 12c now. LOL

I am off Googling for "anonymous PL/SQL blocks" that may help me. I assume there isn't a good resource to suggest on that?
Connor McDonald
December 01, 2016 - 12:55 am UTC

For experimenting, you can put just about *everything* in an anonymous block.

eg Lets say you had to write a proc and a function (but dont have the privs). You could something like:


declare
   my_local_variable int;
   ...

   procedure my_proc is
   begin
      ...
   end;

   function my_func return date is
   begin
     my_proc;
     return my_calculated_date;
   end;

   procedure main(p_parameter varchar2) is
   begin
     ...
   end;

begin
   ...
   ...
   main('Hello');
   ...
   ...
end;




Read-Only access and PL/SQL User Functions

Ken, December 01, 2016 - 3:13 am UTC

Chris! You da'MAN!

Thank you so much! I don't know why I was having so many issues but your reassurance and example helped me ignore all the examples out there and use yours without "Create".

Here is what you've inspired; I hope it helps someone else in my predicament. This should run unedited for anyone and gives just a couple of basic ideas.

Begin
    Declare
    l_message VARCHAR2 (100) := 'Hi There!';
    
    PROCEDURE hello_world IS
        BEGIN
          l_message := 'Goodbye!';
          DBMS_OUTPUT.put_line (l_message);
    END hello_world;
    
    PROCEDURE 
    hello_place (place_in IN VARCHAR2)
    IS
      l_message  VARCHAR2 (100);
    BEGIN
      l_message  := 'Hello ' || place_in;
      DBMS_OUTPUT.put_line (l_message);
    END hello_place;
    
    BEGIN
       hello_world;
       hello_place('Home');
    END;
end;
Begin
    declare
    l_message VARCHAR2 (100);
    
    FUNCTION 
    hello_message 
       (func_place_in IN VARCHAR2) 
        RETURN VARCHAR2
    IS
    BEGIN
       RETURN 'Hello ' || func_place_in;
    END hello_message;
    
    BEGIN
      l_message := hello_message ('Universe');
      DBMS_OUTPUT.put_line (l_message);
    END;
End;
/

Cheers! and THANK YOU, this is exactly what I needed.
Ken
Chris Saxon
December 01, 2016 - 9:50 am UTC

Thanks, glad we could help.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library