Re: PL/SQL Help

Manjula Krishnan wrote:
>
> Can someone tell me what is wrong with my procedure :
>
> create or replace procedure test
> (keywd1 varchar2 , join1 varchar2 , keywd2 varchar2) as
> kcursor integer;
>
> begin
> kcursor := dbms_sql.open_cursor;
> dbms_sql.parse(kcursor, 'SELECT PATHNAME, DESCR, ASC_NUM FROM
> POMPEII, KEYWORD WHERE KEYWORD.PICTURE = POMPEII.ASC_NUM AND
> LOWER(KEYWORDS.KEYWRDS) = LOWER(' || keywd1 ||' )' ||
> join1 || 'LOWER(KEYWORDS.KEYWRDS) = LOWER(' || keywd2 || ' )',
> dbms_sql.v7);
> dbms_sql.close_cursor(kcursor);
> end;

Look closely at the use of keywd in the select statement. If the parameter keywd contains 'hello' then that part of the select statement comes out to:

  LOWER(KEYWORDS.KEYWRDS) = LOWER(hello)...

As you can see, it is trying to pass the contents of the column "hello" to the LOWER function -- probably not what you entend. Try this:

  LOWER(KEYWORDS.KEYWRDS) = LOWER(''' || keywd1 ||''' )' ||

which, when parsed, comes out like:

  LOWER(KEYWORDS.KEYWRDS) = LOWER('hello')...

I think is what you are trying to do.

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"