Using %ROWTYPE for INSERTs and UPDATEs

The below shows how to use %ROWTYPE for INSERTs and UPDATEs. I have not seen UPDATE table SET ROW syntax very often.

————————————–

SQL> create table cds(id number, artist_name varchar(255), album_name varchar2(255));

Table created.

SQL> create sequence cds_seq;

Sequence created.

SQL> DECLARE
2 l_new_cd cds%ROWTYPE;
3 BEGIN
4 l_new_cd.id := cds_seq.nextval;
5 l_new_cd.album_name := ‘The Future’;
6 l_new_cd.artist_name := ‘Leonard Cohen’;
7 INSERT INTO cds
8 VALUES l_new_cd;
9 END;
10 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> col artist_name format a20
SQL> col album_name format a20
SQL> select * from cds;

ID ARTIST_NAME ALBUM_NAME
———- ——————– ——————–
1 Leonard Cohen The Future

SQL> DECLARE
2 l_updated_cd cds%ROWTYPE;
3 BEGIN
4 l_updated_cd.id := cds_seq.nextval;
5 l_updated_cd.album_name := ‘Oracular Spectacular’;
6 l_updated_cd.artist_name := ‘MGMT’;
7 UPDATE cds
8 SET ROW = l_updated_cd
9 WHERE album_name = ‘The Future’;
10* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select * from cds;

ID ARTIST_NAME ALBUM_NAME
———- ——————– ——————–
1 MGMT Oracular Spectacular
————————————–

Do you think MGMT’s album was named after a particularly impressive OUG?

Explicit connection to a database without using tnsnames

Time for a quick “did you know..?“

You can connect to an Oracle Database using SQLPLUS without the need for a tnsnames.ora file using the following syntax:

sqlplus sys/password@//server:1521/orcl as sysdba

F7: Handy SQL*Plus Shortcut Key

When using SQL*Plus via the Command Prompt in Windows, you can press F7 on the keyboard at any time to view a list of all the recent commands that you have run (see image on the left). You can scroll up and down the list with the arrow keys and replay any of the commands using the Enter key.

This is a feature of the Command Prompt but is particularly useful when working with SQL*Plus.

One other handy feature of the Command Prompt (again with particular relevance to SQL*Plus) is that you can press the UP and DOWN arrows to cycle through a history of commands that you have issued.

Anyone know of any other nifty shortcuts for use in SQL*Plus?