• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1197
  • Last Modified:

Sending msg to mq from pl/sql using jvm.

I have to send a msg to mq with a pl/sql proc.
The following proc
DECLARE
           status varchar2(4000);
BEGIN
           execute immediate  AQMUT02.COMMON_ERROR_LOGGER.PUBLISHMESSAGE(
                          'Test Message from PL/SQL block SR /26 ',  
                           'com.uss.mes.errorhandling.client.errorqueue');
           --dbms_output.put_line('Status: ' || status);
END;
Gives me the following error: but send the message to mq anyway!
ORA-00900: invalid SQL statement
ORA-06512: at line 4

When I try this:
DECLARE
           status varchar2(4000);
BEGIN
           status:=  AQMUT02.COMMON_ERROR_LOGGER.PUBLISHMESSAGE(
                           'Test Message from PL/SQL block SR 4/26 10',
                            'com.uss.mes.errorhandling.client.errorqueue');
           --dbms_output.put_line('Status: ' || status);
END;

There is no error, the message is sent to mq.

What is the problem with execute immediate?
0
Rao_S
Asked:
Rao_S
  • 4
  • 4
  • 2
3 Solutions
 
sdstuberCommented:
don't use execute immediate

execute immediate "executes" a string, like dynamic sql

since your function returns a string, I assume a result status

execute immediate is attempting to run your status as if it were a sql statement or procedure call

just call the function like you have in your second example
0
 
slightwv (䄆 Netminder) Commented:
execute immediate needs a valid SQL call.  You are calling a function called AQMUT02.COMMON_ERROR_LOGGER.PUBLISHMESSAGE

That by itself isn't a valid SQL call.  If you can paste it into SQLPLUS then you can use it with execute immediate.

any reason you want to use execute immediate?

you can try:
execute immediate  ' declare status varchar2(4000); begin   status := AQMUT02.COMMON_ERROR_LOGGER.PUBLISHMESSAGE(
                          ''Test Message from PL/SQL block SR /26 '',  
                           ''com.uss.mes.errorhandling.client.errorqueue''); end; '
     
0
 
Rao_SAuthor Commented:
When I try this, I get the following error:
DECLARE
           status varchar2(4000);
BEGIN
            AQMUT02.COMMON_ERROR_LOGGER.PUBLISHMESSAGE(
                 'Test Message from PL/SQL block SR 4/26 11',
                 'com.uss.mes.errorhandling.client.errorqueue');
           --dbms_output.put_line('Status: ' || status);
END;

ORA-06550: line 4, column 13:
PLS-00221: 'PUBLISHMESSAGE' is not a procedure or is undefined
ORA-06550: line 4, column 13:
PL/SQL: Statement ignored

Why does it not recognize - publishmessage?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
sdstuberCommented:
because you're trying to invoke a function (which returns a result)
 as if it were a procedure which does not

your second example in the original question is correct.

call the function and return the result into something  (like the status variable)
0
 
sdstuberCommented:
it's the same idea as if you tried to do this...

begin
    CONCAT('hello ', 'world');
end;


concat returns a string,  I must do something with that string

this works because the function result has a place to go

declare
   mystring varchar2(20);
begin
   mystring := CONCAT('hello ','world');
end;
0
 
Rao_SAuthor Commented:
Thanks!
I know a function returns a value! Sometimes we miss the very obvious.
0
 
sdstuberCommented:
"'PUBLISHMESSAGE' is not a procedure "

that's the key part of the error message for you

it is NOT a "procedure"

it is a "function"
0
 
Rao_SAuthor Commented:
Another question: How do I capture an error?  If my msg does not go thru to mq?
0
 
slightwv (䄆 Netminder) Commented:
>>Another question...

Really should be 'another question'...
0
 
Rao_SAuthor Commented:
Ok, agree.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now