CR10 with Oracle 9i without using temp table 2

[thisisboni]

Hi,

I need to write a sproc and then report off the sproc via Crystal Enterprise.
I read the need of a ref cursor from the white papers etc..

My question is that is there a way we can report striaght of the parameters returned in the ref cursor - like in SQL SERVER we do a 'fetch next from <cursor_name> into <var_names>' at the end to get the record set which can be utilized at the crystal end.
Most (rather all) the white paper examples do a
Open <ref_cursor> FOR
SELECT *
FROM <table_name>

is there a way I can do the same without the results returned to a table and still be able to get the entire record set at the crystal end ?

Thanks
/Sam

 

[Turkbear]

Hi,
The REFCURSOR, since it is defined as an OUT, will return the entire result set created by your Select statement...

There is no additional table involved...So I am not sure I fully understand your question..





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

 

[thisisboni]

here is a simple sample problem which might help understanding what I am looking for !
here is a white paper example

Test_Table
(ID number(5),
Firstname varchar2(30),
Lastname varchar2(30),
Birthday date);

CREATE OR REPLACE PACKAGE Test_Package
AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE;
PROCEDURE Test_Procedure (
Test_Cursor IN OUT Test_Type,
Test_Parameter IN Test_Table.ID%TYPE
);
END Test_Package;

CREATE OR REPLACE PROCEDURE Test_Procedure (
Test_Cursor IN OUT Test_Package.Test_Type,
Test_Parameter IN Test_Table.ID%TYPE)
AS
BEGIN
OPEN Test_Cursor FOR
SELECT *
FROM Test_Table
WHERE Test_Table.ID = Test_Parameter;
END Test_Procedure;



NOW - suppose there is another table
test_table2 (ID number(5), Joining date)

and I would like to have the 'joining' column in the report as well.
then how do I do this - one way is by making a temp table
(ID number(5),
Firstname varchar2(30),
Lastname varchar2(30),
Birthday date,
joining date)
and then make the REF CUR return this rowtype and inside the proc
OPEN Test_Cursor FOR
SELECT *
FROM Tmp_Table
WHERE Tmp_Table.ID = Test_Parameter;
END Test_Procedure;

is there a way I can do this without the tmp table ?

/sam

 

[Turkbear]

Hi,
Just use both the SP and the test_table2 in your report and join them as usual..OR,( and better) do the join in the REFCURSOR Select statement...

SELECT *
FROM Test_Table,test_table2
WHERE Test_Table.ID = Test_Parameter and
test_table2.ID = Test_Table.ID;

BTW, Using Select * is not a good practice, it is usually best to specify the actual data fields...









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

 

[thisisboni]

thats seems ok - just 2 things

is Oracle sproc good with joins to a table (I remember reading something with Crystal not good at joining sprocs to tables etc... )

second thing is if I do the join in the REFCURSOR Select statement then what would be the return type for the ref_cursor ? (it cannot be a test_table%ROWTYPE for now it will contain the joining column from the test_table2 as well)

thanks,
Sam

 

[Turkbear]

Hi,
I think you can define it as a SYS_REFCURSOR ( weakly typed)...


I have joined SPs and Tables as long as the datatypes were exactly the same for the Joining field..It is still better to do it all at the database side of things..








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

 

[thisisboni]

thanks - lemme be at this - I sure will get back to this thread at completion (if I can !!).

 

[synapsevampire]

Right, you'll recognize poor performance if you join in Crystal.

Turk is spot on when suggesting that you do it all in the SP, there's no reason to consider another means, Oracle will ALWAYS outperform Crystal, and if something changes in the database, just go change the SP and the report is functional again, as opposed to opening the client.

Given the primitive SP example shown, I'd consider using Views instead, one advantage to this is that you can join Views to each other within Crystal, allowing for reusability with standard queries, and Oracle will do the work, and SPs have the nuisance that you can't use multivalue parameters with them.

-k

 

[Turkbear]

Hi,
Actually, synapse, I have found Oracle code that allows you to pass a multi-value list to a SP for processing as an IN clause..
I got it from the asktom Oracle site:

http://asktom.oracle.com/pls/ask/f?...0_P8_DISPLAYID,F4950_P8_CRITERIA:210612357425

Works a treat but the Crystal end needs a little tweaking to be sure the values get passed as CSV without surrounding quotes, like "Smith,Jones,Tammy" Not "Smith","Jones","Tammy"





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

 

[synapsevampire]

Yeah, I do that on occasion, Turk, but it's a kludge, and obviously a horrible user experience, and I've written of it often in the forums here.

There are arrays as parameters available in SQL Server now, but of course Crystal doesn't support this yet.

-k

 

[Turkbear]

Hi,
I agree as to the kludge, but since we use all custom code to produce parameter name/value pairs, we have kept the ugliness away from our users.
We also have the advantage that 98% of our reports are from Oracle datasources so once we created the set of functions/procs needed it was universally available for those rare times it was needed.







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

 

[thisisboni]

I'm gonna 'star' both you folks on this one as Turk's stuff was leading and synapse's idea about views also worked good.

Thanks a bunch guys