Solved

Calling SP from Oracle View

Posted on 2007-11-20
11
697 Views
Last Modified: 2013-12-07
Can you call a SP from within a View select statement?
if so, what is the command?



CREATE OR REPLACE VIEW INSTR_LIST

(SDMTREPORTID, LOBJCLAID, LOBJID, TAG_NO, DESCRIPTION, 

 STATUS, INSTTYPE)

AS 

SELECT a.sdmtreportid AS sdmtreportid, a.lobjclaid, a.lobjid,

       TRIM (a.stag) AS tag_no,

          TRIM (stitle),

       (CALL TO STORED PROCEDURE THAT RETURNS A STRING) AS insttype

  FROM fcrobj a

 WHERE TRIM (a.sclassname) LIKE '%Instrument%' AND sobjtype = 'Object'

/

Open in new window

0
Comment
Question by:obrienj
  • 5
  • 5
11 Comments
 
LVL 18

Accepted Solution

by:
Jinesh Kamdar earned 500 total points
ID: 20320103
Yes, you can call a function from within the view query. Replace package, function_name and parameters below.
CREATE OR REPLACE VIEW INSTR_LIST

(SDMTREPORTID, LOBJCLAID, LOBJID, TAG_NO, DESCRIPTION, 

 STATUS, INSTTYPE)

AS 

SELECT a.sdmtreportid AS sdmtreportid, a.lobjclaid, a.lobjid,

       TRIM (a.stag) AS tag_no,

          TRIM (stitle),

       package.function_name(parameters) AS insttype

  FROM fcrobj a

 WHERE TRIM (a.sclassname) LIKE '%Instrument%' AND sobjtype = 'Object'

/

Open in new window

0
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20320110
No we can call SP in Inline select query statement. For this you will have to create a function.
0
 

Author Comment

by:obrienj
ID: 20321631
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'

Open in new window

0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20321670
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.
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20321705
In short, Remove all DML statements from your function if you want to use it in a SQL expression.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:obrienj
ID: 20321933
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.
0
 

Author Comment

by:obrienj
ID: 20321948
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;

/

Open in new window

0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20321977
>> 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.
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20322051
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.
0
 

Author Comment

by:obrienj
ID: 20322167
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.
0
 

Author Closing Comment

by:obrienj
ID: 31410126
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now