Home » SQL & PL/SQL » SQL & PL/SQL » convert the given sql to procedure
convert the given sql to procedure [message #572429] Wed, 12 December 2012 01:20 Go to next message
mvrkr44
Messages: 54
Registered: December 2012
Member
i have the below query
select to_char(report_date, 'YYYY MM Mon'), count(1) no_of_times
from (
select to_date('&&YYYYMMDD', 'YYYYMMDD')+rownum report_date
, mod(rownum,14) mod_result
from all_objects
where
rownum <= 365)
where mod_result=0
group by to_char(report_date, 'YYYY MM Mon')
order by to_char(report_date, 'YYYY MM Mon');

need to convert as procedure based on input date parameter.

I will pass the input date from java environment and need to see the sql query output in front end.
Re: convert the given sql to procedure [message #572441 is a reply to message #572429] Wed, 12 December 2012 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 59109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Why do you want to convert this in a procedure?
Just execute it in Java and loop on the result set to output it in front end.

Regards
Michel
Re: convert the given sql to procedure [message #572443 is a reply to message #572429] Wed, 12 December 2012 01:53 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Hope this helps!


CREATE OR REPLACE
PROCEDURE p1(
    l_date IN VARCHAR2)
IS
  l_num      NUMBER;
  v_rpt_date CHAR(21);
type rc
IS
  ref
  CURSOR;
    l_rc rc;
  BEGIN
    OPEN l_rc FOR 'select to_char(report_date, ''YYYY MM Mon''), count(1) as no_of_times
from (
select to_date(:1, ''YYYYMMDD'')+rownum report_date
, mod(rownum,14) mod_result
from all_objects
where 
rownum <= 365)
where mod_result=0
group by to_char(report_date, ''YYYY MM Mon'')
order by to_char(report_date, ''YYYY MM Mon'')' USING l_date ;
    LOOP
      FETCH l_rc INTO v_rpt_date,l_num;
    dbms_output.put_line('date='||v_rpt_date||' -- count='||l_num);
    EXIT
  WHEN l_rc%notfound;
  END LOOP;
END;
/

[Updated on: Wed, 12 December 2012 01:55]

Report message to a moderator

Re: convert the given sql to procedure [message #572447 is a reply to message #572443] Wed, 12 December 2012 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I hope you do not write your code like this otherwise I feel sorry for those that will maintain it.

Regards
Michel
Re: convert the given sql to procedure [message #572450 is a reply to message #572443] Wed, 12 December 2012 02:50 Go to previous messageGo to next message
mvrkr44
Messages: 54
Registered: December 2012
Member
Thanks
Re: convert the given sql to procedure [message #572452 is a reply to message #572447] Wed, 12 December 2012 03:05 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Hi Michel

Please let me know what you didn't like in the code so that i would keep that in mind while coding in future

FYI i used to be DBA but u made me Sad

thx
Rishwinger

[Updated on: Wed, 12 December 2012 03:10]

Report message to a moderator

Re: convert the given sql to procedure [message #572458 is a reply to message #572452] Wed, 12 December 2012 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 59109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you think that:
    OPEN l_rc FOR 'select to_char(report_date, ''YYYY MM Mon''), count(1) as no_of_times
from (
select to_date(:1, ''YYYYMMDD'')+rownum report_date
, mod(rownum,14) mod_result
from all_objects
where 
rownum <= 365)
where mod_result=0
group by to_char(report_date, ''YYYY MM Mon'')
order by to_char(report_date, ''YYYY MM Mon'')' USING l_date ;

is something readable (OK you will say that this OP's query but you could format anyway).

Regards
Michel
Re: convert the given sql to procedure [message #572461 is a reply to message #572458] Wed, 12 December 2012 04:10 Go to previous message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Michel,

I was concentrating on creating procedure rather than on the query, recently i learnt about ref cursor ,just trying to get my hands dirty with ref cursor.
Thanks neways for the tip, i'll keep that in mind.

Smile


Thx
Rishwinger
Previous Topic: Need Function/sql query
Next Topic: Calculate Materialized view Refresh time
Goto Forum:
  


Current Time: Wed Sep 17 09:02:50 CDT 2014

Total time taken to generate the page: 0.28373 seconds