calling reports from forms

I have been trying to call my report from form through this code but neither it gives me any error nor its showing any report.

declare
report_id Report_Object;
report_job_id VARCHAR2(100);
BEGIN
report_id:= find_report_object('REPORT5');
report_JOB_ID:=run_report_object(report_id);
END;

Comm Mode : async
desctype : screen
Ex mode: runtime
file name : F:\Docs\Forms\vivek.rdf

report5 is the name of report object in the navigator.

What could be the problem??Hi,

Does Your report works fine? Is it fetching the required records ?|||Yep perfectly !

Originally posted by satish_ct
Hi,

Does Your report works fine? Is it fetching the required records ?|||Try using RUN_REPORT(<<filename>>)|||Originally posted by shelva
Try using RUN_REPORT(<<filename>>)

RUN_REPORT runs only from report as SRW.RUN_REPORT and not from form.|||Hey sorry u r right
its ALL_REPORT(<<FIILE NAME>>) from forms

Or in ur method, just give full path of the reports file name|||Originally posted by shelva
Hey sorry u r right
its ALL_REPORT(<<FIILE NAME>>) from forms

Or in ur method, just give full path of the reports file name

Never heard of it and neither it works.|||Oye again typing mistake

its CALL_REPORT.. sorry again

Calling Oracle Procedures

I've seen some threads on this issue, but no clear resolution. Here is my
procedure declare:
PROCEDURE get_releases (
p_CNUMBER IN VARCHAR2,
p_HIDDENSTATE IN NUMBER,
p_COMPLIANCE IN NUMBER,
p_OBSOLETE IN NUMBER,
p_CORE IN NUMBER,
p_REL_CUR OUT RelCurTyp);
RelCurTyp is defined as an out cursor in the package definition.
I've created the report and data source and I'm trying to execute this text
command type:
get_releases :cnumber, :hiddenState, :compliance, :obsolete
It then asks me to enter the values for the parameters, but I then get
ORA-00900: invalid SQL statement.
What is the correct way to call this procedure? Will the oledb automatically
handle the returned cursor?I wanted to know how can we call Oracle stored procedures from Microsoft
Reporting Services. I could pass a sql text and get a record set, but I want
to execute a store proc and get the record set, and kindly let me know how to
pass a refcursor to get the record set into the reporting services.
Tom, please let me know when you have your answer. Thanks!
"Tom" wrote:
> I've seen some threads on this issue, but no clear resolution. Here is my
> procedure declare:
> PROCEDURE get_releases (
> p_CNUMBER IN VARCHAR2,
> p_HIDDENSTATE IN NUMBER,
> p_COMPLIANCE IN NUMBER,
> p_OBSOLETE IN NUMBER,
> p_CORE IN NUMBER,
> p_REL_CUR OUT RelCurTyp);
> RelCurTyp is defined as an out cursor in the package definition.
> I've created the report and data source and I'm trying to execute this text
> command type:
> get_releases :cnumber, :hiddenState, :compliance, :obsolete
> It then asks me to enter the values for the parameters, but I then get
> ORA-00900: invalid SQL statement.
> What is the correct way to call this procedure? Will the oledb automatically
> handle the returned cursor?|||I tried everything I could think of, and just before the laptop was about to
take a flying leap out of the window, I decided to re-do the report from
scratch.
I added a new report in the solution, copied all of the controls from the
old report to the new one and re-created the Data object as follows:
- Command type = Storedprocedure
- The only text to be added is : COMPLIANCE_PKG.get_releases
- Added the parameters by clicking the "..." next to the Dataset, and set
the parameters in the parameters tab(the out param is not required).
The DataSource is pointing to MS OLE DB Provider for Oracle and the Type of
the data source is set to Oracle, so the connection string should only have
"data source={your DS}"
It now works like a charm.|||Hi Tom,
I am working on the similar situation and call the following Oracle proc
from a MSRS report in MS Visual Studio:
MPC.Report_Performance_Main_5 :Response_Interval, :Launch_List
Response_Interval and Launch_List are both query parameters and report
parameters. The proc is:
Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
ip_launch_list IN VARCHAR2,
results_cur OUT T_RESULT_CURSOR)
When I pushed the preview button, I got the following error:
ORA-00972:identifier is too long
ORA-06512:at "SYS.DBMS_UTILITY", line 114
ORA-06512: at line 1
The stored proc looks fine. It seems there is a problem from MSRS to pass
the input parameters.
Any clues would be very appreciated!
James
"Tom" wrote:
> I tried everything I could think of, and just before the laptop was about to
> take a flying leap out of the window, I decided to re-do the report from
> scratch.
> I added a new report in the solution, copied all of the controls from the
> old report to the new one and re-created the Data object as follows:
> - Command type = Storedprocedure
> - The only text to be added is : COMPLIANCE_PKG.get_releases
> - Added the parameters by clicking the "..." next to the Dataset, and set
> the parameters in the parameters tab(the out param is not required).
> The DataSource is pointing to MS OLE DB Provider for Oracle and the Type of
> the data source is set to Oracle, so the connection string should only have
> "data source={your DS}"
> It now works like a charm.|||TOM!!
you are great man!! thank you soooooo much,,, i have been trying to do that
for 3 !! and your posting solved my problem...
thaaaaaaaaaaaaaaank you
"Tom" wrote:
> I tried everything I could think of, and just before the laptop was about to
> take a flying leap out of the window, I decided to re-do the report from
> scratch.
> I added a new report in the solution, copied all of the controls from the
> old report to the new one and re-created the Data object as follows:
> - Command type = Storedprocedure
> - The only text to be added is : COMPLIANCE_PKG.get_releases
> - Added the parameters by clicking the "..." next to the Dataset, and set
> the parameters in the parameters tab(the out param is not required).
> The DataSource is pointing to MS OLE DB Provider for Oracle and the Type of
> the data source is set to Oracle, so the connection string should only have
> "data source={your DS}"
> It now works like a charm.|||Where do I set 'Command type = Storedprocedure'? or How do I recreate the
dataset?
"Tom" wrote:
> I tried everything I could think of, and just before the laptop was about to
> take a flying leap out of the window, I decided to re-do the report from
> scratch.
> I added a new report in the solution, copied all of the controls from the
> old report to the new one and re-created the Data object as follows:
> - Command type = Storedprocedure
> - The only text to be added is : COMPLIANCE_PKG.get_releases
> - Added the parameters by clicking the "..." next to the Dataset, and set
> the parameters in the parameters tab(the out param is not required).
> The DataSource is pointing to MS OLE DB Provider for Oracle and the Type of
> the data source is set to Oracle, so the connection string should only have
> "data source={your DS}"
> It now works like a charm.

call stored procedure in cursor

Hi all
Can I call and use the stored procedure resultset in the
cursor like
DECLARE WeeklyYield_Cursor Cursor For
dbo.RPT_WeeklyYieldCycleTimeForWeekRange '301','327'
Open WeeklyYield_Cursor
Fetch next from WeeklyYield_Cursor into @.WeekID,@.LotID
While @.@.Fetch_Status = 0
begin
up_RPT_PrepareWeeklyDefectDetailData @.Lotid
up_RPT_UpdateWeeklyYieldCycleTime
Fetch next from WeeklyYield_Cursor into @.WeekID,@.LotID
end
CLOSE WeeklyYield_Cursor
DEALLOCATE WeeklyYield_Cursor
Plese help
AnandHi Anand,
You have to insert the results of the stored procedure into a temporary
table and then define the cursor on the temporary table:
CREATE TABLE #WeeklyYield (WeekID INT, LotID INT)
INSERT INTO #WeeklyYield
EXEC dbo.RPT_WeeklyYieldCycleTimeForWeekRange '301','327'
Keep in mind though that cursor are much slower than set based solutions, so
you might want to look if you can rewrite things. The least you should do is
declare your cursor as LOCAL FAST_FORWARD for optimal cursor performance.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Anand" <gurusanand1@.sifymail.com> wrote in message
news:079401c35705$e6e13e00$a301280a@.phx.gbl...
> Hi all
> Can I call and use the stored procedure resultset in the
> cursor like
> DECLARE WeeklyYield_Cursor Cursor For
> dbo.RPT_WeeklyYieldCycleTimeForWeekRange '301','327'
> Open WeeklyYield_Cursor
> Fetch next from WeeklyYield_Cursor into @.WeekID,@.LotID
> While @.@.Fetch_Status = 0
> begin
> up_RPT_PrepareWeeklyDefectDetailData @.Lotid
> up_RPT_UpdateWeeklyYieldCycleTime
> Fetch next from WeeklyYield_Cursor into @.WeekID,@.LotID
> end
> CLOSE WeeklyYield_Cursor
> DEALLOCATE WeeklyYield_Cursor
> Plese help
> Anand|||I assume you're asking if you can use
"dbo.RPT_WeeklyYieldCycleTimeForWeekRange '301','327'" as the cursor...
the answer is no...If you are using SQL Server 2000, you could use a
table function to return a selectable resultset. I've never actually done
it from a cursor, but have done it as part of otehr select statements.
Can't be all that different.
TG
Anand wrote:
> Hi all
> Can I call and use the stored procedure resultset in the
> cursor like
> DECLARE WeeklyYield_Cursor Cursor For
> dbo.RPT_WeeklyYieldCycleTimeForWeekRange '301','327'
> Open WeeklyYield_Cursor
> Fetch next from WeeklyYield_Cursor into @.WeekID,@.LotID
> While @.@.Fetch_Status = 0
> begin
> up_RPT_PrepareWeeklyDefectDetailData @.Lotid
> up_RPT_UpdateWeeklyYieldCycleTime
> Fetch next from WeeklyYield_Cursor into @.WeekID,@.LotID
> end
> CLOSE WeeklyYield_Cursor
> DEALLOCATE WeeklyYield_Cursor
> Plese help
> Anand
/n/n/n==================================*** Sent via DeveloperKB.com http://www.developerkb.com ***
For all your programming needs.
==================================

Calendar Table

I am trying to get all dates starting from June 20 , 2004 to yesterday but
the table includes today's that (which i dont want).
DECLARE @.Tmp TABLE (DOM smalldatetime)
DECLARE @.day smalldatetime
DECLARE @.DOM smalldatetime
SET @.day = 'June 20, 2004'
WHILE @.day < GetDate()
BEGIN
INSERT INTO @.Tmp
SELECT @.day as DOM
SET @.Day = @.Day + 1
END@.day is not < GETDATE() until it hits the next day (because of the time
component). You need to remove the time component from GETDATE() somehow.
I have done so in the example below:
DECLARE @.Tmp TABLE (DOM smalldatetime)
DECLARE @.day smalldatetime
DECLARE @.DOM smalldatetime
SET @.day = 'June 20, 2004'
WHILE @.day < CONVERT(datetime,CONVERT(char(8),GetDate(),112))
BEGIN
INSERT INTO @.Tmp
SELECT @.day as DOM
SET @.Day = @.Day + 1
END
Keith
"Asim" <Asim@.discussions.microsoft.com> wrote in message
news:090F9AA5-BF5D-44C4-9405-96D6D7276493@.microsoft.com...
>I am trying to get all dates starting from June 20 , 2004 to yesterday but
> the table includes today's that (which i dont want).
> DECLARE @.Tmp TABLE (DOM smalldatetime)
> DECLARE @.day smalldatetime
> DECLARE @.DOM smalldatetime
> SET @.day = 'June 20, 2004'
> WHILE @.day < GetDate()
> BEGIN
> INSERT INTO @.Tmp
> SELECT @.day as DOM
> SET @.Day = @.Day + 1
> END|||Thankyou very much..
"Keith Kratochvil" wrote:
> @.day is not < GETDATE() until it hits the next day (because of the time
> component). You need to remove the time component from GETDATE() somehow.
> I have done so in the example below:
> DECLARE @.Tmp TABLE (DOM smalldatetime)
> DECLARE @.day smalldatetime
> DECLARE @.DOM smalldatetime
> SET @.day = 'June 20, 2004'
> WHILE @.day < CONVERT(datetime,CONVERT(char(8),GetDate(),112))
> BEGIN
> INSERT INTO @.Tmp
> SELECT @.day as DOM
> SET @.Day = @.Day + 1
> END
>
> --
> Keith
>
> "Asim" <Asim@.discussions.microsoft.com> wrote in message
> news:090F9AA5-BF5D-44C4-9405-96D6D7276493@.microsoft.com...
> >I am trying to get all dates starting from June 20 , 2004 to yesterday but
> > the table includes today's that (which i dont want).
> >
> > DECLARE @.Tmp TABLE (DOM smalldatetime)
> > DECLARE @.day smalldatetime
> > DECLARE @.DOM smalldatetime
> >
> > SET @.day = 'June 20, 2004'
> >
> > WHILE @.day < GetDate()
> > BEGIN
> > INSERT INTO @.Tmp
> > SELECT @.day as DOM
> > SET @.Day = @.Day + 1
> > END
>
>

Calendar Table

I am trying to get all dates starting from June 20 , 2004 to yesterday but
the table includes today's that (which i dont want).
DECLARE @.Tmp TABLE (DOM smalldatetime)
DECLARE @.day smalldatetime
DECLARE @.DOM smalldatetime
SET @.day = 'June 20, 2004'
WHILE @.day < GetDate()
BEGIN
INSERT INTO @.Tmp
SELECT @.day as DOM
SET @.Day = @.Day + 1
END@.day is not < GETDATE() until it hits the next day (because of the time
component). You need to remove the time component from GETDATE() somehow.
I have done so in the example below:
DECLARE @.Tmp TABLE (DOM smalldatetime)
DECLARE @.day smalldatetime
DECLARE @.DOM smalldatetime
SET @.day = 'June 20, 2004'
WHILE @.day < CONVERT(datetime,CONVERT(char(8),GetDate
(),112))
BEGIN
INSERT INTO @.Tmp
SELECT @.day as DOM
SET @.Day = @.Day + 1
END
Keith
"Asim" <Asim@.discussions.microsoft.com> wrote in message
news:090F9AA5-BF5D-44C4-9405-96D6D7276493@.microsoft.com...
>I am trying to get all dates starting from June 20 , 2004 to yesterday but
> the table includes today's that (which i dont want).
> DECLARE @.Tmp TABLE (DOM smalldatetime)
> DECLARE @.day smalldatetime
> DECLARE @.DOM smalldatetime
> SET @.day = 'June 20, 2004'
> WHILE @.day < GetDate()
> BEGIN
> INSERT INTO @.Tmp
> SELECT @.day as DOM
> SET @.Day = @.Day + 1
> END|||Thankyou very much..
"Keith Kratochvil" wrote:

> @.day is not < GETDATE() until it hits the next day (because of the time
> component). You need to remove the time component from GETDATE() somehow.
> I have done so in the example below:
> DECLARE @.Tmp TABLE (DOM smalldatetime)
> DECLARE @.day smalldatetime
> DECLARE @.DOM smalldatetime
> SET @.day = 'June 20, 2004'
> WHILE @.day < CONVERT(datetime,CONVERT(char(8),GetDate
(),112))
> BEGIN
> INSERT INTO @.Tmp
> SELECT @.day as DOM
> SET @.Day = @.Day + 1
> END
>
> --
> Keith
>
> "Asim" <Asim@.discussions.microsoft.com> wrote in message
> news:090F9AA5-BF5D-44C4-9405-96D6D7276493@.microsoft.com...
>
>

Calendar Table

I am trying to get all dates starting from June 20 , 2004 to yesterday but
the table includes today's that (which i dont want).
DECLARE @.Tmp TABLE (DOM smalldatetime)
DECLARE @.day smalldatetime
DECLARE @.DOM smalldatetime
SET @.day = 'June 20, 2004'
WHILE @.day < GetDate()
BEGIN
INSERT INTO @.Tmp
SELECT @.day as DOM
SET @.Day = @.Day + 1
END
@.day is not < GETDATE() until it hits the next day (because of the time
component). You need to remove the time component from GETDATE() somehow.
I have done so in the example below:
DECLARE @.Tmp TABLE (DOM smalldatetime)
DECLARE @.day smalldatetime
DECLARE @.DOM smalldatetime
SET @.day = 'June 20, 2004'
WHILE @.day < CONVERT(datetime,CONVERT(char(8),GetDate(),112))
BEGIN
INSERT INTO @.Tmp
SELECT @.day as DOM
SET @.Day = @.Day + 1
END
Keith
"Asim" <Asim@.discussions.microsoft.com> wrote in message
news:090F9AA5-BF5D-44C4-9405-96D6D7276493@.microsoft.com...
>I am trying to get all dates starting from June 20 , 2004 to yesterday but
> the table includes today's that (which i dont want).
> DECLARE @.Tmp TABLE (DOM smalldatetime)
> DECLARE @.day smalldatetime
> DECLARE @.DOM smalldatetime
> SET @.day = 'June 20, 2004'
> WHILE @.day < GetDate()
> BEGIN
> INSERT INTO @.Tmp
> SELECT @.day as DOM
> SET @.Day = @.Day + 1
> END
|||Thankyou very much..
"Keith Kratochvil" wrote:

> @.day is not < GETDATE() until it hits the next day (because of the time
> component). You need to remove the time component from GETDATE() somehow.
> I have done so in the example below:
> DECLARE @.Tmp TABLE (DOM smalldatetime)
> DECLARE @.day smalldatetime
> DECLARE @.DOM smalldatetime
> SET @.day = 'June 20, 2004'
> WHILE @.day < CONVERT(datetime,CONVERT(char(8),GetDate(),112))
> BEGIN
> INSERT INTO @.Tmp
> SELECT @.day as DOM
> SET @.Day = @.Day + 1
> END
>
> --
> Keith
>
> "Asim" <Asim@.discussions.microsoft.com> wrote in message
> news:090F9AA5-BF5D-44C4-9405-96D6D7276493@.microsoft.com...
>
>