-
I posted this to the developers forum. I think I should also ask here since most people here are also very knowledgeable in PL/SQL:
hi, I am a DBA new to package/procedure/function writing, I just have some simple questions to clerify my basic understanding a bit. Acutally I don't really know where to start asking. So any feedback whether related to the specific questions or not are GREATLY appreciated!
1. Well, first of all, are procedure, function etc. objects in a DB, just as table and index are objects in a db?
2. What kind of tool do you use to write your package/precedure etc? I mean if you read a book, they have the codes, but do you write it in notpad/vi, or do you have some tools to use, sort of when you do C programming? Same thing for debugging, what do you use?
3. Is this correct: Once you have a procedure written, you run it under say user TEST in oracle DB, then the procedure is created in the DB under TEST schema. Then if you have another procedure/function referening to this procedure later, as long as it is within the same schema, you don't have to put anything in front of it to identify it? How do you use this procedure if you are a diff. user, i.e. diff. schema?
4. How do you excecute a procedure? Is it SQL>EXEC procedure?
5. How do you save a procedure/function etc? I mean do you save the code as a text file? Or onces you run the code and created the procedure, do you still need the code again ever?
6. How do you modify a procedure, whether during debugging or modify at later time? Do you always have to do create or replace procedure and rerun the creation script? If so, will that break down anything that uses the procedure?
I guess what I need is really some practical advice important but not easy to find. So ANY help is appreciated!
Thanks.
-
Well, I will try answering some of your questions:
1) Procedure/functions are not the objects as they donot require any allocation of space by the DB.
2) Sql plus is the widely used tool to create the database procedures/functions. However all other front end tools like forms/procedure builders also allow you to write the scripts.
3).You can call any procedures with in the procedures just by call its name (eg begin
proc_name;
end;) how ever if you want to can the procedure owned by other schema , first of all you should have the permission to execute that procedure and later say schema.procedure_name.
4)Yes
5) If your procedur/fun is in a text file just say
sql> @filename
6)Only way to modify the procedure is to use
"create or replace procedure proc_name".
I think this helps you
Badrinath
-
Hi jt,
1. From the databases sight of view, PL/SQL-procedures and -functions are objects like tables. Have a look at the view "user_objects".
2. We write our programs on Windows-Clients using the SQL-Navigator from Quest Software. ( http://www.quest.com ).
It doubles the speed of our rookies and our veterans.
5+6. The SQL-Navigator saves the procedure in the database, using the 'create or replace' internally. When you want to edit it, it is retrieved from the database. So you are sure to edit the version that is really stored in the database.
When you compile, all other procs calling your proc are set invalid. You can recompile them by "ALTER PROCEDURE QWERTZ COMPILE".
You can view your stored code using the ALL_SOURCES (or ALL_SOURCE, i'm not sure because not at work now) view.
Commit;
6502
-
Hi, thank you both very much for the help! I still need to read/practice alot, but this really helps in answering questions not easy to find in a book!
-
One more thing,,if you want your proc/fun/pkg to be executed by another schema then you need to give an execute permission on the object to the particular user.
cheers
