Hyperion 8.5 & Oracle Stored Procedure

[oratan]

Dear All,

I'm test to call Stored Procedure of Oracle from Hyperion Designer now.

I made SP without the parameter, and was able to display a result of dataset in Hyperion with Ref Cursor first. this is ok.

next, I made SP with one parameter, and write scripts.
ActiveDocument.Sections["Query"].SetStoredProcParam("test",1);
ActiveDocument.Sections["Query"].ProcessStoredProc();

but, I get error message...

error message: SQL API: [SQLExecDirectW], SQL RETURN: [-1], SQL STATE: [HY000], SQL NATIVE ERROR: [6550], SQL MESSAGE: [[DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-06550: line 1, column 8:
PLS-00306: wrong number or types of arguments in call to 'TEST_PROC'
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored]

I thought through the night, but do not understand a cause.
Do you know a cause?
thank you,

best regards,
oratan

[cavagnaro]

I think you are passing an integer and the SP might be expecting a string, check how you declared the variables in the SP.

[oratan]

Hi, cavagnaro

thank you nice advice.
I checked SQL to see your indication, and I looked for even GOOGLE.

http://businessintelligence.ittoolbox.com/groups/technical-functional/brio-l/oracle-stored-procedure-error-hyperion-85-2692350

this is wrong test code.
************************************
-- package
create or replace PACKAGE ICon_cur_pack
is
type status_cursor is ref cursor;
END ICon_cur_pack;

create or replace PROCEDURE TestAgentStatus002 (
testid IN varchar2
,status_cur OUT ICon_cur_pack.status_cursor
)
is
BEGIN
open status_cur for select * from gc_login where logincode = testid;
END;
************************************


this is ok code. I upset a parameter.
************************************
create or replace PROCEDURE TestAgentStatus003 (
status_cur OUT ICon_cur_pack.status_cursor,
testid IN number
)
is
BEGIN
dbms_output.put_line(testid);
open status_cur for select * from gc_login where id = testid;
END;
************************************

I did not understand causes well, but worked.
thank you.

ora_tan

[cavagnaro]

The problem was that you were sending a integer parameter to a string variable:

SetStoredProcParam("test",1); where 1 is the parameter and as it doesn't have quotes is considered an integer but in the SP the variable was declared as varchar.

[bulut]

Hi oratan,

I am trying to call stored procedure that accepts ref cursor as out parameter from Hyperion.
But i couldn't achieve this.

You sad that you were able to this. Can u help me how can i do this.

It says wrong number or type of argument in call to 'HYP_CURSOR'

Our hyperion version is 9.3.1 and oracle version is 10.2.0

[b]create or replace package hyp_ref_cusrsor is
    type t_cusror is ref cursor;
end;
   
create or replace procedure hyp_cursor(ret1 out hyp_ref_cusrsor.t_cusror) is

    begin
     
      open  ret1 for select * from hyp_tmp1 m;
     
    end;[/b]


"I made SP without the parameter, and was able to display a result of dataset in Hyperion with Ref Cursor first. this is ok."

Regards