Hi Shoeb,
One problem is, that you are attempting to both describe your problem as
well as the solution. For example, you are saying
Here is my problem ...
Here is my proposed solution ...
How can I make my proposed solution work ...
I am guessing that your proposed solution is based on how you think Oracle
should work, and not how it actually works. For example, I don't see why you
think you need temporary tables. You probably don't, but I can't say for
sure because I still don't understand the problem you are trying to solve.
Can you state your problem something like this:
I am trying to provide a result set to another procedure. The source of the
data is the following tables bla bla bla ... The join criteria for these
tables is bla bla bla. I need to return the following columns ...
Column XXX is derived from this calculation ...
You get the idea?
Regards,
Mike
On Tue, Feb 15, 2011 at 12:56 AM, Shoeb Bojagar <shoeb.boja...@gmail.com>wrote:
> 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 Oracle-PLSQL@googlegroups.com
> To unsubscribe from this group, send email to
> oracle-plsql-unsubscr...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
