Re: HELP ! Whats wrong with this procedure ??

mthgd_at_tdk.dk (Martin T.) instructed their monkeys to type:
>Is there anybody who can help me ! What is the matter with this
>procedure.................
>
>SQL> CREATE PROCEDURE sam.kis_addr(p_kis_nr in varchar2(26))
> 2 AS
> 3 BEGIN
> 4 SELECT DISTINCT cdmcaa.addr1, cdmpcode.postcode, cdmpcode.town,
>cdmcaa.id
> 5 FROM cdmcomp, cdmcaa, cdmpcode, installation
<snip>

You can't just select like this inside a procedure, you either need to define the select statement as a cursor, or do a select... into.... I suggest you have a read of your PL/SQL reference manual.

Alternatively, if you're using SQL*Plus, then just use substitution variables in a SQL script, e.g.:

SQL> select * from emp
2 where emp_no = &empno;

&empno is a substition variable, and you will be asked for the value of it when you run the query (unless it's already defined). You can use &1, &2 etc. for command line parameter substitution.

Mark Styles
Oracle developer and DBA
http://www.lambic.co.uk/company Received on Wed Jul 14 1999 - 06:44:39 CDT