Oracle Error Function May Not Be Used In Sql
Contents |
10:05 am UTC Category: Developer � Version: 8.1.7 Whilst you are here, check out some content from the AskTom team: The first matching row Latest Followup You pls 00231 anonymous block Asked Tom Iam trying to use a function( which is part of
Oracle Call Function In Select
a package) from within my sql statement, and Iam getting the error pls-00231: function name may not be ora-00904 used in SQL But when I remove it from the package and create it invidudually , it is working fine. Can you kindly let me know as why pls-00231: function name may not be used in SQL occurs and how we should trouble shoot it. thank you and we said... Is the function in the specification. If not, it is not visible to sql. Consider: ops$tkyte@ORA717DEV.US.ORACLE.COM> create or replace package my_pkg 2 as 3 procedure p; 4 5 end; 6 / Package created. ops$tkyte@ORA717DEV.US.ORACLE.COM> ops$tkyte@ORA717DEV.US.ORACLE.COM> create or replace package body my_pkg 2 as 3 4 function f return number 5 is 6 begin 7 return 1; 8 end; 9 10 11 procedure p 12 is 13 begin 14 for x in ( select f from dual ) 15 loop 16 dbms_output.put_line( 'Yes' ); 17 end loop; 18 end; 19 20 end; 21 / Warning: Package Body created with compilation errors. ops$tkyte@ORA717DEV.US.ORACLE.COM> show errors Errors for PACKAGE BODY MY_PKG: LINE/COL ERROR -------- ----------------------------------------------------------------- 14/13 PL/SQL: SQL Statement ignored 14/20 PLS-00231: function 'F' may not be used in SQL ops$tkyte@ORA717DEV.US.ORACLE.COM> ops$tkyte@ORA717DEV.US.ORACLE.COM> create or replace package my_pkg 2 as 3 procedure p; 4 function f return number; 5 end; 6 / Package created. ops$tkyte@ORA717DEV.US.ORACLE.COM> ops$tkyte@ORA717DEV.US.ORACLE.COM> alter package my_pkg compile body; Package body altered. ops$tkyte@ORA717DEV.US.ORACLE.COM> ops$tkyte@ORA717DEV.US.ORACLE.COM> exec my_pkg.p Yes PL/SQL procedure successfully completed. Until the function is "exposed" the SQL engine cannot see it. Reviews Write a Review October 28, 2004 - 8:47 pm UTC Reviewer: Prasad February 11, 2005 - 8:33 am UTC Reviewer: Duke Ganote from Warsaw, Indiana USA (aka "Orthopaedic Capitol of the World" & "Lake City") Great tip!
function problem Discussion in 'SQL PL/SQL' started by Marco, Sep 24, 2012. Marco Active Member Messages: 9 Likes Received: 0 Trophy Points: 55 Hi everyone, please tell me what is wrong with this code: DECLARE empty_val varchar2(100); function test_fun (val_1 NUMBER, val_2 NUMBER) return number IS total number(5); BEGIN total := val_1 + val_2; return total; END test_fun; BEGIN select test_fun(3, 5) from dual; END; / I get this error: Error starting at line 1 https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1895113969657 in command: DECLARE empty_val varchar2(100); function test_fun (val_1 NUMBER, val_2 NUMBER) return number IS total number(5); BEGIN total := val_1 + val_2; return total; END test_fun; BEGIN select test_fun(3, 5) from dual; END; Error report: ORA-06550: line 12, column 10: PLS-00231: function 'TEST_FUN' may not be used in SQL ORA-06550: line 12, column 10: PL/SQL: ORA-00904: : invalid identifier http://www.club-oracle.com/threads/custom-function-problem.6572/ ORA-06550: line 12, column 3: PL/SQL: SQL Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action: I checked many tutorials but I can not find what is wrong. Thanks, Marco. Marco, Sep 24, 2012 #1 kiran.marla Forum Genius Messages: 403 Likes Received: 52 Trophy Points: 505 Location: Khammam Code (SQL): SQL> RUN 1 1 DECLARE 2 ABC NUMBER; 3 4 FUNCTION test_fun (val_1 NUMBER, val_2 NUMBER) RETURN NUMBER IS 5 total NUMBER(5); 6 BEGIN 7 total := val_1 + val_2; 8 RETURN total; 9 END test_fun; 10 11 BEGIN 12 ABC := test_fun(3, 5); 13 DBMS_OUTPUT.PUT_LINE('OUTPUT = '||ABC); 14* END; OUTPUT = 8 PL/SQL PROCEDURE successfully completed. SQL> kiran.marla, Sep 24, 2012 #2 Marco likes this. dariyoosh Forum Advisor Messages: 118 Likes Received: 19 Trophy Points: 260 In addtion here is the description of the error according to oracle http://docs.oracle.com/cd/E11882_01/server.112/e17766/pcmus.htm#sthref18172 oracle online documentation said: PLS-00231: function 'string' may not be used in SQL Cause: A
GoogleВойтиСкрытые поляПоиск групп или сообщений
SQL Column/Function Name Precedence Realities: Calling PL/SQL Functions in SQL Examples of Embedded PL/SQL PL/SQL is a procedural language extension to SQL, so you can also issue native calls to SQL statements such as SELECT, INSERT, and UPDATE from within your PL/SQL programs. Until Release 2.1 of PL/SQL (which comes with Oracle7 Release 7.1 of the RDBMS), however, you weren't able to place your own PL/SQL functions inside a SQL statement. NOTE: The capabilities described in this chapter are available only in PL/SQL Release 2.1 and above. 17.1 Looking at the Problem The restriction on putting PL/SQL functions inside an SQL statement often resulted in cumbersome SQL statements and redundant implementation of business rules. Suppose, for example, you need to calculate and use an employee's total compensation both in native SQL and also in your forms. The computation itself is straightforward enough: Total compensation = salary + bonus My SQL statement would include this formula: SELECT employee_name, salary + NVL (bonus, 0) FROM employee; while my Post-Query trigger in my Oracle Forms application would employ the following PL/SQL code: :employee.total_comp := :employee.salary + NVL (:employee.bonus, 0); In this case, the calculation is very simple, but the fact remains that if you need to change the total compensation formula for any reason (different kinds of bonuses, for example), you would then have to change all of these hardcoded calculations both in the SQL statements and in the front end application components. A far better approach is to create a function that returns the total compensation: FUNCTION total_comp (salary_in IN employee.salary%TYPE, bonus_in IN employee.bonus%TYPE) RETURN NUMBER IS BEGIN RETURN salary_in + NVL (bonus_in, 0); END; Then I could replace the formulas in my code as follows: SELECT employee_name, total_comp (salary, bonus) FROM employee; :employee.total_comp