2

Totally newbie with database. Now I have created a function which returns a NUMBER, I just simply want to check the result of this function. The tool I am using is PL/SQL Developer but I can't find a output window or whatever. And of course I don't want to write anything into any table in this case. As a C++/C# developer I find it a bit hard to learn how to develop PL/SQL. I can't even easily see the compilation error of my function.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
tete
  • 4,859
  • 11
  • 50
  • 81

2 Answers2

6

I just simply want to check the result of this function.

You could simply call the function in the SELECT statement.

For example,

SQL> CREATE OR REPLACE
  2    FUNCTION f_get_date
  3      RETURN DATE
  4    IS
  5      v_date DATE;
  6    BEGIN
  7      v_date := SYSDATE;
  8      RETURN v_date;
  9    END;
 10  /

Function created.

SQL>
SQL> sho err
No errors.
SQL>
SQL> SELECT f_get_date FROM dual;

F_GET_DATE
--------------------
27-AUG-2015 17:06:31

SQL>

If you are new to PL/SQL Developer tool, you might find PL/SQL Developer Settings helpful.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • As I understand this is SQL PLUS but I am asking for PL/SQL developer. For example "sho err" doesn't work here. I am sure for a veteran like you they are basically the same thing but not to me. Besides, I can only access the client side. – tete Aug 27 '15 at 11:43
  • but as many already suggested select functio() from dual is definitely working – tete Aug 27 '15 at 11:51
  • @tete pl/sql developer has a window that is perfectly equivalent to sql/plus. it is called "command window" (file->new->command window) – Carlo Sirna Aug 27 '15 at 12:03
  • @CarloSirna yes, now I discovered when somebody mentions "XX window", it could also mean New -> "XX window". I always thought it means something in the Tool or Window menu. – tete Aug 27 '15 at 12:08
  • @tete Go through the PL/SQL Developer settings link I gave you, you will discover a lot of other features. – Lalit Kumar B Aug 27 '15 at 16:29
  • @tete Also most of the GUI based client tools are capable of supporting majority of SQL*Plus commands. PL/SQL Developer would need a license, you could try SQL Developer which is a free tool. – Lalit Kumar B Aug 27 '15 at 16:40
2

1) there is a "sort of" output window: try to execute this in a sql window (or in a test window

  begin
        DBMS_OUTPUT.PUT_LINE( 'hello world');
  end;

you should see "hello world" in the "output" tab of the sql window/test window. if you don't see it, just be sure that the "enabled" checkbox in such tab is checked.

2) if your function has no side effects (it does modify table data during its execution) you can use it in a select statement, as @a_horse_with_no_name already told you.

3) to see compilation errors be sure to compile procedures/triggers/functions/packages inside a "program window". a program window is the pl/sql developer window designed exactly for editing code that has to be compiled. it shows you compilation errors, hints, has code folding, refactorings...

4) if you want to run your funcition inside a debugger, you should use a "test window". I suggest you to give a read to the manual of pl/sql developer first.

Carlo Sirna
  • 1,201
  • 6
  • 11
  • Thank you for your answer. But 1) I am using SQL Window and yet I don't see a "enabled" checkbox. 2) even though I can see "program window" window mentioned on the user's guide but can't find straightly how to open it. When the first page of Google search for "program window pl sql developer" only shows three occurrences of the words "program window", it is really frustrating. 3) BTW I tried to google the shortcut to comment code block, but with no luck either – tete Aug 27 '15 at 11:40
  • just to be sure: you are using this program, right? http://www.allroundautomations.com/plsqldev.html – Carlo Sirna Aug 27 '15 at 11:54
  • if you are using pl/sql developer from allroundautomations: a new program window can be opened from file->new->program window Moreover you can switch the window type of any window by using the context menu (inside of the window itself) "change window to..." – Carlo Sirna Aug 27 '15 at 11:55
  • yes Carlo, that's the one I am using. A sad fact is when I am searching online, many pages mix with Oracle SQL developer. What can't they change a name :) But anyway, now I am able to see the result, and I have sit down to read the user's guide for debugging – tete Aug 27 '15 at 12:00
  • ok: the SQL window has three tabs: SQL Output and statistics. the dbms_output.put_line goes to the Output tab. in such tab there is the "Enabled" checkbox... and there is also the maximum number of bytes you can write by dbms_output – Carlo Sirna Aug 27 '15 at 12:06