[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sending msg to mq from pl/sql using jvm.

Posted on 2011-04-26
10
Medium Priority
?
1,169 Views
Last Modified: 2012-05-11
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
Comment
Question by:Rao_S
  • 4
  • 4
  • 2
10 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1336 total points
ID: 35467278
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
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 664 total points
ID: 35467285
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
 

Author Comment

by:Rao_S
ID: 35467337
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1336 total points
ID: 35467369
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 35467402
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
 

Author Closing Comment

by:Rao_S
ID: 35467415
Thanks!
I know a function returns a value! Sometimes we miss the very obvious.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35467424
"'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
 

Author Comment

by:Rao_S
ID: 35467528
Another question: How do I capture an error?  If my msg does not go thru to mq?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35467532
>>Another question...

Really should be 'another question'...
0
 

Author Comment

by:Rao_S
ID: 35467564
Ok, agree.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month18 days, 22 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question