Home » Other » Client Tools » Test Stored Procedure in TOAD
Test Stored Procedure in TOAD [message #125647] Tue, 28 June 2005 08:07 Go to next message
jmhmaine
Messages: 3
Registered: June 2005
Junior Member
I've been using SQL Server for the last 5 years, and just getting back to using Oracle. I want to have test new and altered stored procedures from TOAD. With SQL Server, using Query Analyzer you can call:
exec storedprocName param1, param2

an the recordset is displayed. I recall that this can't be done in TOAD, that you have create a Cusor and line print to view the resultset. I tried creating test SQL but it is erroring out. Here is my code:

DECLARE rpt_cursor PK_TEST.refcur_Output;
resultset rpt_cursor%rowtype;
BEGIN
PK_TEST.sp_sel_status_by_id(1, 'Y', rpt_cursor);
FETCH rpt_cursor into resultset;
WHILE rpt_cursor%found LOOP
dbms_output.putline(resultset.site);
FETCH rpt_cursor into resultset;
END LOOP;
END;

It errors on the second line when I use %rowtype. Error message:
ORA-06550: line 2, column 13:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 2, column 13:
PL/SQL: Item ignored
ORA-06550: line 5, column 25:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 5, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 7, column 27:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 7, column 7:
PL/SQL: Statement ignored
ORA-06550: line 8, column 28:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 8, column 6:
PL/SQL: SQL Statement ignored

Does anyone know how to make this SQL work, or have a better way of Testing a Stored Procedure within TOAD? Note, I don't have TOAD debugger, and would rather not open SQL*Plus. Thanks.
Re: Test Stored Procedure in TOAD [message #125693 is a reply to message #125647] Tue, 28 June 2005 14:58 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I prefer to run my code from sqlplus (from within Toad using SQL Editor > Execute SQL via SQL*Plus). But this should work fine on TOAD. You might need to manually turn dbms_output on.

CREATE TABLE ABC (A  NUMBER);
insert into abc values(1);
insert into abc values(9);
insert into abc values(13);
insert into abc values(999);

CREATE OR REPLACE PACKAGE ref_cur_tst
IS
   CURSOR c1 IS SELECT a FROM abc;
   TYPE t_cur IS REF CURSOR RETURN c1%ROWTYPE;

   PROCEDURE get_abc (cv_cur IN OUT t_cur);
END ref_cur_tst;
/

CREATE OR REPLACE PACKAGE BODY ref_cur_tst
AS
   PROCEDURE get_abc (cv_cur IN OUT t_cur)
   IS
   BEGIN
      OPEN cv_cur FOR SELECT a FROM abc;
   END get_abc;
END ref_cur_tst;
/

-- (sqlplus) set serveroutput on;

DECLARE
   a       abc.a%TYPE;
   cv_c1   ref_cur_tst.t_cur;
BEGIN
   ref_cur_tst.get_abc (cv_c1);

   LOOP
      FETCH cv_c1 INTO a;
      EXIT WHEN cv_c1%NOTFOUND;
      DBMS_OUTPUT.put_line (a);
   END LOOP;
   CLOSE cv_c1;
END;
/
Re: Test Stored Procedure in TOAD [message #125871 is a reply to message #125693] Wed, 29 June 2005 11:28 Go to previous messageGo to next message
jmhmaine
Messages: 3
Registered: June 2005
Junior Member
Thanks for the reply. I was looking for a solution that returns all the columns from the recordset from the stored proc. I also want to make it generic enough that I can use other stored procs without setting types per column for each proc, I'd rather just reference the row type.
Re: Test Stored Procedure in TOAD [message #125881 is a reply to message #125871] Wed, 29 June 2005 12:56 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:68212348056
Re: Test Stored Procedure in TOAD [message #125887 is a reply to message #125881] Wed, 29 June 2005 13:50 Go to previous messageGo to next message
jmhmaine
Messages: 3
Registered: June 2005
Junior Member
That doesn't appear to have anything to do with the rowtype or Stored Procedures. Why are you referencing that page? Thanks.
Re: Test Stored Procedure in TOAD [message #125888 is a reply to message #125887] Wed, 29 June 2005 14:15 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
It covers the question if it's possible to describe a ref cursor (weak ref cursor).

You can use the %rowtype to set up the ref cursor (strongly typed) or use a weakly typed ref cursor to handle a dynamic query. The problem seems to be that the "client" portion of the code can't describe the ref cursor format at runtime.

-- dummy cursor to get %rowtype below
-- no joins even required - it's just to get the structure
   CURSOR c1 IS SELECT a.ename, b.deptname FROM emp a, dept b;
   TYPE t_cur IS REF CURSOR RETURN c1%ROWTYPE;


-- or just use emp%rowtype to match whole table structure (strong ref cursor).
TYPE t_cur IS REF CURSOR RETURN emp%ROWTYPE;


-- or just define a weak ref cursor.
TYPE t_cur IS REF CURSOR;
Previous Topic: Cannot see synonyms in toad.
Next Topic: reg TOAD
Goto Forum:
  


Current Time: Thu Mar 28 08:49:26 CDT 2024