Re: PL/SQL and SQL Navigator

Pavel.Vet_at_volny.cz (Pavel Vetesnik) wrote in message news:<3657dfa4.0207291045.52a2ffb7_at_posting.google.com>...
> > > Hello,
> > >
> > > as you see, I am still beginner in using Oracle (ver 8.1.7.) and the
> > > SQL Navigator.
> > > I can run SQL commands in the Navigator without troubles. But as I
> > > found, I can't run the PL/SQL commands.
> > > For example I wanted to see, if a table exists. So I wrote this
> > > construction:
> > > ======================
> > > if exists (select 1 from user_tables where table_name='C_POLOZ') then
> > > begin
> > > select 1 from dual;
> > > else
> > > select 2 from dual;
> > > end if;
> > > ======================
> > > But I got ORA00900: Invalid SQL command
> > >
> > > As I learned, I may need to install a "procedural option". And I also
> > > learned (in http://groups.google.com/groups?hl=cs&lr=&ie=UTF-8&oe=UTF-8&selm=36162906.493E9E01%40websoft.com.au)
> > > that this option can be installed running catproc.sql script.
> > >
> > > Frankly - I am quite afraid of running this, because it must be run
> > > with administrator's privileges and I don't know if it will not hurt
> > > our existing code.
> > > So my questions:
> > > (1) Is installing "procedural option" (catproc.sql) safe?
> > > (2) Will this really help me in running PL/SQL commands in SQL
> > > Navigator (SQL Editor Window).
> > > (3) Should I use another tool to write Pl/SQL scripts?
> > >
> > > Thank you in advance!
> > > Pavel
 

> > 1 Yes. But likely it has already been installed. Re-installing won't
> > do any harm provided you run catproc.sql using sql*plus, connected as
> > sys. Any other user and you will have havoc.
> > 2 Provided you enter code using correct pl/sql. 'If exists' does exist
> > in t-sql, it doesn't exist in pl/sql. There is also no need to use it,
> > as a non-existing table will raise an exception in your code.
> > 3 Why? You should try to learn pl/sql and to read the pl/sql reference
> > manual.
> > You shouldn't think pl/sql is the same as t-sql.
> >
> > Regards
> >
> > Sybrand Bakker
> > Senior Oracle DBA
>
> Thank you very much for your comments. As I see I need to study more
> than I expected...
> But please what did you mean with the sentence
> >>>Any other user and you will have havoc.<<<?
>
> Pavel

If you don't connect to SYS to run catproc but use a different account, catproc.sql will run, but it will have a bunch of nasty results. Sometimes people run this using SYSTEM. It seems to work, but once you completed the run, you will soon note you have run into bigger trouble.

Regards

Sybrand Bakker
Senior Oracle DBA Received on Wed Jul 31 2002 - 09:12:11 CEST