ORA-00911 error and COMMIT

#1

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
Reports.

Thanks!

#2

On Jun 16, 12:13*pm, William Maslin <CATmas... (AT) cvm (DOT) msstate.edu> wrote:

Quote:

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
Reports.

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.

#3

On Jun 16, 12:13*pm, William Maslin <CATmas... (AT) cvm (DOT) msstate.edu> wrote:

Quote:

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
Reports.

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.

#4

On Jun 16, 12:13*pm, William Maslin <CATmas... (AT) cvm (DOT) msstate.edu> wrote:

Quote:

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
Reports.

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.

#5

On Jun 16, 12:13*pm, William Maslin <CATmas... (AT) cvm (DOT) msstate.edu> wrote:

Quote:

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
Reports.

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.

#6

In article
<bf92f83f-57a3-44d6-8897-2db3485d5543 (AT) m3g2000hsc (DOT) googlegroups.com>,
Ken Denny <ken (AT) kendenny (DOT) com> wrote:

Quote:

On Jun 16, 12:13*pm, William Maslin <CATmas... (AT) cvm (DOT) msstate.edu> 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
Reports.

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!

#7

In article
<bf92f83f-57a3-44d6-8897-2db3485d5543 (AT) m3g2000hsc (DOT) googlegroups.com>,
Ken Denny <ken (AT) kendenny (DOT) com> wrote:

Quote:

On Jun 16, 12:13*pm, William Maslin <CATmas... (AT) cvm (DOT) msstate.edu> 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
Reports.

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!

#8

In article
<bf92f83f-57a3-44d6-8897-2db3485d5543 (AT) m3g2000hsc (DOT) googlegroups.com>,
Ken Denny <ken (AT) kendenny (DOT) com> wrote:

Quote:

On Jun 16, 12:13*pm, William Maslin <CATmas... (AT) cvm (DOT) msstate.edu> 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
Reports.

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!

#9

In article
<bf92f83f-57a3-44d6-8897-2db3485d5543 (AT) m3g2000hsc (DOT) googlegroups.com>,
Ken Denny <ken (AT) kendenny (DOT) com> wrote:

Quote:

On Jun 16, 12:13*pm, William Maslin <CATmas... (AT) cvm (DOT) msstate.edu> 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
Reports.

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!

#10

"William Maslin" <CATmaslin (AT) cvm (DOT) msstate.edu> wrote

Quote:

In article
bf92f83f-57a3-44d6-8897-2db3485d5543...oglegroups.com>,
Ken Denny <ken (AT) kendenny (DOT) com> wrote:

On Jun 16, 12:13 pm, William Maslin <CATmas... (AT) cvm (DOT) msstate.edu> 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
Reports.

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';

Jim