Re: Help on SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 04 May 1998 19:48:16 GMT
Message-ID: <35541738.14489424_at_192.86.155.100>


A copy of this was sent to mehesh_at_bmerhcb1.ca.nortel.com (Mahesh Menon) (if that email address didn't require changing) On 4 May 1998 16:18:04 GMT, you wrote:

>
>hi,
> a couple of SQL related queries from an SQL 'moron'.
>
> ONE
> ---
> consider the following table which contains
> the following tuple:
>
> SITNO NAME CATEGORY
> ---------------------------------------
> 500 Mahesh UNIX
>
> i'd like to add another tuple with name = 'MENON'
> and category = 'DOS'. the sitno field should be
> generated automatically and should be MAX(SITNO) + 1.
>
> can this be done using a single SQL statement
> with or without the use of temporary tables.
>

Yes it can (and I hesitate to tell you how because it is a bad practice) but assuming a unique index on SITNO...

insert into T
select max(sitno)+1, 'MENON', 'DOS'
from T;

will do what you ask -- but it will also make it so that new records can only be added by one person at a time (and if two people try it at the same time, one of them will get an error message about a duplicate key).

The preferred method would be to use a sequence such as:

SQL> create sequence T_seq;

SQL> insert into T values ( T_seq.nextval, 'MENON', 'DOS' );

Sequences are non-blocking number generators. Many people can use concurrently without blocking. They will generate gaps and they will not be sequential but they will be unique.

>
> TWO
> ---
> how do i insert tuples into a table containing a
> 'long' field using SQL*PLUS.
>

Character string literals in sql statments are limited in size. In order to get around this limit in SQL*Plus, you can use pl/sql variables of upto 32k in size and put them in the statment instead of a character.

For example:

declare

    p_long_string long;
begin

    p_long_string := 'lots and lots and lots and lots of text .....
......
.....';

    insert into t values ( p_long_string ); end;
/

>
>mahesh "clueless" menon
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon May 04 1998 - 21:48:16 CEST

Original text of this message