I'm trying to send email via stored procedure and calling that stored proc from Webfocus. I created table file of the stored proc. Stored procedure works if i don't have code starting from "EXEC msdb..sp_send_dbmail". If I include SP_SEND_DBMAIL, Webfocus report doesn't return anything and keeps searching ( sometimes locks the database ).

Can some one tell me what is wrong in the query? Note: If I run this stored proc from sql query analyzer, it works fine and sends email.


Below is the Webfocus code -
TABLE FILE SP_TEMP PRINT SP_MINAKSHI_TEMP.ANSWERSET1.PRODUCTNAME
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML


Stored Procedure -

ALTER PROCEDURE [dbo].[sp_temp]
AS
BEGIN

IF EXISTS ( SELECT * FROM db_travel_system_test.dbo.temp_trams_matchings2 )
BEGIN
DELETE FROM db_travel_system_test.dbo.temp_trams_matchings2
END

insert into db_travel_system_test.dbo.temp_trams_matchings2
select trams.Confirmation_Number
, trams.Passenger_First_Name + ' ' + trams.Passenger_last_Name as 'PassengerName'
, trams.Agent_Name
, trams.Cruise_Length
, convert(varchar(10) , trams.Booked_Date,110) as 'BookedDate'
, trams.ship_name
, vign.Sail_Date__c
from dbo.webfocus_data_TRAMS_bookings as trams
right join (
select a.Number_of_Passengers__c,a.Cruise_Line__c, destination__C, Itinerary__c, a.Name as sailingsname, a.Vendor_Code__c, b.ship_code__c,
CONVERT(varchar(10), Sail_Date__c,110) as sail_date__c
from salesforcebackups.dbo.Sailings__c a, salesforcebackups.dbo.Ship__c b
where a.Ship__c = b.Id
and YEAR(sail_date__c) >= 2013
and RecordTypeId = '01280000000G5XbAAK'
) as vign on trams.SupplierID = vign.Vendor_Code__c and trams.ShipCode = vign.Ship_Code__c and trams.Cruise_Start_Date = vign.Sail_Date__c
inner join webfocus_report_agency_data agencydata on trams.AgencyID = agencydata.AgencyID
where vign.Ship_Code__c = 'SE'
and sail_date__c = '01-04-2013'
and SupplierID = 'CRYST'
and trams.Confirmation_Number = '1224600'

EXEC msdb..sp_send_dbmail
@profile_name = 'SQLRestricted'
, @recipients = 'xyz@abc.com'
, @copy_recipients = 'xyz@abc.com'
, @subject = 'TRAMS Matchings'
, @query = 'SELECT * FROM db_travel_system_test.dbo.temp_trams_matchings2'
, @body_format ='text'
, @query_result_header = 1
, @query_result_no_padding =1


END

This message has been edited. Last edited by: <Kathryn Henning>,

Hi Mmahadik,

As per IBI, Stored Procedures must adhere to the following rules:

A cursor must be defined with:
1) The TYPE statement in a PACKAGE or PROCEDURE.

2) An associated record layout of the answer set to be returned.

3) The cursor must be opened in the procedure.

4) No fetching is allowed in the stored procedure. The Adapter for Oracle fetches the answer set.

E.g.,

CREATE OR REPLACE PACKAGE pack1 AS
TYPE nfrectype IS RECORD (
pename emp.ename%TYPE,
pjob emp.job%TYPE,
pMgr emp.mgr%TYPE);
TYPE nfcurtype IS REF CURSOR RETURN nfrectype ;
PROCEDURE proc1(v_empno IN NUMBER,c_saltable IN OUT nfcurtype);
END pack1 ;
/
sho error

CREATE OR REPLACE PACKAGE BODY pack1 AS
PROCEDURE proc1 (v_empno IN NUMBER,c_saltable IN OUT nfcurtype)
IS
BEGIN
OPEN c_saltable FOR SELECT ename, job, mgr
FROM scott.emp
WHERE EMPNO = v_empno;
END proc1 ; -- end of procedure
END pack1; -- end of package body
/
sho error

Then call your SP from WebFOCUS using EX or CALL

Thanks,
Rifaz

This message has been edited. Last edited by: Rifaz,

Hi Rifaz, thanks for your reply.

I can call the above procedure without package only issue is executing msdb..sp_send_dbmail command. Query gives results if i don't include that code.

Appreciate your response. Please let me know how to send email via stored procedure in webfocus.

I've had a scenario like that, you may have a look on "Creating a Dynamic Address List in Report Caster." It may help.

Thanks,
Rifaz

I'm not using report caster. this is a normal .fex report built on above stored procedure call.

Hi Mmahadik,

While we wait to see if another customer can help with this issue, please keep us posted on the final solution from your case. Thank you in advance for sharing with all.

Regards,

Kathryn

;(

I need to run a package/stored procedure from oracle too.

CREATE OR REPLACE PACKAGE svstst.pack1 AS
TYPE nfrectype IS RECORD (
poperation plan_table.operation%TYPE,
poptions plan_table.options%TYPE,
pobject_name plan_table.object_name%TYPE);
TYPE nfcurtype IS REF CURSOR RETURN nfrectype ;
PROCEDURE proc1(var1 IN NUMBER,c_saltable IN OUT nfcurtype);
END pack1 ;
/
sho error

CREATE OR REPLACE PACKAGE BODY svstst.pack1 AS
PROCEDURE proc1 (var1 IN NUMBER,c_saltable IN OUT nfcurtype)
IS
BEGIN
OPEN c_saltable FOR SELECT operation, options, object_name
FROM svstst.plan_table
WHERE object_instance = var1 ;
END proc1 ; -- end of procedure
END pack1; -- end of package body
/
sho error


### FEX

SET SQLENGINE = SQLORA

SQL SET SERVER bzmkttst

SQL SQLORA

ex svstst.pack1.proc1;

END



### error

(FOC1400) SQLCODE IS 6550 (HEX: 00001996)
: ORA-06550: line 1, column 26:
: PLS-00103: Encountered the symbol "," when expecting one of the
: following:
: ( ) - + case mod new not null
:
: table continue avg count current exists max min prior sql
: stddev sum variance execute multiset the both leading
: trailing forall merge year month day hour minute second
: timezone_hour timezone_minute timezone_region timezone_abbr
: time timestamp interval date
L (FOC1405) SQL PREPARE ERROR.

This message has been edited. Last edited by: Ricardo Augusto,

I just got it.

SET SQLENGINE = SQLORA

SQL SET SERVER bzmkttst

SQL SQLORA

ex svstst.pack1.proc1 1;

TABLE FILE SQLOUT
PRINT *
END

Good to hear!