Calling SP from Oracle View

No we can call SP in Inline select query statement. For this you will have to create a function.

thanks for the input there, I have no problem changing it to a function.  I am still having some problems though:
Attached is the code.  It compiles fine.  And the function executes okay on it's own.  But when I call it from here I get the following error message:

ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "AVENIO.GETTARGET", line 19

Any ideas what this means?  Is this a completely different problem with my code, or am I missing something?

SELECT a.sdmtreportid AS sdmtreportid, a.lobjclaid, a.lobjid,
       TRIM (a.stag) AS tag_no,
          TRIM (stitle)
       || ' '
       || (SELECT slabel
             FROM fobj
            WHERE lobjid =
                     (SELECT ltarid
                        FROM ftar
                       WHERE lobjid =
                                (SELECT lparentid
                                   FROM fshadow
                                  WHERE lobjid = a.lobjid
                                    AND lobjclaid = a.lobjclaid)
                         AND lobjclaid = a.lobjclaid
                         AND lcriclaid = 101
                         AND lcriid = 133)
              AND lobjclaid = 10005) AS description,
     	getTarget(0,0,a.lObjClaId,a.lObjId,101,133) AS Status,
       (SELECT slabel
          FROM fobj
         WHERE lobjid =
                  (SELECT ltarid
                     FROM ftar
                    WHERE lobjid =
                             (SELECT lparentid
                                FROM fshadow
                               WHERE lobjid = a.lobjid
                                 AND lobjclaid = a.lobjclaid)
                      AND lobjclaid = a.lobjclaid
                      AND lcriclaid = 101
                      AND lcriid = 135)
           AND lobjclaid = 10005) AS insttype
  FROM fcrobj a
 WHERE TRIM (a.sclassname) LIKE '%Instrument%' AND sobjtype = 'Object'

Select allOpen in new window

Restrictions on Calling Functions from SQL Expressions

To be callable from SQL expressions, a user-defined function must:
• Be a stored function
• Accept only IN parameters
• Accept only valid SQL data types, not PL/SQL specific types, as parameters
• Return data types that are valid SQL data types, not PL/SQL specific types
• Functions called from SQL expressions cannot contain DML statements.
• Functions called from UPDATE/DELETE statements on a table T cannot contain DML on the same table
T.
• Functions called from an UPDATE or a DELETE statement on a table T cannot query the same table.
• Functions called from SQL statements cannot contain statements that end the transactions.
• Calls to subprograms that break the previous restriction are not allowed in the function.

In short, Remove all DML statements from your function if you want to use it in a SQL expression.

What?? Are you saying I can't have any DML statements - (i.e. any select/insert/update etc statements) - within my function if I want to call it from within another sql expression?  
Sure that pretty much renders the ability useless.
Oh, this hurts.

The following is the function i am calling:

CREATE OR REPLACE FUNCTION "GETTARGET"                           (
--  sGetTarget   OUT CHAR,
  lClaID       IN NUMBER,
  lID          IN NUMBER,    
  lShaClaID	   IN NUMBER,
  lShaID	   IN NUMBER,
  lCharClaID   IN NUMBER,
  lCharID	   IN NUMBER)   
 
  return char
AS
BEGIN 
DECLARE
	sGetTarget				 Char(500);
	lParentClaID			 NUMBER;
	lParentID				 NUMBER;
BEGIN	
 
/* Method to load adn return current target for characteristic and root item specified. Note, instance ids may also be involved.*/			
	    
    DELETE FROM STEMPPROPERTIES;  	
    DELETE FROM sHie;                 	    	
    DELETE FROM SPROPERTIES;    	
	IF lClaId = 0 THEN
	   SELECT MAX(lParentClaID) INTO lParentClaID FROM FSHADOW WHERE lobjid = lShaId AND lobjclaid = lShaClaID;
	END IF; 	
	
	INSERT INTO sHie (lObj1ClaID, lObj1ID, lObj2ClaID, lObj2ID)	
		VALUES (0, 0, lClaID, lID);	
		
    INSERT INTO sHie (lObj1ClaID, lObj1ID, lSeqNo, lObj2ClaID, lObj2ID)
        SELECT lObj1ClaID, lObj1ID, lSeqNo, lObj2ClaID, lObj2ID
        FROM fHIE 
        WHERE lObj1ClaID = lClaID
        	AND lObj1ID = lID
        	AND lObj2ClaID = equates.eOCLS;        
        	
	--If current item is an instance, load parent characteristics.    				 
	IF lShaClaID != 0 AND lShaID != 0 THEN	
			
		INSERT INTO sHie (lObj1ClaID, lObj1ID, lObj2ClaID, lObj2ID)	
			VALUES (0, 0, lShaClaID, lShaID);		
    
    	INSERT INTO sHie (lObj1ClaID, lObj1ID, lSeqNo, lObj2ClaID, lObj2ID)
        	SELECT lObj1ClaID, lObj1ID, lSeqNo, lObj2ClaID, lObj2ID
        	
        	FROM fHIE 
        	WHERE lObj1ClaID = lShaClaID
        		AND lObj1ID = lShaID
        		AND lObj2ClaID = equates.eOCLS;        
    END IF;	 
    
    INSERT INTO sHie (lObj1ClaID, lObj1ID, lObj2ClaID, lObj2ID)	
			VALUES (0, 0, lCharClaID, lCharID);	       	
        	
    --Load Targets   
    INSERT INTO STEMPPROPERTIES (lObjClaID, lObjID, lObj1ClaID, lObj1ID, lObj2ClaID, lObj2ID, sTarget, bStatus)
        SELECT 
        	fTAR.lObjClaID, 
        	fTAR.lObjID, 
        	sHie.lObj1ClaID, 
        	sHie.lObj1ID, 
        	sHie.lObj2ClaID, 
        	sHie.lObj2ID, 
        	CASE WHEN fTAR.lTarClaID = 0 THEN
        		   	fTAR.sTarget 
        	ELSE
        		   	fOBJ.sLabel
        	END,		        		   	
        	1
        FROM sHie        	
        	left outer join fTAR on sHie.lObj2ClaID = fTAR.lCriClaID         		
            	AND sHie.lObj2ID = fTAR.lCriID
            	AND (EXISTS (SELECT NULL 
            				FROM sHie c 
            				WHERE (c.lObj2ClaID = fTAR.lObjClaID 
            			 			AND c.lObj2ID = fTAR.lObjID)) 
            			 		OR fTAR.lObjClaID = fTAR.lCriClaID)
         	left outer join fOBJ on fTAR.lTarClaID = fOBJ.lObjClaID
         		AND fTAR.lTarID = fOBJ.lObjID;          			 		   
        				        				   
	SELECT MAX(sTarget) INTO sGetTarget
	FROM
	(
		--Check local
		SELECT a.sTarget
		FROM STEMPPROPERTIES a
		WHERE (lObjClaID = lClaID 
			OR lObjClaID = lShaClaID)
		UNION
		--Check parent class
		SELECT a.sTarget
		FROM STEMPPROPERTIES a
		INNER JOIN sHie 
		ON (sHie.lObj2ClaID = lObjClaID
    				AND sHie.lObj2ID = lObjID)
				OR (sHie.lObj2ClaID = lShaClaID
    				AND sHie.lObj2ID = lShaID)    					
		WHERE lObjClaID = 106 
			AND NOT EXISTS(SELECT NULL 
							FROM STEMPPROPERTIES b 
							WHERE (b.lObjClaID = lClaID 
						   				OR b.lObjClaID = lShaClaID)
						   			AND b.lObj2ClaID = lCharClaID 
						   			AND a.lObj2ID = b.lObj2ID
                                	AND b.bStatus = a.bStatus)   
		UNION
		--Check sub class
		SELECT a.sTarget
		FROM STEMPPROPERTIES a
		INNER JOIN sHie 
		ON (sHie.lObj2ClaID = lObjClaID
    			AND sHie.lObj2ID = lObjID)
				OR (sHie.lObj2ClaID = lShaClaID
    			AND sHie.lObj2ID = lShaID)    					
		WHERE lObjClaID = 106 
			AND NOT EXISTS(SELECT NULL 
						FROM STEMPPROPERTIES  b 
						WHERE (b.lObjClaID = lClaID 
						   		OR b.lObjClaID = lShaClaID)
						   	AND b.lObj2ClaID = 101 
						   	AND a.lObj2ID = b.lObj2ID
						   	AND b.bStatus = a.bStatus)                                     	
			AND shie.lSeqNo IN (SELECT 
						MAX(lSeqNo) 
						FROM FHIE 
						WHERE fHIE.lObj1ClaID = sHie.lObj1ClaID 
						   		AND fHIE.lObj1ID = sHie.lObj1ID AND fHIE.lObj2ClaID = 106)
		UNION
		--Check attribute
		SELECT a.sTarget
		FROM STEMPPROPERTIES a 
		WHERE lObjClaID = 101 
			AND NOT EXISTS(SELECT NULL 
						FROM STEMPPROPERTIES  b 
						WHERE ((lObjClaID = lClaID 
								OR lObjClaID = lShaClaID 
								OR lObjClaID = 106) 
						   	AND b.bStatus = a.bStatus) 
			AND a.lObj2ID = b.lObj2ID)
		UNION
		--Load empty records.
		SELECT a.sTarget
		FROM STEMPPROPERTIES a 
		WHERE NOT EXISTS(SELECT NULL 
						FROM STEMPPROPERTIES  b 
						WHERE ((b.lObjClaID = lClaID 
									OR b.lObjClaID = lShaClaID)
								OR b.lObjClaID = 106
								OR b.lObjClaID = 101) 
							AND b.bStatus = a.bStatus)    					
                                    
	) TAB;	
	RETURN sGetTarget;
END;
END;
/

Select allOpen in new window

>> Are you saying I can't have any DML statements - (i.e. any select/insert/update etc statements) -
I said just DML statements - SELECT is *NOT* a DML statement and hence can be used within your function. DML statements can also be used in functions but such functions cannot be called within SQL expressions.

What is it that your function is trying to achieve within the function that so very badly requires a DML before it can be used within a SELECT? Post your function code and we might be able to suggest a work-around for it.

This is a very unwise approach. Functions to be used in SQL expressions should not be treated as functions that are associated with Stored Procedures. Foll. scenarios should be considered while designing functions to be used within SQL expressions.

Invoking User-Defined Functions from SQL Expressions

SQL expressions can reference PL/SQL user-defined functions. Anywhere a built-in SQL function
can be placed, a user-defined function can be placed as well.

Advantages :
• Permits calculations that are too complex, awkward, or unavailable with SQL
• Increases data independence by processing complex data analysis within the Oracle server, rather than by retrieving the data into an application
• Increases efficiency of queries by performing functions in the query rather than in the application
• Manipulates new types of data (for example, latitude and longitude) by encoding character strings and using functions to operate on the strings

If it were allowed, the DMLs would get executed everytime u fire a SELECT on the view which is, I believe, not what you want. I would suggest you to create a separate SP that would do all the DMLs and limit this function to just retrieving the return value using a SELECT. Then you can call the SP and then try to SELECT from that view. That way both your requirements are accomplished.

I am using the Views etc in a pretty unusual way I guess.  These views are for a particular Crystal Report I am running, it is not something used constantly.  The Function above is to get a particular value within the database.  It's a complex search and there was no way to complete it via a select query (I can not stand over this fact, as i didn't create it, and I am not an oracle guru)
I did believe it would get executed for every row every time I do a select from the view, that is what I did expect, and wanted - As the rows within the view will be different every single time I do a select.
Perhaps I am missing something, and there is a much easier way to achieve this... Ill look into it.

Thanks for your help.  I re-wrote the function to get rid of any DML statements within it.  I can now call it from within my View, and it works perfectly.  Thanks,
j