Re: owa_util.listprint

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 18 Jan 1999 13:13:41 GMT
Message-ID: <36a531fc.1568525@192.86.155.100>

A copy of this was sent to "Nicodemus Chan" <nicodemus_at_pacific.net.sg> (if that email address didn't require changing) On Mon, 18 Jan 1999 20:32:36 +0800, you wrote:

>Are there any code samples to get this function to work?
>
>This is the error message that I get:
>
>
>OWS-05101: Execution failed due to Oracle error 904
>ORA-00904: invalid column name
>ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
>ORA-06512: at "SYS.DBMS_SQL", line 32
>ORA-06512: at "WAIMEI.OWA_UTIL", line 1420
>ORA-06512: at "WAIMEI.OWA_UTIL", line 1568
>ORA-06512: at "WAIMEI.SV_CAL", line 24
>ORA-06512: at line 1
>
>
>My code is below:
>
> owa_util.listprint('select maintain.site_id a, site.name b, ''null'' n
> from maintain, site
> where maintain.site_id = site.site_id
> and maintain.user_id = ' || urec.user_id,
> 'yh', 1, TRUE);
>

lets say urec.user_id = SCOTT when you ran this, then the query would be:

select maintain.site_id a, site.name b, 'null' n   from maintain, site
 where maintain.site_id = site.site_id
   and maintain.user_id = SCOTT

So, it is looking for a column named SCOTT in maintain or site and not finding it. Also, it is selecting a character string constant 'null', not a database NULL. Unless you want everything in your list selected, you better just select NULL. You want to code:

> owa_util.listprint('select maintain.site_id a, site.name b, null n
> from maintain, site
> where maintain.site_id = site.site_id
> and maintain.user_id = ''' || urec.user_id || '''',
> 'yh', 1, TRUE);

I prefer to have a little function like:

function dquote( p_string in varchar2 ) return varchar2 is
begin

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

As that will

1- quote the string
2- replace any quotes in the string with double quotes so they are escaped...

Then you may code:

> owa_util.listprint('select maintain.site_id a, site.name b, null n
> from maintain, site
> where maintain.site_id = site.site_id
> and maintain.user_id = ' || dquote(urec.user_id),
> 'yh', 1, TRUE);

Also, it looks like "and maintain.user_id = urec.user_id" might be a candidate for a bind variable which would avoid this whole nasty quote problem AND let you use shared sql (faster parser for subsequent executions of that query by other sessions using different values for urec.user_id)....

Try something like:

owa_util.listprint(

    owa_util.bind_variables( 'select maintain.site_id a, site.name b, null n

                                from maintain, site
                               where maintain.site_id = site.site_id
                                 and maintain.user_id = :x1', 
                              ':x1', urec.user_id ),

    'yh', 1, TRUE);

will work for you....  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

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

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Jan 18 1999 - 07:13:41 CST