Re: Whats wrong with this PL/SQL

A copy of this was sent to sjoshi_at_ingr.com (if that email address didn't require changing) On Fri, 18 Feb 2000 15:10:40 GMT, you wrote:

>Can anyone tell me what is wrong with this query. It bombs out with
>this error message
>ORA-06550: line 32, column 3:
>PLS-00306: wrong number or types of arguments in call to 'DEFINE_COLUMN'
>ORA-06550: line 32, column 3:

define_column is not overloaded for the rowid type -- hence the call to define_column below is finding the closest type to rowid (varchar2) and then finding you don't have enough inputs to bind a varchar2. Try

PROCEDURE DEFINE_COLUMN_ROWID

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 C                              NUMBER(38)              IN
 POSITION                       NUMBER(38)              IN
 COLUMN                         ROWID                   IN

>DECLARE
>
>CURSOR getNos IS
> select partition_no from pd_pframe.pdtable_113 where
>discipline_indx_no=1;
>
>ddTable1 VARCHAR2(52) := 'Select line_id, rowid from
>dd_pframe.pdtable_12_';
>--ddTable2 VARCHAR2(32) := 'Where line_id Like ';
>
>updTable1 VARCHAR2(32) := 'UPDATE dd_pframe.pdtable_12_';
>updTable2 VARCHAR2(32) := ' SET line_id = ';
>updTable3 VARCHAR2(32) := 'WHERE ROWID =';
>varTotalTable VARCHAR2(100);
>
>int_tableNo NUMBER; --store partition_no
>cid1 INTEGER; --store cursor id1
>cid2 INTEGER; --store cursor id2
>charTableNo VARCHAR(4);
>
>varLineId VARCHAR2(32); --store the lineId
>varRowId ROWID; --stores dd row
>
>BEGIN
> OPEN getNos;
> LOOP
> FETCH getNos INTO int_tableNo;
> EXIT WHEN getNos%notfound;
> charTableNo := LTRIM(TO_CHAR(int_tableNo,'9999'));
>
> cid1 := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cid1, ddTable1 || charTableNo || ';',
>DBMS_SQL.V7);
> DBMS_SQL.DEFINE_COLUMN(cid1, 1, varLineId, 32);
> DBMS_SQL.DEFINE_COLUMN(cid1, 2, varRowId);
>
> LOOP
> IF
> DBMS_SQL.FETCH_ROWS(cid1) = 0 THEN
> EXIT;
> ELSE
> DBMS_SQL.COLUMN_VALUE(cid1, 1,
>varLineId);
> DBMS_SQL.COLUMN_VALUE(cid1, 2,
>varRowId);
> DBMS_OUTPUT.PUT_LINE('Values: ' ||
>varLineId || ' , ' || varRowId);
> END IF;
> END LOOP;
>
> END LOOP;
> CLOSE getNos;
>
>END;
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Feb 18 2000 - 10:18:19 CST