Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Crystal Reports & PL/SQL?
- Thread starter lutzs
- Start date
You can't run PL/SQL from Crystal. The SQL designer is only for individual SQL statements. It can't handle blocks.
Point your report directly at your stored procedure, and use the procedure - not the SQL Designer query - as your datasource.
Naith
Point your report directly at your stored procedure, and use the procedure - not the SQL Designer query - as your datasource.
Naith
- Thread starter
- #3
In Report Designer:
Make sure File/Options/Show "Stored Procedures" is checked on.
In Data Explorer (from Database/Add Database to Report). Find your datasource - most likely through ODBC or More Data Sources.
A submenu of Tables vs Stored Procedures should appear, showing the Tables and Stored procs in your database respectively.
Naith
Make sure File/Options/Show "Stored Procedures" is checked on.
In Data Explorer (from Database/Add Database to Report). Find your datasource - most likely through ODBC or More Data Sources.
A submenu of Tables vs Stored Procedures should appear, showing the Tables and Stored procs in your database respectively.
Naith
Guest_imported
New member
- Jan 1, 1970
- 0
Hi,
i have found the submenu of tables and stored procedures.
At first, my procedur "wpart" is not in the menu. I can't find it.
When I try one of the existing procedures (add) I get the following error message:
"ODBC Error- Oracle Driver- Ora-06550
PLS: 00221: 'VORTAG' is not a procedure or is not defined
PL/SQL: Statement ignored"
Stephanie
i have found the submenu of tables and stored procedures.
At first, my procedur "wpart" is not in the menu. I can't find it.
When I try one of the existing procedures (add) I get the following error message:
"ODBC Error- Oracle Driver- Ora-06550
PLS: 00221: 'VORTAG' is not a procedure or is not defined
PL/SQL: Statement ignored"
Stephanie
Assuming you've got the correct database, and "wpart" has been compiled successfully, what should make it different from other procedures that do appear in the list.
With regard to the Vortag error, this is one of the few times an Oracle error message is actually specific enough to be helpful. It suggests that Vortag has not been declared or may actually be another object - like a function.
Compile wpart, refresh the Data Explorer list, and reconnect the report.
Naith
With regard to the Vortag error, this is one of the few times an Oracle error message is actually specific enough to be helpful. It suggests that Vortag has not been declared or may actually be another object - like a function.
Compile wpart, refresh the Data Explorer list, and reconnect the report.
Naith
- Thread starter
- #7
I don't really know why you're trying to execute your stored procedure with SQLoader. SQLoader is only used for loading data into tables. Think of it as a glorified "INSERT INTO" or "SELECT INTO" command.
Because you're going to be directly querying your stored procedure, you don't need to sweat SQLoader at all. By all means, do use SQLoader if you have to query a table instead of a stored procedure, but can only source the data from wpart's output. But I think that's putting an extra - and ultimately unnecessary - step into the equation.
In whatever Oracle tool you're using, you'll find a Compile option in one of your menus. Use it to compile wpart. This basically commits the procedure to your inventory of stored procedures.
Then, when you check out the Data Explorer in Crystal Reports, wpart will be right there.
Naith
Because you're going to be directly querying your stored procedure, you don't need to sweat SQLoader at all. By all means, do use SQLoader if you have to query a table instead of a stored procedure, but can only source the data from wpart's output. But I think that's putting an extra - and ultimately unnecessary - step into the equation.
In whatever Oracle tool you're using, you'll find a Compile option in one of your menus. Use it to compile wpart. This basically commits the procedure to your inventory of stored procedures.
Then, when you check out the Data Explorer in Crystal Reports, wpart will be right there.
Naith
- Thread starter
- #9
Hi,
ok, i have compiled the stored procedure and it's visible in the Data Explorer (Crystal Reports).
If I click on "Add" = Error Message:
"ODBC error: [Microsoft][ODBC driver for oracle]ORA-06550: Line 1, Column 14:
PLS-00905: Object SYSTEM.WPART is invalid
ORA-06550: Line 1, Column 7:
PL/SQL: Statement ignored
ok, i have compiled the stored procedure and it's visible in the Data Explorer (Crystal Reports).
If I click on "Add" = Error Message:
"ODBC error: [Microsoft][ODBC driver for oracle]ORA-06550: Line 1, Column 14:
PLS-00905: Object SYSTEM.WPART is invalid
ORA-06550: Line 1, Column 7:
PL/SQL: Statement ignored
Stephanie,
You might want to try executing this procedure successfully in an Oracle environment before you give it to Crystal, because it looks as if you still have a few things the procedure would like you to iron out here.
Your error is occuring because Oracle thinks that SYSTEM.WPART is an invalid name. That is to say, it thinks that this object has been altered, dropped, replaced, or attributed to SYSTEM when, in fact, the stored procedure belongs to another user.
There is also the possibility that whatever user you are connecting Crystal to Oracle with does not have the permissions to do what you're trying to do with WPART. I'm not sure if this last one should be the case if you can see the stored proc in the Data Explorer, but just to be safe, make sure.
Open an Oracle session as your Crystal user, and try to execute SYSTEM.WPART - actually referring to it like that; OWNER.WPART. When you can execute that successfully, then try connecting in Crystal again.
Naith
You might want to try executing this procedure successfully in an Oracle environment before you give it to Crystal, because it looks as if you still have a few things the procedure would like you to iron out here.
Your error is occuring because Oracle thinks that SYSTEM.WPART is an invalid name. That is to say, it thinks that this object has been altered, dropped, replaced, or attributed to SYSTEM when, in fact, the stored procedure belongs to another user.
There is also the possibility that whatever user you are connecting Crystal to Oracle with does not have the permissions to do what you're trying to do with WPART. I'm not sure if this last one should be the case if you can see the stored proc in the Data Explorer, but just to be safe, make sure.
Open an Oracle session as your Crystal user, and try to execute SYSTEM.WPART - actually referring to it like that; OWNER.WPART. When you can execute that successfully, then try connecting in Crystal again.
Naith
synapsevampire
Programmer
CR is particular about which connectivity is used, and the type OF SP, it must have a in/out cursor.
Crystal has whitepapers on this, check their knowledgebase, and there are lots of posts here on tek-tips related to this.
I'm told that CR 9 will support PL/SQL.
-k kai@informeddatadecisions.com
Crystal has whitepapers on this, check their knowledgebase, and there are lots of posts here on tek-tips related to this.
I'm told that CR 9 will support PL/SQL.
-k kai@informeddatadecisions.com
- Thread starter
- #12
That's certainly *a* problem, because it means that DBA Studio doesn't think that the procedure compiled without errors.
But before you tackle that, SynapseVampire brought up a very key point, which I actually overlooked.
Your procedure essentially has to satisfy the criteria that was mentioned in the prior post. If you go to
and have a look for
SCR_Oracle_Stored_Procedures.pdf
you'll be taken through what your stored procedure needs to look like in order to report off it successfully.
Naith
But before you tackle that, SynapseVampire brought up a very key point, which I actually overlooked.
Your procedure essentially has to satisfy the criteria that was mentioned in the prior post. If you go to
and have a look for
SCR_Oracle_Stored_Procedures.pdf
you'll be taken through what your stored procedure needs to look like in order to report off it successfully.
Naith
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question