SEO

Hi all,

I'm trying to write a script in which I need the result of a query to
perform an insert in a table.

Something like this:

var = select max(Xid) from tbl_X;
var = var + 1;
insert into tbl_X (Xid) values (var);

Is this possible without uising Pl/SQL?

monesseldeur wrote:
> Hi all,
>
> I'm trying to write a script in which I need the result of a query to
> perform an insert in a table.
>
> Something like this:
>
> var = select max(Xid) from tbl_X;
> var = var + 1;
> insert into tbl_X (Xid) values (var);
>
> Is this possible without uising Pl/SQL?

Yes.

But far more likely to create problems.

Is this a school assignment?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On 4 dec, 09:03, DA Morgan <damor...@psoug.org> wrote:
> monesseldeur wrote:
> > Hi all,
>
> > I'm trying to write a script in which I need the result of a query to
> > perform an insert in a table.
>
> > Something like this:
>
> > var = select max(Xid) from tbl_X;
> > var = var + 1;
> > insert into tbl_X (Xid) values (var);
>
> > Is this possible without uising Pl/SQL?
>
> Yes.
>
> But far more likely to create problems.
>
> Is this a school assignment?
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor...@x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

No, a work assignment ;-))

It is used as an installationscript, thus only run once.

How can it be done?

On 4 ÄÅË, 10:47, monesseldeur <raymondesseld...@gmail.com> wrote:
> Hi all,
>
> I'm trying to write a script in which I need the result of a query to
> perform an insert in a table.
>
> Something like this:
>
> var = select max(Xid) from tbl_X;
> var = var + 1;
> insert into tbl_X (Xid) values (var);
>
> Is this possible without uising Pl/SQL?

insert into tbl_X (Xid)
select max(Xid)+1 from tbl_X

On Dec 4, 1:47 am, monesseldeur <raymondesseld...@gmail.com> wrote:
> Hi all,
>
> I'm trying to write a script in which I need the result of a query to
> perform an insert in a table.
>
> Something like this:
>
> var = select max(Xid) from tbl_X;
> var = var + 1;
> insert into tbl_X (Xid) values (var);
>
> Is this possible without uising Pl/SQL?

Certainly, but it's not the brightest of ideas. From one session I
ran this:

SQL> @insert_w_var
SQL> --
SQL> -- Create test table
SQL> --
SQL> create table ins_test(keycol number, keyval varchar2(20));

Table created.

SQL>
SQL> --
SQL> -- Add primary key constraint
SQL> --
SQL> alter table ins_test add constraint ins_test_pk primary key
(keycol);

Table altered.

SQL>
SQL> --
SQL> -- Insert data
SQL> --
SQL> insert all
2 into ins_test
3 (keycol, keyval)
4 values
5 (1, 'First value')
6 into ins_test
7 (keycol, keyval)
8 values
9 (2, 'Second value')
10 into ins_test
11 (keycol, keyval)
12 values
13 (3, 'Third value')
14 into ins_test
15 (keycol, keyval)
16 values
17 (4, 'Fourth value')
18 into ins_test
19 (keycol, keyval)
20 values
21 (5, 'Fifth value')
22 into ins_test
23 (keycol, keyval)
24 values
25 (6, 'Sixth value')
26 select * from dual;

6 rows created.

SQL>
SQL> --
SQL> -- Commit changes
SQL> --
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- Start questionable process to
SQL> -- insert more data based upon
SQL> -- current max(keycol) value
SQL> --
SQL>
SQL> --
SQL> -- Set a variable to reuse the returned
SQL> -- max value
SQL> --
SQL> --
SQL> -- Rather dumb idea as the max() from
SQL> -- any query will miss any new values
SQL> -- inserted by other sessions and not
SQL> -- yet committed
SQL> --
SQL> column maxval new_value next_key
SQL>
SQL> --
SQL> -- Return current max(keycol)
SQL> --
SQL> -- Not reliable in the least
SQL> -- unless there is only one user
SQL> -- ever on the system, and it's
SQL> -- you
SQL> --
SQL> select max(keycol) maxval from ins_test;


MAXVAL
----------

6

SQL>
SQL> --
SQL> -- Use the saved value for the next insert
SQL> --
SQL> -- Not a good idea
SQL> --
SQL> -- Using a sequence and a trigger is far
SQL> -- more reliable and recommended
SQL> --
SQL> insert into ins_test (keycol, keyval)
2 values (&next_key + 1, 'Next value in line');
old 2: values (&next_key + 1, 'Next value in line')
new 2: values ( 6 + 1, 'Next value in line')

1 row created.

SQL>
SQL> --
SQL> -- Wait for a minute so a second insert of this
SQL> -- same type can occur from a different session
SQL> --
SQL>
SQL> exec dbms_lock.sleep(60);

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Commit the changes, or try to
SQL> --
SQL> commit;

Commit complete.

SQL>
SQL> spool off

While in the midst of that dbms_lock.sleep call I ran this from a
second session:

SQL> @ins_w_var_second_sess
SQL> --
SQL> -- Start questionable process to
SQL> -- insert more data based upon
SQL> -- current max(keycol) value
SQL> --
SQL>
SQL> --
SQL> -- Set a variable to reuse the returned
SQL> -- max value
SQL> --
SQL> --
SQL> -- Rather dumb idea as the max() from
SQL> -- any query will miss any new values
SQL> -- inserted by other sessions and not
SQL> -- yet committed
SQL> --
SQL> column maxval new_value next_key
SQL>
SQL> --
SQL> -- Return current max(keycol)
SQL> --
SQL> -- Not reliable in the least
SQL> -- unless there is only one user
SQL> -- ever on the system, and it's
SQL> -- you
SQL> --
SQL> select max(keycol) maxval from ins_test;


MAXVAL
----------

6

SQL>
SQL> --
SQL> -- Use the saved value for the next insert
SQL> --
SQL> -- Not a good idea
SQL> --
SQL> -- Using a sequence and a trigger is far
SQL> -- more reliable and recommended
SQL> --
SQL> insert into ins_test (keycol, keyval)
2 values (&next_key + 1, 'Next value in line');
old 2: values (&next_key + 1, 'Next value in line')
new 2: values ( 6 + 1, 'Next value in line')
insert into ins_test (keycol, keyval)
*
ERROR at line 1:
ORA-00001: unique constraint (BING.INS_TEST_PK) violated


SQL>
SQL> --
SQL> -- Wait for a minute so a second insert of this
SQL> -- same type can occur from a different session
SQL> --
SQL>
SQL> exec dbms_lock.sleep(60);

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Commit the changes, or try to
SQL> --
SQL> commit;

Commit complete.

SQL>
SQL> spool off

Notice the SAME max(key_col) appears, even after I've inserted a new
value in the table, because the first insert hasn't committed. Also,
which you can't see, was the effect of session #2 being locked and
waiting while the dbms_lock.sleep(60) call finished in session #1.
Your 'process' doesn't scale well at all, and it doesn't behave as you
might expect. Again, it's not the brightest idea.

The accepted manner to do this is to use an ordered sequence and a
trigger, such that any session can insert into the table and not
provide conflicting values for the key column or columns:

SQL> @insert_w_trig
SQL> --
SQL> -- Create test table
SQL> --
SQL> create table ins_test(keycol number, keyval varchar2(20));

Table created.

SQL>
SQL> --
SQL> -- Add primary key constraint
SQL> --
SQL> alter table ins_test add constraint ins_test_pk primary key
(keycol);

Table altered.

SQL>
SQL> --
SQL> -- Add a sequence, and a trigger, to populate the key column
SQL> --
SQL> create sequence ins_test_seq
2 start with 1
3 increment by 1
4 nomaxvalue
5 nocycle
6 order;

Sequence created.

SQL>
SQL> create or replace trigger pop_ins_test_pk
2 before insert on ins_test
3 for each row
4 begin
5 select ins_test_seq.nextval
6 into :new.keycol
7 from dual;
8 end;
9 /

Trigger created.

SQL>
SQL> --
SQL> -- Insert data
SQL> --
SQL> insert all
2 into ins_test
3 (keyval)
4 values
5 ('First value')
6 into ins_test
7 (keyval)
8 values
9 ('Second value')
10 into ins_test
11 (keyval)
12 values
13 ('Third value')
14 into ins_test
15 (keyval)
16 values
17 ('Fourth value')
18 into ins_test
19 (keyval)
20 values
21 ('Fifth value')
22 into ins_test
23 (keyval)
24 values
25 ('Sixth value')
26 select * from dual;

6 rows created.

SQL>
SQL> --
SQL> -- Commit changes
SQL> --
SQL> commit;

Commit complete.

SQL>
SQL> insert into ins_test (keyval)
2 values ('Next value in line');

1 row created.

SQL>
SQL> --
SQL> -- Wait for a minute so a second insert of this
SQL> -- same type can occur from a different session
SQL> --
SQL>
SQL> exec dbms_lock.sleep(60);

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Commit the changes, or try to
SQL> --
SQL> commit;

Commit complete.

SQL>
SQL> select * from ins_test;

KEYCOL
KEYVAL
----------
--------------------
8 Next value in
line
1 First
value
2 Second
value
3 Third
value
4 Fourth
value
5 Fifth
value
6 Sixth
value
7 Next value in
line

8 rows selected.

SQL> spool off

And, from a second session inserting at the same time:

SQL> @ins_w_trig_second_sess
SQL> --
SQL> -- Using a sequence and a trigger is far
SQL> -- more reliable and recommended
SQL> --
SQL> insert into ins_test (keyval)
2 values ('Next value in line');

1 row created.

SQL>
SQL> --
SQL> -- Wait for a minute so a second insert of this
SQL> -- same type can occur from a different session
SQL> --
SQL>
SQL> exec dbms_lock.sleep(60);

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Commit the changes, or try to
SQL> --
SQL> commit;

Commit complete.

SQL>
SQL> select * from ins_test;

KEYCOL
KEYVAL
----------
--------------------
8 Next value in
line
1 First
value
2 Second
value
3 Third
value
4 Fourth
value
5 Fifth
value
6 Sixth
value
7 Next value in
line

8 rows selected.

SQL> spool off

Note there were no problems with either insert 'hanging' due to the
data changes not being committed, and no constraint violations do to
an erroneous select strategy.

To answer your question, yes, it CAN be done, sort of. But, I
wouldn't recommend it. Ever.


David Fitzjarrell

On 4 Dec, 07:47, monesseldeur <raymondesseld...@gmail.com> wrote:
> Hi all,
>
> I'm trying to write a script in which I need the result of a query to
> perform an insert in a table.
>
> Something like this:
>
> var = select max(Xid) from tbl_X;
> var = var + 1;
> insert into tbl_X (Xid) values (var);
>
> Is this possible without uising Pl/SQL?

Have you considered a database sequence?

http://download.oracle.com/docs/cd/B....htm#sthref883

HTH

-g

On Dec 3, 11:47 pm, monesseldeur <raymondesseld...@gmail.com> wrote:
> Hi all,
>
> I'm trying to write a script in which I need the result of a query to
> perform an insert in a table.
>
> Something like this:
>
> var = select max(Xid) from tbl_X;
> var = var + 1;
> insert into tbl_X (Xid) values (var);
>
> Is this possible without uising Pl/SQL?

How about:
insert into tbl_X(Xid)
select max(Xid) + 1 from tbl_X;

As others have pointed out, you probably want to look into sequences.