Re: Whats wrong with this PL/SQL

1. For ROWID variables you must use define_column_rowid.
2. Where is your dbms_sql.execute call? You must put it somewhere after all the define_columns.

Martin

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.
Received on Fri Feb 18 2000 - 11:41:42 CST