Re: Oracle's 'mysql_insert

From: Chris Leonard <chris_at_databaseguy.com>
Date: Sat, 12 Jan 2002 23:39:44 -0600
Message-ID: <A4908.178$N1.166076@news.uswest.net>

Opi,

Use this syntax to retrieve the sequence value into your bind variable:

SELECT godir_seq.nextval INTO :dohajzlu FROM DUAL;

Also, after you have retrieved / generated a sequence value, you can get its value using NEXTVAL. For example, the following example uses the same value from myseq in all three INSERT statements.

BEGIN

   INSERT INTO mytab1 VALUES (myseq.nextval, 1, 2, 3);
   INSERT INTO mytab2 VALUES (myseq.currval, 'A', 'B, 'C', 'D');
   INSERT INTO mytab3 VALUES (myseq.currval, NULL, 5, 'ABC');

END; I'm not doing PHP, of course, just the PL/SQL part, but I hope that helps. And yes, all the docs are at tahiti.oracle.com, as Bricklen said.

HTH,
Chris

Chris Leonard
MCSE, MCDBA, MCT, OCP, CIW
The Database Guy at PPI
http://www.propoint.com
Brainbench MVP for Oracle Admin
http://www.brainbench.com

"Bricklen Anderson" <bricklen_at_shaw.ca> wrote in message news:3C40FA56.D7F3B629_at_shaw.ca...
> try tahiti.oracle.com or asktom.oracle.com
> there are tons of links out there, if you just do a few searches I'm
> sure you can find something useful.
>
> Cheers,
>
> Bricklen
>
> Opi wrote:
> >
> > Hi,
> >
> > I don't want to annoy you with mysql stuff, but that describes my
problem
> > best.
> >
> > I need to get the last value added to the table's id column, what is get
> > from a sequence.
> > 1) Is using SEQUENCE the best way to get the incrementing id's value?
> > 2) How to get the inserted value? Do I need to write a PL script?
> >
> > Now, I am trying this: (the code is written in PHP)
> >
> > $sql = "
> > BEGIN
> > :dohajzlu := godir_seq.nextval;
> > INSERT INTO godir VALUES(:dohajzlu, :pod, :xpath);
> > END;";
> >
> > $stmt = OCIParse($spoj,$sql);
> > OCIBindByName($stmt,":dohajzlu",&$b_curid,32);
> > OCIBindByName($stmt,":pod",&$b_pod,32);
> > OCIBindByName($stmt,":xpath",&$b_xpath,32);
> > OCIExecute($stmt);
> >
> > But this throws an error:
> > OCIStmtExecute: ORA-06550: line 1, col 1: PLS-00103: found "" while
> > expecting one of: begin declare ... blah blah ... and so on.
> > I should say that " BEGIN :dohajzlu := 55; END; " works fine.
> > " BEGIN :dohajzlu := (SELECT godir_seq.nextval FROM dual); END; "
doesn't.
> >
> > I'm still turning at the same place, because noone was kind enough to
tell
> > me where to get WHOLE oracle manual for FREE. I still have several f***g
bad
> > written tutorials, lot of them about sql*plus, what I don't need at all.
So
> > please, if you have something big about Oracle8i SQL && PL/SQL, send it
to
> > me!!! Or just a link. And resolving this problem would help me much,
too.
> > Thanks, Opi
> >
> > Thanks, Opi
>
Received on Sat Jan 12 2002 - 23:39:44 CST

Re: Oracle's 'mysql_insert_id'?