TECHNOLOGY
How to Execute Procedure in SQL Developer: A Step-by-Step Guide
This is how to execute procedure in SQL Developer: the Run dialog leaves each input NULL until you replace it, then you read the log or stop on a breakpoint.
This is how to execute procedure in SQL Developer when you already have the procedure in the database. You start from its node in the Connections navigator, or from a SQL Worksheet on the same connection.
Choosing Run does not send the procedure name by itself. SQL Developer opens the Run PL/SQL dialog, writes an anonymous block, and leaves every input at NULL until you replace it. The log you get is the result of that block.
The steps below cover the dialog, a worksheet call you can save, a procedure that lives in a package, the panes that show DBMS_OUTPUT and ref cursors, and a debug session that stops on a breakpoint.
What Does the Run Dialog Write Before the Procedure Starts?
The Run PL/SQL dialog names the target procedure or function and shows the block that will actually run. Oracle’s walkthrough of a procedure named EMP_LIST starts from a generated assignment, PMAXROWS:= NULL. You change that NULL to the value you want to test, such as 5, and click OK. The report lands in the Running – Log window.
You can type the value in the parameter grid at the top of the dialog or edit the assignment in the block. The block is what the database receives. A procedure with an OUT parameter needs a variable in that block to hold the value coming back, which is why a published harness for GET_MAXSAL_BY_DEPT looks like this before you touch it:
DECLARE
DNO NUMBER;
MAXSAL NUMBER;
BEGIN
DNO:= NULL;
GET_MAXSAL_BY_DEPT(
DNO => DNO,
MAXSAL => MAXSAL
);
DBMS_OUTPUT.PUT_LINE('MAXSAL = ' || MAXSAL);
END;
The dialog writes named notation, so DNO => DNO ties the argument to the parameter name rather than to its position. In that walkthrough, setting DNO to 50 made Running – Log print MAXSAL = 4500, which was the value that procedure returned for department 50 in that database, not a number SQL Developer invented.
- IN: Assign a literal or a variable before the call, because the procedure only reads it.
- OUT: Declare a variable, pass it, and print it after the call if you want the value in the log.
- IN OUT: Assign a starting value, then print the variable again, because the procedure can replace it.
Leaving the generated NULL in place still executes the procedure. It executes with a null input, and a finished log does not tell you that you forgot to type a value.
How to Execute Procedure in SQL Developer From the Navigator
Connect with a user that can see the procedure, expand the schema, open Procedures, and click the name. A warning icon means the last compile failed. Open the source, fix the line the compiler names, and click Compile on the editor toolbar. In one Oracle lab the first compile of AWARD_BONUS failed because a RAISE statement was missing its semicolon, and the second compile succeeded after that semicolon was added.
Right-click the procedure and choose Run, or click Edit and then the Run icon above the source. Both open the same dialog. Replace each NULL, then click OK. Running – Log shows the session connecting, any lines the block prints, and a process-exited message when the block is done.
For AWARD_BONUS, Oracle’s procedure testing walkthrough uses 149 for EMP_ID and 2000 for SALES_AMT. Those two inputs are enough to see a real salary change in that sample schema, which is a better check than running the block while both values are still NULL.
The source toolbar is the same set of actions you will use for the rest of the job. Run starts a normal execution. Compile checks the source without debug information. Compile for Debug prepares a later debug session. Debug starts that session. Profile collects execution statistics instead of stepping. The same controls are described under running and debugging functions and procedures in the SQL Developer user’s guide.
CHECK THE BLOCK BEFORE YOU CLICK OK
- Each input: The generated assignment is NULL until you type a real value in the grid or in the block.
- The names: Leave the named arrows in place unless you mean to rebuild the argument list yourself.
- The print: An OUT value reaches the log when the block prints it or the dialog already added that print.
- The icon: A warning icon on the procedure means you compile and fix the source before you trust a run.
A process-exited line means the block finished with the values written in it. If the table you expected to change is unchanged, read those assignment lines again before you change the procedure.
How to Call a Procedure From a SQL Worksheet
The dialog is enough for one test. A call you want to repeat belongs in a worksheet on the same connection. Write the block, then press F5 (Run Script) so the whole script runs. Ctrl+Enter runs only the statement at the cursor, which drops the rest of a multi-line block and the trailing slash.
SET SERVEROUTPUT ON
BEGIN
award_bonus(emp_id => 149, sales_amt => 2000);
END;
/
EXEC is a script command for its own line, as in EXEC award_bonus(149, 2000). Placing EXEC between BEGIN and END raises PLS-00103, because the compiler is already inside PL/SQL and will not accept that command there. Delete EXEC and call the procedure by name.
An OUT parameter needs a variable before the call. Inside a block, declare a local variable and pass it. In a script, a bind variable does the same job: VAR maxsal NUMBER, then EXEC get_maxsal_by_dept(dno => 50, maxsal =>:maxsal), then PRINT maxsal. The colon marks the bind variable. Without it, PL/SQL looks for a local name and fails.
Text values take single quotes and numbers do not, which is the same rule the dialog uses when it writes a string into the block. SET SERVEROUTPUT ON, run with F5, sends DBMS_OUTPUT to the Script Output pane for that script. That is a different switch from the DBMS Output pane.
How Package Procedures and Functions Change the Run
Once the call itself is clear, the object type changes where you click. A procedure inside a package is launched from the package, not from the small node listed under it. Right-click the package name, choose Run, and pick the unit in the Target list. Target lists the procedures and functions in that package, and changing it rewrites the parameter grid and the block together.
A function uses the same dialog. The block declares a variable for the return value and assigns the call to it, so you read that variable the way you read an OUT parameter. A function that returns a ref cursor is the case that looks empty in the log, because printing the cursor variable does not list the rows. The code editor fills an Output Variables grid for that cursor after the run.
Where Does Procedure Output Appear in SQL Developer?
Three panes can show a finished procedure, and they do not share one switch. Running – Log belongs to Run and Debug from the editor. It shows the connection text, lines printed by the generated block, and the exit message.
The DBMS Output pane is for worksheet sessions. Open View, then DBMS Output, click the green plus, and choose the connection. That turns server output on for the connection, and PUT and PUT_LINE write into the tab. Close the tab when you want capture to stop. A null argument to PUT_LINE is easy to miss: the pane moves to a new line and shows nothing, so several nulls in a row look like a silent procedure until a later call prints a real value.
Script Output is the third pane. SET SERVEROUTPUT ON plus F5 prints there, not in the DBMS Output pane. If the pane is enabled and still empty, check whether the script sent its lines to Script Output instead.
A ref cursor returned through the code editor is not a DBMS_OUTPUT line. After the run, open Output Variables. The grid shows the first 100 rows of that cursor. Compile and Compile for Debug do not use these panes. Their messages go to the Compiler log, which is also what a warning icon on the procedure is pointing at.
How to Debug a Procedure in SQL Developer
A log line tells you what came back. It does not show which assignment inside the procedure produced it. Debugging runs the procedure with the inputs you supply and pauses on the line you marked.
Open the procedure in the code editor. The gutter, the debug toolbar, and the data tabs live there, not in a worksheet that merely contains the call. Click Compile for Debug before you set a breakpoint. You can start a session without that compile, and the breakpoints will not hold. The debug compile adds overhead, so click Compile again, without the debug option, when the test is over and this source is the copy you want to keep.
Click in the gutter beside an executable line. A red circle marks the breakpoint, and a second click removes it. A declaration does not count. The line has to be one the engine will run. The AWARD_BONUS lab sets that circle on line 8, clicks Debug, and accepts the same inputs as the earlier run. The session stops on line 8.
The debugger’s default is to run until it hits a breakpoint. With no breakpoint set, the procedure finishes and the toolbar flashes past. Set the debugger preference to Step Into when you want the session to wait on the first line, including the anonymous block the dialog built. Jeff Smith, Distinguished Product Manager for the tool at Oracle, describes that preference as the way to keep a forgotten breakpoint from turning the session into a plain run.
What Each Debugger Button Does
The Debugging – Log tab holds the controls once the session is waiting. These are the actions the user’s guide names, with the default debugger keys beside the ones that have a widely published shortcut. Tools, then Preferences, then Shortcut Keys is where a local install can override them.
| Control | What one click does |
|---|---|
| Find Execution Point | Jumps to the next line the debugger will run |
| Step Over (F8) | Runs the next statement and does not enter a call, unless that call has a breakpoint |
| Step Into (F7) | Runs one statement, and enters the subprogram when the line is a call |
| Step Out (Shift+F7) | Leaves the current subprogram and stops on the next line in the caller |
| Step to End of Method | Goes to the last statement of the current subprogram |
| Resume (F9) | Continues from the current breakpoint |
| Pause | Halts without exiting, so Resume can continue the same session |
| Terminate | Halts and exits. Start again from Run or Debug on the source toolbar |
F9 in this toolbar is Resume. In a worksheet, F9 is also a default for Run Statement, beside Ctrl+Enter, so the key follows whichever window has focus. In the AWARD_BONUS lab, Step Over leaves the breakpoint, the Data tab shows the salary and commission the SELECT just read, and Resume runs the rest of the procedure.
What the Data and Watches Tabs Show
The Data tab lists variables using your Data preferences. Smart Data uses the Smart Data preferences, and a right-click inside that window opens those preferences. Watches hold expressions you add. Empty watches usually mean the unit was not compiled for debug, so go back and compile for debug before you blame the watch list.
While the session is paused you can change a variable’s value and force a branch without editing the source. Hover a name in the editor to see what it holds. A pass count on a breakpoint lets a loop run for that many hits of the line before execution stops, which is the practical way through a long loop.
What Blocks a SQL Developer Debug Session?
Those buttons work only after the session attaches. On the JDWP debugger the database calls your computer, because the debugger process sits on the client. A firewall in front of your machine, or a missing access control list on Database 12c and later, stops that return path before any breakpoint matters. The log shows a CONNECT_TCP call aimed at your host and port.
Privileges fail one step earlier. The dialog reports an ORA-01031 insufficient privileges error and states that the session requires DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE. You also need the right to execute the procedure, because a debug run still executes it. DEBUG ANY PROCEDURE is the grant that covers a unit you do not own.
The grants do not replace Compile for Debug. A session with both debug privileges still runs straight through when the procedure was compiled as a normal object. Compile for debug, put the breakpoint on an executable line, and start the session again.
SQL Developer 20.2 added a second debugger, DBMS_DEBUG. It opens another connection from the client instead of asking the database to call you back, so it does not need the JDWP access control list. It can take longer to start, and it does less than JDWP. The switch sits in the debugger preferences when the callback path is blocked and nobody can open it for you.
Bookmarks, the Profiler, and Remote Debugging
A long procedure is awkward to cross with the scrollbar. Click Navigate, then Toggle Bookmark, and an icon appears in the gutter. Go to Next Bookmark and Go to Previous Bookmark move through the marks. Remove Bookmarks from File clears the current editor, and Remove All Bookmarks clears every open editor. Go to Line and Go to Last Edit are on that same Navigate menu.
Profiling answers a different question from stepping. On Oracle Database Release 11.1 or later, right-click the procedure and choose Profile, or click Profile on the editor toolbar. The same Run PL/SQL dialog appears, the procedure runs, and the report opens on the Execution Profiles tab. You get the number of calls and the time spent in the procedure itself, which the profiler labels function time or self time. Use it when you want to know where the time went, not what a variable held on a single line.
Remote debugging is for a procedure started by another program, or from another computer. Set it up before that program runs. In the session that will execute the procedure, connect the debugger with EXEC DBMS_DEBUG_JDWP.CONNECT_TCP(‘remote1’, ‘4000’), using the host and the port where SQL Developer is listening. The environment variable ORA_DEBUG_JDWP=host=mypc:port=1234 does the same job. Then right-click the connection, choose Remote Debug, and complete the attach dialog. The breakpoint still has to be an executable line, and the session that connected the debugger has to be the session that runs the procedure.
Frequently Asked Questions
How Do You Show Line Numbers in the Procedure Editor?
Right-click the blank gutter beside the code and choose Toggle Line Numbers. To keep numbers on in every editor, enable Show Line Numbers under the Code Editor line gutter preferences. The numbers are how you match a compile message, or a breakpoint, to the line you think you clicked.
Can You Save the Block the Run Dialog Builds?
Yes, the Run dialog can save the block to a file after you edit it, which is worth doing once you have added a print for an OUT value or extra handling for a cursor. The next Run builds a fresh block from the current signature, so the file is the copy of the test you already tuned.
How Do You Clear or Save DBMS Output?
Clear erases the text in the DBMS Output pane, and Save writes that text to a file you name. Neither button turns capture off. Close the tab for that connection to stop new lines, and use Add New DBMS Output Tab when you want capture on for a connection again.
What Does Freeze Content Do on a Procedure Tab?
Freeze Content is the pin on the procedure tab. While the pin is on, clicking another object in the Connections navigator opens a separate tab and leaves this procedure where it is. Click the pin again and the tab is available for reuse, which is the same pin behavior the navigator uses on a table viewer.
How Do You Print a Ref Cursor From a Worksheet?
Declare a ref cursor bind variable, call the function or procedure, and print the variable as a script. For a function that returns employees in department 90, the script is var rc refcursor, then exec:rc:= get_emps(90), then print rc. Run it with F5. Do not open the cursor in your block if the procedure already opened it, or the call fails with PLS-00382.
Read the assignment lines in the dialog before you click OK. A block that exits cleanly still ran with those values, NULL included. When the debug session is done, compile the procedure once more without the debug option so the copy you keep is the normal compile.
-
TRAVEL3 years agoSouthwest Preboarding: Who Qualifies and What to Do
-
NEWS2 months agoFalse AI Intel Nearly Put US Crews on a Chinese Ship
-
LIFESTYLE3 years agoHow Often Do You Have to Change a Monkey’s Diaper?
-
ENTERTAINMENT2 months agoDunes Air Sues Nora Fatehi Over Its Luxury Jet
-
BUSINESS3 months agoTim Cook’s $4.6 Trillion Apple Still Runs on One Phone
-
LIFESTYLE3 years agoHow Long Does It Take for Armpit Hair to Grow?
-
NEWS2 months agoOpenAI Posts a Navier-Stokes Proof Clay Has Not Touched
-
TRAVEL2 years agoSmallest Airlines in the US: Niche Carriers and Unique Flights
