Could it be that test is a keyword? If so, you'll need to properly quote it. (I don't happen to know how to quote identifiers in Oracle.)

Update: On second thought, not likely.

test is not a keyword but i changed my table name from test to prod...

SQL statment runs and insert the requred input in the table , so SQL statment is not a problem

I can make it simpler by doing

Insert into prod (ss)
select max_ss + 1 FROM (SELECT MAX (ss) 
max_ss FROM prod)
[download]

Better to use a sequence:

create or replace sequence prod_seq minvalue 1 nomaxvalue start with x
+xxx increment by 1 cache 20 noorder nocycle
[download]

Then your insert select can use the nextval function:

insert into prod (ss)
 select prod_seq.nextval from dual
[download]

You need to create the sequence only once, but be sure to set the "start with xxxx" to a value higher than anything in your table.

Paul

SELECT statment runs fine as sson as I put INSERT INTO <TABLE NAME> it gives me error message

But I see in DBI the insert statent is done

$sth = $dbh->prepare(qq{
    INSERT INTO people (fullname, age) VALUES (?, ?)
  });
[download]

Would that make any difference

No, DBI/DBD just passes the sql statement to the database. But from the syntax of INSERT, it looks like you are not allowed to have a specific column list to insert into if you have a subquery (maybe it depends on the Oracle or Oracle client library version?).

But, I agree with the poster elsewhere in this thread that a sequence would be a much better way to get an incrementing value.

use DBI;
my $dd = DBI->connect('dbi:Oracle:', 'scott', 'tiger',
  { RaiseError => 1, PrintError => 0 });
my $sth = $dd->prepare("insert into prod (ss) select 1+max(ss) from pr
+od");
$sth->execute;
$dd->disconnect;
[download]

still same issue... no idea whats going on