executing an oracle stored procedure in xMII 11.5

[Former Member]

Dear all,

I am facing problem executing an oracle stored procedure using sql query in MII. The SP does not have any input or output parameters & consists of only 2 insert statements. I tried to use Command Mode, FixedQuery & FixedQuery With output mode, but the SP doesn't run.

This is the error i get when i use :

execute InsertTest or exec InsertTest - A SQL Error has occurred on query, ORA-00900: invalid SQL statement

I read in one of the posts to use 'CALL' instead of 'exec' or 'execute'. Even with this i get error which states:

A SQL Error has occurred on query, ORA-06576: not a valid function or procedure name

The syntax i used is CALL InsertTest - 'InsertTest' is the SP name.

I also checked Sam's comment in one of the posts about jdbc driver. We are using oracle 9i, so i guess there is no problem with the version of DB.

The stored procedure is working fine in SQL Developer, How else can i invoke the SP in MII?

Any help would be greatly appreciated.

Thanks,

Sushma.

[Former Member · Accepted solution]

Hi Sushma,

might sound silly but still want to confirm that you have also put braces after the stored procedure name.

CALL InsertTest()

or

CALL InsertTest([Param.1])

Regards,

Anil

[Former Member · Accepted solution]

Hi Anil,

I tried using braces also... it doesnt work.

Regards,

Sushma.

[sidnooradarsh · Accepted solution]

Hi Sushma,

These below points could help you to slove your problem

1) Choose command mode especially if you are inserting or updating data and at the same time not planning to return any data back using stored procedures.

( Though you can still go for Fixed query for insertion/updation but query template will error out but insertion/updation will still happen inspite of query template error)

2) Even if you are not sending any input parameters to stored procedure still you need to show braces for example

CALL TEST_PROC()

-

---

Please note no semicolon at the end of statement.

3) Use FixedQueryWithOutput for Stored Procdures returning data.

You cannot use this mode without any binding variable, a binding variable is nothing but a varible to hold the data returned from Procedure.

If you still have issues then Please check your Stored Procedures code, could be some problem in there and you never know it could be the cause for this error.

Regards,

Adarsh

[Former Member · Accepted solution]

Hi Adarsh,

I used a command mode & calling my SP like this : CALL TEST25()

This time i got a different error : A SQL Error has occurred on query, ORA-06575: Package or function TEST25 is in an invalid state

Any idea what it means, have u faced this error anytime? The stored procedure is working fine in oracle, it is inserting data properly into the tables.

I've checked help & followed the steps as it is, even then it isn't working.

Regards,

Sushma.

[Former Member · Accepted solution]

Sushma,

Even if you are not using parameters you should use ? inside the bracers.

Have you tried?

 CALL test25(?) 

Edited by: Luiz Lahuerta on Jul 24, 2009 7:45 PM

[Former Member · Accepted solution]

Hi Luiz,

i tried calling a '?', i got this error : Fatal Error

A SQL Error has occurred on query, ORA-01008: not all variables bound

Regards,

Sushma.

[Former Member · Accepted solution]

Hi all,

for insert create procedure

CREATE PROCEDURE MII_TEST_INSUPD 
(ID_IN IN NUMBER, 
 NAME_IN IN VARCHAR2)
IS
BEGIN
  -- UPDATE ROW
  UPDATE TEST SET 
          NAME = NAME_IN
  WHERE 
          ID = ID_IN;
              
  -- NOT RETURN INSERT NEW LINE IN TABLE
  IF SQL%ROWCOUNT = 0 THEN 
     INSERT INTO TEST (ID, NAME) VALUES (ID_IN, NAME_IN);                         
  END IF;  
END;

In MII you create a query template

Mode - Command

FixedQuery - insert the code below

CALL MII_TEST_INSUPD ([Param.1],'[Param.2]')

for returns the grid using procedures in oracle you need create a package on oracle server

CREATE PACKAGE PKG_test IS
  TYPE cursortype is ref cursor;
  PROCEDURE test (mycursor in out cursortype);
END PKG_test;

CREATE PACKAGE BODY PKG_test IS

  PROCEDURE test (mycursor in out cursortype) AS
  BEGIN
     open mycursor for select * from test;
  END;

END PKG_test;

In MII you create a query template

Mode - FixedQueryWithOutput

FixedQuery - insert the code below

CALL PKG_TEST.TEST(?)

Danilo

[sidnooradarsh · Accepted solution]

Hi Sushma,

A SQL Error has occurred on query, ORA-06575: Package or function TEST25 is in an invalid state

The above error comes up when your Procedure/Function/Packages has some compilation errors.

It could be some syntax error or it might have happened something like this

Say that you have two SP's SP1 and SP2

and you are calling SP2 from SP1,

Say that you have made some changes or fixed some bug in SP2 and then you need to ensure that you re-complie the dependent SP1 else the SP1 will never come to know what changes/fixes you have made to SP2

And it will always keeps you giving an error when actually there is no error, because the SP 1 is looking for older version of SP2 which had bug earlier.

Could you please post your SP so that it will be easy to debug.

Thanks,

Adarsh

[Former Member · Accepted solution]

Hi Sushma,

Are you using the same database user in the MII Data Server that you are using in SQL Developer?

If not, and the MII data server's database user doesn't have a synonym to the stored procedure, you will need to use the fully qualified name for the stored proc. For example, use sp_owner.TableInsert instead of TableInsert.

-- Marc

[Former Member · Accepted solution]

Dear All,

Thank you so much for all your help. Calling an SP is working now.

I just removed the '?' from the statement : Call Test25() , this worked.

It is strange, the same statement didnt work earlier, but it is working now.

Thanks to all of you ...

Regards,

Sushma.

[Former Member]

Sushma,

Can you provide the SP you wrote?

-Suresh

[Former Member]

Sushma,

You should use a SQL query on mode FixedQueryWithOutput and use the following code:

 CALL procedure_name(?) 

when there are parameters you use:

 CALL procedure_name('[Param.1]','[Param.2]',?) 

Hope this helps.

Edited by: Luiz Lahuerta on Jul 22, 2009 6:15 PM

Edited by: Luiz Lahuerta on Jul 22, 2009 10:09 PM

[Former Member]

Hi Luiz,

My SP doesn't have any in or out parameters. Even in that case do i have to use braces, after the procedure name?

I tried both the options you've mentioned. This time i get a binding error :

A SQL Error has occurred on query, ORA-06553: PLS-306: wrong number or types of arguments in call to 'TEST25'

Regards,

Sushma.

[Former Member]

Hi ,

Please check the stored procedure once again.

http://localhost/LighthammerCMS/Help/Connectors/IDBCConnector.htm

This page helps you to identify the problem.

If you are not returning any data Use COMMAND Mode.

Regards,

Kishore

[agentry_src]

What do the log files show?

Mike

[Former Member]

Hi Micheal,

I am not seeing any error in the log file, but sometimes i can see an error in java console saying : server is currently disabled(IDBC).

Does it have any relevance to my problem? becasue it does not come up everytime.

Regards,

Sushma.

[agentry_src]

Did you check security on the stored procedure (with respect to the MII data connector user)?

Edited by: Michael Appleby on Jul 22, 2009 3:16 PM

[Former Member]

Hi Micheal,

I am running the stored procedure from the same namespace. What else could have gone wrong with my code?

Regards,

Sushma.