Re: Whats wrong with this PL/SQL

Hi.

 In Oracle 8i you must use DEFINE_COLUMN_ROWID instead of DEFINE_COLUMN  for ROWID type field(s).
 If you are using Oracle 7x/80 - try declaring varRowId as VARCHAR2  field ( I don't remember if DEFINE_COLUMN_ROWID exists in these  versions ).

 HTH. Michael.

In article <88jndd$26n$1_at_nnrp1.deja.com>,   sjoshi_at_ingr.com 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:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Feb 19 2000 - 13:08:20 CST