Sorry for not providing the code in previous mail, this is what i am trying,
Friends i really need help on this one, i need to create a temporary
table to store all the date i select based on where clause and i need
to return that data, here i am facing two problems,
1st one is that i cant create a temporary table in the procedure and
2nd one is that i am not sure if sys_refcursor i am using will be able
to retun the compete data.
Please Help me friends.
create or replace procedure
spdastgoshwara2r(para_internaldocumentnumber IN number, para_srocode
IN number) is
ref1 sys_refcursor;
/*
MODULE NAME
1.INPUT FORM ENTRY
PURPOSE OF FUNCTION
->USED TO GET FEE DETAILS DATA FROM DOCFEEPAID AND DOCFEECALCULATED
TABLE IN INPUT FORM ENTRY
*/
begin
--DROP TABLE IF EXISTS tempPhotoDatatable ;
CREATE GLOBAL TEMPORARY TABLE tempPhotoDatatable AS
SELECT
RegistrationMaster.InternalDocumentNumber,
DATE_PART('year',RegistrationMaster.Stamp2DateTime) AS
RegYear,
SROMaster.SROMname as SROName,
ArticleMaster.M_article_name as ArticleName,
PartyDetails.FName,
PartyDetails.MName,
PartyDetails.LName,
PartyDetails.Address,
PartyDetails.PinCode,
PartyDetails.Age,
PartyDetails.PANNo,
PartyDetails.PartySRNO AS PartyNumber ,
PartyDetails.PhotoData,
PartyDetails.ThumbData,
PartyMaster.M_party_name as E_party_name,
to_char(RegistrationMaster.Stamp3DateTime,'DD / MM /
YYYY HH12 :
MI : SS AM') as Stamp3DateTime ,
to_char(RegistrationMaster.Stamp4DateTime,'DD / MM /
YYYY HH12 :
MI : SS AM') AS Stamp4DateTime ,
to_char(RegistrationMaster.Stamp5DateTime,'DD / MM /
YYYY HH12 :
MI : SS AM') AS Stamp5DateTime,
RegistrationMaster.DocNumber,
RegistrationMaster.ArticleDescription,
SROMaster.SROCode,
ConfigurationSettings.SROStamp,
SROMaster.SROMshortName,
SROMaster.SROEshortName,
PartyDetails.PhotoTime,
PartyDetails.PhotoPath,
PartyDetails.ThumbPath,
to_char(current_date,'DD / MM / YYYY HH12 : MM : SS')
as Stamp2DateTime,
to_char(RegistrationMaster.DateOfExecution,'Month DD
YYYY') as
DateOfExecution,
1 AS ROWID,
registrationmaster.bookcode
FROM RegistrationMaster INNER JOIN PartyDetails ON
RegistrationMaster.InternalDocumentNumber =
PartyDetails.InternalDocumentNumber AND
RegistrationMaster.SROCode = PartyDetails.SROCode INNER
JOIN
SROMaster ON RegistrationMaster.SROCode =
SROMaster.SROCode INNER JOIN
PartyMaster ON PartyDetails.Party_Code =
PartyMaster.party_code INNER JOIN
ArticleMaster ON RegistrationMaster.Article_code =
ArticleMaster.article_code INNER JOIN
ConfigurationSettings ON RegistrationMaster.SROCode =
ConfigurationSettings.SROCode
WHERE RegistrationMaster.InternalDocumentNumber =
para_InternalDocumentNumber
AND RegistrationMaster.SROCode = para_SROCode --AND
PartyDetails.PhotoData IS NOT NULL
ORDER BY PhotoTime;
/*-------------------------------------------------------------------------------------------
ALTER TABLE tempPhotoDatatable ADD ROWID SERIAL;
DROP INDEX IF EXISTS tempPhotoDatatable_auto_increment_key;
ALTER SEQUENCE tempPhotoDatatable_ROWID_seq START WITH 1
INCREMENT BY 1;
-------------------------------------------------------------------------------------------*/
OPEN ref1 FOR
SELECT * FROM tempPhotoDatatable ORDER BY ROWID;
RETURN;
end spdastgoshwara2r;
======================================================================================================================
2nd eg.
CREATE OR REPLACE PROCEDURE spctspatrak(para_fromdate IN timestamp
with time zone, para_todate IN timestamp with time zone,
para_villagecode IN number, para_distcode IN number, para_srocode IN
number)
RETURNS SETOF refcursor AS
$BODY$
ref1 sys_refcursor;
tmp_date timestamp without time zone;
BEGIN
--DROP TABLE IF EXISTS temp1 ;
CREATE GLOBAL TEMPORARY TABLE temp1 AS
SELECT DISTINCT InternalDocumentNumber,srocode
FROM receiptdetails
WHERE InternalDocumentNumber = param_internaldocumentnumber AND
SROCode = param_srocode;
select into tmp_date towithoutTZ(param_receiptdate);
OPEN ref1 FOR
SELECT temp1.InternalDocumentNumber,
FeeRules.DescriptionD AS Description,
DocFeePaid.AmountPaid,
RegistrationMaster.NoOfPages
FROM
temp1,RegistrationMaster,FeeRules,DocFeePaid
where temp1.InternalDocumentNumber =
RegistrationMaster.InternalDocumentNumber AND
temp1.SROCode =
RegistrationMaster.SROCode and
temp1.InternalDocumentNumber =
DocFeePaid.InternalDocumentNumber AND
temp1.SROCode = DocFeePaid.SROCode and
DocFeePaid.FeeRuleCode =
FeeRules.FeeRuleCode
and (DocFeePaid.AmountPaid > 0.0) AND
(DocFeePaid.InternalDocumentNumber = param_internaldocumentnumber)
AND (DocFeePaid.SROCode = param_srocode
) AND
DocFeePaid.ReceiptDate =tmp_date
order by FeeRules.feerulecode;
RETURN;
END spctspatrak;
--
Shoeb Bojagar.
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
