Temp Table in oracle and Crystal report

[ali1227]

Crystal report 9 / Oracle 9i/ ODBC Connection

I wrote this package and procedure in oracle 9i. It compile without any error but when i try to open this procedure from crystal report i am getting the error message. My question is can i use the temp table like i use in this procedure for crystal report or not. If not then what is the best way to do this. I need to have temp table so i can manipulate data like we do in SQL Server


PACKAGE CalllogPackage
AS TYPE CalllogType IS REF CURSOR;
end CalllogPackage;

procedure TestForTempTbl (
CalllogCursor IN OUT CalllogPackage.CalllogType)
as

l_tname varchar2(30) default 'temp_table_' || userenv('sessionid');
begin
execute immediate 'create global temporary table ' ||
l_tname || ' on commit delete rows
as
select * from tracker where 1=0 ';

execute immediate 'insert into ' || l_tname ||
' select * from tracker';

open CalllogCursor for
'select * from ' || l_tname || ' order by loginid';

execute immediate 'drop table ' || l_tname;
end;

 

[Turkbear]

Hi,
What error message?..
You do not appear to be passing anything to the SP - Is there a parameter defined in the report?

Editorial Note:
It is usually not a good idea to try to replicate SqlServer behavior in Oracle..

Try building a View in Oracle using the code in your SP instead of trying to create a temporary table ( usually not something that is used in Oracle)..







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[IdoMillet]

The LAST statement in the SP must be a SELECT statement (you have a drop table as the las statement).

hth,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[ali1227]

i am only testing this procedure that's why i did not pass any value to procedure. the error i am getting when i select the procedure from crytal report and click next is

Query engine error: 'HY000:[ORACLE][ODBC][Ora]ORA-06550:line 1, column 14:
PLS-00905:Object Heat.TESTFORREPORT is invalid
ORA-06550:line 1, column 7:

What i am trying to achieve here is to dynamixally create a table, load any data in the table with any kind of parameter and select from the table and yhen drop the temp table.

Can some one tell me how can i acheive this in oracle

 

[IdoMillet]

Start by dropping the table (from the previous run). End with a SELECT...

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[ali1227]

Ido i have followed your instruction now error message has changed. Now it is

Query engine error: 'HY000:[ORACLE][ODBC][Ora]ORA-01031:insuffient privilages
ORA-06512: at 'Heat.TESTFORREPORT',line 7:
ORA-06512 at line 1

But i have privilages full privilages of test environment. Line 7 is execute immediate 'create global temporary table ' . Have any idea why is it giving error

 

[Turkbear]

Hi,
The permissions are needed by the SP - see the docs about permissions in SPs and DDL...





To Paraphrase:"The Help you get is proportional to the Help you give.."