Home » SQL & PL/SQL » SQL & PL/SQL » NO ROWS SELECTED when using BIND VARIABLE
NO ROWS SELECTED when using BIND VARIABLE [message #219735] Thu, 15 February 2007 15:01 Go to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
declare
sql_text long;
maxval number := 15;
startval number := 1 ;
currentval NUMBER := startval;
v_tnum varchar2(27) := padtaxnum(:usr_string); <= not sure if ok
type rc is ref cursor;
v_rc rc;
begin
for v_rc in 1..15 loop
 -- get D data
sql_text := 'select ';
sql_text := sql_text ||' d.l_num,' ;
sql_text := sql_text ||' d.p_num,';
sql_text := sql_text ||' trunc(r.t_date) r_date,' ;
sql_text := sql_text ||' dl.type rec_type,' ;
sql_text := sql_text ||' nvl(dt.c_amt, 0) amount,' ;
sql_text := substr(sql_text,1,length(sql_text)-1) || ' FROM d_tran d,' ;
sql_text := sql_text ||' ttable tl,' ;
sql_text := sql_text ||' rcpt r,' ;
sql_text := sql_text ||' docl dl,';
sql_text := sql_text ||' d_tran dt' ;
sql_text := sql_text ||' WHERE tl.t_num = :v_tnum';
--sql_text := sql_text ||'WHERE tl.t_num =''0900-141.00-02.00-037.000'' ';
sql_text := sql_text ||' AND d.l_num LIKE  ''D%'' ' ;
sql_text := sql_text ||' AND d.l_num    = tl.l_num ';
sql_text := sql_text ||' AND d.p_num     = tl.p_num ';
sql_text := sql_text ||' AND d.rcpt_num  = r.rcpt_num ';
sql_text := sql_text ||' AND d.d_id  = dl.d_id ';
sql_text := sql_text ||' AND d.rcpt_num  = dt.rcpt_num(+)';
sql_text := sql_text ||' AND d.t_num    = dt.t_num(+) ';
sql_text := sql_text ||' union' ;  <= not getting 'D' records
-- get M data
sql_text := 'select ' ;
sql_text := sql_text ||' d.l_num,' ;
sql_text := sql_text ||' d.p_num,';
sql_text := sql_text ||' trunc(r.t_date) r_date,' ;
sql_text := sql_text ||' dl.type rec_type,' ;
sql_text := sql_text ||' nvl(mt.m_amt, 0)amount,' ;
sql_text := substr(sql_text,1,length(sql_text)-1) || ' FROM d_tran d,' ;
sql_text := sql_text ||' ttable tl,' ;
sql_text := sql_text ||' rcpt r,' ;
sql_text := sql_text ||' doc dl,';
sql_text := sql_text ||' m_tran mt' ;
sql_text := sql_text ||' WHERE tl.t_num = :v_tnum'; -- no rows
--sql_text := sql_text ||' WHERE tl.t_num =''0900-141.00-02.00-037.000'' ';  -- this code works
sql_text := sql_text ||' AND d.l_num LIKE  ''M%'' ' ;
sql_text := sql_text ||' AND d.l_num    = tl.l_num ';
sql_text := sql_text ||' AND d.p_num     = tl.p_num ';
sql_text := sql_text ||' AND d.rcpt_num  = r.rcpt_num ';
sql_text := sql_text ||' AND d.d_id  = dl.d_id ';
sql_text := sql_text ||' AND d.rcpt_num  = mt.rcpt_num(+)';
sql_text := sql_text ||' AND d.trans_num    = mt.t_num(+) ';
sql_text := sql_text ||' AND rownum < 16';
sql_text := sql_text ||' order by r_date desc' ;
end loop;
p(sql_text);
open :rc for sql_text using v_tnum; -- no rows selected
--open :rc for sql_text ; -- works fine
end;
/


output w/ hardcoded value in where clause:
L_NUM    P_NUN  R_DATE    R_TYPE                    AMOUNT
--------- ------ --------- ----------------------------------M00021371 739    28-AUG-06 S/MOP                        0
M00021356 878    08-AUG-06 S/MOP                        0
M00021335 349    12-JUL-06 M...                      352800
-- it seems as though the D record was either overwritten or
-- my upper part of the code priot to the UNION did not execute
-- very strange.


should get w/ hardcoded value in where clause:
L_NUM    P_NUN  R_DATE    R_TYPE                    AMOUNT
--------- ------ --------- ----------------------------------M00021371 739    28-AUG-06 S/MOP                        0
M00021356 878    08-AUG-06 S/MOP                        0
D00012458 864    12-JUL-06 D..                       392000
M00021335 349    12-JUL-06 M...                      352800

[Updated on: Fri, 16 February 2007 09:25]

Report message to a moderator

Re: NO ROWS SELECTED when using BIND VARIABLE [message #219794 is a reply to message #219735] Fri, 16 February 2007 03:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
A few points:

1) Could you post the actual code that you are using. This isn't the actual code, as you define a variable v_t_num varchar2(27) in the declare block, but then go on to use v_tnum

2) What value is v_tnum given when you run the code. Seeing as how this is the change that causes the problems, I'd say it's the prime suspect.

3) Why are you using dynamic Sql? There is nothing in that query that requires the use of dynamic Sql. You're just making things harder for yourself.

4) Stop using the LONG data type. It was obsolete in Oracle 8i. Use Clob, or just a Varchar2(32767) (which will be as long as a LONG in pl/sql)
Re: NO ROWS SELECTED when using BIND VARIABLE [message #219886 is a reply to message #219794] Fri, 16 February 2007 09:48 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Thanks for your input.

1. Is just a typo I fixed it.

2. You are right about v_tnum. I ran the code again last night and printed(using print v_tnum) the value for v_tnum, sure enought it is blank. It was suggested to me to call that procedure in my declaration but what good would that do? I have no idea how to sustain that value and use it in my where clause. Right on point.

3. I am not sure how I ended up w/ NDS and honestly I'm still trying to digest the difference as I am learning as I go along. I frankly, have no preference. I just need to get this to work by whichever means.

4. point well taken and thnaks for the tip. Will chage that.

Any idea on why I am not getting the D record?
Re: NO ROWS SELECTED when using BIND VARIABLE [message #219888 is a reply to message #219886] Fri, 16 February 2007 10:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yes, I see what the problem, is, and it wouldn't have happened if you were using a straight forwards cursor.

In the middle of your code to build the SQL statement, you have these lines:
sql_text := sql_text ||' AND d.t_num    = dt.t_num(+) ';
sql_text := sql_text ||' union' ;  <= not getting 'D' records
-- get M data
sql_text := 'select ' ;
sql_text := sql_text ||' d.l_num,' ;
sql_text := sql_text ||' d.p_num,';

This one sql_text := 'select ' ; is completely overwriting all of the code before it that you used to create a cursor to get the 'D' records - your SQL is only half the size you think it is.

As a rule of thumb, don't use NDS unless you've tried everything else first.

I reckon you can rewrite your code like this.
(I think you want to get rid of the ROWNUM < 16 - it's almost certainly not doing what you think it is.)
(I've removed that pointless 15 iteration loop as well):
declare
currentval NUMBER := startval;
v_tnum varchar2(27) := padtaxnum(:usr_string); <= not sure if ok
type rc is ref cursor;
v_rc rc;
begin
open :v_rc for 
select  d.l_num,
        d.p_num,
        trunc(r.t_date) r_date,
        dl.type rec_type,
        nvl(dt.c_amt, 0) amount
 FROM   d_tran d,' ;
        ttable tl,
        rcpt r,
        docl dl,
        d_tran dt
 WHERE tl.t_num    = v_tnum
 AND   d.l_num LIKE 'D%' 
 AND   d.l_num     = tl.l_num 
 AND   d.p_num     = tl.p_num 
 AND   d.rcpt_num  = r.rcpt_num 
 AND   d.d_id      = dl.d_id 
 AND   d.rcpt_num  = dt.rcpt_num(+)
 AND   d.t_num     = dt.t_num(+) 
 union  -- not getting 'D' records
select  d.l_num,
        d.p_num,
        trunc(r.t_date) r_date,
        dl.type rec_type,
        nvl(mt.m_amt, 0) amount,
 FROM   d_tran d,
        ttable tl,
        rcpt r,
        doc dl,
        m_tran mt
 WHERE tl.t_num     = v_tnum
 AND   d.l_num LIKE 'M%'
 AND   d.l_num      = tl.l_num 
 AND   d.p_num      = tl.p_num 
 AND   d.rcpt_num   = r.rcpt_num 
 AND   d.d_id       = dl.d_id 
 AND   d.rcpt_num   = mt.rcpt_num(+)
 AND   d.trans_num  = mt.t_num(+) 
 AND   rownum < 16
 order by r_date desc

end;
Re: NO ROWS SELECTED when using BIND VARIABLE [message #219907 is a reply to message #219888] Fri, 16 February 2007 11:32 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Your way is much simpler that what I concocted earlier. However I am still getting no rows selected w/o the hard coded value:

I made the following change and below is why:

v_tnum varchar2(27) := exec converttnum; -- won't compile

I get this error:

SQL> /
v_tnum varchar2(27) := exec convertnum; -- will this work?
*
ERROR at line 3:
ORA-06550: line 3, column 29:
PLS-00103: Encountered the symbol "CONVERTNUM" when expecting one of the following:
. ( * @ % & = - + ; < / > at in is mod not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like
between ||
The symbol "." was substituted for "CONVERTTNUM" to continue.

The reason is I have the above procedure which calls padtaxnum function instead since I need to be prompted for input. here is the procedure:

create or replace procedure converttnum

IS
v_tnum ttable.t_number%type;
sql_str varchar2(100);
begin
sql_str := 'select distinct padtaxnum(''&usr_string'') FROM ttable';
run; -- I forget to add this line in my proc
end;
I get the following output:
but only when I took these steps not by calling the procedure:

SQL> accept usr_string char prompt " Enter The ID Number : "
Enter The ID Number : 0900141000200037000
SQL> select distinct padtaxnum(&usr_string) from ttable;
old 1: select distinct padtaxnum(&usr_string) from ttable
new 1: select distinct padtaxnum(0900141000200037000) from ttable

PADTAXNUM('0900141000200037000')
------------------------------------
0900-141.02.00-037.000 -- this is perfect I need that in v_tnum
and it'll all be dandy...

I went ahead and did the following and here the result:

SQL> exec convertnum;

PL/SQL procedure successfully completed.

SQL> run
1* select distinct padtaxnum('&usr_string') from ttable
old 1: select distinct padtaxnum('&usr_string') from tax_map_liber_pages
new 1: select distinct padtaxnum('0900141000200037000') from ttable

PADTAXNUM('0900141000200037000')
--------------------------------------------------------------------------------------
0900-141.02.00-037.000

Thanks for all you help.

P.S.
You wrote: "I think you want to get rid of the ROWNUM < 16 - it's almost certainly not doing what you think it is.)".

But I need to return 15 rows (records) or less in all user search.
I was able to achieve that successfully in straight SQL+ qry.

[Updated on: Fri, 16 February 2007 13:20]

Report message to a moderator

Re: NO ROWS SELECTED when using BIND VARIABLE [message #219954 is a reply to message #219907] Fri, 16 February 2007 23:34 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
v_tnum varchar2(27) := exec converttnum; -- won't compile

EXEC[UTE] is a SQL*Plus command and so it cannot be used in PL/SQL block.
If convertnum would be a stored function, you will get its value calling
Quote:
v_tnum varchar2(27) := convertnum;

However it is PROCEDURE
create or replace procedure converttnum IS
v_tnum ttable.t_number%type;
sql_str varchar2(100);
begin
sql_str := 'select distinct padtaxnum(''&usr_string'') FROM ttable';
run; -- I forget to add this line in my proc
end;

Very strange. You assign SQL_STR a value and call SQL*Plus RUN command (which should throw the same error you are complaining above). Or is RUN another of your stored procedures (not clever name for it)? In my opinion it is totally useless.

By the way are you aware that getting result
SQL> select distinct padtaxnum(&usr_string) from ttable;
does only assure ttable has ANY row and does not say anything about its content. So it MAY contain some column containing padtaxnum(&usr_string) as well as MAY NOT. In addition you inner join the TTABLE with another three tables. How are you sure the row with desired T_NUM exists in TTABLE and has corresponding records in D_TRAN, RCPT and DOCL?

By the way what do you want to achieve by this?
select  <...>,
        nvl(dt.c_amt, 0) amount
 FROM   d_tran d,
        <...>,
        d_tran dt
 WHERE <...>
 AND   d.rcpt_num  = dt.rcpt_num(+)
 AND   d.t_num     = dt.t_num(+) 

except of extending result set when duplicate (RCPT_NUM, T_NUM) in D_TRAN? Or is it just a typo as in second select you use M_TRAN mt instead?

So my suggestion is:
assign a desired value to USR_STRING VARIABLE
check if the table contains your value by
select COUNT(*) from TTABLE where T_NUM = padtaxnum(:usr_string);

take the JRowBottom's solution and PRINT its result
if it does not show what you expect try to simplify the queries (comment joins) and run it till it returns anything
if you are in trouble print your EXACT output (eg. i picked CONVERTNUM although sometimes you used CONVERTTNUM) and use [CODE] tags for it
Re: NO ROWS SELECTED when using BIND VARIABLE [message #219985 is a reply to message #219794] Sat, 17 February 2007 05:24 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
JRowbottom wrote on Fri, 16 February 2007 09:07
3) Why are you using dynamic Sql? There is nothing in that query that requires the use of dynamic Sql. You're just making things harder for yourself.

He was aked that repeatedly on his other thread. He said it was "for obvious reasons: bind var and usr interactive." I asked him what on Earth those "obvious" things were supposed to mean and he started a new thread. We can keep asking but I don't think we're going to get a straight answer.

JRowbottom wrote on Fri, 16 February 2007 09:07
4) Stop using the LONG data type. It was obsolete in Oracle 8i. Use Clob, or just a Varchar2(32767) (which will be as long as a LONG in pl/sql)

The SQL LONG datatype is obsolete, but in PL/SQL it's just a shortcut for VARCHAR2(32760) so I don't really see anything wrong with it. The manual does appear a bit confused about the two separate things though, possibly because they have the same name.
Previous Topic: How we will call another procedure within procedure??
Next Topic: OUTER UNION CORR
Goto Forum:
  


Current Time: Thu Dec 08 16:31:24 CST 2016

Total time taken to generate the page: 0.25243 seconds