Home » SQL & PL/SQL » Client Tools » Return data from Oracle for Crystal (10g)
Return data from Oracle for Crystal [message #470299] Mon, 09 August 2010 09:05 Go to next message
Michael Gaarde
Messages: 29
Registered: August 2010
Junior Member
Hi, I have problems in Oracle returning data from a stored procedure. In MS SQL it's quite simple.

i.e.

CREATE OR REPLACE PACKAGE globalpkg
AS
TYPE RCT1 IS REF CURSOR;
TRANCOUNT INTEGER := 0;
IDENTITY INTEGER;
END;
/


next the stored procedure:

CREATE OR REPLACE PROCEDURE mytest
(
RCT1 OUT GLOBALPKG.RCT1
)
AS
BEGIN
OPEN RCT1 FOR
SELECT *
FROM EMPLOYEE;
END;

Next trying to run the Stored procedure:

VARIABLE resultSet REFCURSOR
EXEC mytest (:resultSet);


Result always returns this error:

REFCURSOR - Unrecognized type.


I have tried using sample on the internet, but all returns the same error, what am I missing

Thanks in advance

Michael

Re: Return data from Oracle for Crystal [message #470303 is a reply to message #470299] Mon, 09 August 2010 09:11 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Can you please copy paste your SQL session with error?

regards,
Delna
Re: Return data from Oracle for Crystal [message #470304 is a reply to message #470299] Mon, 09 August 2010 09:13 Go to previous messageGo to next message
BlackSwan
Messages: 22714
Registered: January 2009
Senior Member
when all else fails, Read The Fine FAQ

http://www.orafaq.com/node/63
Re: Return data from Oracle for Crystal [message #470305 is a reply to message #470299] Mon, 09 August 2010 09:14 Go to previous messageGo to next message
Michael Gaarde
Messages: 29
Registered: August 2010
Junior Member
Hi, thanks for a quick reply

CREATE OR REPLACE PACKAGE globalpkg
AS
TYPE RCT1 IS REF CURSOR;
TRANCOUNT INTEGER := 0;
IDENTITY INTEGER;
END;
/


CREATE OR REPLACE PROCEDURE mytest
(
RCT1 OUT GLOBALPKG.RCT1
)
AS
BEGIN
OPEN RCT1 FOR
SELECT *
FROM EMPLOYEE;
END;
/

VARIABLE resultSet REFCURSOR;
EXEC mytest (:resultSet);

="Line",="Pos",="Text"
1,,"Create package, executed in 0 sec."
10,,"Create procedure, executed in 0.015 sec."
22,,REFCURSOR - Unrecognized type.
,,Total execution time 0.015 sec.
Re: Return data from Oracle for Crystal [message #470306 is a reply to message #470305] Mon, 09 August 2010 09:15 Go to previous messageGo to next message
cookiemonster
Messages: 10922
Registered: September 2008
Location: Rainy Manchester
Senior Member
What tool are you running this in?
Re: Return data from Oracle for Crystal [message #470307 is a reply to message #470306] Mon, 09 August 2010 09:21 Go to previous messageGo to next message
Michael Gaarde
Messages: 29
Registered: August 2010
Junior Member
Using SQL TOOLS (1.4.2), when I try to use the Stored Procedure in Business Objects 3.1 universe designer I get this error:

Execption: DBD, ORA-06550: line1, column 7:
PLS-00306: wrong number or types of arguments in call to 'MYTEST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
State: N/A

Re: Return data from Oracle for Crystal [message #470309 is a reply to message #470307] Mon, 09 August 2010 09:21 Go to previous messageGo to next message
BlackSwan
Messages: 22714
Registered: January 2009
Senior Member
I don't know what you have.
I don't know what you do.
I don't know what you see.
It is really, really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.
Re: Return data from Oracle for Crystal [message #470310 is a reply to message #470299] Mon, 09 August 2010 09:24 Go to previous messageGo to next message
Michel Cadot
Messages: 58929
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
VARIABLE resultSet REFCURSOR

This a SQL*Plus command.

SQL> create or replace procedure p (p_cur in out sys_refcursor)
  2  is
  3  begin
  4    open p_cur for select * from emp where rownum<=3;
  5  end;
  6  /

Procedure created.

SQL> var x refcursor;
SQL> exec p(:x)

PL/SQL procedure successfully completed.

SQL> print x
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17/12/1980 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 20/02/1981 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 22/02/1981 00:00:00       1250        500         30

3 rows selected.


Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Return data from Oracle for Crystal [message #470311 is a reply to message #470309] Mon, 09 August 2010 09:24 Go to previous messageGo to next message
Michael Gaarde
Messages: 29
Registered: August 2010
Junior Member
Hi BlackSwan,

This should be it

CREATE OR REPLACE PACKAGE globalpkg
AS
TYPE RCT1 IS REF CURSOR;
TRANCOUNT INTEGER := 0;
IDENTITY INTEGER;
END;
/


CREATE OR REPLACE PROCEDURE mytest
(
RCT1 OUT GLOBALPKG.RCT1
)
AS
BEGIN
OPEN RCT1 FOR
SELECT *
FROM EMPLOYEE;
END;
/

--Running the sp

VARIABLE resultSet REFCURSOR;
EXEC mytest (:resultSet);

Result:

="Line",="Pos",="Text"
1,,"Create package, executed in 0 sec."
10,,"Create procedure, executed in 0.015 sec."
22,,REFCURSOR - Unrecognized type.
,,Total execution time 0.015 sec.
Re: Return data from Oracle for Crystal [message #470313 is a reply to message #470307] Mon, 09 August 2010 09:26 Go to previous messageGo to next message
Michel Cadot
Messages: 58929
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michael Gaarde wrote on Mon, 09 August 2010 16:21
Using SQL TOOLS (1.4.2), when I try to use the Stored Procedure in Business Objects 3.1 universe designer I get this error:

Execption: DBD, ORA-06550: line1, column 7:
PLS-00306: wrong number or types of arguments in call to 'MYTEST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
State: N/A

Use predefined SYS_REFCURSOR datatype unless you want a strongly typed ref cursor (which is currently not the case in your package).

Regards
Michel

Re: Return data from Oracle for Crystal [message #470315 is a reply to message #470311] Mon, 09 August 2010 09:29 Go to previous messageGo to next message
Michael Gaarde
Messages: 29
Registered: August 2010
Junior Member
Hi Michel

thanks for the sample, I tried it, but get the same error in SQL Tools:

"REFCURSOR - Unrecognized type"

/Michael
Re: Return data from Oracle for Crystal [message #470317 is a reply to message #470315] Mon, 09 August 2010 09:32 Go to previous messageGo to next message
Michael Gaarde
Messages: 29
Registered: August 2010
Junior Member
Michel,

Althoug I do get the error in SQL Tools, I'm able to use the stored procedure in Business Objects

THANK YOU (and the same goes to the rest of you for the quick reply)

/Michael
Re: Return data from Oracle for Crystal [message #470318 is a reply to message #470315] Mon, 09 August 2010 09:33 Go to previous messageGo to next message
cookiemonster
Messages: 10922
Registered: September 2008
Location: Rainy Manchester
Senior Member
Try using sqlplus instead
Re: Return data from Oracle for Crystal [message #470319 is a reply to message #470315] Mon, 09 August 2010 09:33 Go to previous messageGo to next message
Michel Cadot
Messages: 58929
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said:

Quote:
VARIABLE resultSet REFCURSOR
This a SQL*Plus command.


Try to find the equivalent for your tool.

Regards
Michel
Re: Return data from Oracle for Crystal [message #470321 is a reply to message #470319] Mon, 09 August 2010 09:55 Go to previous messageGo to next message
Michael Gaarde
Messages: 29
Registered: August 2010
Junior Member
Hi Michel,

that was probably why, I'm an MSSQL man and was not aware that the different tools behaved differently.

You will probably disagree (at least most appear to do so Smile) that the useage of temp tables makes sense, but I have to convert a number of complicated MS SQL sp's to Oracle (10g), the MS SQL sp's all make heavy use of temporary tables and table variables.

I tried to add this to your working sample:

CREATE GLOBAL TEMPORARY TABLE my_temp_table (column1 NUMBER, column2 NUMBER) ON COMMIT DELETE ROWS;

But get errors, sp the question is is it possible at all to created temp tables inside a Oracle sp ?





Re: Return data from Oracle for Crystal [message #470323 is a reply to message #470321] Mon, 09 August 2010 09:57 Go to previous messageGo to next message
BlackSwan
Messages: 22714
Registered: January 2009
Senior Member
>But get errors, sp the question is is it possible at all to created temp tables inside a Oracle sp ?
yes, create TEMP table can be done, but usually NOT needed in Oracle & more efficient without them
Re: Return data from Oracle for Crystal [message #470324 is a reply to message #470299] Mon, 09 August 2010 10:03 Go to previous messageGo to next message
Michael Gaarde
Messages: 29
Registered: August 2010
Junior Member
If if create it outside the sp then i can get it to work, but I would like to have it inside, i.e.

PROMPT CREATE OR REPLACE PROCEDURE p
CREATE OR REPLACE procedure p2 (p_cur in out sys_refcursor)
is
begin

CREATE GLOBAL TEMPORARY TABLE my_temp_table2 (name1 VARCHAR(255), name2 VARCHAR(255));

INSERT INTO my_temp_table2
select Name1, Name2 from EXEMPLOYEE where rownum<=3

open p_cur for select * from my_temp_table2;
end;
/


Results in

Line Pos Text
1 PROMPT CREATE OR REPLACE PROCEDURE p
2 Create procedure, executed in 0.016 sec.
6 5 PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe

Total execution time 0.032 sec.
Re: Return data from Oracle for Crystal [message #470325 is a reply to message #470324] Mon, 09 August 2010 10:06 Go to previous messageGo to next message
BlackSwan
Messages: 22714
Registered: January 2009
Senior Member
>CREATE GLOBAL TEMPORARY TABLE my_temp_table2 (name1 VARCHAR(255), name2 VARCHAR(255));
Can NOT do DDL directly from inside PL/SQL.
EXECUTE IMMEDIATE must be used, but then all SQL must also use EXECUTE IMMEDIATE since it does not know about new table
Re: Return data from Oracle for Crystal [message #470328 is a reply to message #470325] Mon, 09 August 2010 10:12 Go to previous messageGo to next message
Michael Gaarde
Messages: 29
Registered: August 2010
Junior Member
Any chance that you can provide me with a sample "solving" this:

PROMPT CREATE OR REPLACE PROCEDURE p
CREATE OR REPLACE procedure p2 (p_cur in out sys_refcursor)
is
begin

CREATE GLOBAL TEMPORARY TABLE my_temp_table2 (name1 VARCHAR(255), name2 VARCHAR(255));

INSERT INTO my_temp_table2
select Name1, Name2 from EXEMPLOYEE where rownum<=3;

open p_cur for select * from my_temp_table2;
end;
/


Re: Return data from Oracle for Crystal [message #470330 is a reply to message #470328] Mon, 09 August 2010 10:15 Go to previous messageGo to next message
BlackSwan
Messages: 22714
Registered: January 2009
Senior Member
>Any chance that you can provide me with a sample "solving" this:
Not from me.
I won't support bad/poor/misguided implementation.
TEMP table is NOT needed so do it right for Oracle NOW!
Re: Return data from Oracle for Crystal [message #470331 is a reply to message #470330] Mon, 09 August 2010 10:17 Go to previous messageGo to next message
Michael Gaarde
Messages: 29
Registered: August 2010
Junior Member
Smile

Thought so, but I don't have a choice, the MS SQL is extremely complicated and makes heavy usage of temp tables, so with the given time in mind I have to find a solution, sorry for stepping outside good practice
Re: Return data from Oracle for Crystal [message #470334 is a reply to message #470331] Mon, 09 August 2010 10:21 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
A Global Temporary Table in Oracle is created ONCE, not every time a procedure runs.

So in your case it would be :

CREATE GLOBAL TEMPORARY TABLE my_temp_table2 (name1 VARCHAR(255), name2 VARCHAR(255));

CREATE OR REPLACE procedure p2 (p_cur in out sys_refcursor)
is
begin

INSERT INTO my_temp_table2
select Name1, Name2 from EXEMPLOYEE where rownum<=3;

open p_cur for select * from my_temp_table2;
end;
/



Every database session can see the Global Temporary Table, but the contents is session-specific, that is the session sees only it's own data.
Re: Return data from Oracle for Crystal [message #470335 is a reply to message #470334] Mon, 09 August 2010 10:26 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
One further thing.

One way to preserve the "multiple selects into temporary tables that are then used in another select" from SQL Server in Oracle would be to use the WITH clause, for example:

WITH my_temp_table1 AS (SELECT 10 c1,20 c2 FROM dual), 
     my_temp_table2 AS (SELECT 10 c3,30 c4 FROM dual) 
SELECT * 
  FROM my_temp_table1
  join my_temp_table2 ON c3=c1
Re: Return data from Oracle for Crystal [message #470337 is a reply to message #470299] Mon, 09 August 2010 10:33 Go to previous messageGo to next message
Michael Gaarde
Messages: 29
Registered: August 2010
Junior Member
Thanks Thomas

I tried creating the temp table out side the sp, but when I use it in Business Objects I get

"Exception: DBD, ORA-08103: object no longer exists, State: N/A"

which kind of makes sense as the temp table was created in another session ?

Could the solution be to have a permanent table using i.e. sys_guid as a unique identifier ?

Re: Return data from Oracle for Crystal [message #470338 is a reply to message #470324] Mon, 09 August 2010 10:36 Go to previous messageGo to next message
cookiemonster
Messages: 10922
Registered: September 2008
Location: Rainy Manchester
Senior Member
You'd need to use execute immediate to create the table in the procedure but I really wouldn't.
If you really need a temp table (and I doubt you do) then create it seperately and just insert/update/delete it in your procedure.
Or better yet don't use one at all.
The reasons why they get used in mssql do not apply to oracle.

EDIT: added some extra words since my reply no longer made sense due to additional replies.

[Updated on: Mon, 09 August 2010 10:38]

Report message to a moderator

Re: Return data from Oracle for Crystal [message #470339 is a reply to message #470337] Mon, 09 August 2010 10:37 Go to previous messageGo to next message
cookiemonster
Messages: 10922
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michael Gaarde wrote on Mon, 09 August 2010 16:33
Thanks Thomas

I tried creating the temp table out side the sp, but when I use it in Business Objects I get

"Exception: DBD, ORA-08103: object no longer exists, State: N/A"

which kind of makes sense as the temp table was created in another session ?



It doesn't actually. Global Temporary tables are permanent objects. It's only the data in them that is temporary.
Re: Return data from Oracle for Crystal [message #470340 is a reply to message #470338] Mon, 09 August 2010 10:38 Go to previous messageGo to next message
Michael Gaarde
Messages: 29
Registered: August 2010
Junior Member
Cookiemonster, You are probably right, but I have sp's with thousand+ lines of codes with calls to webservices etc. I don't see that I can do this in one Oracle statement
Re: Return data from Oracle for Crystal [message #470341 is a reply to message #470337] Mon, 09 August 2010 10:38 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
To clarify:

You have to create the temporary table and the procedure ONCE.

Just CALL the procedure from your client application, don't re-create it.
Re: Return data from Oracle for Crystal [message #470343 is a reply to message #470341] Mon, 09 August 2010 10:41 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

...
so with the given time in mind
...
thousand+ lines of codes with calls to webservices etc.


If there are "webservices etc..." also, then it might not be possible in the in the "given time in mind", since webservices etc.. also work completely different in Oracle.
Re: Return data from Oracle for Crystal [message #470345 is a reply to message #470343] Mon, 09 August 2010 10:44 Go to previous messageGo to next message
Michael Gaarde
Messages: 29
Registered: August 2010
Junior Member
Thomas,

Got the Webservices to react correctly,

I'm quite OK with creting the temp table outside the SP, problem is that when I call the sp from Business Objects then it says that the object no longer exists
Re: Return data from Oracle for Crystal [message #470346 is a reply to message #470345] Mon, 09 August 2010 10:51 Go to previous messageGo to next message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then you have an error in your code somewhere. It works that way:

First session to create the objects:

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Aug 9 17:48:23 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE GLOBAL TEMPORARY TABLE my_temp_table2 (name VARCHAR(255));

Table created.

SQL>
SQL> CREATE OR REPLACE procedure p2 (p_cur in out sys_refcursor)
  2  is
  3  begin
  4
  5  INSERT INTO my_temp_table2
  6  select 'foo' FROM dual;
  7
  8  open p_cur for select * from my_temp_table2;
  9  end;
 10  /

Procedure created.

SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


New Session that calls the procedure:

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Aug 9 17:48:49 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> VARIABLE resultSet REFCURSOR
SQL> EXEC p2 (:resultSet);

PL/SQL procedure successfully completed.

SQL> print resultset

NAME
--------------------------------------------------------------------------------

foo

SQL>
Re: Return data from Oracle for Crystal [message #470389 is a reply to message #470299] Mon, 09 August 2010 13:55 Go to previous messageGo to next message
Michael Gaarde
Messages: 29
Registered: August 2010
Junior Member
Hi Thomas,

can't thank you enough for your effort.

agree that it works as you describe, but not when calling from i.e. Business Objects where you just refer to the sp.

pls. see attachedhttp://www.herreklubben.dk/files/capture.jpg

Re: Return data from Oracle for Crystal [message #470512 is a reply to message #470389] Tue, 10 August 2010 02:41 Go to previous message
ThomasG
Messages: 3099
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, I Googled for "Universe Desinger" but haven't found anything helpful. Are there any examples for Oracle in the programs documentation?
Previous Topic: Problem with floating point values
Next Topic: logon trigger creates 2 records
Goto Forum:
  


Current Time: Thu Aug 28 11:01:48 CDT 2014

Total time taken to generate the page: 0.07012 seconds