Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Location
    Pittsburgh, PA
    Posts
    10

    Question Unanswered: Displaying results from my procedure

    I'm pretty new to Oracle and I'm trying to figure out how to display the results of my procedure that uses dynamic sql. This is the code that I'm using. When I execute it, nothing is displayed to me. I imagine I need a DBMS_OUTPUT.Putline(), but I'm not sure where or what to actually output.

    Code:
    PROCEDURE pa_dyn
    IS
      TYPE cur_typ IS REF CURSOR;
      v_pa_cursor cur_typ; -- Declare a cursor variable
      v_pa_query            VARCHAR2(200);
      v_project_oid project_associate.project_oid%Type; -- declar v_project_oid based on the project_oid column in the project_associate table
      v_pa_title project_associate.title%Type;
      v_project_associate_oid project_associate.project_associate_oid%Type;
    BEGIN
      v_pa_query := 'SELECT project_oid, title, project_associate_oid FROM project_associate WHERE project_oid = :g_id';
      -- Open a cursor variable
      OPEN v_pa_cursor FOR v_pa_query USING v_project_oid;
      -- Fetch row into variables
        LOOP
        DBMS_OUTPUT.put_line(v_project_oid);
          FETCH v_pa_cursor
            INTO v_project_oid, v_pa_title, v_project_associate_oid;
          EXIT WHEN v_pa_cursor%NOTFOUND;
        
        Execute Immediate v_pa_query;
        
        END LOOP;
      CLOSE v_pa_cursor;
    END;

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You need to use

    SET SERVEROUTPUT ON

    in SQL*Plus before running the procedure.

  3. #3
    Join Date
    Jan 2010
    Location
    Pittsburgh, PA
    Posts
    10
    Quote Originally Posted by shammat View Post
    You need to use

    SET SERVEROUTPUT ON

    in SQL*Plus before running the procedure.
    I guess I'm not using SQL*Plus because when I ran the procedure with that statement, it said:

    Processing ...
    SET SERVEROUTPUT ON

    SQL*Plus command ignored.
    Processing ...
    Executing...

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I guess I'm not using SQL*Plus
    If you don't know, how are we supposed to?

    - I'm driving a car and I'd like to spit to the left.
    - Open the window first.
    - There's no window; I guess I'm not driving a car, after all.

    Gee ...

Posting Permissions

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