ROWID as data type : rowid « Select Query « Oracle PL / SQL






ROWID as data type

 
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> --

 








Related examples in the same category

1.Intersect rowid