8 Replies Latest reply: Nov 2, 2013 2:47 PM by Tiny Penguin RSS

    Demonstrating PL/SQL Functions Using SQL Developer

    Tiny Penguin

      Good afternoon,

       

      I'm starting to write some PL/SQL functions to replace some of the SQL that I use most frequently.  A couple of very simple examples would be:

       

       

      create or replace function func_test (p_1 number) return number
      
      is
      
      x number;
      y number;
      
      begin
      
      
      x :=1;
      y :=2;
      
      
      return p_1 * x * y;
      
      
      end func_test;
      
      

       

      create or replace function func_test2 (p_1 varchar2) return varchar2
      
      
      is
      
      
      return_val varchar2(10);
      
      
      begin
      
      
      select p_1 into return_val from dual;
      
      
      return return_val;
      
      
      end func_test2;
      

       

      However, at my workplace I haven't been granted create function privileges yet until I can demonstrate some examples, which is understandable.

       

      For the time being, without these privileges, is there a way I can build/test functions in principle locally using SQL Developer without the need to write the functions to our database? I.e. can I demonstrate the above in SQL Developer, but without wrapping in create or replace syntax?

       

      I hope this isn't too vague.

       

      Using Oracle 11gR2 (not logged in to workplace database at the moment for specific version no.)

      SQL Developer 3.4

       

      Thanks,

       

      TP

        • 1. Re: Demonstrating PL/SQL Functions Using SQL Developer
          sb92075

          you should install Oracle DB on your own PC so you can have total access to all Oracle privileges

          • 2. Re: Demonstrating PL/SQL Functions Using SQL Developer
            Tiny Penguin

            Hello sb,

             

            Sure, but then I wouldn't have access to all the data in our client database to test functions under various circumstances.

             

            Also, I suppose it's not good practice to constantly write/replace/drop functions to/from a database when developing them? Better to test the function in principle and then write to the database?

             

            Thanks,

             

            TP

            • 3. Re: Demonstrating PL/SQL Functions Using SQL Developer
              sb92075

              populating test DB with data is a solvable problem.

               

              You don't need client data to test code (functions).

              You only need sample test data; which generally is less than a few dozen records per table.

              • 4. Re: Demonstrating PL/SQL Functions Using SQL Developer
                Frank Kulash

                Hi,

                 

                The obvious solution is to get the privileges.  If your employer really wants you to do something, they need to give you the necessary privileges to do it.  It's silly for them to tell you to do something, but refuse to let you do it.

                 

                Failing that, you can install Oracle on your own machine, as suggested above.  It's free and legitimate if you're only using it for learning and developing.  Oracle Express Edition is very easy to install.

                 

                As a last resort, you can write functions and procedures that are local to an anonymous block, like this:

                 

                SET SERVEROUTPUT  ON

                 

                DECLARE
                    function func_test (p_1 number) return number 
                    is 
                        x   number; 
                        y   number; 
                    begin 
                        x := 1; 
                        y := 2; 
                        return p_1 * x * y; 
                    end func_test; 
                BEGIN
                    dbms_output.put_line (  func_test (3)
                                         || ' = results of func_test'
                                         );
                END;
                /

                You can only call this kind of function from the anonymous block in which it is defined.

                • 5. Re: Demonstrating PL/SQL Functions Using SQL Developer
                  rp0428

                  Sure, but then I wouldn't have access to all the data in our client database to test functions under various circumstances.

                  Huh? Why not? It's your database so what keeps you from creating a database link to your client database where all the data is?

                  Also, I suppose it's not good practice to constantly write/replace/drop functions to/from a database when developing them? Better to test the function in principle and then write to the database?

                  Huh? Why not? What you think a dev database is for if not for development?

                   

                  Based on your two posts so far in this thread it's understandable why they don't want to give you privileges yet. Those sample 'functions' you posted are NOT a good use for functions.

                   

                  In sql developer you can just create and save the queries you use most often. There is no need to create functions for that.

                   

                  But if you do need an anonymous function now and then just create one using sql*plus syntax:

                  declare

                  function func_test (p_1 number) return number

                  is

                  x number;

                  y number;

                  begin

                  x :=1;

                  y :=2;

                  return p_1 * x * y;

                  end func_test; 

                  begin

                     dbms_output.put_line(func_test(3));

                     dbms_output.put_line(func_test(4));

                     dbms_output.put_line(func_test(5));

                  end;

                  /

                  • 6. Re: Demonstrating PL/SQL Functions Using SQL Developer
                    Frank Kulash

                    Hi,

                    TinyPenguin wrote:

                     

                    Hello sb,

                     

                    Sure, but then I wouldn't have access to all the data in our client database to test functions under various circumstances.

                     

                    Also, I suppose it's not good practice to constantly write/replace/drop functions to/from a database when developing them? Better to test the function in principle and then write to the database?

                     

                    Thanks,

                     

                    TP

                    You can write functions (and procedures, but I'll just say "functions" from now on) in one database that read data from a different database, using a datbase link.

                     

                    Repeatedly writing and replacing functions is the best way to develop them.  In fact, it's the only reasonable way to develop them.  Write as little as possible, sometimes less than a line, then test it.  If the function stops working after a revision, that narrows down the problem of finding the error.  If something simply doesn't work, you won't waste your time writing a lot of code that depends on it.

                    You don't want to be replacing functions a Production database very often.  That's one of the many reasons why you develop in a Development database.

                    • 7. Re: Demonstrating PL/SQL Functions Using SQL Developer
                      Tiny Penguin

                      populating test DB with data is a solvable problem.

                       

                      You don't need client data to test code (functions).

                      You only need sample test data; which generally is less than a few dozen records per table.

                       

                      Absolutely, of course. Our client database is pretty messy though, and includes data prior to the implementation of more recent business rules that I need to take account of. Useful perspective though, thanks.

                       

                       

                      Sure, but then I wouldn't have access to all the data in our client database to test functions under various circumstances.

                      Huh? Why not? It's your database so what keeps you from creating a database link to your client database where all the data is?

                      Also, I suppose it's not good practice to constantly write/replace/drop functions to/from a database when developing them? Better to test the function in principle and then write to the database?

                      Huh? Why not? What you think a dev database is for if not for development?

                       

                      Based on your two posts so far in this thread it's understandable why they don't want to give you privileges yet. Those sample 'functions' you posted are NOT a good use for functions.

                       

                      In sql developer you can just create and save the queries you use most often. There is no need to create functions for that.

                       

                      But if you do need an anonymous function now and then just create one using sql*plus syntax:

                       

                       

                      Our IT department are pretty sensitive about how they allow access, even to the dev environment. As you've identified, I'm not naturally a programmer so the option to play around with the data to develop some representative examples about how we can simplify and devolve SQL reporting to more members of staff is useful to me. I just wrote those two function quickly for the purpose of posting some sample data, which I thought would be helpful. Thanks for illustrating how to return their output using an anonymous block.

                       

                      Hi,

                       

                      The obvious solution is to get the privileges.  If your employer really wants you to do something, they need to give you the necessary privileges to do it.  It's silly for them to tell you to do something, but refuse to let you do it.

                       

                      Failing that, you can install Oracle on your own machine, as suggested above.  It's free and legitimate if you're only using it for learning and developing.  Oracle Express Edition is very easy to install.

                       

                      As a last resort, you can write functions and procedures that are local to an anonymous block, like this:

                       

                      Thanks Frank. Yeah I'm going to speak with our DBA next week about privileges. I've got XE/SQL Developer installed on my own computer - I wrote those sample functions using them - I just wasn't sure how to call/return anonymous blocks as both you and rp identified to develop at work as an interim solution.

                       

                      Thanks a lot All,

                       

                      TP.

                      • 8. Re: Demonstrating PL/SQL Functions Using SQL Developer
                        Tiny Penguin

                        FrankKulash wrote:

                         

                        Hi,

                        TinyPenguin wrote:

                         

                        Hello sb,

                         

                        Sure, but then I wouldn't have access to all the data in our client database to test functions under various circumstances.

                         

                        Also, I suppose it's not good practice to constantly write/replace/drop functions to/from a database when developing them? Better to test the function in principle and then write to the database?

                         

                        Thanks,

                         

                        TP

                        You can write functions (and procedures, but I'll just say "functions" from now on) in one database that read data from a different database, using a datbase link.

                         

                        Repeatedly writing and replacing functions is the best way to develop them.  In fact, it's the only reasonable way to develop them.  Write as little as possible, sometimes less than a line, then test it.  If the function stops working after a revision, that narrows down the problem of finding the error.  If something simply doesn't work, you won't waste your time writing a lot of code that depends on it.

                        You don't want to be replacing functions a Production database very often.  That's one of the many reasons why you develop in a Development database.

                         

                        V useful perspective thanks. I was coming at the problem from the other direction - refine as much as possible, then deploy to the test environment for further development. Our data team exists at a mid-way point between IT and front-facing services so we have a tendency towards developing proof of concepts before demonstrating to IT.

                         

                        Thanks,

                         

                        TP