Oracle configuration. “create or replace” function create invalid object

-1

I recently moved my database to a new server (both Oracle 11g).
In the new host, there is a weird behavior, that whenever I run something like:

create or replace test_trigger

   before insert or update

   on test_table

   for each row

begin

   select 1 from dual;

end;

/

The trigger created is invalid without any error in log. I tried to recompile, it still invalid.

It only happen with the function "create or replace". If I drop the trigger and re-create again, it would be valid.

My question is, did I config something incorrectly? How can I check it? Thank you.

edited Nov 23 '18 at 19:18

Littlefoot

20.6k71433

asked Nov 23 '18 at 7:52

Frzzy

124113

" If I drop the trigger and re-create again, it would be valid." Valid? Not the posted trigger it wouldn't.
– APC
Nov 24 '18 at 13:58

Running this command should give you the error message in Littlefoot's answer. I'm not sure what you mean by there's not error in "log". It should be there in your sqlplus output, assuming that's what you're using.
– eaolson
Nov 24 '18 at 23:15

add a comment |

-1

I recently moved my database to a new server (both Oracle 11g).
In the new host, there is a weird behavior, that whenever I run something like:

create or replace test_trigger

   before insert or update

   on test_table

   for each row

begin

   select 1 from dual;

end;

/

The trigger created is invalid without any error in log. I tried to recompile, it still invalid.

It only happen with the function "create or replace". If I drop the trigger and re-create again, it would be valid.

My question is, did I config something incorrectly? How can I check it? Thank you.

edited Nov 23 '18 at 19:18

Littlefoot

20.6k71433

asked Nov 23 '18 at 7:52

Frzzy

124113

" If I drop the trigger and re-create again, it would be valid." Valid? Not the posted trigger it wouldn't.
– APC
Nov 24 '18 at 13:58

Running this command should give you the error message in Littlefoot's answer. I'm not sure what you mean by there's not error in "log". It should be there in your sqlplus output, assuming that's what you're using.
– eaolson
Nov 24 '18 at 23:15

add a comment |

-1

I recently moved my database to a new server (both Oracle 11g).
In the new host, there is a weird behavior, that whenever I run something like:

create or replace test_trigger

   before insert or update

   on test_table

   for each row

begin

   select 1 from dual;

end;

/

The trigger created is invalid without any error in log. I tried to recompile, it still invalid.

It only happen with the function "create or replace". If I drop the trigger and re-create again, it would be valid.

My question is, did I config something incorrectly? How can I check it? Thank you.

edited Nov 23 '18 at 19:18

Littlefoot

20.6k71433

asked Nov 23 '18 at 7:52

Frzzy

124113

I recently moved my database to a new server (both Oracle 11g).
In the new host, there is a weird behavior, that whenever I run something like:

create or replace test_trigger

   before insert or update

   on test_table

   for each row

begin

   select 1 from dual;

end;

/

The trigger created is invalid without any error in log. I tried to recompile, it still invalid.

It only happen with the function "create or replace". If I drop the trigger and re-create again, it would be valid.

My question is, did I config something incorrectly? How can I check it? Thank you.

oracle plsql oracle11g configuration database-trigger

edited Nov 23 '18 at 19:18

Littlefoot

20.6k71433

asked Nov 23 '18 at 7:52

Frzzy

124113

edited Nov 23 '18 at 19:18

Littlefoot

20.6k71433

asked Nov 23 '18 at 7:52

Frzzy

124113

edited Nov 23 '18 at 19:18

Littlefoot

20.6k71433

edited Nov 23 '18 at 19:18

Littlefoot

20.6k71433

edited Nov 23 '18 at 19:18

Littlefoot

20.6k71433

asked Nov 23 '18 at 7:52

Frzzy

124113

asked Nov 23 '18 at 7:52

Frzzy

124113

asked Nov 23 '18 at 7:52

Frzzy

124113

" If I drop the trigger and re-create again, it would be valid." Valid? Not the posted trigger it wouldn't.
– APC
Nov 24 '18 at 13:58

Running this command should give you the error message in Littlefoot's answer. I'm not sure what you mean by there's not error in "log". It should be there in your sqlplus output, assuming that's what you're using.
– eaolson
Nov 24 '18 at 23:15

add a comment |

" If I drop the trigger and re-create again, it would be valid." Valid? Not the posted trigger it wouldn't.
– APC
Nov 24 '18 at 13:58

Running this command should give you the error message in Littlefoot's answer. I'm not sure what you mean by there's not error in "log". It should be there in your sqlplus output, assuming that's what you're using.
– eaolson
Nov 24 '18 at 23:15

" If I drop the trigger and re-create again, it would be valid." Valid? Not the posted trigger it wouldn't.
– APC
Nov 24 '18 at 13:58

Running this command should give you the error message in Littlefoot's answer. I'm not sure what you mean by there's not error in "log". It should be there in your sqlplus output, assuming that's what you're using.
– eaolson
Nov 24 '18 at 23:15

add a comment |

1 Answer
1

active

oldest

votes

Code you posted won't compile, not in any Oracle database I know. Why? Wrong syntax.

Here's a demonstration:

SQL> create or replace test_trigger

  2     before insert or update

  3     on test_table

  4     for each row

  5  begin

  6     select 1 from dual;

create or replace test_trigger

                  *

ERROR at line 1:

ORA-00922: missing or invalid option





SQL> end;

SP2-0042: unknown command "end" - rest of line ignored.

SQL> /

So, what's wrong with it?

create (or replace) wants to know what you're going to create. "test_trigger"? As far as Oracle is concerned, that could be "mickey_mouse" and the result will be the same. It is the trigger keyword that is missing

SELECT in PL/SQL requires an INTO clause, so that you could store the result into something

in order to be able to do that, you have to declare a variable

Here's code that, actually, compiles:

SQL> create or replace trigger test_trigger   --> this

  2     before insert or update

  3     on test_table

  4     for each row

  5  declare

  6    l_dummy number;     --> this

  7  begin

  8     select 1

  9     into l_dummy       --> this

 10     from dual;

 11  end;

 12  /



Trigger created.



SQL>

So, it seems that you misinterpret reality.

answered Nov 23 '18 at 19:17

Littlefoot

20.6k71433

you're right normally the answer should be this related to the given syntax, but OP asks about the weird behaviour of the current situation.
– Barbaros Özhan
Nov 23 '18 at 19:35

1

Aha; I think I get your point, @Barbaros. You (and the OP) are saying that Oracle just won't execute create or replace, but drop + create instead. Well, if that's the case, a full environment versions (Oracle + operating system) might help. Metalink could provide some more info as well. If nothing helps, a service request should be opened.
– Littlefoot
Nov 23 '18 at 19:40

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53442604%2foracle-configuration-create-or-replace-function-create-invalid-object%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

Code you posted won't compile, not in any Oracle database I know. Why? Wrong syntax.

Here's a demonstration:

SQL> create or replace test_trigger

  2     before insert or update

  3     on test_table

  4     for each row

  5  begin

  6     select 1 from dual;

create or replace test_trigger

                  *

ERROR at line 1:

ORA-00922: missing or invalid option





SQL> end;

SP2-0042: unknown command "end" - rest of line ignored.

SQL> /

So, what's wrong with it?

create (or replace) wants to know what you're going to create. "test_trigger"? As far as Oracle is concerned, that could be "mickey_mouse" and the result will be the same. It is the trigger keyword that is missing

SELECT in PL/SQL requires an INTO clause, so that you could store the result into something

in order to be able to do that, you have to declare a variable

Here's code that, actually, compiles:

SQL> create or replace trigger test_trigger   --> this

  2     before insert or update

  3     on test_table

  4     for each row

  5  declare

  6    l_dummy number;     --> this

  7  begin

  8     select 1

  9     into l_dummy       --> this

 10     from dual;

 11  end;

 12  /



Trigger created.



SQL>

So, it seems that you misinterpret reality.

answered Nov 23 '18 at 19:17

Littlefoot

20.6k71433

you're right normally the answer should be this related to the given syntax, but OP asks about the weird behaviour of the current situation.
– Barbaros Özhan
Nov 23 '18 at 19:35

1

Aha; I think I get your point, @Barbaros. You (and the OP) are saying that Oracle just won't execute create or replace, but drop + create instead. Well, if that's the case, a full environment versions (Oracle + operating system) might help. Metalink could provide some more info as well. If nothing helps, a service request should be opened.
– Littlefoot
Nov 23 '18 at 19:40

add a comment |

Code you posted won't compile, not in any Oracle database I know. Why? Wrong syntax.

Here's a demonstration:

SQL> create or replace test_trigger

  2     before insert or update

  3     on test_table

  4     for each row

  5  begin

  6     select 1 from dual;

create or replace test_trigger

                  *

ERROR at line 1:

ORA-00922: missing or invalid option





SQL> end;

SP2-0042: unknown command "end" - rest of line ignored.

SQL> /

So, what's wrong with it?

create (or replace) wants to know what you're going to create. "test_trigger"? As far as Oracle is concerned, that could be "mickey_mouse" and the result will be the same. It is the trigger keyword that is missing

SELECT in PL/SQL requires an INTO clause, so that you could store the result into something

in order to be able to do that, you have to declare a variable

Here's code that, actually, compiles:

SQL> create or replace trigger test_trigger   --> this

  2     before insert or update

  3     on test_table

  4     for each row

  5  declare

  6    l_dummy number;     --> this

  7  begin

  8     select 1

  9     into l_dummy       --> this

 10     from dual;

 11  end;

 12  /



Trigger created.



SQL>

So, it seems that you misinterpret reality.

answered Nov 23 '18 at 19:17

Littlefoot

20.6k71433

you're right normally the answer should be this related to the given syntax, but OP asks about the weird behaviour of the current situation.
– Barbaros Özhan
Nov 23 '18 at 19:35

1

Aha; I think I get your point, @Barbaros. You (and the OP) are saying that Oracle just won't execute create or replace, but drop + create instead. Well, if that's the case, a full environment versions (Oracle + operating system) might help. Metalink could provide some more info as well. If nothing helps, a service request should be opened.
– Littlefoot
Nov 23 '18 at 19:40

add a comment |

Code you posted won't compile, not in any Oracle database I know. Why? Wrong syntax.

Here's a demonstration:

SQL> create or replace test_trigger

  2     before insert or update

  3     on test_table

  4     for each row

  5  begin

  6     select 1 from dual;

create or replace test_trigger

                  *

ERROR at line 1:

ORA-00922: missing or invalid option





SQL> end;

SP2-0042: unknown command "end" - rest of line ignored.

SQL> /

So, what's wrong with it?

create (or replace) wants to know what you're going to create. "test_trigger"? As far as Oracle is concerned, that could be "mickey_mouse" and the result will be the same. It is the trigger keyword that is missing

SELECT in PL/SQL requires an INTO clause, so that you could store the result into something

in order to be able to do that, you have to declare a variable

Here's code that, actually, compiles:

SQL> create or replace trigger test_trigger   --> this

  2     before insert or update

  3     on test_table

  4     for each row

  5  declare

  6    l_dummy number;     --> this

  7  begin

  8     select 1

  9     into l_dummy       --> this

 10     from dual;

 11  end;

 12  /



Trigger created.



SQL>

So, it seems that you misinterpret reality.

answered Nov 23 '18 at 19:17

Littlefoot

20.6k71433

Code you posted won't compile, not in any Oracle database I know. Why? Wrong syntax.

Here's a demonstration:

SQL> create or replace test_trigger

  2     before insert or update

  3     on test_table

  4     for each row

  5  begin

  6     select 1 from dual;

create or replace test_trigger

                  *

ERROR at line 1:

ORA-00922: missing or invalid option





SQL> end;

SP2-0042: unknown command "end" - rest of line ignored.

SQL> /

So, what's wrong with it?

create (or replace) wants to know what you're going to create. "test_trigger"? As far as Oracle is concerned, that could be "mickey_mouse" and the result will be the same. It is the trigger keyword that is missing

SELECT in PL/SQL requires an INTO clause, so that you could store the result into something

in order to be able to do that, you have to declare a variable

Here's code that, actually, compiles:

SQL> create or replace trigger test_trigger   --> this

  2     before insert or update

  3     on test_table

  4     for each row

  5  declare

  6    l_dummy number;     --> this

  7  begin

  8     select 1

  9     into l_dummy       --> this

 10     from dual;

 11  end;

 12  /



Trigger created.



SQL>

So, it seems that you misinterpret reality.

answered Nov 23 '18 at 19:17

Littlefoot

20.6k71433

answered Nov 23 '18 at 19:17

Littlefoot

20.6k71433

answered Nov 23 '18 at 19:17

Littlefoot

20.6k71433

answered Nov 23 '18 at 19:17

Littlefoot

20.6k71433

you're right normally the answer should be this related to the given syntax, but OP asks about the weird behaviour of the current situation.
– Barbaros Özhan
Nov 23 '18 at 19:35

1

Aha; I think I get your point, @Barbaros. You (and the OP) are saying that Oracle just won't execute create or replace, but drop + create instead. Well, if that's the case, a full environment versions (Oracle + operating system) might help. Metalink could provide some more info as well. If nothing helps, a service request should be opened.
– Littlefoot
Nov 23 '18 at 19:40

add a comment |

you're right normally the answer should be this related to the given syntax, but OP asks about the weird behaviour of the current situation.
– Barbaros Özhan
Nov 23 '18 at 19:35

1

Aha; I think I get your point, @Barbaros. You (and the OP) are saying that Oracle just won't execute create or replace, but drop + create instead. Well, if that's the case, a full environment versions (Oracle + operating system) might help. Metalink could provide some more info as well. If nothing helps, a service request should be opened.
– Littlefoot
Nov 23 '18 at 19:40

you're right normally the answer should be this related to the given syntax, but OP asks about the weird behaviour of the current situation.
– Barbaros Özhan
Nov 23 '18 at 19:35

Aha; I think I get your point, @Barbaros. You (and the OP) are saying that Oracle just won't execute create or replace, but drop + create instead. Well, if that's the case, a full environment versions (Oracle + operating system) might help. Metalink could provide some more info as well. If nothing helps, a service request should be opened.
– Littlefoot
Nov 23 '18 at 19:40

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Htykuut