|
SQL>
SQL> CREATE TABLE MyTable(MyRow INTEGER, MyDesc VARCHAR2(50));
Table created.
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 v_CursorID NUMBER;
3 v_SelectRecords VARCHAR2(500);
4 v_NUMRows INTEGER;
5 v_MyNum INTEGER;
6 v_MyText VARCHAR2(50);
7 v_MyROWID ROWID;
8 v_TotRow INTEGER;
9
10 BEGIN
11 v_CursorID := DBMS_SQL.OPEN_CURSOR;
12 v_SelectRecords := 'SELECT * from MyTable FOR UPDATE';
13
14
15 DBMS_SQL.PARSE(v_CursorID,v_SelectRecords,DBMS_SQL.V7);
16 DBMS_SQL.DEFINE_COLUMN(v_CursorID,1,v_MyNum);
17 DBMS_SQL.DEFINE_COLUMN(v_CursorID,2,v_MyText,50);
18
19 v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
20 LOOP
21 IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
22 EXIT;
23 END IF;
24
25 v_TOTROW := DBMS_SQL.LAST_ROW_COUNT;
26 v_MyROWID := DBMS_SQL.LAST_ROW_ID;
27 DBMS_OUTPUT.PUT_LINE('The last row count is: ' || v_TOTROW || ' The last ROWID is: ' || v_MyROWID);
28
29 DBMS_SQL.COLUMN_VALUE(v_CursorId,1,v_MyNum);
30 DBMS_SQL.COLUMN_VALUE(v_CursorId,2,v_MyText);
31
32 DBMS_OUTPUT.PUT_LINE(v_MyNum || ' ' || v_MyText);
33
34 END LOOP;
35
36 EXCEPTION
37 WHEN OTHERS THEN
38 RAISE;
39
40 DBMS_SQL.CLOSE_CURSOR(v_CursorID); -- Close the cursor
41
42 END;
43 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table mytable;
Table dropped.
SQL>
SQL> --
|