Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Location
    NorthWest
    Posts
    10

    Unanswered: Executing Oracle Stored Procedure in Toad

    I am using Toad from Quest Software ... and I would like to know how can view the output of a stored procedure, after executing it.

    So I have this table:
    Code:
    CREATE TABLE test_inventory (
    	   id number primary key,
    	   product varchar2(100),
    	   st varchar2(2),
    	   quantity number
    );
    Code:
    And this stored procedure:
    CREATE OR REPLACE PROCEDURE sp_get_inventory (
      p_st IN test_inventory.st%type,
      p_product OUT test_inventory.product%type,
      p_quantity OUT test_inventory.quantity%type
    )
    AS
    BEGIN
      SELECT product, quantity
      INTO p_product, p_quantity
      FROM test_inventory
      WHERE st = p_st;
    END sp_get_inventory;
    I am trying to run it by right clicking the procedure and selecting the Execute Procedure option, and checking the OUTPUT Out Tags feature. I'm verifying the DBMS Output, but there is nothing there, even if I have data in the table.

    The executing PL/SQL code is this one:
    Code:
    DECLARE 
      P_ST VARCHAR2(200);
      P_PRODUCT VARCHAR2(200);
      P_QUANTITY NUMBER;
    
    BEGIN 
      P_ST := 'WA';
      P_PRODUCT := NULL;
      P_QUANTITY := NULL;
    
      SP_GET_INVENTORY ( P_ST, P_PRODUCT, P_QUANTITY );
      COMMIT; 
    END;
    The more people I now, the more I love my dog.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by FlashMagnuM
    I'm verifying the DBMS Output, but there is nothing there
    I suspect that may be because your code does not produce any output. To generate DBMS Output you could use, well, the DBMS_OUTPUT package...
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    exactly, your procedure does not spit out anything.
    look up: dbms_output.put_line
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Sep 2003
    Location
    NorthWest
    Posts
    10
    So I'm doing it like below, and nothing is printed:

    Code:
    DECLARE 
      P_ST VARCHAR2(200);
      P_PRODUCT VARCHAR2(200);
      P_QUANTITY NUMBER;
    BEGIN 
      P_ST := 'WA';
      P_PRODUCT := NULL;
      P_QUANTITY := NULL;
    
      SP_GET_INVENTORY(P_ST, P_PRODUCT, P_QUANTITY);
      DBMS_OUTPUT.PUT_LINE(P_PRODUCT || ' = ' || P_QUANTITY);
      COMMIT; 
    END;
    Any hints?
    The more people I now, the more I love my dog.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    DECLARE 
      P_ST VARCHAR2(200);
      P_PRODUCT VARCHAR2(200);
      P_QUANTITY NUMBER;
    BEGIN 
      DBMS_OUTPUT.ENABLE(100000);
      P_ST := 'WA';
      P_PRODUCT := NULL;
      P_QUANTITY := NULL;
      SP_GET_INVENTORY(P_ST, P_PRODUCT, P_QUANTITY);
      DBMS_OUTPUT.PUT_LINE(P_PRODUCT || ' = ' || P_QUANTITY);
      COMMIT; 
    END;
    When all else fails, Read The Fine Manual
    http://download.oracle.com/docs/cd/B...ut.htm#i999293
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    Before running the anonymous PL/SQL block, you have to click on the DBMS_OUTPUT tab in TOAD, & change that little red-light to a green-light. It's like you're turning DBMS_OUTPUT "on".

    It's similar to using SQL*Plus and executing the "set serveroutput on" statement.

    --=Chuck

  7. #7
    Join Date
    Sep 2003
    Location
    NorthWest
    Posts
    10
    Quote Originally Posted by chuck_forbes
    Before running the anonymous PL/SQL block, you have to click on the DBMS_OUTPUT tab in TOAD, & change that little red-light to a green-light. It's like you're turning DBMS_OUTPUT "on".

    It's similar to using SQL*Plus and executing the "set serveroutput on" statement.

    --=Chuck
    Thanks Chuck, is working like a charm. But now I'm encountering the situation when the stored procedure returns more than one record, and the script is bombing with this error: exact fetch returns more than requested number of rows. I'll dig for this.
    Last edited by FlashMagnuM; 08-19-09 at 14:04.
    The more people I now, the more I love my dog.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    when in doubt, run in sqlplus first. do not rely on a gui application which you probably are not applying into a production environment.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •