Why No Output When Plsql Anonymous Block Completes

Why no output when PLSQL Anonymous block completes?

Viewing the DBMS_OUTPUT depends on the program.

SQL*Plus and Oracle SQL Developer

Run SET SERVEROUTPUT ON; first. This is all that's necessary in SQL*Plus or recent versions of Oracle SQL Developer.

SET SERVEROUTPUT ON;
begin
    dbms_output.put_line('Testing output');
end;
/

PL/SQL Developer

Output is automatically detected and displayed in the "Output" tab.

oracle does not display output - anonymous block completed

I'm trying to display a message in oracle sql develper on oracle 11g.

In SQL Developer tool, you need to view the DBMS_OUTPUT window.

Alternatively, you could use SET SERVEROUTPUT ON and execute the anonymous block as a script or press F5.

PL/SQL Procedure with cursor anonymous block completed

You are mixing metaphors here. You either need to use all dynamic SQL syntax here or none at all.

However you do not really need dynamic SQL for what you are doing, just make the l_cursor_1 type as an out parameter.

Try something like this:

<code>
CREATE OR REPLACE PROCEDURE Search_Testimonials(keyword VARCHAR2 IN,
                                                oResults IN OUT l_cursor_1,
                                                oStatus     OUT VARCHAR)
IS
type l_cursor_1 is REF CURSOR;
-- Temp_Content VARCHAR(255);
err_code                     varchar2(30);
err_msg                      varchar2(200);
BEGIN
   oStatus := "1";
   OPEN oResults FOR
      SELECT T_Content
      FROM Testimonial
      WHERE T_Content LIKE '%' || Keyword || '%';
      oStatus := "0";
EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
         oStatus := "2";
         err_code := SQLCODE;
         err_msg := SUBSTR (SQLERRM, 1, 200);
      DBMS_OUTPUT.PUT_LINE('ERROR: '|| err_code || ' : ' || err_msg ); 
--       RAISE;
END Search_Testimonials;
</code>

Check the oStatus before processing the output if it's 2 you have an error or if 1 no data.

You can extend the Exception processing by inserting the error code, message, proc name into an error table.

Also for performance reasons I would not use the %Keyword% construct by default. use Keyword% as default and pass "% some keyword" to do the same. If you have a index on the column it will never be used with %Keyword% construct ....

Hope this helps

Nick

Why do I see no output from this PL/SQL block?

A LOOP without an EXIT statement is one way to generate an infinite loop in PL/SQL

BEGIN
  LOOP
    null;
  END LOOP;
END;

You could also write a WHILE loop that never ends

BEGIN
  WHILE( true )
  LOOP
    NULL;
  END LOOP;
END;

---

Related Topics