Solved

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

Posted on 2003-11-19
11
934 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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 101

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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. …
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…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

707 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