Solved

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

Posted on 2003-11-19
11
924 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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
 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

809 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