Solved

Switching Oracle databases from Crystal Reports. Syntax of stored procedures.

Posted on 2003-11-19
11
921 Views
Last Modified: 2012-06-21
I am switching Databases in Crystal Reports 9 in runtime using Report Designer Component (craxdrt9.dll)

Crystal Reports refer to Oracle stored procedure that has SQL statement :
SELECT 1
INTO aaa
FROM DUAL
WHERE EXISTS (SELECT *
             FROM TestTable
)
During running I've got error:
Fail to execute SQL statement. OCI Call: OCIStmtExecute

When I've changed SQL in Oracle stored procedure to
 SELECT COUNT(*)
 INTO aaa
 FROM TestTable
 
no exception occurred.

Is it correct behavior of Crystal Report Designer Component?
What are the syntax requirements for Oracle stored procedures that are called from Crystal Report?
0
Comment
Question by:lizza
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 13

Accepted Solution

by:
EwaldL earned 125 total points
ID: 9778023
i would think that an INTO clause would not be supported in general, although the simple select without nesting works fine for you.  why do you need the INTO here? could you drop it? It may also be better to specify the actual field name in the select part

Here is more info on using stored procedures with crystal
http://support.crystaldecisions.com/communityCS/TechnicalPapers/SCR_Oracle_Stored_Procedures.pdf.asp
http://support.crystaldecisions.com/communityCS/TechnicalPapers/storproc.pdf.asp
0
 

Author Comment

by:lizza
ID: 9778324
Test SQL for Stored procedures.
CREATE TABLE TestTable (a1 NUMBER);

INSERT INTO TestTable VALUES (1);

CREATE OR REPLACE PACKAGE cur_types
AS
   TYPE curTyp IS REF CURSOR;
END;
/

CREATE OR REPLACE PROCEDURE TestSP1 (cv OUT Cur_Types.CurTyp)
IS
   aaa   NUMBER;
BEGIN
   SELECT 1
     INTO aaa
     FROM DUAL
    WHERE EXISTS (SELECT *
                    FROM TestTable P
                   WHERE a1 < 0);

   --open dummy cursor
   OPEN cv FOR
      SELECT sysdate
        FROM dual;
END;
/


CREATE OR REPLACE PROCEDURE TestSP2 (cv OUT Cur_Types.CurTyp)
IS
   aaa   NUMBER;
BEGIN
   SELECT COUNT (*)
     INTO aaa
     FROM TestTable
     WHERE a1 < 0;

   --open dummy cursor
   OPEN cv FOR
      SELECT sysdate
        FROM dual;
END;
/
SP TestSP1 works only with original database. When change database at runtime, exception occurs.
SP TestSP2 works with original and changed databases.
0
 
LVL 42

Expert Comment

by:frodoman
ID: 9778887
Just a thought, but when you change the database does: (SELECT * FROM TestTable P      WHERE a1 < 0) actually return anything?

The reason for your error could be that in the first SP you're selecting a fixed value INTO a variable but only if your WHERE EXISTS returns at least 1 record.  If it returns no records than you're doing an INSERT INTO that isn't selecting any data - an exception for Oracle.

The second SP works because your INSERT INTO is a COUNT, which will always return a value (whether the value is zero or otherwise).
0
 

Author Comment

by:lizza
ID: 9779078
When I change database SELECT * FROM TestTable P WHERE a1 < 0 return nothing (in original database too).
Actually I use the same database, but different database users.
I just wonder that TestSP1 works ALWAYS correctly for original user, but throws exception after changing user at runtime. (Table TestTable has the same content for both users)
Now I know how to avoid such situations. I'll change all SP to use COUNT instead of using EXISTS, but this will decrease the performance.

0
 
LVL 42

Expert Comment

by:frodoman
ID: 9779176
The alternative is to catch the exception and handle it in your sp but that has overhead of it's own...

EXCEPTION
   WHEN NO_DATA_FOUND THEN    
       (Your code here);

END;
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 100

Expert Comment

by:mlmcc
ID: 9779320
I don't know about CR9 but previous versions of Crystal have had trouble with SQL with multiple select clauses which could explain why the second version works while the first doesn't.

mlmcc
0
 
LVL 3

Expert Comment

by:AnnetteHarper
ID: 9779324
I don't think that the errors that you're getting have anything to do with running the procedures from Crystal Reports. Have you tried just executing them from SQL*Plus logged in as the two different users?

Since you're only populating TestTable with one record where a1=1, the Select statement in TestSP1 should ALWAYS result in an error being raised and therefore the Open cursor statement would never be executed, resulting in an error when Crystal tries to fetch data from it. The Select statement in TestSP2 will ALWAYS return something even if it's zero, so the logic will continue and the cursor will be opened.

So, the real question is why TestSP1 works even for the original user? Either TestTable is populated with something where a1 is less than 0 for that user, or some other odd condition.
0
 
LVL 3

Expert Comment

by:AnnetteHarper
ID: 9783017
lizza,

Just feel like I should reiterate. You can use a Select Into statement in a stored procedure. There is no reason that you can't. I've done it. You can do pretty much anything in the stored procedure as long as it results in an open REF Cursor for Crystal. Crystal doesn't know or care what happens inside the procedure as long as it returns one IN OUT REF CURSOR parameter and no other OUT parameters. This is a different situation than creating a SQL Command where a SELECT INTO would not be allowed since it is a PL/SQL statement.

The INTO is not what is causing your problem here. Your problem MUST be related to the fact that the cursor is not getting opened properly because there is an error condition encountered before the open statement. Verify that at least one record exists in the TestTable for each user, that that record has a1=1, and change the WHERE a1 < 0 to WHERE a1 > 0, and I think you'll get success for both schemas. If not, please let me know.

If you must do a Select Into that may fail due to "No Data Returned" you should create exception logic so that your cursor will still get opened anyway.

Annette Harper
0
 
LVL 42

Expert Comment

by:frodoman
ID: 9786971
Annette - I believe that's what I already said, but I agree :-)  - Frodoman
0
 
LVL 3

Expert Comment

by:AnnetteHarper
ID: 9787032
Yes, you did, I should have mentioned that. I apologize.
0
 
LVL 42

Expert Comment

by:frodoman
ID: 9787094
Not a problem!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now