在Oracle Toad中调用带输出参数的存储过程，如何查看输出结果？

Hey there, let's break down why you're seeing "PL/SQL procedure successfully completed" but no output for P_SESSION_TYPE_OUT. This usually boils down to one of two common issues—let's walk through fixing them:

1. First, make sure DBMS_OUTPUT is enabled #

Most PL/SQL editors (like SQL Developer, PL/SQL Developer) don't turn on DBMS_OUTPUT by default. Even if you've got DBMS_OUTPUT.Put_Line in your code, it won't print anything unless this is enabled.

How to fix it: #

Run this command right before your PL/SQL block:

SET SERVEROUTPUT ON;

If you're using a GUI tool, look for a "Enable DBMS_OUTPUT" button in the toolbar, or right-click in the output panel and select the option to turn it on.

2. Verify the procedure's parameter mode #

Double-check how the P_SESSION_TYPE_OUT parameter is defined in the PCK_SESSION.GET_LAST_SESSION procedure. It needs to be marked as OUT or IN OUT for the procedure to pass a value back to your variable.

To check the procedure definition: #

Run this query to pull up the package body code:

SELECT text
FROM user_source
WHERE name = 'PCK_SESSION' AND type = 'PACKAGE BODY'
ORDER BY line;

Look for the GET_LAST_SESSION procedure definition—it should look something like this:

PROCEDURE GET_LAST_SESSION(
    P_USERNAME IN VARCHAR2,
    P_SESSION_TYPE_OUT OUT VARCHAR2
);

If the parameter is indeed OUT, but you still get no output, the procedure might not be assigning a value to it (for example, if there's no matching session record for USER01). In that case, P_SESSION_TYPE_OUT will stay NULL, and your DBMS_OUTPUT line will just show P_SESSION_TYPE_OUT = (with nothing after it).

3. Optional: Clean up your code for clearer debugging #

You can remove the unnecessary P_SESSION_TYPE_OUT := NULL; line (since PL/SQL initializes VARCHAR2 variables to NULL by default) and add a check for NULL to make the output more informative:

SET SERVEROUTPUT ON;

DECLARE 
    P_USERNAME VARCHAR2(32767) := 'USER01'; 
    P_SESSION_TYPE_OUT VARCHAR2(32767); 
BEGIN 
    PCK_SESSION.GET_LAST_SESSION ( 
        P_USERNAME, 
        P_SESSION_TYPE_OUT 
    ); 
    
    IF P_SESSION_TYPE_OUT IS NOT NULL THEN
        DBMS_OUTPUT.Put_Line('P_SESSION_TYPE_OUT = ' || P_SESSION_TYPE_OUT);
    ELSE
        DBMS_OUTPUT.Put_Line('P_SESSION_TYPE_OUT is NULL—no session found, or the procedure didn''t set a value');
    END IF;
END;
/

Start with enabling DBMS_OUTPUT first—that's the most frequent culprit here. If that doesn't work, dig into the procedure's logic to make sure it's actually populating the output parameter.

内容的提问来源于stack exchange，提问作者civesuas_sine