When creating a stored procedure in oracle, it appears to execute, but it does nothing. The stored procedure never gets saved. Why?
stored procedure in oracle example with in out parameter
oracle execute stored procedure with parameters
create stored procedure in oracle for select statement
get execution time of stored procedure oracle
oracle stored procedure tutorial for beginners
execute stored procedure with multiple parameters oracle
oracle stored procedure example
I'm trying to create a stored procedure in Oracle SQL using
TOAD, but it's not doing anything. There's no error, no message, no nothing, when attempting to create it. It just seems to have gone through, but it doesn't.
The query looks like this:
CREATE OR REPLACE PROCEDURE PottyUseRange (formatty varchar2, start varchar2, end varchar2) AS BEGIN SELECT TO_CHAR(TIME_RANGE, formatty) as CURRENT_DATE, SUM(CASE WHEN PORTA_POTTY = 'LM' THEN 1 ELSE 0 END) as LM_SEARCH, SUM(CASE WHEN PORTA_POTTY = 'AO' THEN 1 ELSE 0 END) as AO_SEARCH, SUM(CASE WHEN PORTA_POTTY = 'RO' THEN 1 ELSE 0 END) as RO_SEARCH, SUM(CASE WHEN PORTA_POTTY = 'FL' THEN 1 ELSE 0 END) as FL_SEARCH, SUM(CASE WHEN PORTA_POTTY IN ('LM', 'AO', 'RO', 'FL') THEN 1 ELSE 0 END) as TOTAL FROM CORE.DATE_TEST WHERE to_char(TIME_RANGE, formatty) >= to_char(start, formatty) AND to_char(TIME_RANGE, formatty) <= to_char(end, formatty) GROUP BY TO_CHAR(TIME_RANGE, formatty) ORDER BY TO_CHAR(TIME_RANGE, formatty) ASC; EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,'Proc failed - '||SQLCODE||' -ERROR- '||SQLERRM); END;
And when I attempt to call it (I'm aware it's an empty query, but it will let me know if it exists or not):
BEGIN POTTYUSERANGE(); END;
I get this error:
PLS-00201: identifier 'POTTYUSERANGE' must be declared
When I call it using
CALL POTTYUSERANGE();, I get this error:
ORA-06576: not a valid function or procedure name
How do I properly create a stored procedure for this query?
I need to insert this into a
BULK COLLECTION and return it to the user. It has to be limited so it doesn't cause excessive memory consumption. These are rather large result sets, and they look like this:
+--------------+----+----+----+----+-------+ | CURRENT_DATE | LM | AO | RO | FL | TOTAL | +--------------+----+----+----+----+-------+ | 1/2/2012 | 01 | 02 | 03 | 04 | 10 | +--------------+----+----+----+----+-------+ | 1/4/2013 | 02 | 03 | 04 | 05 | 14 | +--------------+----+----+----+----+-------+
Do I need a view? Stored proc? What do I need?
If I were you, this is how I'd code the above procedure:
create or replace procedure pottyuserange (p_date_format in varchar2, p_start_date in varchar2, p_end_date in varchar2, p_ref_cursor out sys_refcursor) as begin open p_ref_cursor for select to_char(time_range, p_date_format) as current_date, lm_search, ao_search, ro_search, fl_search, total from (select trunc(time_range) time_range, sum(case when porta_potty = 'LM' then 1 else 0 end) as lm_search, sum(case when porta_potty = 'AO' then 1 else 0 end) as ao_search, sum(case when porta_potty = 'RO' then 1 else 0 end) as ro_search, sum(case when porta_potty = 'FL' then 1 else 0 end) as fl_search, sum(case when porta_potty in ('LM', 'AO', 'RO', 'FL') then 1 else 0 end) as total from core.date_test where trunc(time_range) >= to_date(p_start_date, p_date_format) and trunc(time_range) <= to_date(p_end_date, p_date_format) group by trunc(time_range)) order by time_range asc; end pottyuserange; /
- the addition of the out parameter to return the cursor
- the additon of the
open p_ref_cursor forline, which is what creates the pointer to the cursor
- the changing of the predicates to do the date comparison as DATEs rather than strings
- the addition of the procedure name after the closing
- much clearer names for the parameter names. I would recommend changing the name of the procedure to something clearer too - that way, your code becomes much more self-documenting and easier to maintain in the future.
- the way I've moved the base of the query into a subquery and changed the outer query to order by the time_range field directly - since this is still in DATE format, it will order the results as expected. Thanks to Alex Poole for pointing out the issue with the ordering.
As for your issues running this in Toad, some versions of Toad have a bug (in my experience) where running the code via the Execute as statement / F9 button fails to do anything. If this is the case, try running it as a script (F5).
To test the above procedure in Toad (as a script) or in SQL*Plus, run the following:
variable rc refcursor; begin PottyUseRange('YYYY-MM-DD', '1/1/2008', '10/12/2015', :rc); end; / print rc;
(This creates a SQLPlus variable "rc", which you pass into the procedure as a bind variable. You can then use the SQLPlus print function to loop through and display the results.)
FWIW, here's what I see when I run show errors in SQL*Plus:
SQL*Plus: Release 126.96.36.199.0 Production on Mon Oct 12 17:34:32 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 188.8.131.52.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> create or replace procedure test 2 as 3 begin 4 null; 5 end test; 6 / Procedure created. SQL> show errors; No errors. SQL>
Developing and Using Stored Procedures, Because PL/SQL is executed in the database, you can include SQL This next section of this chapter is "Creating and Using Standalone Procedures and Functions", shows you how to From the File menu, select Save to save the new function. while others are used only by ohter functions and procedures and are never� Overview of Stored Procedures. You already know how to interact with the database using SQL, but it is not sufficient for building enterprise applications. PL/SQL is a third generation language that has the expected procedural and namespace constructs, and its tight integration with SQL makes it possible to build complex and powerful applications.
You've declare the procedure as:
PottyUseRange (formatty varchar2, start varchar2, end varchar2)
But you're calling it with no arguments:
BEGIN POTTYUSERANGE(); END;
There is no procedure matching the call you made. You need to pass the appropriate number of parameters, which can be literal values here as they are all IN parameters, e.g.:
BEGIN POTTYUSERANGE('X', 'Y', 'Z'); END;
Though with more meaningful values, of course. You can also pass local variables instead of constant literals.
But you say you're getting
PLS-00201: identifier 'POTTYUSERANGE' must be declared with one call, and
ORA-06576: not a valid function or procedure name with another, which means you either aren't actually creating it at all (you're types the code i but not executed it), or you're working in two separate schemas. You haven't shown a schema prefix in the
create call, so you may be removed that for privacy reasons, or you're creating and calling separate sessions. If your current user doesn't own the procedure and there isn't a synonym, you need to prefix it with the owner - guessing from the table owner:
BEGIN CORE.POTTYUSERANGE('X', 'Y', 'Z'); END;
As Lalit noted, you have compilation errors anyway, so calling would give
PLS-00905: object SCHEMA.POTTYUSERANGE is invalid. You can see the errors with
show errors or by querying the
user_errors view (or
all_errors if you're creating objects in another schema, which seems to be the case here), which would tell you:
PLS-00103: Encountered the symbol "START" when expecting one of the following: <an identifier> <a double-quoted delimited-identifier> current delete exists prior
Start and end are reserved words. You can use more appropriate names (what is starting?) or a generic prefix like
CREATE OR REPLACE PROCEDURE PottyUseRange (p_formatty varchar2, p_start varchar2, p_end varchar2) AS BEGIN SELECT TO_CHAR(TIME_RANGE, formatty) as CURRENT_DATE, SUM(CASE WHEN PORTA_POTTY = 'LM' THEN 1 ELSE 0 END) as LM_SEARCH, SUM(CASE WHEN PORTA_POTTY = 'AO' THEN 1 ELSE 0 END) as AO_SEARCH, SUM(CASE WHEN PORTA_POTTY = 'RO' THEN 1 ELSE 0 END) as RO_SEARCH, SUM(CASE WHEN PORTA_POTTY = 'FL' THEN 1 ELSE 0 END) as FL_SEARCH, SUM(CASE WHEN PORTA_POTTY IN ('LM', 'AO', 'RO', 'FL') THEN 1 ELSE 0 END) as TOTAL FROM CORE.DATE_TEST WHERE to_char(TIME_RANGE, p_formatty) >= to_char(p_start, formatty) AND to_char(TIME_RANGE, p_formatty) <= to_char(p_end, formatty) GROUP BY TO_CHAR(TIME_RANGE, p_formatty) ORDER BY TO_CHAR(TIME_RANGE, p_formatty) ASC; EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,'Proc failed - '||SQLCODE||' -ERROR- '||SQLERRM); END; /
But you also need to select into something when you're working in PL/SQL, e.g. declare local variables like
l_timerange etc. if you're going to be doing something with them locally. But you seem to be expecting multiple values, so you'd need to bulk select into a collection. It isn't clear what this is supposed to achieve though. If you want to pass those values back to the caller it might be simpler to use a ref cursor to return the result set; but then it isn't really clear if you actually need a procedure/function at all, or just a plain SQL query, or perhaps a view...
As a further issue, you're comparing dates as strings, and grouping/ordering by those too:
to_char(TIME_RANGE, formatty) >= to_char(start, formatty)
Comparing will only work at all for certain formats, and as you're passing a variable format in that's asking for problems; and even when it works may not be efficient. Ordering will also only work for some formats - if it makes sense to order at all (again, depends what you're doing with the results!). Convert your passed start/end strings to dates using the format that's passed in, and compare those:
TIME_RANGE >= to_date(p_start, formatty)
... or if possible pass a date into the procedure instead of a string.
Catching exceptions like this is also dangerous. You're assuming that whoever calls the procedure will have server output enabled and will do something with the error. Unless you can sensibly handle an exception you shouldn't catch it, and certainly shouldn't squash it like this.
Using Procedures and Packages, The following example shows a stored procedure that is similar to the Therefore, users never have to be granted the privileges to the objects SQL> EXECUTE CREATE SEQUENCE emp_sequence > START WITH 8000 INCREMENT BY 10; that was saved in the compiled state of PRINT_ENAME gets sent across to� 0 When creating a stored procedure in oracle, it appears to execute, but it does nothing. The stored procedure never gets saved. The stored procedure never gets saved. Why?
You should successfully compile the procedure before execute it.
To see compilation errors you can use
SHOW ERRORS. From there you can get the errors in your select statement.
Coding PL/SQL Procedures and Packages, The following example shows a stored procedure that is similar to the To execute the following, use CREATE OR REPLACE PROCEDURE . Therefore, users never need to be granted the privileges to the objects referenced by a procedure. of get_emp_name that was saved in the compiled state of print_ename gets� I'm trying to create a stored procedure in Oracle SQL using TOAD, but it's not doing anything. There's no error, no message, no nothing, when attempting to create it. It just seems to have gone through, but it doesn't. The query looks like this:
execute below query to know for errors while compilation
select * from SYS.USER_ERRORS where lower(NAME) = 'pottyuserange ' and type = 'PROCEDURE'
Ask TOM "How can I track the execution of PL/SQL and SQL?", In this fashion, you can see where in a procedure someone is based on the values in it down to one SQL statement in a script which goes into never never land. Do you have any suggestions how to do that using SQL information stored in query during executing this code, and it shows 'ROLLBACK' and nothing else. Use the CREATE PROCEDURE statement to create a standalone stored procedure or a call specification. A procedure is a group of PL/SQL statements that you can call by name. A call specification (sometimes called call spec) declares a Java method or a third-generation language (3GL) routine so that it can be called from SQL and PL/SQL.
5 Using Procedures, Functions, and Packages, You can create, modify, run, and drop stored The SQL CREATE PROCEDURE statement is used Description of xe_create_proc_sql.gif follows In the Name field, enter a name for the saved WHEN OTHERS THEN NULL; -- for other exceptions do nothing� How to Call PL/SQL Stored Procedures in Oracle Database. After successfully creating and compiling the stored procedure, next you have to call this subroutine. You can do so in multiple ways such as: Call a PL/SQL stored procedure using EXECUTE statement. Call a PL/SQL stored procedure using an Anonymous PL/SQL block.
Oracle Stored Procedures Tips, Oracle stored procedures and triggers are faster than traditional code, which means they and into the Oracle stored procedures, the application programs become nothing The below prototype defines the syntax for creating a procedure. If definer syntax is specified in the Authid clause, the procedure executes with the� you can write a java stored procedure OR use the scheduler (read about dbms_scheduler) to run OS commands. The output of these could be redirected to a file or easier still, returned as a string output. And you can of course call remote stored procedures that return a string So, write a stored procedure (java) on the unix machine:
1 Restrictions on Stored Programs, The restrictions for stored procedures also apply to the DO clause of Event Because local variables are in scope only during stored program execution, references to them are not permitted in prepared statements created within a stored program. Support for these statements is not required by the SQL standard, which� Could you please give me an example where we can pass multi-dimension array as parameter to a stored procedure and that procedure returns two arrays, ERROR_ARRAY and RESULT_ARRAY. Lets say, I pass some values to a procedure which has to insert rows in the emp table.
- Oracle stored procedures don't just run SQL. What do you want to do with the results? Do you really want a view or user-defined function?
SQL*Plus, after compiling the procedure, do this
- Also see, WHEN OTHERS
- Also, in Toad, how are you attempting to compile the code? I've had problems in some versions of Toad where using F9 / Execute Statement fails to run the
create or replace ...code, but F5 / Execute as script works just fine. If in doubt, highlight the procedure and run it as a script.
I need to insert this into a BULK COLLECTION.... I would highly recommend returning a ref cursor instead. This returns a pointer to the cursor, and allows the calling code to pull the data across as and when it needs it, almost as if the calling code had run the SQL directly. Much better, IMO, than collecting everything into a collection and then passing it across in one big lump!
- You've left a couple of references to
formatty, but I think the OP got the gist *8-) But might be worth mentioning that the ordering might not be as expected if the format isn't something that sorts helpfully (e.g. DD/MM/YYYY)? And if you're changing names, not too keen on the
current_datealias - it works but could be a bit confusing.
- @SecuriTree - in SQL*Plus you'd need a slash on a line on its own to execute the buffer. IDEs may or may not expect it but it's usually safer to include it. And your call has to include the fourth argument that Boneist added, which has to be declared by the caller (as it's an OUT parameter). (You could also make it a function that returns a ref cursor instead)
- @Boneist Are you sure about this
where trunc(time_range) >= to_char(p_start_date, p_date_format)It is comparing date on L.H.S. to a string on R.H.S. I think you missed it, else, it should have been
- @AlexPoole I request you to please undelete your answer, as it has several important points.
- I agree. Alex, please undelete your answer. I was just about to ask myself. You had a lot of really good advice in the post, and it will help others.
- You can't call it with no arguments because you're trying to call a different procedure (because the specification is different).
- I find two other serious issues. 1.
RAISE. The exception block is itself a bug.
- @SecuriTree - are you creating it in your own schema? It looks like it from the create command but perhaps not. You may need to look in
all_errorsto find it. And what you select into depends on what you want to do with the results; if the caller needs the data back then what it's expecting or can handle will also matter. At the moment it isn't clear why you're using a stored procedure.
- @SecuriTree I think that you come from a non-database background, most probably a front-end developer. All the suggestions here by me and others are to help you :-). Could you try the following steps: 1.
SQL*Plusshould be available with your database installation, so please open
SQL*Plus, and compile your procedure and after that, do
SHO ERR. 2. If you still fail to open
SQL*Plus, then please google how to use show errors/
SQL*Pluswindow in TOAD for Oracle. And repeat the steps. All the best!
- The INTO clause error is nothing but as I already said to look at this docs.oracle.com/cd/E11882_01/appdev.112/e25519/… Basically,
PL/SQLengines are different, so you cannot directly execute a SQL statement in PL/SQL.