Re: OK... maybe I'm being stupid... HELP

From: Mark Styles <styles-nospam_at_lambic.co.uk>
Date: Mon, 05 Jul 1999 14:36:09 GMT
Message-ID: <3780c09e.21814908@news.intra.bt.com>

"Nick Bull" <nick_at_imat.demon.co.uk> instructed their monkeys to type:

>CREATE OR REPLACE PROCEDURE HowManyCustomers
> ( BankName IN VARCHAR2,
> Surname IN VarChar2,
> HowMany OUT NUMBER ) IS
>BEGIN
> HowMany := SELECT COUNT(*) FROM BankName
> WHERE CustomerSurname = Surname;

Two problems here, first, you don't select into a variable like that, you should either define a cursor or use 'select blah into variable' syntax.

Secondly, you can't use a variable name in a from clause. For this you need to read up on dynamic SQL in the Application Developer's Guide

You want something like:

CREATE OR REPLACE FUNCTION HowManyCustomers   (BankName IN VARCHAR2, SURNAME IN VARCHAR2) RETURN NUMBER IS

	v_query		varchar2(50);
	c_cursor	integer;
	v_count		integer;
BEGIN
	v_query := 'select count(*) from ' || BankName || 
                        'where CustomerSurname = :surname';
	c_cursor := dbms_sql.open_cursor;
	dbms_sql.parse(c_cursor, v_query, dbms_sql.v7);
	dbms_sql.bind_variable(c_cursor, ':surname',Surname);
	dbms_sql.define_column(c_cursor,1,v_count);
	dbms_sql.execute(c_cursor);
	dbms_sql.fetch_rows(c_cursor);
	dbms_sql.column_value(c_cursor,1,v_count);
	return v_count;

END;          Mark Styles
Oracle developer and DBA
http://www.lambic.co.uk/company Received on Mon Jul 05 1999 - 09:36:09 CDT