Re: PL/SQL and SQL Navigator

From: Thomas Olszewicki <ThomasO_at_NoSpam.cpas.com>
Date: Wed, 31 Jul 2002 01:23:57 GMT
Message-ID: <N_G19.113879$WsS.29777_at_news01.bloor.is.net.cable.rogers.com>

Pavel
Try this:

CREATE OR REPLACE FUNCTION FindTable( cTable VARCHAR2 ) RETURN NUMBER AS

   nRtn NUMBER;
   CURSOR curTab IS SELECT 1 FROM USER_TABLES WHERE TABLE_NAME = cTable;    recTab curTab%ROWTYPE;

   BEGIN

      OPEN curTab;
      FETCH curTab INTO recTab;
      IF curTab%NOTFOUND THEN
       nRtn := 0;
      ELSE
       nRtn := 1;
      END IF;
      CLOSE curTab;
      RETURN nRtn;

   END; Pl/SQL is more like real programming language than T-SQL script : You declare and use variables and cursors , you can crate new types (even classes and objects)
Use your CA-VO knowledge and you will like Pl/SQL as much. Just remember to add ';'...
Happy VOing with Oracle.
HTH
Thomas Olszewicki

"Pavel Vetesnik" <Pavel.Vet_at_volny.cz> wrote in message news:3657dfa4.0207290218.c11c33b_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.49 3E9E01%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
Received on Wed Jul 31 2002 - 03:23:57 CEST