Incidentally, I noticed that Shoeb's initial solution looks a lot like how
coding is done in SQL Server and Sybase. We first narrow down the result set
into dynamically created temp tables then query the result from the temp
tables once the data has been queried. For example, in pseudo T/SQL:
begin
select a.column_a, a.column_b, a.column_c
into #temp_table_1
from table_a a
where a.column_a = ..something...
select column_a, column_b, column_c from #temp_table_1
end
of course, this is a PL/SQL list and not a T/SQL list so this approach
probably isn't appropriate. What Shoeb's probably implying is this dynamic
temp table which I am not convinced is necessary.
The create table and alter table commands look like they can be handled by
implementing an analytical function. Shoeb's querying data, appending a
column then serializing the value in the appended column using a sequence
which the procedure starts from the value "1" every time. This is an ideal
candidate for an analytical function like row_number.
try:
select row_number() over ( partition by InternalDocumentNumber order by
PhotoTime ) as rowid,
column_a,
column_b,
column_c, etc.
from ...
where ...
order by PhotoTime
My next question is what will you be using to accept this data set? Another
stored procedure? An external process such as a Crystal Report or some other
application? This will decide the next step in how to package the above
query in the appropriate data structure for delivery.
Rich Pascual
On Tue, Feb 15, 2011 at 8:09 AM, Michael Moore <michaeljmo...@gmail.com>wrote:
> 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
>
--
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
