Home » SQL & PL/SQL » SQL & PL/SQL » HOW TO CURSOR OUT FROM A TEMP TABLE
HOW TO CURSOR OUT FROM A TEMP TABLE [message #239122] Mon, 21 May 2007 09:17 Go to next message
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 #239125 is a reply to message #239122] Mon, 21 May 2007 09:26 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Have a look at http://www.orafaq.com/forum/t/59964/91729/
and you might get some help faster
Re: HOW TO CURSOR OUT FROM A TEMP TABLE [message #239157 is a reply to message #239122] Mon, 21 May 2007 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Execute:
set role none;
Do you still see the tables?

In addition, this way of coding is very bad for Oracle. It is a direct port of T-SQL and don't work in Oracle (even if it seems to).

Regards
Michel
Re: HOW TO CURSOR OUT FROM A TEMP TABLE [message #239289 is a reply to message #239157] Mon, 21 May 2007 23:24 Go to previous messageGo to next message
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?
Re: HOW TO CURSOR OUT FROM A TEMP TABLE [message #239291 is a reply to message #239122] Mon, 21 May 2007 23:47 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>When to use temporary tables?
With Oracle almost never.
What problem are you really trying to solve?
icon5.gif  Re: HOW TO CURSOR OUT FROM A TEMP TABLE [message #239294 is a reply to message #239291] Mon, 21 May 2007 23:56 Go to previous messageGo to next message
dehseth
Messages: 4
Registered: May 2007
Junior Member
My problem is

I have lots of tables in my db, I need to create a report on jsp web page. So I write an stored procedure which creates a temporary table and inserts the calculation results into this table. I chose to use temp. table cause its isolated from users and not needed to written on hard drive and also faster.

I declare a REF CURSOR type in package body and tried to return this temp tables ref cursor. I need to take the result set with java and put it into my web page so that ppl can see report.

How can i do that? And if temp table is not useful why is it exists?

thanx.. Cool
Re: HOW TO CURSOR OUT FROM A TEMP TABLE [message #239315 is a reply to message #239294] Tue, 22 May 2007 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Michel Cadot i tried set role none code but it doesn't work

Of course, this was not a solution but a way to show you why it does not work.
So if you use "set role none", do you still see the tables, not in your program but at SQL*Plus prompt level?
Quote:
I declare a REF CURSOR type in package body and tried to return this temp tables ref cursor

You likely can do it with original tables.
Quote:
I chose to use temp. table cause its isolated from users

Remember in Oracle read does not block write and write does not block read. Each query is isolated from others.
Quote:
if temp table is not useful why is it exists?

Just because people that want to port T-SQL without any effort and try to think the difference between how work RDBMS asked Oracle many and many times during years to add them.

Regards
Michel
icon5.gif  Re: HOW TO CURSOR OUT FROM A TEMP TABLE [message #239331 is a reply to message #239315] Tue, 22 May 2007 01:09 Go to previous messageGo to next message
dehseth
Messages: 4
Registered: May 2007
Junior Member
Then my question is

Can I create a cursor with writing code (or an array) and return it to my Java app?

Tnx everyone! Cool
Re: HOW TO CURSOR OUT FROM A TEMP TABLE [message #239337 is a reply to message #239331] Tue, 22 May 2007 01:18 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you can return a ref cursor to your Java app.

Regards
Michel
Previous Topic: How to Drop a particular column in a table.
Next Topic: Function Overloading
Goto Forum:
  


Current Time: Tue Dec 06 10:09:26 CST 2016

Total time taken to generate the page: 0.14919 seconds