CREATE OR REPLACE PROCEDUREdispemp
IS
no NUMBER;
nm VARCHAR2(30);
chndl INTEGER;
ignore INTEGER;
BEGIN
chndl := dbms_sql.open_cursor;
DBMS_SQL.PARSE(chndl, 'select eno, ename from emp', DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(chndl, 1, no);
DBMS_SQL.DEFINE_COLUMN(chndl, 2, nm, 30);
ignore := DBMS_SQL.EXECUTE(chndl);
-- Fetch a row from the table
LOOP
IF DBMS_SQL.FETCH_ROWS(chndl) > 0 THEN
-- get column values of the row
DBMS_SQL.COLUMN_VALUE(chndl , 1, no);
DBMS_SQL.COLUMN_VALUE(chndl, 2, nm);
DBMS_OUTPUT.PUT_LINE(no || ' ' || nm);
ELSE
EXIT;
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(chndl);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(chndl) THEN
DBMS_SQL.CLOSE_CURSOR(chndl);
END IF;
END;
/
No comments:
Post a Comment