I'm trying to develop a stored procedure for a Crystal reports.
Here's the stored procedure. The SP uses the following package for
the REF CURSOR.
CREATE OR REPLACE PACKAGE J1R_TYPES AS
TYPE ReturnCursorTyp e IS REF CURSOR;
END J1R_TYPES;
CREATE OR REPLACE PROCEDURE custom_report(
STARTDATE_STR_I N IN VARCHAR2 DEFAULT '01/01/2000',
STOPDATE_STR_IN IN VARCHAR2 DEFAULT '01/01/2000',
RC1 OUT J1R_TYPES.Retur nCursorType)
AS
startdate DATE;
stopdate DATE;
startdate_str VARCHAR2(10);
stopdate_str VARCHAR2(10);
SQLtext VARCHAR2(2000);
BEGIN
startdate_str := STARTDATE_STR_I N;
stopdate_str := STOPDATE_STR_IN ;
startdate := TO_DATE(startda te_str, 'mm/dd/yyyy');
stopdate := TO_DATE(stopdat e_str, 'mm/dd/yyyy');
SQLtext := 'select max(query_origi nal) as query, count(q.query_p k) as
query_count, ';
SQLtext := SQLtext || 'max(url) as url, count(url) as url_count from
query q, match m, pick p, document d ';
SQLtext := SQLtext || 'where q.query_pk = m.query_pk and m.match_pk =
p.match_pk ';
SQLtext := SQLtext || 'and p.document_pk = d.document_pk and
q.query_pk <> 0 and q.create_date >= startdate ';
SQLtext := SQLtext || 'and q.create_date <= stopdate group by
query_original, url ';
OPEN RC1 FOR SQLtext;
END custom_report;
Whenever I try to run the report in Crystal, I keep getting an invalid
column commance at the 'OPEN RC1 FOR SQLTEXT' line. Can anyone tell
me how I can run the stored procedure in SqlPlus? I executed the SQL
command and I'm sure the column names are correct. Any help is
greatly appreciated. Thanks.
Sam 1 6099 sv*****@hotmail .com (sam) wrote in message news:<ce******* *************** ****@posting.go ogle.com>... I'm trying to develop a stored procedure for a Crystal reports. Here's the stored procedure. The SP uses the following package for the REF CURSOR.
CREATE OR REPLACE PACKAGE J1R_TYPES AS TYPE ReturnCursorTyp e IS REF CURSOR; END J1R_TYPES;
CREATE OR REPLACE PROCEDURE custom_report( STARTDATE_STR_I N IN VARCHAR2 DEFAULT '01/01/2000', STOPDATE_STR_IN IN VARCHAR2 DEFAULT '01/01/2000', RC1 OUT J1R_TYPES.Retur nCursorType)
AS
startdate DATE; stopdate DATE; startdate_str VARCHAR2(10); stopdate_str VARCHAR2(10); SQLtext VARCHAR2(2000);
BEGIN
startdate_str := STARTDATE_STR_I N; stopdate_str := STOPDATE_STR_IN ;
startdate := TO_DATE(startda te_str, 'mm/dd/yyyy'); stopdate := TO_DATE(stopdat e_str, 'mm/dd/yyyy');
SQLtext := 'select max(query_origi nal) as query, count(q.query_p k) as query_count, '; SQLtext := SQLtext || 'max(url) as url, count(url) as url_count from query q, match m, pick p, document d '; SQLtext := SQLtext || 'where q.query_pk = m.query_pk and m.match_pk = p.match_pk '; SQLtext := SQLtext || 'and p.document_pk = d.document_pk and q.query_pk <> 0 and q.create_date >= startdate '; SQLtext := SQLtext || 'and q.create_date <= stopdate group by query_original, url ';
OPEN RC1 FOR SQLtext;
END custom_report;
Whenever I try to run the report in Crystal, I keep getting an invalid column commance at the 'OPEN RC1 FOR SQLTEXT' line. Can anyone tell me how I can run the stored procedure in SqlPlus? I executed the SQL command and I'm sure the column names are correct. Any help is greatly appreciated. Thanks.
variable cur refcursor;
begin
your procedure(p1,p2 , :cur);
end;
/
print :cur
Sybrand Bakker
Senior Oracle DBA
Sam This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Michael Trosen |
last post by:
Hi Everyone,
I hope someone can help, I'm pretty new to pro*c programming.
I have the following application setup:
a pro*c program calls a stored procedure and recieves a cursor back:
the cursor is defined as: SQL_CURSOR delpt_cursor
|
by: dinesh prasad |
last post by:
I'm trying to use a servlet to process a form, then send that data to
an SQL server stored procedure. I'm using the WebLogic 8 App. server.
I am able to retrieve database information, so I know my application
server can talk to the database.
I've determined the failure occurs when the the following statement is
executed: cstmt.execute(); (due...
|
by: Jarrod Morrison |
last post by:
Hi all
Im relatively new to using stored procedures and im not sure if it is
possible to do what I am trying to do so any help here is greatly
appreciated. I am using the variable @MachineName which is obviously the
local machine name mainly in this procedure. What is loop through from the
first character of the variable to the last and use...
|
by: marc |
last post by:
I've been developing a stored procedure that uses a user defined
function in the query portion of the procedure. However, since the end
product needs to allow for dynamic table names, the UDF will not work.
I've been trying to get this to work with converting the UDF to a
procedure, but I'm having no luck.
Here is the background on what I'm...
|
by: Rhino |
last post by:
I've spent the last couple of hours trying to figure out how to debug a Java
stored procedure and am just going in circles. The last straw came when I
got "Cannot open input stream for default" when I launched the IBM
Distributed Debugger via
D:\IBMDebug>idebug.exe -qdaemon -quiport=8000,8001
First, a bit of background.
I am running DB2...
| |
by: Daniel Manes |
last post by:
I've been trying to copy tables from a linked server to a SQL Server
Express database. Express seems to have no direct/automatic way to do
it, so I've been looking into doing this by hand (i.e., with a T-SQL
procedure).
I've discovered some system stored procedures that seem relevant (like
sp_tables_ex and sp_columns_ex). But they're...
|
by: nishi57 |
last post by:
I hope I can get some help regarding this issue, which has been going
on for a while. I have a desktop user who is having problem running
"Stored Procedures". The DB2 Connect application works fine but when he
runs the stored procedure, he gets the following error message.
"SYSPROC".CSGCSB54 - Run started.
Data returned in result sets is...
|
by: SOI_0152 |
last post by:
Hi all!
Happy New Year 2008. Il hope it will bring you love and happyness
I'm new on this forum.
I wrote a stored procedure on mainframe using DB2 7.1.1 and IBM language c.
Everything works fine.
Now we decided to move from mainframe IMS-DB2 to Windows 2003 server-DB2 UDB
for LUW 9.5.
|
by: sam |
last post by:
I'm trying to develop a stored procedure for a Crystal reports.
Here's the stored procedure. The SP uses the following package for
the REF CURSOR.
CREATE OR REPLACE PACKAGE J1R_TYPES AS
TYPE ReturnCursorType IS REF CURSOR;
END J1R_TYPES;
CREATE OR REPLACE PROCEDURE custom_report(
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| | |