How to check the version of an Oracle Database with PL/SQL

Login to the database and execute the following:

[sql]select * from v$version;
–OR:
select * from product_component_version;[/sql]

Result:

[text]Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
PL/SQL Release 11.1.0.7.0 – Production
CORE    11.1.0.7.0    Production
TNS for 64-bit Windows: Version 11.1.0.7.0 – Production
NLSRTL Version 11.1.0.7.0 – Production[/text]

 

 

RegEx use in Find with PLSQL Developer

The following are supported regular expression operators for use in PL/SQL Developer by Allround Automations:[text]a+ One or more occurrences of a
a* Zero or more occurences of a
a? Zero or one (i.e. optional) occurence of a
a{n} Exactly n occurences of a
a{n,} n or more occurences of a
a{,m} Zero or at most m occurences of a
a{n,m} At least n but not more than m occurences of a
a|b Either a or b
a||b a or b or both a and b in any order
abc a followed by b followed by c
[abc] A single character, one of a or b or c
[a-b] A single character, ranging in value from a to b inclusive
[^abc] A single character, any except a, b or c
(abc) a followed by b followed by c
“abc” The letters a followed by b followed by c with no special significance attached to a, b or c
. Any character except a newline
\a The letter a, with no special significance attached to a, special forms:\t The tab character
\n The newline character
\r The return character
\f The formfeed character
\b The backspace character
\xNN The hex character NN
\0ooo The octal character ooo
\w A single character, one of [a-zA-Z0-9_]
\W Any single character not matching \w
\d A single character [0-9]
\D A single character not matching \d
\s A whitespace character [\t\r\n\f\b\ ]
\S A single character not matching \s[/text]
_________________________
All credits to Marco Kalter (Allround Automations) for this tip!

how to create a cursor to read through table rows

[sql]declare
  counter1 number := 0;
  counter2 number := 0;
  cursor cur_t is
    SELECT * from emp where e_name = 'Ptere'; --set cursor, a select statement
  rec_t emp%rowtype; --defines this is a row from the database

begin
  counter1 := 0;
  counter2 := 0; --some counters to log the records being changed
  open cur_t; --open cursor

  loop
    --begin loop
  
    fetch cur_t
      into rec_t; --fetches cursor row into the rec_t variable
  
    exit when cur_t%notfound;
    UPDATE emp SET e_name = 'Peter' where e_name = 'Ptere';
    counter1 := counter1 + 1; --increment updates
    insert into update_log
      (record_id, table_name, description) --useful for ref. purposes
    values
      (rec_t.emp_id, 'emp', 'Peter');
    counter2 := counter2 + 1; --increment inserts
    commit;
  end loop;
  --row_count := sql%rowcount; --sql counter for update statements
  dbms_output.put_line('EMP: ' || 'name changed, updated: ' || counter1);
  dbms_output.put_line('insert into UPDATE_LOG: ' || counter2);
  close cur_t; --close the cursor
end;
[/sql]

Note for DBMS output we need to set the command prompt:[sql]set serveroutput on size 1000000 [/sql]format wrapped

how to find specific text in oracle schema using a procedure and save to table

The following function will find any text matching the variable set within the procedure.
(the output will display on screen -dbms_output)

To execute: <SQL> [sql]execute update_found_records_proc;[/sql]

We could also add a parameter on calling the procedure as:
CREATE OR REPLACE PROCEDURE FIND_IN_RECORDS_PROC(val IN varchar2) IS…
note: ‘val’ is set once and used anywhere later in the code

Credit:: Rene Nyffenneger on Oracle

PL/SQL code: (more…)