some basic questions

[jt]

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.

[Kancha]

If you are DBA and if u don't mind then I want to give one suggestion. Its better to work as pl/sql programmer at least for one year. Then go for DBA position. Please don't mind.

For PL/SQL development and maintenance there are many tools like TOAD (Tool for Oracle Application Development), PL/SQL development Tool, etc. But it is better to go for NOTEPAD at beginning to get familiar with all basic operations.

Kancha

[manas]

I also agree with what Kancha has to say. It's better to work in PL/SQL for at least 3-4 months before jumping into DBA work.
well here are my answers to your queries.

1. Well, first of all, are procedure, function etc. objects in a DB, just as table and index are objects in a db?

A> YES.

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?

A> You can use a textpad/notepad for the same. However there are tools to help you in coding also like TOAD, PLEDIT etc.

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?

A> You have to create a public synonym to access it from other schema.

4. How do you excecute a procedure? Is it SQL>EXEC procedure?

A> YES. There is another alternative to put it in an anonymous block and execute it.

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?

A> As long as you are saving a file with .sql extension, you can compile it without even specifying it's extension. For example, if file name is test.sql you can just say SQL > @test. But if you save the file as anything other than .sql, yuo have to completely specify the file name to compile. In this case, if file name is test.txt you need to compile it as SQL> @test.txt


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?

A> You have to specify the procedure or package or function as create or replace to edit it later. Otherwise you have to drop the existing one before compiling the new edited version. A new compilation will invalidate the objects which are dependent on this.

[jt]

Thank you both for the answers and advice. I am going backwards, i.e. after being a DBA for couple of years, now I am finally trying to catching up the development part. So I need all the help I can get! :)

Could you explain a little more about compile? When you first run a script say test.sql to create procedure TEST by doing SQL>@test.sql, the procedure will be created under your schema. Do you mean this is also compilation of the procedure? Or this only creates it but you have to compile somehow separately? Also, you said when you create or replace a procedure, you invalidate all others that reference to this proc, and you have to recompile those. How do you do that? How do you know which ones are affected? And how do you verify afterwards all affected ones are compiled successfully and are valid now? Thanks!

[gshaf]

J.T,

When you specify the '@' character in SQL*PLUS, you're actually compiling the package, procedure, or function. For example, if the test.sql script resides in some directory (in Windows), your command would look like this:

SQL> @C:\my_dbscripts\test
(NOTE: you don't need to put the .sql after the file name)

If the compliation is successful, it would say:
Package/Procedure/Function created.

If you need to execute the package/procedure/function you'd use the 'EXEC' statement.

Hope this helps!

- Gary