Select data for update : Select Into « PL SQL « Oracle PL / SQL






Select data for update

    

SQL> CREATE TABLE myTable (
  2    id          INTEGER PRIMARY KEY,
  3    clobData CLOB NOT NULL
  4  );

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE initClob(clob_par IN OUT CLOB,id_par IN INTEGER) IS
  2  BEGIN
  3    SELECT clobData INTO clob_par FROM myTable WHERE id = id_par;
  4  END initClob;
  5  /

Procedure created.

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE readClob(id_par IN INTEGER) IS
  2    clobVariable CLOB;
  3    charVariable VARCHAR2(50);
  4    offsetPos INTEGER := 1;
  5    amount_var INTEGER := 50;
  6  BEGIN
  7    initClob(clobVariable, id_par);
  8    DBMS_LOB.READ(clobVariable, amount_var, offsetPos, charVariable);
  9    DBMS_OUTPUT.PUT_LINE('charVariable = ' || charVariable);
 10    DBMS_OUTPUT.PUT_LINE('amount_var = ' || amount_var);
 11  END readClob;
 12  /

Procedure created.

SQL>
SQL> CREATE OR REPLACE PROCEDURE copy_example IS
  2    clobSrc CLOB;
  3    clobDest CLOB;
  4    src_offsetPos INTEGER := 1;
  5    dest_offsetPos INTEGER := 7;
  6    amount_var INTEGER := 5;
  7  BEGIN
  8    SELECT clobData INTO clobSrc FROM myTable WHERE id = 2;
  9    SELECT clobData INTO clobDest FROM myTable WHERE id = 1 FOR UPDATE;
 10
 11    readClob(1);
 12    DBMS_LOB.COPY(clobDest, clobSrc, amount_var,dest_offsetPos, src_offsetPos);
 13    readClob(1);
 14
 15    ROLLBACK;
 16  END copy_example;
 17  /

Procedure created.

SQL>
SQL>
SQL> drop table myTable;

Table dropped.

   
    
    
    
  








Related examples in the same category

1.Select value from table into variable
2.Oracle returns an error when a SELECT statement returns more than one row
3.Catch too_many_rows Exception for 'Select into' statement
4.Multiple-Row SELECT Command with Several Exception-Handling Routines
5.Select count result into a variable
6.Output variable after 'select into'
7.SELECT into value pair
8.Select the number of employees into the l_emp_count variable
9.Select into and subquery
10.Select single value into variable
11.Select two columns into a cursor variable
12.Select value into a number variable in a for loop
13.Select value to variable one by one
14.If no records are retrieved for a SELECT - INTO statement the following error is returned
15.If too many records are returned for a SELECT - INTO statement the following error is returned
16.no_data_found from select ... into
17.select bulk collect into table collection
18.Use subquery in pl/sql block
19.This script demonstrates how to do a non-bulk select into elements of a PL/SQL table.
20.TOO_MANY_ROWS exception and select into command
21.Store max(salary) to a variable
22.Store max(tableName.column) to tableName.column.type variable
23.Bulk Collection: fetch a single row from the ALL_OBJECTS table.
24.Calculate salary by adding salary with max(salary)
25.Select value from aggregate function to variable