We help IT Professionals succeed at work.

Display result in PL/SQL

scissorhand
scissorhand asked
on
begin
coll.countStudent();
end;


The procedure countStudent contains sql that display the result of the query.
But I only get this message:
PL/SQL complete successfully

How to get the output? I have tried using
set serveroutput on

but still get the same result
Comment
Watch Question

Commented:
Hi.

If you want output from a PL/SQL procedure...
use:

DBMS_OUTPUT.PUT_LINE('TEXT GOES HERE' || sSomeVariable);

Do this while looping thrue the cursor for the SELECT statment.

Normally you don't want output from PL/SQL... except form return values. DBMS... is good for debugging though.

Regards
Jonas Montonen

Commented:
You must have the SERVEROUTPUT variable set to ON to see the output.

Set it to OFF to stop the SP from displaying output.

Commented:
hi
If you call your procedure from SqlPlus(Worksheet), you should set variable
set serveroutput on;

Author

Commented:
Sorry to tell u that I use a telnet to connect to my college server. I have on serveroutput but still can not

Author

Commented:
Also this procedure is created using java and register it to the system. I use system.out.println

Author

Commented:
Also this procedure is created using java and register it to the system. I use system.out.println
Commented:
You have to SET serveroutput ON and also call dbms_java.set_output with the output buffersize.

Here is an example :

1) Create the java source file JavaProc.java
class JavaProc {
 public static void main(String[] arg_in) {
    system.out.println("Your input is : " + arg_in[0]);
  }
}

2) Load the .java file into the database.
$> loadjava -u scott/tiger JavaProc.java

3) Publish the Java code to PL/SQL by creating the PL/SQL wrapper.
CREATE OR REPLACE PROCEDURE javaproc(cmd VARCHAR2)
AS LANGUAGE JAVA
name 'JavaProc.main(java.lang.String[])';
/

4) Call the procedure.
SQL> set serveroutput on
Server Output                   ON
SQL> call dbms_java.set_output(2000);
Statement processed.
SQL> exec JavaProc('How are you ?');
Statement processed.
Your input is : How are you ?

Author

Commented:
Thanx for your help. I just need a statement to complete my work