企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 452|回复: 5

Select from Oracle Procudure in TI

[复制链接]

74

主题

421

帖子

580

积分

高级会员

Rank: 4

积分
580
QQ
发表于 2014-3-16 20:25:34 | 显示全部楼层 |阅读模式
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
回复

使用道具 举报

89

主题

395

帖子

598

积分

高级会员

Rank: 4

积分
598
QQ
发表于 2014-3-16 23:01:10 | 显示全部楼层
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.

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

83

主题

416

帖子

588

积分

高级会员

Rank: 4

积分
588
QQ
发表于 2014-3-16 23:20:59 | 显示全部楼层
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
回复 支持 反对

使用道具 举报

74

主题

392

帖子

562

积分

高级会员

Rank: 4

积分
562
QQ
发表于 2014-3-16 23:33:24 | 显示全部楼层
Search the board for posts about Oracle, ODBC and using SQLFetchScroll and Unicode. These posts will give you a few ideas to try.
回复 支持 反对

使用道具 举报

67

主题

407

帖子

573

积分

高级会员

Rank: 4

积分
573
QQ
发表于 2014-3-17 00:30:28 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

73

主题

397

帖子

567

积分

高级会员

Rank: 4

积分
567
QQ
发表于 2014-3-17 01:04:57 | 显示全部楼层
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
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|小黑屋|企业绩效管理网 ( 京ICP备14007298号   

GMT+8, 2020-9-21 13:28 , Processed in 0.212500 second(s), 12 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表