This discussion is archived
8 Replies Latest reply: Nov 2, 2013 12:47 PM by TinyPenguin RSS

Demonstrating PL/SQL Functions Using SQL Developer

TinyPenguin Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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
    TinyPenguin Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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
    TinyPenguin Newbie
    Currently Being Moderated

    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
    TinyPenguin Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points