This content has been marked as final. Show 9 replies
It looks like you want a FUNCTION, not a PROCEDURE.
A procedure does not return a value. The RETURN statement in a procedure just means "stop executing".
A function does return a value. The RETURN statement in a function means "stop executing *and pass back the following value* ".
Since a procedure can't pass back a value, it doesn't make any sense to have a value in the RETURN statement of a procedure, and so the compiler doesn't allow it.
Also, what is the datatype of open_time?
If open_time is a DATE, then don't compare it to a string, such as
Compare DATEs to other DATEs. The value returned by TO_DATE is a DATE, so it makes sense to say
If open_time is not a DATE (or TIMESTAMP) then you have a much more serious problem.
open_time > TO_DATE ( '07/01/2011' , 'MM/DD/YYYY' )
This has nothing to do with the ORA-06650 error, however.
It looks like you want a function, not a procedure.
bostonmacosx wrote:That blog allows you to post questions. Wouldn't that be a better place to ask this question, or perhaps the Apex forum?
Sorry new to the PL of SQL butI want this to be run in a chart block in APEX.
Oracle Application Express (APEX)
If you do decide to post the question elsewhere, then mark this thread as "Answered" first.
CREATE OR REPLACE FUNCTION test RETURN VARCHAR2 is l_var VARCHAR2(32767); BEGIN l_var := 'SELECT NULL LINK,to_char(open_time,"YYYY-Q") DATIME,'; for r1 in ( SELECT DISTINCT DECODE(REPAIR_TYPE,'DEPARTMENT PURCHASE','DEPARTMENT','USER OWNED','USER OWNED','BCCR','BCCR','','NO ENTRY') '-' DECODE(HARDWARE_WARRANTY_STATUS,'YES','WARRANTY','NO','NON-WARRANTY','UNKNOWN','UNKNOWN','','N/A') as STATE from TABLE where (ASSIGNMENT = 'HARDWARE REPAIR' or ASSIGNMENT = 'CONTRACTED HARDWARE REPAIR') and (HW_HARDWARE_TYPE_BC='LAPTOP' OR HW_HARDWARE_TYPE_BC='DESKTOP') and open_time>'7/1/2011' ) LOOP l_var := l_var || 'sum(decode(state,''' || r1.state || ''',"1",0)) ' || r1.state || ','; END LOOP; l_var := rtrim(l_var, ','); l_var := l_var || ' FROM SMINCREQ GROUP BY to_char(open_time,"YYYY-Q") order by to_char(open_time,"YYYY-Q")'; RETURN l_var; END; Function Created. Then execute select test from dual;
When I mention that procedure does not has a return statement i mean a return value.
the return metioned in procedure is a control statement which orders the pl/sql engine to take the control back to calling program. The procedure itself does not returns the value the value '20' displayed is ecause of dbms_output.put_line and the value stored in 'c'; at that point is continued to be value of c.