Re: PL/SQL Help

Hello,
there are several ways to do this.

What you've always got to do:

- be granted the CREATE PROCEDURE system privilege
- run a SQL*Plus window
- type "set serveroutput on"

Method one (quick and dirty):
- write your PL/SQL code in your favorite text editor alongside your sql window
- copy / paste the code from the editor into the sql window - push Enter key, type a slash / and Enter again; this creates and compiles the code
- then you can call your procedure / function using the "execute" command

Method two (using only SQL*Plus interface): - Same as previous, except you simply type your code, line by line in the SQL*Plus window itself; you can push Enter as often as you want; SQL*Plus does nothing until you type the slash (see above)

When doing the "slash" operation, you either get "Procedure created" or "Function created" or you get a warning saying your code has been created with compilation errors. Unfortunately there is no way to getting it more precisely, so you have to check your code by yourself.

The advantage of the first method is that you can save your code to a normal file, and that you can use a suitable editor that uses, for example, color coding and syntax highlighting. After modifying your code, you simply do the same process again: paste, slash, execute. Attention: this only works if you use CREATE OR REPLACE PROCEDURE or FUNCTION (not simply CREATE).

With the second method, you also got to know about the SQL*Plus buffering, the save / get methods to recall objects from the SQL*Plus buffer. But you need not worry: of course your code is not lost with method two! You can get the text of your coding from a data dictionary view:
select Text from USER_SOURCE where Name = 'your_procedure_name' and Type = 'PROCEDURE' order by line;
Of course, if you did a function you replace procedure by function in the statement.

If after some time and for some reason, the procedure / function has an "invalid" status, you can manually recompile the code by using:

alter procedure procedure_name compile; alter function function_name compile;

You can drop procedures / functions using the "drop procedure procedure_name;" and "drop function function_name;" statements.

This was an outline, I hope it helps you. You really got to find out how you like it best. As to PL/SQL, there are of course many caveats and gotchas. I give you the details of a book that has helped my very much with SQL, SQL*Plus and PL/SQL. It is a big book, but really helpful:

Oracle 8 The Complete Reference (also covers Oracle7) George Koch, Kevin Loney
Oracle Press / Osborne
ISBN: 0-07-88-2396-X Have nice programming,

Anne-Marie Ternes

Maurice Isler wrote:

> I'm new to Oracle and I don't quite understand how to implement,
> compile and run PL/SQL code. I believe the code can be written from
> the SQL command line but how do I compile it and implement it? any
> information would be greatly appreciated.
>
> thanks
Received on Tue Nov 16 1999 - 09:10:19 CST