Re: how to convert SQLserver proc to work in Oracle?

From: Guido Konsolke <Guido.Konsolke_at_triaton.com>
Date: Tue, 15 Oct 2002 13:14:23 +0200
Message-ID: <1034679356.296748_at_news.thyssen.com>

"Hakan Jakobsson" <hakan_at_lge.com> wrote... < (snipped)

Hi Hakan,

sorry, I don't know TOAD but there has to be an option like 'compile' or so to create a stored procedure. I think creating procs in Oracle works quite similar to SQL-Server. Your proc could look like:

CREATE PROCEDURE sp_tblname_add(

   in_name VARCHAR(50),
   in_email VARCHAR(100),
   in_headline VARCHAR(200),
   in_text VARCHAR(1000))

 AS
BEGIN
 INSERT INTO tbl_name (name,email,headline,text)  VALUES(in_name,in_email,in_headline,in_text); END;
>
> but it doesn't work

What error message(s) do you get?

> when i do 'drop procedure sp__tblname_add'
> it says 'SP_TBL NAME_ADD doesn't exist'

You didn't compile the proc, so it doesn't exist. Ergo: your DROP command fails.

>
> you can also run a stored proc buy 'exec sp_name'
> how do you exec a proc in oracle?

In SQL*PLUS: execute sp_name;

>
> and how do you create auto id (SQLserver's Identifyer) in Oracle?
>
> i mean an id that increases itself and your not suppose to
> add id when you're inserting rows into the table.

This nice little thing is called sequence. Just look at the docs (create sequence, alter sequence and so on).

>
> thank you very much
>
> keep on rockin'

I always will...;-))

>
> --
> Kind Regards
> Hakan Jakobsson
> System Developer
> LGESE IT Department

hth,
Guido Received on Tue Oct 15 2002 - 13:14:23 CEST