Re: Q: No WOW output from PL/SQL loop on cursor select. Help, please?
Date: 1996/01/13
Message-ID: <4d8nae$pt_at_inet-nntp-gw-1.us.oracle.com>#1/1
Sounds like CPROD is defined as a CHAR in the database, NOT a VARCHAR.
Use
select product,bun,release,tc_auto from qam
where product = rpad(pname,XXXX);
where XXXX will be filled in with the database length of product as it is defined in the data dictionary.
When comparing a CHAR column to a constant, for example:
select * from qam where product = 'TIRKS'
the string is auto padded to the right length, so TIRKS is really 'TIRKS '.
When using a bind variable, the underlying type is used, so the varchar2 in the following pl/sql is NOT blank padded.
ral_at_cc.bellcore.com (Ron Levenberg) wrote:
>Help, please! I've got WOW up and running, but for some strange reason
>my test package
>doesn't display the records that should be retrieved in a for loop
>with
>a cursor; I can print literals and other variables OK in the same
>package.
>This seems to be the last roadblock before I can usefully apply WOW.
>My test package looks like this:
>create or replace package test is
> procedure t(pname in varchar2);
> procedure ttirks;
>end;
>/
>create or replace package body test is
> procedure t(pname in varchar2) is
> cursor cprod( pname varchar2) is
> select product,bun,release,tc_auto from qam
> where product = pname;
> begin
> htp.dbg := true;
> htp.htitle('Automation Report');
> htp.preOpen;
> dbms_output.put_line('This is a test of dbms_output');
> htp.p('Looking for product ' || pname);
> for prod in cprod('TIRKS') loop
> htp.p(prod.product || prod.bun || prod.release
>|| prod.t
>c_auto);
> end loop;
> htp.preClose;
> wow.sig('test');
> end;
> procedure ttirks is
> cursor cprod is
> select product,bun,release,tc_auto from qam
> where product = 'TIRKS';
> begin
> htp.dbg := true;
> htp.htitle('Automation Report');
> htp.preOpen;
> htp.p('Looking for product ' || 'TIRKS');
> for prod in cprod loop
> htp.p(prod.product || prod.bun || prod.release
>|| prod.t
>c_auto);
> end loop;
> htp.preClose;
> wow.sig('test');
> end;
>end;
>/
>show errors;
>If I execute the same select statement directly in sqlplus, as
>[www_at_carib:/users/www/dba] sqlplus /
>SQL*Plus: Release 3.1.3.7.1 - Production on Fri Jan 12 14:45:18 1996
>Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
>Connected to:
>Oracle7 Server Release 7.1.6.2.0 - Production Release
>PL/SQL Release 2.1.6.2.0 - Production
>SQL> select product,bun,release,tc_auto from qam where product =
>'TIRKS';
>PRODUCT BUN RELEASE TC_AUTO
>---------- --- -------- ----------
>TIRKS Prv 15.5
>TIRKS Prv 16
>TIRKS Prv 16.1
>TIRKS Prv 16.2
>TIRKS Prv 16.3
>TIRKS Prv 16.4
>TIRKS Prv 17 100
>TIRKS Prv 17.1 98
>SQL>
>The output on the web page looks like this. Notice that none of the
>8 records get output.
><img src="/images/wow.gif">
><title>Automation Report</title><h1>Automation Report</h1>
><pre>
>This is a test of dbms_output
>Looking for product TIRKS
></pre>
><hr>
>This page was produced by
><b>Wow</b> version 1.0 on 96/01/12 14:41 GMT+1<br>
><a href="/cgi-bin/wow/wow.showsource?cname=test">Inspect pl/sql source
>code</a>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Received on Sat Jan 13 1996 - 00:00:00 CET