Toad does not display the table variable output when a stored procedure or package is executed.
Steps to reproduce this problem:
1) Launch Toad execute the table and insert script from this file: toad table.doc
2) Now create the PKG_TOAD package from these files: PKG_TOAD.pkg, PKG_TOAD.pkgb
3) Load the 'PKG_TOAD' package into the Procedure Editor.
4) Go to 'Set Parameters' and select 'Options'
5) Enable the "Enable DBMS Output before debug session" option for the 'Pkg_Toad.SP_TOAD' procedure.
6) Now execute the package. Toad will retrieve 2 lines:
ROWS_AFFECTED = 10
ORA_ERROR_CODE = 0
7) Now launch SQL Navigator and execute the 'PKG_TOAD' package. SQLNav will retrieve 22 lines:
rows_affected = 10
ora_err_code = 0
out_special_charge_c(1) = BAS
out_special_charge_c(2) = BAS
out_special_charge_c(3) = BAS
out_special_charge_c(4) = ENC
out_special_charge_c(5) = BAS
out_special_charge_c(6) = ENC
out_special_charge_c(7) = BAS
out_special_charge_c(8) = ENC
out_special_charge_c(9) = BAS
out_special_charge_c(10) = ENC
out_freight_charge_a(1) = 741
out_freight_charge_a(2) = 741
out_freight_charge_a(3) = 754
out_freight_charge_a(4) = 44.35
out_freight_charge_a(5) = 2125.8
out_freight_charge_a(6) = 42.52
out_freight_charge_a(7) = 2177.59
out_freight_charge_a(8) = 43.55
out_freight_charge_a(9) = 2274.1
out_freight_charge_a(10) = 45.48
The strange thing is that both Toad and SQL Navigator runs the same anonymous block, but gets different results:
ROWS_AFFECTED := NULL;
ORA_ERR_CODE := NULL;
This feature has been implemented in Toad for Oracle 9.0. Please visit this link to download Toad for Oracle 9.0: http://support.quest.com/Portal/Logon.asp?ReturnURL=/support_download/Downloads.asp.
SQL Navigator can output the table variables that the procedure returns.