In article <QeF5k.5731$7A1.2936@[EMAIL PROTECTED]
>,
"gym dot scuba dot kennedy at gmail" <kennedyii@[EMAIL PROTECTED]
> wrote:
> "William Maslin" <CATmaslin@[EMAIL PROTECTED]
> wrote in message
> news:CATmaslin-59BEAB.18200016062008@[EMAIL PROTECTED]
> > In article
> > <bf92f83f-57a3-44d6-8897-2db3485d5543@[EMAIL PROTECTED]
>,
> > Ken Denny <ken@[EMAIL PROTECTED]
> wrote:
> >
> >> On Jun 16, 12:13 pm, William Maslin <CATmas...@[EMAIL PROTECTED]
>
wrote:
> >> > Hi,
> >> >
> >> > I'm been playing with using Filemaker Pro to generate and send sql
to
> >> > an
> >> > Oracle 10g database. The following works fine:
> >> >
> >> > UPDATE BEVIS DL TEST RESULTS ENTRY
> >> >
> >> > SET BEVIS DL TEST RESULTS ENTRY.RESULT VALUE = 'A section of liver
is
> >> > examined microscopically. '
> >> >
> >> > WHERE BEVIS DL TEST RESULTS ENTRY.RESULT NAME = 'HISTOPATHOLOGY'
AND
> >> >
> >> > BEVIS DL TEST RESULTS ENTRY.ACCESSION ID = '30257' AND
> >> >
> >> > BEVIS DL TEST RESULTS ENTRY.TEST CODE = '6005';
> >> >
> >> > But when I add a COMMIT to the end I get an ORA-00911 error.
> >> >
> >> > UPDATE BEVIS DL TEST RESULTS ENTRY
> >> >
> >> > SET BEVIS DL TEST RESULTS ENTRY.RESULT VALUE = 'A section of liver
is
> >> > examined microscopically. '
> >> >
> >> > WHERE BEVIS DL TEST RESULTS ENTRY.RESULT NAME = 'HISTOPATHOLOGY'
AND
> >> >
> >> > BEVIS DL TEST RESULTS ENTRY.ACCESSION ID = '30257' AND
> >> >
> >> > BEVIS DL TEST RESULTS ENTRY.TEST CODE = '6005';
> >> >
> >> > COMMIT;
> >> >
> >> > These statements are generated in Filemaker "calculation" fields in
> >> > which text and field values are concatenated to produce statements
like
> >> > those above. Please understand admit that I'm a complete Oracle and
SQL
> >> > novice. The little that I've learned has come from SQL Visual
> >> > Quickstart Guide and from hacking queries that I've stolen from
Crystal
> >> > Re****ts.
> >> >
> >> > Thanks!
> >>
> >> It could be that it's executing them in a single dynamic SQL command
> >> which should contain one command but it contains two. Try adding a
> >> "BEGIN" at the beginning and an "END;" at the end.
> >
> > I tried your suggestion and now I'm getting an ORA-06550 error: line 1
> > column 6:
> > PLS-00103: Encountered the symbol ";" when expecting the following:
> > begin case, declare, exit, goto... (etc.)
> >
> > There's something about one of those semicolons that Oracle doesn't
> > like. I think that the semicolon is the problem with the ORA-00911
> > error, too.
> >
> > Thanks for your help!
> You shouldn't be dynamically generating sql statements use nind
variables.
> Also looking at your sql statement it doesn't make sence
>
> is BEVIS DL TEST RESULTS ENTRY the table name? Don't do that. It is
going
> to drive you nuts when you have to use another tool to update or select
from
> a table. For example as it is written it won't work in sqlplus. I am
> guessing whatever filemaker pro is doing is trying to send 2 statements
as 2
> ( the update and then the commit.) Send them as 2 statements.
>
> UPDATE BEVIS DL TEST RESULTS ENTRY
>
> SET BEVIS DL TEST RESULTS ENTRY.RESULT VALUE = 'A section of liver is
> examined microscopically. '
>
> WHERE BEVIS DL TEST RESULTS ENTRY.RESULT NAME = 'HISTOPATHOLOGY' AND
>
> BEVIS DL TEST RESULTS ENTRY.ACCESSION ID = '30257' AND
>
> BEVIS DL TEST RESULTS ENTRY.TEST CODE = '6005';
>
Thanks, Jim. Yes, BEVIS DL_TEST_RESULTS_ENTRY is a table name (I don't
know where the underlines went when I pasted it in to this newsreader).
Filemaker has a feature called scripts which are like little robots or
stored procedures. One of these is "Execute SQL." I generate the SQL
in a filemaker "calculation" field (where static SQL and filemaker field
values are concatenated) and the Execute SQL script sends the contents
of that field to Oracle. Works slick as a whistle. I could send the
UPDATE without the COMMIT just fine. Adding the COMMIT is what was
messing me up. And it turns out that the semicolon after the BEGIN was
the problem
Thanks!
Bill
|
