Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Maybe you monks can see my error? I sure don't!
use DBI;

#connect to oracle data base
#blah blah blah

my $sql_cmd = "create sequence seq_the_table;";
$dbh->execute( $sql_cmd );

$sql_cmd = "alter table the_table add ( seq number primary key default
+ seq_the_table.nextval );";
$dbh->execute( $sql_cmd );

[download]
The error I keep getting is:
ORA-00907 missing right parennthesis

Do I need to esacpe the ()'s?

thanks

Edit kudra, 2002-08-22 Changed title

Replies are listed 'Best First'.
Re: I can't see the error?
by rbc (Curate) on Aug 21, 2002 at 19:13 UTC
    I would agree with sschneid.
    You can't do what you are trying to do with a single ALTER TABLE command
    You need to execute this SQL (through DBI or sqlplus):
    CREATE SEQUENCE seq_the_table;
ALTER TABLE the_table ADD( the_table_seq NUMBER );
update the_table set the_table_seq = seq_the_table.NEXTVAL;
ALTER TABLE the_table ADD( PRIMARY KEY (the_table_seq));

    [download]
    Hope that helps:)

    Update
    As far as the default seq_the_table.nextval goes.
    Apparently you cannot use a sequence in the default clause.
    At least in Oracle. You could use a before insert trigger
    to set the_table_seq.
[reply]
[d/l]
Re: I can't see the error?
by DamnDirtyApe (Curate) on Aug 21, 2002 at 18:43 UTC

    I may be way off here, but it looks like you're skipping a step. Aren't calls to the database supposed to look like either 

    $sth = $dbh->prepare( $sql_cmd ) ;
$sth->execute ;

    [download]

    or

    $dbh->do( $sql_cmd ) ;

    [download]

    ?


    _______________
    DamnDirtyApe 
    Those who know that they are profound strive for clarity. Those who
would like to seem profound to the crowd strive for obscurity.
            --Friedrich Nietzsche
[reply]
[d/l]
[select]
Re: I can't see the error?
by fglock (Vicar) on Aug 21, 2002 at 18:06 UTC

    You don't have to escape the (), but you might have to escape "" sometimes. Are you using perl variables inside the query or is it just that?

[reply]
Re: I can't see the error?
by smitz (Chaplain) on Aug 22, 2002 at 07:49 UTC
    Cant see the error? 

    $dbh->execute( $sql_cmd ) 
        or die "Couldn't execute: $DBI::errstr";

    [download]
    Always debug to the max. If the error turns out to be purely Oracle's fault, not that of your code, at least you can sleep a little more peacefuly at night.


    SMiTZ
[reply]
[d/l]
Re: I can't see the error?
by neilwatson (Priest) on Aug 21, 2002 at 18:15 UTC
    I don't believe you need the ; for the SQL statement.

    Neil Watson
    watson-wilson.ca

[reply]
Re: I can't see the error?
by sschneid (Deacon) on Aug 21, 2002 at 18:34 UTC
    It's an Oracle syntax error, not a perl problem, so I feel iffy about giving you any specific answers in this forum. However, as a starting point, you might want to check the syntax of your second $sql_cmd string definition.

    Good luck!
    scott.
[reply]
[d/l]

Back to Seekers of Perl Wisdom