HOW TO CURSOR OUT FROM A TEMP TABLE [message #239122] |
Mon, 21 May 2007 09:17  |
dehseth
Messages: 4 Registered: May 2007
|
Junior Member |
|
|
Hey there everybody,
I write a Package As
PACKAGE ISMAREPORTS AS
TYPE bof_daily_prod_type IS REF CURSOR; -- RETURN BOF_DAILY_PROD_TEMP_TBL%ROWTYPE;
PROCEDURE BOF_PERF_SUM_REPORT(DATESTART IN CHAR, DATEEND IN CHAR);
PROCEDURE BOF_DAILY_PROD_REPORT(DATESTART IN CHAR, bof_daily_prod_rs IN OUT bof_daily_prod_type);
END;
and define a REF CURSOR in this package to get resultset.
In package body I create a temp table:
SQL_CMD:='CREATE GLOBAL TEMPORARY TABLE BOF_DAILY_PROD_TEMP_TBL (TARIH DATE, CONVNO NUMBER(1), DOKUMSAYISI NUMBER(4), NET_DOKUM_SURESI NUMBER(5,1)) ON COMMIT DELETE ROWS'; --ON COMMIT PRESERVE ROWS'; --ON COMMIT DELETE ROWS';
dbms_output.put_line('sql:'||sql_cmd);
execute immediate sql_cmd;
And Insert Some Values into this temporary table.
At the end I try to open cursor:
sql_cmd := 'Open bof_daily_prod_rs For Select * From BOF_DAILY_PROD_TEMP_TBL';
dbms_output.put_line('sql: '||sql_cmd);
execute immediate sql_cmd;
but this gives me error:
ORA-00900: invalid SQL statement
ORA-06512: at "ISSMADEV.ISMAREPORTS", line 280
ORA-06512: at line 8
ORA-00942: table or view does not exist
I use Oracle SQL*Plus and see table exists... I am new in oracle actually jsut started as u can understand i use MS SQL Server YUKON before.
I wanted to take these cursor in Java and create a web-page which shows the results.
Any help?
[mod-edit] removed hard to read colors.
[Updated on: Mon, 21 May 2007 09:34] by Moderator Report message to a moderator
|
|
|
|
|
Re: HOW TO CURSOR OUT FROM A TEMP TABLE [message #239289 is a reply to message #239157] |
Mon, 21 May 2007 23:24   |
dehseth
Messages: 4 Registered: May 2007
|
Junior Member |
|
|
Hello back,
Michel Cadot i tried set role none code but it doesn't work.
You've said that this way of coding was bad for oracle, and I wonder why is it bad? When to use temporary tables? And what should I do to accomplish my needs?
|
|
|
|
|
|
|
|