Re: Oracle Scripting Question

Comments in-line.

DTS wrote:

> I'm a bit of a newbie at writing scripts for Oracle. I'm having a hard
> time wrapping my head around all of the different scripting,
> reporting, and script storage abilities Oracle has to offer. Maybe I'm
> making a mountain out of a molehill, but I could use some
> clarification.
>
> -Can I embed SQL DML statements inside of PLSQL blocks?

That is what they are there for.

> -Can I combine a SQLPlus report with a PLSQL code block and print out
> results?

Using DBMS_OUTPUT.PUT_LINE but I'd be highly inclined to purchase a real reporting tool whether Oracle Reports, Discoverer, Crystal, Cognos, whatever.

> -I'd like to be able to keep DML statements for all of the database
> objects required by my applications near the application source code.
> This would include packages, tables, subprograms, triggers, etc. --
> anything and everything to help redeploy the database-side of the
> application. What's the best means of accomplishing this and keeping
> everything synchronized?

Why scripts at all? Just put them into a package as separate procedures and functions?

> -Is creating a subprogram in a package the same as creating a
> standalone subprogram? In other words, will the subprograms I code in
> the package show up as independent objects in the schema?

No. They can be found in ALL_ARGUMENTS provided they are in the package header.

> -Are there any special file extensions that I should be using (aside
> from .sql)? Maybe .pkg for packages??? What's all of this "start file"
> stuff anyways?

Source code naming is irrelevant. Choose whatever you wish.

> -Does it make sense to provide a seperate schema for most every
> application that I build? I am gravitating towards this idea as it
> provides a convenient delineation between applications and would allow
> me to move the entire schema around via SQLLoader and conveniently
> list schema contents from the data dictionary. I would probably need
> to create a generic schema for global packages that I create. Does
> that make sense or is there a better way?

Yes. The value for maintenance and security is substantial. Also consider separate tablespaces for each application as an aide to maintenance.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)