Oracle Stored Procedures - Debug a procedure (
Page 7 of 10 )
Let’s introduce a compilation error into your procedure declaration.
Open your skeleton.sql file in Notepad. Replace the DBMS_OUTPUT.PUT_LINE procedure call with the NULLL statement (notice the three "l"s!), an invalid PL/SQL statement. Your program should look like this:
CREATE OR REPLACE PROCEDURE skeleton
IS
BEGIN
NULLL;
END;
Save your file as skeleton.sql.
From SQL*Plus, open your skeleton.sql file.
SQL*Plus loads the contents of your skeleton.sql file into the SQL*Plus buffer or memory area and presents the SQL*Plus command prompt:
SQL>
1 CREATE OR REPLACE PROCEDURE skeleton
2 IS
3 BEGIN
4 NULLL;
5* END;
SQL>
Execute the contents of the SQL*Plus buffer. Type a front slash and press <enter> like this:
SQL> /
Your procedure is compiled and saved on the database. However, SQL*Plus warns us of compilation errors:
Warning: Procedure created with compilation errors.
Let’s see the compilation errors. First, we need to run two SET commands to ensure the SQL*Plus buffer does not overflow.
At the SQL*Plus command prompt, type:
SQL> SET ARRAYSIZE 1
SQL> SET MAXDATA 60000
SQL>
Again, SQL*Plus remains secretive of the result. Let's see the errors. At the SQL*Plus command prompt, type:
SQL> SHOW ERRORS PROCEDURE skeleton
You should see the compilation error:
LINE/COL
----------------------------------------------
ERROR
----------------------------------------------
4/3
PLS-00201: identifier 'NULLL' must be declared
4/3
PL/SQL: Statement ignored
Oracle doesn't recognize the NULLL statement with the three "l"s. But Oracle won't hold it against you.
Change your procedure declaration in Notepad by inserting the proper NULL statement, and follow the steps to create your procedure again on the Oracle database.
What if you want to completely remove a procedure from your database? That's what we'll cover next.
| | Discuss Oracle Stored Procedures | | | | | | | Hi i am madhu. Its a great article but if you have continued the artice with one... | | | | | | Hi,
This article is simply superb.The author didn't miss anything regarding Stored... | | | | | | where I can find an example as easy to follow as this
with parameters... | | | | | | Helo , I am a beginner in Oracle. I must say that within short time i could grasp... | | | | | | hi
i read all the pages of stored procedure, it is very good for beginers,
but... | | | | | | Hello ! i m UDAI . i read this whole basic tut on STORED PROCEDURES.It's a nice... | | | | | | very good basic instruction. it's the start of real pl/sql programming. 10 thumbs... | | | | | | Hi, i am Sukumar Reddy.v
the article on stored procedures is great
but it will... | | | | | | This article about stored procedure explained well and very easy to understand. My... | | | | | | for only who do't know stored procedures at all.u have taken only skeleton and... | | | | | | Hi Im senthil its really a good article I like very much step by step explanation is... | | | | | | How to run stored procedure which has IN parameter | | | | | | I can't believe people are responding positively to this article. Yes, it is well... | | | | | | This article contains so little information regarding procedures
this is not... | | | | | | hi i read ur article and get that it's abest article for the oracle new cumers.
and... | | | | | | Hi, i am Ankur Agarwal, this article is well but it lacks various things like... | | | | | | You have 10 thumbs?!?!?!? ;o) | | | | | | hi , I m PRASHANT SRIVASTAVA this article is very good but should have some basic... | | | | | | Hello,
This is Radha. I am basic learner for stored procedure. This site... | | | | | | Hi, I am venkat, It is very good, we request some more example procedures to... | | | | | | Hi i am Niranjan, Nice article on Stored Procedures.........very clear and brief.... | | | | | | This is Mir, real quick and instant info about the subject with clear understanding,... | | | | | | This article is very helpful for the PL/SQL beginers and easy to understand and will... | | | | | | yes, i also thinks more the examples more the learning..good to see articles but... | | | | | | Its is very easy to understand,who know little bit knowlodge in Sql,those are play... | | | | | | What was explained here could have been accomplished only using Oracle SQL Developer... | | | | | | This is an example... not a book or a class room. If you want more examples and in... | | | | | | Hi i am Vara Rama Krishna, Nice article on Stored Procedures.........very clear and... | | | | | | That was a lot of pages for a ridiculously small amount of information. I can't... | | | | | | hi
this is saurabh gulati, n i read all the pages of stored procedure,
... | | | | | | store procedure in oracle allow programer to put data in a data base throw
PL\SQL... | | | | | | simply awsome and excellent
should have been good if continued further
with... | | | | | | hi, i am Sunny Duggal.
the article is excellent but more and more practical examles... | | | | | | Hi
this is a great article for the beginners and i really appreciate if you could... | | | | | | Definintly not enough information here to do anything the slightest bit useful... | | | | | | Hi, I am kaps. It is really greate article for the beginers.But it is not complete... | | | | | | One glaring omission in this article is how to retrieve the definition of a... | | | | | | Hi.. This article is really nice to read it. if more number of examples are there... | | | | | | Really it should be continued.... wonderful narration!! | | | | | | HI IAM SHAM, THIS ARTICLE IS SUPERB, EASY TO UNDERSTAND, READERS WOULD BECOME ENTHU... | | | | | | Hi I am Mohammed Pasha,Last 15 min I just walk through this article which is shown... | | | | | | This Article is very helpful for me.
But can you countinue this article with
some... | | | | | | Hello, this is Sagar. It is really good stuff for beginners. It would have continued... | | | | | | Hi I m Nitin ,first time i m readng pl\sql but when i read it seems to me thats i... | | | | | | hi this is sambasiva.
this very exciting stuff.really very nice. | | | | | | This is infact the best means to study the stored procedure. It would have been more... | | | | | | this is a very nice idea to learn about procedures. any one who feel to learn oracle... | | | | | | Yes Madhu is right. Should have gone a bit further about parameters and... | | | | | | This article rocked me mann but you should have further cracked the details | | | | | | Hi Madhu, How are you doing? its been long time talking to you ,.. missing you..... | | | | | | Quite good material to start understanding basics of stored procedure but do not... | | | | | | yes - count them
0
1
10 | | | | | | >>> Post your comment now! | | | | | |
|
 |