Link to home
Create AccountLog in
Avatar of finance_teacher
finance_teacher

asked on

Oracle 10g Function -- Small Fix ?

Please post updated code that fixes the below FUNCTION.

Basically I have pass the Global_Output_Type parameter into this function and want to return 'EMAIL THIS' if the Global_Output_Type parameter is like '.%' or = 'QC CODE'
---------------------------------------------------------------------------------------
FUNCTION get_doc_text_OutputType (Global_Output_Type IN VARCHAR2) RETURN VARCHAR2
Is
   Local_OutputType             DOCUMENT_TEXT_TAB.OUTPUT_TYPE%Type;
   cursor GET_OUTPUTTYPE is
      if GLOBAL_OUTPUT_TYPE like '.%'
      or GLOBAL_OUTPUT_TYPE = 'QC CODE'
          then Local_OutputType = 'EMAIL THIS'
      ;
Begin
   OPEN  Get_OutputType;
   FETCH Get_OutputType INTO Local_OutputType;
   if GET_OUTPUTTYPE%NOTFOUND then
      Local_OutputType := 'NO_OUTPUT_TYPE';
   END IF;
   CLOSE Get_OutputType;
   RETURN Local_OutputType;
END get_doc_text_OutputType;
Avatar of anumoses
anumoses
Flag of United States of America image

Basically I have pass the Global_Output_Type parameter into this function and want to return 'EMAIL THIS' if the Global_Output_Type parameter is like '.%' or = 'QC CODE'
---------------------------------------------------------------------------------------
FUNCTION get_doc_text_OutputType (Global_Output_Type IN VARCHAR2) RETURN VARCHAR2
Is
   Local_OutputType             DOCUMENT_TEXT_TAB.OUTPUT_TYPE%Type;
   cursor GET_OUTPUTTYPE is
      if GLOBAL_OUTPUT_TYPE in( '.%' , 'QC CODE')
          then Local_OutputType = 'EMAIL THIS'
      ;
Begin
   OPEN  Get_OutputType;
   FETCH Get_OutputType INTO Local_OutputType;
   if GET_OUTPUTTYPE%NOTFOUND then
      Local_OutputType := 'NO_OUTPUT_TYPE';
   END IF;
   CLOSE Get_OutputType;
   RETURN Local_OutputType;
END get_doc_text_OutputType;
Your concept is worng

When you declare cursor,
you have to select from an object which is missing

Then you opne and fetch. Then comes the if statement
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

You cannot have an IF in the cursor like that.

A cursor is to hold the output from a select.
Just move the IF statement after the begin.

Forget the fetch.

On mobile right now so I can't provide complete code.

Are you wanting the .% anywhere in the input string?  If so, use INSTR if not SUBSTR. The other check is straight forward.
FUNCTION get_doc_text_OutputType (Global_Output_Type IN VARCHAR2) RETURN VARCHAR2
Is
   Local_OutputType             DOCUMENT_TEXT_TAB.OUTPUT_TYPE%Type;
   cursor GET_OUTPUTTYPE is
     select global_output_type
       from  your_table;
Begin
   OPEN  Get_OutputType;
   FETCH Get_OutputType INTO Local_OutputType;
   if GET_OUTPUTTYPE%NOTFOUND then
      Local_OutputType := 'NO_OUTPUT_TYPE';
   Elsif GLOBAL_OUTPUT_TYPE in( '.%' , 'QC CODE')
          then Local_OutputType = 'EMAIL THIS';
   END IF;
   CLOSE Get_OutputType;
   RETURN Local_OutputType;
END get_doc_text_OutputType;
If the above posts don't help you out can you please provide more information?

Sample data and expected results would help a lot.
A cursor is used to walk through many records, but your code is only calling the fetch once, of course as you are creating a function, so you are only selecting one item.  If that is what you really wish to do, why are you declaring a cursor at all?  
Avatar of finance_teacher

ASKER

Ok, I will test and let everyone know.
ASKER CERTIFIED SOLUTION
Avatar of pcscooter66
pcscooter66

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Ok, I will retest.