Solved

calling PL/SQL from SQL

Posted on 2001-07-17
10
1,472 Views
Last Modified: 2012-05-04
I have a package with one public function

Inside the package I have severeal DB-Manipulation (Inserting new Records)

Now I want to call this Function from outside, best from a SQL Query

When I try this like

Select MyPackage.MyFunction (Parameter1, Parameter2, Parameter3) FROM DUAL

I will get an error like

Function ImportDoc does not guarantee not to update DB


How can I avoid this? Read something about Restrict Pragmas. How to aply them? Is it even possible to call the function from SQL?

Thanx 4 answer

Choli
0
Comment
Question by:Choli100298
10 Comments
 

Expert Comment

by:Kirilloff
ID: 6288954
This function should not modify any tables. This function should not change any packaged variables. You have to declare your function in the package header and declare PRAGMA for it:

PRAGMA RESTRICT_REFERENCES(function, WNDS, WNPS);
0
 

Expert Comment

by:eyalshani
ID: 6288974
Hi Choli,

You can only use pl/sql functions from sql if you don't manipulate on the database (like insert rows). so, If your function add rows to a table, you can't use it in a sql query.

where are you trying to call this function from?
you can use pl/sql blocks (if its a shell script), or stored_procedure Class from your code (java, C++ etc').

Restirct Pragamas are code which you add to your stored procedure which check that things when compile (like in this case, that you  don't manipulate on the data). but if your stored procedure changes the data - they won;t help you.

  Eyal.



0
 
LVL 2

Expert Comment

by:racher
ID: 6288977
Yes, you can call a function from sql, but not if the function 'updates the database'

To be callable from SQL statements, a stored function must obey "purity" rules, which are meant to control side effects. One is when called from a SELECT statement or a parallelized INSERT, UPDATE, or DELETE statement, the function cannot modify any database tables.

The Subprograms Chapter in the PL/SQL Manual gives you the full details.


The PRAGMA RESTRICT you mentioned is to check for violations of the rules.

The pragma asserts that a function does not read and/or
write database tables and/or package variables. For example, the following pragma asserts that packaged function credit_ok writes no database state (WNDS) and
reads no package state (RNPS):
CREATE PACKAGE loans AS
...
FUNCTION credit_ok RETURN BOOLEAN;
PRAGMA RESTRICT_REFERENCES (credit_ok, WNDS, RNPS);
END loans;

Hope that helps

0
 

Author Comment

by:Choli100298
ID: 6288983
Ok, did it with all this pragmas (I've severeal functions inside, so I think I'm right that I have to declare them all in the header and set a Restrict_reference for them..)

But still I've some errors. I was able to figure them out by DBMS_SQL calls. Is there any possibillity to avoid this errors?

If not, what other ways do I have to call my StoredProcedures from Outside (e.g. another UNIX-Server)?

Choli
0
 

Author Comment

by:Choli100298
ID: 6289033
Ok, did it with all this pragmas (I've severeal functions inside, so I think I'm right that I have to declare them all in the header and set a Restrict_reference for them..)

But still I've some errors. I was able to figure them out by DBMS_SQL calls. Is there any possibillity to avoid this errors?

If not, what other ways do I have to call my StoredProcedures from Outside (e.g. another UNIX-Server)?

Choli
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:Choli100298
ID: 6289042
Ok, there where some delays, so my last comment was made before racher and eyalshany...

Means, the first part of the last comment is canceled ;-)

But there's still the question how to call this functions from outside...

Thanx for any hints!
0
 
LVL 2

Accepted Solution

by:
racher earned 100 total points
ID: 6289072
Does other UNIX server have sqlplus?

If that is the case write a script that executes the stored procedure :

execute dp_package_name.procedure_name

or if a function

DECLARE
  v_return_value  VARCHAR2(100);
BEGIN
  v_return_value := dp_package_name.function_name;
END;
/


0
 
LVL 3

Expert Comment

by:UsamaMunir
ID: 6289159
To be callable from SQL statements, a function must obey the following "purity"
rules, which are meant to control side effects:
n When called from a SELECT statement or a parallelized INSERT, UPDATE, or
DELETE statement, the function cannot modify any database tables.

1 When called from an INSERT, UPDATE, or DELETE statement, the function
cannot query or modify any database tables modified by that statement.

2 When called from a SELECT, INSERT, UPDATE, or DELETE statement, the
function cannot execute SQL transaction control statements (such as COMMIT),
session control statements (such as SET ROLE), or system control statements
(such as ALTER SYSTEM). Also, it cannot execute DDL statements (such as
CREATE) because they are followed by an automatic commit.
If any SQL statement inside the function body violates a rule, you get an error at
run time (when the statement is parsed).
You can use the pragma (compiler directive) RESTRICT_REFERENCES to check for
violations of the rules. The pragma asserts that a function does not read and/or
write database tables and/or package variables. For example, the following pragma
asserts that packaged function credit_ok writes no database state (WNDS) and
reads no package state (RNPS):
CREATE PACKAGE loans AS
...
FUNCTION credit_ok RETURN BOOLEAN;
PRAGMA RESTRICT_REFERENCES (credit_ok, WNDS, RNPS);
END loans;
Note: A static INSERT, UPDATE, or DELETE statement always violates WNDS. It also
violates RNDS (reads no database state) if it reads any columns. A dynamic INSERT,
UPDATE, or DELETE statement always violates WNDS and RNDS.


Using PRAGMA RESTRICT_REFERENCES

To assert the purity level, code the pragma RESTRICT_REFERENCES in the package
specification (not in the package body). The pragma must follow the function
declaration, but it does not need to follow it immediately. Only one pragma can
reference a given function declaration.
To code the pragma RESTRICT_REFERENCES, use the following syntax:
PRAGMA RESTRICT_REFERENCES (
Function_name, WNDS [, WNPS] [, RNDS] [, RNPS] [, TRUST] );
Where:
You can pass the arguments in any order. If any SQL statement inside the function
body violates a rule, then you get an error when the statement is parsed.
In the example below, the function compound neither reads nor writes database or
package state; therefore, you can assert the maximum purity level. Always assert
the highest purity level that a function allows. That way, the PL/SQL compiler
never rejects the function unnecessarily.
CREATE PACKAGE Finance AS -- package specification
FUNCTION Compound
(Years IN NUMBER,
Amount IN NUMBER,
Rate IN NUMBER) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (Compound, WNDS, WNPS, RNDS, RNPS);
END Finance;
CREATE PACKAGE BODY Finance AS --package body
FUNCTION Compound
(Years IN NUMBER,
Amount IN NUMBER,
Rate IN NUMBER) RETURN NUMBER IS
WNDS Writes no database state (does not modify database tables).
RNDS Reads no database state (does not query database tables).
WNPS Writes no package state (does not change the values of packaged
variables).
RNPS Reads no package state (does not reference the values of packaged
variables).
TRUST Allows easy calling from functions that do have RESTRICT_
REFERENCES declarations to those that do not.
Note: You may need to set up the following data structures for
certain examples to work:
CREATE TABLE Accts (
Yrs NUMBER
Amt NUMBER
Acctno NUMBER
Rte NUMBER);

BEGIN
RETURN Amount * POWER((Rate / 100) + 1, Years);
END Compound;
-- no pragma in package body
END Finance;
Later, you might call compound from a PL/SQL block, as follows:
DECLARE
Interest NUMBER;
Acct_id NUMBER;
BEGIN
SELECT Finance.Compound(Yrs, Amt, Rte) -- function call
INTO Interest
FROM Accounts
WHERE Acctno = Acct_id;
Using the Keyword TRUST The keyword TRUST in the RESTRICT_REFERENCES
syntax allows easy calling from functions that have RESTRICT_REFERENCES
declarations to those that do not. When TRUST is present, the restrictions listed in
the pragma are not actually enforced, but rather are simply trusted to be true.
When calling from a section of code that is using pragmas to one that is not, there
are two likely usage styles. One is to place a pragma on the routine to be called, for
example on a "call specification" for a Java method. Then, calls from PL/SQL to this
method will complain if the method is less restricted than the calling function. For
example:
CREATE OR REPLACE PACKAGE P1 IS
FUNCTION F1 (P1 NUMBER) RETURN NUMBER IS
LANGUAGE JAVA NAME ?CLASS1.METHODNAME(int) return int?;
PRAGMA RESTRICT_REFERENCES(F1,WNDS,TRUST);
FUNCTION F2 (P1 NUMBER) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES(F2,WNDS);
END;
CREATE OR REPLACE PACKAGE BODY P1 IS
FUNCTION F2 (P1 NUMBER) RETURN NUMBER IS
BEGIN
RETURN F1(P1);
END;
END;
Here, F2 can call F1, as F1 has been declared to be WNDS.
The other approach is to mark only the caller, which may then make a call to any
function without complaint. For example:
CREATE OR REPLACE PACKAGE P1a IS
FUNCTION F1 (P1 NUMBER) RETURN NUMBER IS
LANGUAGE JAVA NAME ?CLASS1.METHODNAME(int) return int?;
FUNCTION F2 (P1 NUMBER) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES(F2,WNDS,TRUST);
END;
CREATE OR REPLACE PACKAGE BODY P1a IS
FUNCTION F2 (P1 NUMBER) RETURN NUMBER IS
BEGIN
RETURN F1(P1);
END;
END;
Here, F2 can call F1 because while F2 is promised to be WNDS (because TRUST is
specified), the body of F2 is not actually examined to determine if it truly satisfies
the WNDS restriction. Because F2 is not examined, its call to F1 is allowed, even
though there is no PRAGMA RESTRICT_REFERENCES for F1.
0
 

Author Comment

by:Choli100298
ID: 6412403
TRUST is not working, and so racher was the first one with a satisfactory answer...

thanx anywayh

choli
0
 
LVL 2

Expert Comment

by:racher
ID: 6412667
Thanks

I should have mentioned that if you have a function A that calls a function B and you want to invoke function A from a select statement, then both function A and B should have the PRAGMA RESTRICT_REFERENCES statement

You don't say what version of Oracle you are using but if you can then I would use the new native dynamic sql EXECUTE IMMEDIATE over the older DBMS_SQL, it's so much easier to use for a start!

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

706 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

16 Experts available now in Live!

Get 1:1 Help Now