how to fetch cursor value into varchar2 in pl/sql

Question

help me to solve this error

29/5 PL/SQL: SQL Statement ignored

29/5 PLS-00394: wrong number of values in the INTO list of a FETCH statement

(29 is FETCH tbsp INTO tsinfo; )

62/5 PL/SQL: SQL Statement ignored

62/5 PLS-00394: wrong number of values in the INTO list of a FETCH statement

( 62 is FETCH lock_info INTO lockinfo; -- i want to put database lock result to this tsinfo)

CREATE OR REPLACE PACKAGE final_package as

PROCEDURE final_procedure(var1 in varchar2, dbinfo out varchar2);

END final_package;

/

CREATE OR REPLACE PACKAGE BODY final_package IS
 PROCEDURE final_procedure(var1 in varchar2, dbinfo out varchar2) IS
BEGIN

IF var1 = 'a'

------ /* get tablespaces name, percentage */ ----

THEN

DECLARE

tsinfo varchar2(5000); ---- /* i want to put tablespaces result to this tsinfo */----

CURSOR tbsp is select a.TABLESPACE_NAME as

Tablespace,round((1-((a.BYTES-nvl(b.BYTES,0))/a.BYTES))*100,2)

AS

Percentages from (select TABLESPACE_NAME, sum(BYTES) BYTES from

sys.dba_data_files group by TABLESPACE_NAME) a,

(select

TABLESPACE_NAME, sum(BYTES) BYTES from sys.dba_free_space

group by

TABLESPACE_NAME) b

where

a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)

order by ((a.BYTES-b.BYTES)/a.BYTES) desc;

BEGIN

FOR each_data1 in tbsp

LOOP

FETCH tbsp INTO tsinfo; --- /* i want to put tablespaces result to this tsinfo */ ---

END LOOP;

CLOSE tbsp;

END;

---- /* get database lock status */ ----

ELSIF var1 = 'b' THEN

DECLARE

lockinfo varchar2(1500);

CURSOR lock_info is SELECT vh.sid locking_sid,

vw.sid waiter_sid,

vs.status status,

vs.program program_holding,

vsw.program program_waiting

FROM v$lock vh,

v$lock vw,

v$session vs,

v$session vsw

WHERE(vh.id1, vh.id2) IN (SELECT id1, id2

FROM v$lock

WHERE request = 0

INTERSECT

SELECT id1, id2

FROM v$lock

WHERE lmode = 0)

AND vh.id1 = vw.id1

AND vh.id2 = vw.id2

AND vh.request = 0

AND vw.lmode = 0

AND vh.sid = vs.sid

AND vw.sid = vsw.sid;

BEGIN

FOR each_data in lock_info

LOOP

FETCH lock_info INTO lockinfo; -- i want to put database lock result to this tsinfo

END LOOP;

CLOSE lock_info;

END;

END IF;

END;

END;

/

Klas Lindbäck Klas Lindbäck · Accepted Answer · 2013-10-24T09:12:03

The cursor tbsp selects 2 values:

CURSOR tbsp is 
  select a.TABLESPACE_NAME as Tablespace,
         round((1-((a.BYTES-nvl(b.BYTES,0))/a.BYTES))*100,2) AS Percentages

You need to FETCH into 2 variables (or into a record with 2 fields).

FETCH tbsp INTO tsinfo, percentage; -- You need to declare percentage

The formatting was so horrible that I decided not to scan the code for other errors.

how to fetch cursor value into varchar2 in pl/sql

1 Answers