Re: PL/SQL Question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/08/02
Message-ID: <33e594db.41430263@newshost>#1/1

On Thu, 31 Jul 1997 15:04:53 -0500, Manjula Krishnan <krishnan_at_mailhost.tcs.tulane.edu> 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;
>
>--- It compiles successfully. But, gives me the following errors at
>runtime.
>

Try putting the query in a string first so that an exception block can print it out. for example:

...

   l_query varchar2(4096);
...
begin

   ...
   l_query := 'select .....';
   dbms_sql.parse( kcursor, l_query );
exception

   when others then

      dbms_output.put_line( l_query );
      dbms_output.put_line( sqlerrm );
      raise;

end;
....

What you will see in your case would be (assuming the the value in keywd1 is keywd1 and keywd2 is keywd2 and join1 is AND )

SELECT PATHNAME, DESCR, ASC_NUM FROM
POMPEII, KEYWORD WHERE KEYWORD.PICTURE = POMPEII.ASC_NUM AND

	LOWER(KEYWORDS.KEYWRDS) = LOWER( keywd1 )
	AND  LOWER(KEYWORDS.KEYWRDS) = LOWER( keywd2  )

So it is complaining that the columns keywd1 and keywd2 are not known. You need to either

1.) use bind variables (best option)
2.) quote the character string constants (ok but not optimal)

I like to use the following to quote my strings when I need to:

function dquote( p_str in varchar2 ) return varchar2 is
begin

        return '''' || replace( p_str, '''', '''''' ) || ''''; end;

This ensures that my string has quotes around it and if it contains a quote, it is escaped. Send this function something like "How's it going?" and it will return 'How''s it going?'

Wrap your reference to keywd1 and keywd2 with a call to dquote and it'll probably work (unless there is someother syntax error :)

>SQL> execute test('black', 'and' , 'figure');
>begin test('black', 'and' , 'figure'); end;
>
>*
>ERROR at line 1:
>ORA-00904: invalid column name
>ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
>ORA-06512: at "SYS.DBMS_SQL", line 25
>ORA-06512: at "CLSWORKER.TEST", line 8
>ORA-06512: at line 1
>
>Thanks,
>
>Manjula

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Aug 02 1997 - 00:00:00 CDT