Oracle Procedure

[sgyazuddin]

Hello All;

Iam having an oracle database user defined procedure. Iam able to run the procedure properly with an single in put parameter as shown below.

create or replace procedure CALL_DIM_CUSTOMER(p_ID in number)
is
v_result number;
BEGIN
v_result:=REP_OWNER.wb_rt_api_exec.run_task('TARGET_LOCATION','PLSQLMAP','DIM_CUSTOMER', 'id='||to_char(p_ID) , null, 1);

END;

In turn the procedure calls the data loading transformations with an input parameter.

When iam importing it to framework manager iam getting the following attached error.

Any quick help is highly appreciated.

Reg,
Gyaz

[BIsrik]

As the error says, the procedure is not returning any value. The procedure has to return a value when tested in FM. Check the user guide for more details.

Srik

[Suraj]

The best way is to run the sp using toad or other tools and see if it runs and returns a value.

[kart78]

Check the datatype of parameter passing in FM.it should be same as stored procedure parameter datatype.

[crn.siva]

Hi,

I search solution in cognos support:

When trying to create a Query Subject from a Microsoft SQL Server Stored Procedure in Framework Manager, an error occurs. The stored procedure runs in Query Analyzer. 
 
Symptom
QE-DEF-0179 The procedure call <ProcedureName> is unable to return a result set.
or

RQP-DEF-0179 The procedure call <ProcedureName> is unable to return a result set. 
 



Resolving the problem
Ensure that your stored procedure will return a result set when testing or updating the stored procedure. If the procedure is being tested with input parameters that will not return a result set then the above message will be returned. For example, if the procedure is defined according to:

IF (@input_val = 1)
BEGIN
Select * from TABLE_NAME;ENDGO

Then the only case where a result set will be returned is when the procedure is evaluated with the input_val parameter set to 1. Any other case will not return a result set to Framework Manager or the query engine running the report. When designing your stored procedure the result of the call should always contain a result set. In the above example if an empty result is desired for any case where input_val is not equal to 1 then the following will allow the procedure to validate successfully when imported into Framework Manager:

IF (@input_val = 1)
BEGIN
Select * from TABLE_NAME;ENDELSEBEGIN Select * from TABLE_NAME where 1=0;ENDGO

The ELSE will ensure that there is always a record set returned from the stored procedure while the where clause will ensure that this record set is empty. The empty record set will allow the metadata describing the column names and data types to be retrieved from the database and thus create a valid query subject in Framework Manager. Note: In the case where a stored procedure returns multiple result sets, only the first result set will be used by IBM Cognos ReportNet or Cognos 8. For example if the stored procedure uses something like:
This will only return results for TableA when used within Cognos ReportNet or Cognos 8. No error will be returned to the consumer. If both result sets are required then these must be merged within the stored procedure to generate a single result. This can often be accomplished with a simple union operation between the two queries as:
unionselect columnA from TableB;

[JaiCognos]

Hi,

This is the problem with the stored procedure.Framework Manager do not support out parameters.
Create stored procedure using cursor variables where result set will be given out in this cursor variable which will be referenced as input variable for framework manager.

Type cursor variable and search document in the cogos support you will get the example stored procedure which used this concept of cursor variable.