Select from Oracle Procudure in TI

[paulsimon]

Hi

We have an issue in Oracle which is causing it to take a long time to parse SQL. We are therefore trying to avoid using the normal ?variable? method of parameterising the SQL.

The Oracle guys have suggested using a stored procedure to return a cursor, which could then have proper parameters rather than
?substitution variables?. However, from what I can see it is only possible to read from the cursor that would be returned in an Oracle procedure or programming language, but the Data Source tab of TI only seems to allow a SELECT statement. Does anyone have a way around this?

I have two other alternatives:

1) Write the parameter values to a small table, and use joins to this to implement the selection (This is what I used to do
before ?var? came along.
2) Write a SQL FUNCTION that takes parameters, reads from a Cursor and Pipelines the results out, which then allows me to write SQL * FROM MyFunction. Pretty much at the limits of my Oracle knowledge but I have managed to get this working.

Are there any other ways around this?

Does anyone have any experience of doing this?

Does anyone have any views on which would be the best method?

The Cursor is likely to read in around 400 million rows and after a GROUP BY produce 1.5 million rows to be read into TM1.

Regards

Paul Simon

[afshin]

Try this .

CREATE OR REPLACE
FUNCTION GetData (p_parameter IN table_name.fieldname%TYPE)
RETURN sys_refcursor
IS
c1 sys_refcursor;
BEGIN
OPEN c1 FOR
SELECT
*
FROM
yourtable
where column=p_parameter
return c1;
END GetData ;

your Query should be select GetData (???) from dual and use the prolog to pass the parameters as needed.

Make sure you tune the SQL to use the right indexes
sys_refcursor is a new feature of Oracle 9i and I have it working on Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product and TM1 9.5.1.

Give me a shout if u need any further details.

[fcapell]

Hi everyone!

I was searching the forum looking for a way of loading data into TM1 from an Oracle stored procedure (which is straightforward in SQL Server).

I tried the sys_refcursor approach. I put "select function_name from dual" into TI screen and when I click on Preview, TI hangs and TM1 server gets down. My environment is:

- Cognos Express 10.1
- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
- Oracle ODBC Instant Client for Windows Release 11.2

Could you give me a clue of what I'm doing wrong?

Thanks!

Fausto

[tomok]

Search the board for posts about Oracle, ODBC and using SQLFetchScroll and Unicode. These posts will give you a few ideas to try.

[bking101]

A different approach (if you have it) would to be to use the Cognos ETL tool of Data Manager. Data Manager can be used to load data into your TM1 cubes and it will use the Data Manager engine to perform the task instead of the TM1 engine. If you don't have Data Manager, then you will need to use a process. Data Manager can put data into a TM1 server but can not use a TM1 server as a data source (can't pull it out from TM1)

I do not come on here very regularly anymore after changing companies/positions and not dealing with TM1 anymore but wanted to put out a different alternative to try and solve the issue. If you need details on how to do this I can look through my old information and post that as well.

[fcapell]

Thank you tomok and bking101!

I've been in a hurry so I managed to put the queries right into Turbo Integrator and change the DataSourceQuery variable to put the parameters. It's running in a reasonable time, but I'll get back to this stored procedure approach later.

Best regards,

Fausto