Home » SQL & PL/SQL » SQL & PL/SQL » compilation problems with function (oracle 10g)
compilation problems with function [message #424661] Mon, 05 October 2009 03:59 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Hi,
My function is like this and i am getting compilation errors in it.
create or replace
FUNCTION medExtras RETURN VARCHAR2 AS
	V_MedList VARCHAR2(4000);
	V_MEDICATIONS_ID NUMBER(20, 0);
	V_MEDICATION VARCHAR2(500);
        CURSOR Med_Duplicates IS SELECT DISTINCT MEDICATION FROM EMRCimsMedicationsLkup WHERE PACKING_AVL = 3;
BEGIN
	V_MedList := '';
	 OPEN Med_Duplicates;
	   LOOP
	     FETCH  Med_Duplicates INTO V_MEDICATION;
	       EXIT WHEN Med_Duplicates%NOTFOUND;
		 V_MEDICATIONS_ID := ('SELECT  CML.MEDICATIONS_ID FROM EMRCimsMedicationsLkup CML 
				    INNER JOIN EMRCimsProductDetails CPD ON CPD.CIMS_REF_ID = CML.CIMS_REF_ID
				    WHERE CML.PACKING_AVL = 3 AND MEDICATION = V_MEDICATION
				    ORDER BY MEDICATION ASC, 
				    TO_CHAR(NVL(SUBSTR(CPD.PRODUCT_CODE, 0, INSTR( CPD.PRODUCT_CODE,'~', 0)), 0) AS NUMBER) DESC, 
				    TO_CHAR(NVL(SUBSTR(CPD.PRODUCT_CODE, (INSTR( CPD.PRODUCT_CODE,'~', 0)+1), LENGTH(CPD.PRODUCT_CODE)),0) AS NUMBER) DESC');
	         BEGIN
		      V_MedList = V_MedList || TO_CHAR(V_MEDICATIONS_ID AS VARCHAR) || ',';
		        FETCH Med_Duplicates INTO V_MEDICATION;
		         V_MEDICATIONS_ID := ('SELECT  CML.MEDICATIONS_ID
				    FROM EMRCimsMedicationsLkup CML 
				    INNER JOIN EMRCimsProductDetails CPD ON CPD.CIMS_REF_ID = CML.CIMS_REF_ID
				    WHERE CML.PACKING_AVL = 3 AND MEDICATION = V_MEDICATION
				    ORDER BY MEDICATION ASC, 
				    TO_CHAR(NVL(SUBSTR(CPD.PRODUCT_CODE, 0, INSTR( CPD.PRODUCT_CODE,'~', 0)), 0) AS NUMERIC) DESC, 
				    TO_CHAR(NVL(SUBSTR(CPD.PRODUCT_CODE, (INSTR( CPD.PRODUCT_CODE,'~', 0)+1), LENGTH(CPD.PRODUCT_CODE)),0) AS NUMBER) DESC');    
	         END;
	   END LOOP;
	CLOSE Med_Duplicates;
	V_MedList := V_MedList || '0';
	RETURN V_MedList;               
END;
/


exception is

Error(17,74): PLS-00103: Encountered the symbol "~" when expecting one of the following: ) , * & | = - + < / > at in is mod remainder not rem => .. <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
Re: compilation problems with function [message #424662 is a reply to message #424661] Mon, 05 October 2009 04:01 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session.
Split your lines to not exceed 80 characters.

Regards
Michel
Re: compilation problems with function [message #424667 is a reply to message #424661] Mon, 05 October 2009 04:15 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Whenever you want to put a quote in a dynamic sql string you have to use two quotes to represent the single onw you want.
So:
INSTR( CPD.PRODUCT_CODE,'~', 0)+1
becomes:
INSTR( CPD.PRODUCT_CODE,''~'', 0)+1


However there is nothing in this function that requires the sql to be dynamic so I strongly suggest you stop trying to make it dynamic.
Re: compilation problems with function [message #424668 is a reply to message #424661] Mon, 05 October 2009 04:15 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I don't know TO_CHAR function with syntax
TO_CHAR(...AS NUMBER).
...
TO_CHAR(NVL(SUBSTR(CPD.PRODUCT_CODE, 0, INSTR( CPD.PRODUCT_CODE,'~', 0)), 0) AS NUMBER) DESC, 
...


regards,
Delna
Re: compilation problems with function [message #424670 is a reply to message #424668] Mon, 05 October 2009 04:19 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
how can i rewrite that particular one then
Re: compilation problems with function [message #424671 is a reply to message #424661] Mon, 05 October 2009 04:20 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
That'll be the next error the OP gets after fixing the quotes. Suspect it should be CAST. Or just to_number
Re: compilation problems with function [message #424672 is a reply to message #424661] Mon, 05 October 2009 04:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It looks like you're trying to build a dynamic SQL statment that includes ' characters.

To get a ' inside a string, you need to replace it with '' (two single quotes) otherwise the compiler simply interprets it as the end of the string.That's what's happening here the compiler reads
V_MEDICATIONS_ID := ('SELECT  CML.MEDICATIONS_ID FROM EMRCimsMedicationsLkup CML 
                      INNER JOIN EMRCimsProductDetails CPD ON CPD.CIMS_REF_ID = CML.CIMS_REF_ID
                      WHERE CML.PACKING_AVL = 3 AND MEDICATION = V_MEDICATION
                      ORDER BY MEDICATION ASC, 
                      TO_CHAR(NVL(SUBSTR(CPD.PRODUCT_CODE, 0, INSTR( CPD.PRODUCT_CODE,'~', 0)), 0) AS NUMBER) DESC, 
                      TO_CHAR(NVL(SUBSTR(CPD.PRODUCT_CODE, (INSTR( CPD.PRODUCT_CODE,'~', 0)+1), LENGTH(CPD.PRODUCT_CODE)),0) AS NUMBER) DESC');
it gets to the first ' after the start of the string, and attempts to stop treating things as a string.

Once you've fixed that, you'll notice that you're trying to put this string into a variable defined as NUMBER(20,0) which will cause a few problems.

Then you'll probably notice that you're not doing anything with the second query that you're building (the one inside the BEGIN/END block.

Then you'll hopefully take @Cookiemonsters advice and rewrite the whole thing without bothering with dynamic SQL at all.
Re: compilation problems with function [message #424675 is a reply to message #424672] Mon, 05 October 2009 04:39 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
JRowbottom wrote on Mon, 05 October 2009 10:27

Then you'll probably notice that you're not doing anything with the second query that you're building (the one inside the BEGIN/END block.

Which is possibly just as well since that query is identical to the other one. However, since there is a fetch in that block it means that every other value from the cursor is being skipped.
Re: compilation problems with function [message #424676 is a reply to message #424672] Mon, 05 October 2009 04:40 Go to previous message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
yes,it's working when i replaced '~' as "~"
Previous Topic: How to decrease time of inserting large data
Next Topic: Max function with line item details
Goto Forum:
  


Current Time: Fri Sep 30 10:40:39 CDT 2016

Total time taken to generate the page: 0.05165 seconds