Home » SQL & PL/SQL » SQL & PL/SQL » SP2-0552 Bind variable not declared ERROR
SP2-0552 Bind variable not declared ERROR [message #213894] Fri, 12 January 2007 12:54 Go to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
I need help urgently. Please!!!!


SET SERVEROUTPUT ON;
ACCEPT nTaxNum varchar2(19) Prompt 'Please Enter Tax Map Number: '
DECLARE
TaxNum varchar2(19);
exec :TaxNum := '&nTaxNum'
select substr(x, 1, 4)
||'-'||substr(x,5,3)
||'.'||substr(x,8,2)
||'-'||substr(x,10,2)
||'.'||substr(x,12,2)
||'-'||substr(x,14,3)
||'.'||substr(x,17) -- code never get here
from (select mtl.tax_number x from tax_table)
where :TaxNum = mtl.taxnumber
-- call function to qry database after conversion
-- for loop to get records
/

I am getting this:
SQL> /
Enter value for ntaxmapnum: 0600108000300010001
old 5: exec :TaxNum := '&nTaxNum'
new 5: exec :TaxNum := '0600108000300010001'
SP2-0552: Bind variable "TAXNUM" not declared.
Re: SP2-0552 Bind variable not declared ERROR [message #213898 is a reply to message #213894] Fri, 12 January 2007 12:58 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
It is NOT urgent to me.
Where ever did you come up with this mish/mash of SQL*Plus & PL/SQL syntax?
Visit http://asktom.oracle.com for many fine coding example.
Re: SP2-0552 Bind variable not declared ERROR [message #213907 is a reply to message #213898] Fri, 12 January 2007 13:56 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Sorry for the mismatch I am new at this bear w/ me here:

I did look at your link. Quite helpfull! I came up w/ this:

CREATE OR REPLACE FUNCTION PadTaxNum (u_string IN VARCHAR2) -- usr string
RETURN VARCHAR2
IS
ntaxnum VARCHAR2(25)
:= '&ntaxnum'
substr(x, 1, 4)
||'-'||substr(x,5,3)
||'.'||substr(x,8,2)
||'-'||substr(x,10,2)
||'.'||substr(x,12,2)
||'-'||substr(x,14,3)
||'.'||substr(x,17) x
BEGIN
-- my main qry
RETURN

END;
/
I get a warning: function created w/ compilation errors.

Any idea will be greatly appreciated. Thks!
Re: SP2-0552 Bind variable not declared ERROR [message #213908 is a reply to message #213894] Fri, 12 January 2007 14:07 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
SQL> SHOW ERROR
Re: SP2-0552 Bind variable not declared ERROR [message #213911 is a reply to message #213908] Fri, 12 January 2007 14:17 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
SQL> /
Enter value for ntaxnum: 0600108000300010001
old 5: := '&ntaxnum'
new 5: := '0600108000300010001' -- no conversion

Errors for FUNCTION PADTAXNUM:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/14 PLS-00103: Encountered the symbol "(" when expecting one of the
following:
constant exception <an identifier>
<a double-quoted delimited-identifier> table LONG_ double ref
char time timestamp interval date binary national character
nchar
The symbol "<an identifier>" was substituted for "(" to continue.


13/33 PLS-00103: Encountered the symbol "X" 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 "X" to continue.

17/3 PLS-00103: Encountered the symbol "END" when expecting one of the
following:
( - + ; case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
The symbol ";" was substituted for "END" to continue.

Thks in advance!
Re: SP2-0552 Bind variable not declared ERROR [message #213917 is a reply to message #213911] Fri, 12 January 2007 14:27 Go to previous messageGo to next message
Littlefoot
Messages: 19526
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I admit, I didn't figure out what would be the result of this function, but here it is - it compiles and, actually, returns something. See if you can use it.
SQL> CREATE OR REPLACE FUNCTION PadTaxNum (x IN VARCHAR2) -- usr string
  2    RETURN VARCHAR2
  3    IS
  4  BEGIN
  5    RETURN
  6           SUBSTR(x, 1, 4)
  7    ||'-'||SUBSTR(x,5,3)
  8    ||'.'||SUBSTR(x,8,2)
  9    ||'-'||SUBSTR(x,10,2)
 10    ||'.'||SUBSTR(x,12,2)
 11    ||'-'||SUBSTR(x,14,3)
 12    ||'.'||SUBSTR(x,17);
 13  END;
 14  /

Function created.

SQL> SELECT padtaxnum('My name is Littlefoot') FROM dual;

PADTAXNUM('MYNAMEISLITTLEFOOT')
---------------------------------------------------------------------------

My n-ame. i-s .Li-ttl.efoot

SQL>
Re: SP2-0552 Bind variable not declared ERROR [message #213918 is a reply to message #213894] Fri, 12 January 2007 14:27 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
1) Why is a simple PL/SQL syntax question posted to a SQL Expert forum?
2) If you can't write correct syntax on your own, maybe you should consider dropping this class.
3) The Fine PL/SQL Reference manual can be found at http://tahiti.oracle.com
Re: SP2-0552 Bind variable not declared ERROR [message #213923 is a reply to message #213917] Fri, 12 January 2007 14:41 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Thanks. But it is imperative that I use the bind variable which is what I will be qrying later it may look like this:

select s,d,d,f, from t1, t2, t3, t4
where column1 IN (select column1 from tablex
where column1 := bind variable)
and all my joins etc...
very tricky and complex qry too!

But thanks for your help.
Re: SP2-0552 Bind variable not declared ERROR [message #213926 is a reply to message #213923] Fri, 12 January 2007 15:06 Go to previous messageGo to next message
Littlefoot
Messages: 19526
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you'd better start with something more simple; if this is to be a very tricky and complex query, errors you make at this level of knowledge promises only tears, blood and sweat. Not that it is impossible, but - baby-steps could be solution for you; first learn SQL, then advance to PL/SQL.
Re: SP2-0552 Bind variable not declared ERROR [message #214489 is a reply to message #213926] Tue, 16 January 2007 13:26 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
I finally got it to work btu no conversion took place. I am now sure what am I missing?


Thks!

SQL> ed
Wrote file afiedt.buf

1 CREATE OR REPLACE FUNCTION PadTaxNum (u_string IN VARCHAR2) -- usr string
2 RETURN VARCHAR2
3 IS
4 ntaxnum VARCHAR2(25) := '&u_string';
5 BEGIN
6 RETURN ntaxnum;
7 ntaxnum := SUBSTR(u_string, 1, 4)
8 ||'-'||SUBSTR(u_string, 5,3)
9 ||'.'||SUBSTR(u_string, 8,2)
10 ||'-'||SUBSTR(u_string, 10,2)
11 ||'.'||SUBSTR(u_string, 12,2)
12 ||'-'||SUBSTR(u_string, 14,3)
13 ||'.'||SUBSTR(u_string, 17);
14* END;
SQL> /
Enter value for u_string: 0600108000300010001
old 4: ntaxnum VARCHAR2(25) := '&u_string';
new 4: ntaxnum VARCHAR2(25) := '0600108000300010001';

Function created.

SQL> select padtaxnum('ntaxnum') from ttable where rownum <= 15;

PADTAXNUM('NTAXNUM')
--------------------------------------------------------------------------------
0600108000300010001
0600108000300010001
0600108000300010001
0600108000300010001
0600108000300010001
0600108000300010001
0600108000300010001
0600108000300010001
0600108000300010001
0600108000300010001
0600108000300010001

PADTAXNUM('NTAXNUM')
--------------------------------------------------------------------------------
0600108000300010001
0600108000300010001
0600108000300010001
0600108000300010001

15 rows selected.
Re: SP2-0552 Bind variable not declared ERROR [message #215725 is a reply to message #213894] Tue, 23 January 2007 12:06 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Sorry to have to rehash this one. But do you have any idea please! I am missing a crucial piece of that puzzle and I am not sure where to go from here.

Thks in advance!
Re: SP2-0552 Bind variable not declared ERROR [message #215751 is a reply to message #215725] Tue, 23 January 2007 15:11 Go to previous messageGo to next message
Littlefoot
Messages: 19526
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm sorry, but I still have no idea what is the result you expect.
FUNCTION COMPLIED OK BUT RESULT NOT. [message #215761 is a reply to message #215751] Tue, 23 January 2007 16:19 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Sorry about that. Here we go.
After padding the user input varchar(19)
the following funtion added the '.'. '-' etc...5 in total
SUBSTR(x, 1, 4)
||'-'||SUBSTR(x,5,3)
||'.'||SUBSTR(x,8,2)
||'-'||SUBSTR(x,10,2)
||'.'||SUBSTR(x,12,2)
||'-'||SUBSTR(x,14,3)
||'.'||SUBSTR(x,17);
...
The output becomes a varchar2(25)
SQL> select padtaxnum('ntaxnum') from ttable where rownum <= 15;
is supposed to give the following:

PADTAXNUM('NTAXNUM')
-----------------------------------------------------------------0600-108.00-03.00-010.001
0600-108.00-03.00-010.001
0600-108.00-03.00-010.001
0600-108.00-03.00-010.001
0600-108.00-03.00-010.001
0600-108.00-03.00-010.001
0600-108.00-03.00-010.001
0600-108.00-03.00-010.001
0600-108.00-03.00-010.001
0600-108.00-03.00-010.001
0600-108.00-03.00-010.001
0600-108.00-03.00-010.001
0600-108.00-03.00-010.001
0600-108.00-03.00-010.001
... 15 total

Thanks!
Re: SP2-0552 Bind variable not declared ERROR [message #215768 is a reply to message #213894] Tue, 23 January 2007 18:18 Go to previous messageGo to next message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
Quote:
1 CREATE OR REPLACE FUNCTION PadTaxNum (u_string IN VARCHAR2) -- usr string
2 RETURN VARCHAR2
3 IS
4 ntaxnum VARCHAR2(25) := '&u_string';
5 BEGIN
6 RETURN ntaxnum;
7 ntaxnum := SUBSTR(u_string, 1, 4)
8 ||'-'||SUBSTR(u_string, 5,3)
9 ||'.'||SUBSTR(u_string, 8,2)
10 ||'-'||SUBSTR(u_string, 10,2)
11 ||'.'||SUBSTR(u_string, 12,2)
12 ||'-'||SUBSTR(u_string, 14,3)
13 ||'.'||SUBSTR(u_string, 17);
14* END;
SQL> /

So your function has a parameter u_string, which is unused.
Then a local variable ntaxnum which is assigned a pl/sql variable u_string (if not defined, then asked for it and directly substituted in the function).
You directly return this value. So the calculation you are doing after the RETURN is never proceeded.
You call this function with the string constant 'ntaxnum' (however the function parameter is not used, so it does not matter).
Maybe you want to do this:
1 CREATE OR REPLACE FUNCTION PadTaxNum (u_string IN VARCHAR2) -- usr string
RETURN VARCHAR2
IS
BEGIN
RETURN SUBSTR(u_string, 1, 4)
||'-'||SUBSTR(u_string, 5,3)
||'.'||SUBSTR(u_string, 8,2)
||'-'||SUBSTR(u_string, 10,2)
||'.'||SUBSTR(u_string, 12,2)
||'-'||SUBSTR(u_string, 14,3)
||'.'||SUBSTR(u_string, 17);
END;
/

select padtaxnum(ntaxnum) from ttable where rownum <= 15;

This function has a parameter u_string, which is changed in the desired way and returned as a result.
You call this function with the ntaxnum column value.

Check the differences of the results.
Re: SP2-0552 Bind variable not declared ERROR [message #215976 is a reply to message #215768] Wed, 24 January 2007 08:47 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Thanks for the explanation.
I have 2 questions:
1. did you ommit this line of code on purpose:
ntaxnum VARCHAR2(25) := '&u_string';
if so why? I need that user input to Qry data base with. Therefore, the stored value, is what I use when I call this function. Hence, "select padtaxnum('ntaxnum') from ttable where rownum <= 15;" The parameter is what I need to qry w/.

2. Either way I tested it and got "ntaxnum" : invalid identifier. The same is true for "u_string"

I hope I was clear enough.

Best Regards & Thanks,
Re: SP2-0552 Bind variable not declared ERROR [message #216030 is a reply to message #215976] Wed, 24 January 2007 13:53 Go to previous messageGo to next message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
You did not stated your requirements clearly, so I was just modifying the pl/sql code you provided. But after your last post I am in bigger doubts what you want to achieve.

1. Seems strange to me. This query and substitution is called when you create/replace the function padtaxnum. You should not do it in production. Rather add second parameter to the function and substitute u_string with this new parameter; if you want to ask user for its value in sqlplus, use ACCEPT command, eg.
accept u_string char prompt "Enter the value: "
select padtaxnum('ntaxnum', :u_string) from ttable where rownum <= 15;

2. I have thought ntaxnum is the column name. I do not have any idea, how the VARCHAR2 constant 'ntaxnum' should be used in the padtaxnum function. It writes up to 15 rows (as ttable can have less rows than 15) with the SAME value. Does not make any sense to me.
Re: SP2-0552 Bind variable not declared ERROR [message #216034 is a reply to message #216030] Wed, 24 January 2007 14:48 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Here is what I've done in sqlPlus several weeks back:

select substr(x, 1, 4) -- here is where padding is done
||'-'||substr(x,5,3)
||'.'||substr(x,8,2)
||'-'||substr(x,10,2)
||'.'||substr(x,12,2)
||'-'||substr(x,14,3)
||'.'||substr(x,17) Tax_Number
from (select '&t_number' x from ttable)
where rownum <=15

SQL> /
Enter value for t_number: 1000109000700005001
old 8: from (select '&t_number' x from ttable)
new 8: from (select '1000109000700005001' x from ttable)

TAX_NUMBER
-------------------------
1000-109.00-07.00-005.001 -- note the result after padding
1000-109.00-07.00-005.001 -- hope to store 15 or less in var
1000-109.00-07.00-005.001
1000-109.00-07.00-005.001
1000-109.00-07.00-005.001
...

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'TTable'

I wanted to use a function instead to make it more efficient.
Note 2 things:
The number entered by usr will come from an other app as a variable. This value is entered as 19 varchar after padding this number is 25 varchar2. I wanted to use a bind variable where the stored padded # will be hence the parameter in question: PADTAXNUM('NTAXNUM')
After I succesfully created the function and executed it here is the result: there is no error, but I did not get the desired result that I got using sql plus:

Here it is again:

SQL> ed
Wrote file afiedt.buf

1 CREATE OR REPLACE FUNCTION PadTaxNum (u_string IN VARCHAR2) -- usr string
2 RETURN VARCHAR2
3 IS
4 ntaxnum VARCHAR2(25) := '&u_string';
5 BEGIN
6 RETURN ntaxnum;
7 ntaxnum := SUBSTR(u_string, 1, 4) -- code below did not exec
8 ||'-'||SUBSTR(u_string, 5,3)
9 ||'.'||SUBSTR(u_string, 8,2)
10 ||'-'||SUBSTR(u_string, 10,2)
11 ||'.'||SUBSTR(u_string, 12,2)
12 ||'-'||SUBSTR(u_string, 14,3)
13 ||'.'||SUBSTR(u_string, 17);
14* END;
SQL> /
Enter value for u_string: 0600108000300010001
old 4: ntaxnum VARCHAR2(25) := '&u_string'; -- wrong s/b 19
new 4: ntaxnum VARCHAR2(25) := '0600108000300010001'; -- s/b 25

Function created.

SQL> select padtaxnum('ntaxnum') from ttable where rownum <= 15;

PADTAXNUM('NTAXNUM')
--------------------------------------------------------------------------------
0600108000300010001 -- note there is no padding, I need # padded
0600108000300010001
0600108000300010001
...

I am sorry for all the confusion. I hope I made it clear for you.
Thanks again for all your patience.

Re: SP2-0552 Bind variable not declared ERROR [message #216036 is a reply to message #213894] Wed, 24 January 2007 15:00 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
>7 ntaxnum := SUBSTR(u_string, 1, 4) -- code below did not exec
Of course it did NOT execute because RETURN from this function in LINE#6 immediately preceeding this line.
Re: SP2-0552 Bind variable not declared ERROR [message #216041 is a reply to message #216036] Wed, 24 January 2007 16:23 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Anacedent,

So close yet soooo far. That was pretty good. I just need to debug why data appears to be corrupted.
Here is what the latest change yields:
SQL> r
1 CREATE OR REPLACE FUNCTION PadTaxNum (u_string IN VARCHAR2)
2 RETURN VARCHAR2
3 IS
4 ntaxnum VARCHAR2(25):= '&u_string';
5 BEGIN
6 -- RETURN ntaxnum;
7 ntaxnum := SUBSTR(u_string, 1, 4)
8 ||'-'||SUBSTR(u_string, 5,3)
9 ||'.'||SUBSTR(u_string, 8,2)
10 ||'-'||SUBSTR(u_string, 10,2)
11 ||'.'||SUBSTR(u_string, 12,2)
12 ||'-'||SUBSTR(u_string, 14,3)
13 ||'.'||SUBSTR(u_string, 17);
14 RETURN ntaxnum;
15* END;
old 4: ntaxnum VARCHAR2(25):= '&u_string';
new 4: ntaxnum VARCHAR2(25):= '0300074000500027000';

Function created.

SQL> select padtaxnum('ntaxnum')from ttable where rownum <=15;

Unfortunatly here is what the output looks like:

PADTAXNUM('NTAXNUM')
-----------------------------------------------------------------
ntax-num.-.-.
ntax-num.-.-.
ntax-num.-.-.
ntax-num.-.-.
ntax-num.-.-.
ntax-num.-.-.
ntax-num.-.-.
ntax-num.-.-.
ntax-num.-.-.
ntax-num.-.-.
ntax-num.-.-.

PADTAXNUM('NTAXNUM')
------------------------------------------------------------------------------
ntax-num.-.-.
ntax-num.-.-.
ntax-num.-.-.
ntax-num.-.-.

15 rows selected.


Many thks though.
Re: SP2-0552 Bind variable not declared ERROR [message #216042 is a reply to message #213894] Wed, 24 January 2007 17:16 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
Rather than just type whatever gibberish you think might work, I suggest actually STUDYING either the fine PL/SQL Reference Manual
(found at http://tahiti.oracle.com) or
do some serious reviewing of functioning code example found at http://asktom.oracle.com.
Rhetorical question - what is the difference between "u_string" in LINE#1 and LINE#4?
You need to realize that SQL*PLUS is NOT the same as PL/SQL.
What is valid in one place is not necessarily valid in the other.

[Updated on: Wed, 24 January 2007 17:26] by Moderator

Report message to a moderator

Re: SP2-0552 Bind variable not declared ERROR [message #216064 is a reply to message #216034] Wed, 24 January 2007 21:50 Go to previous messageGo to next message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
oraclenub2006 wrote on Wed, 24 January 2007 21:48
I wanted to use a function instead to make it more efficient.

Instead of one query requiring user input you will each time create/replace a stored function which stores the entered value and then call it (efficiency bonus).
When I look into your first version, no VARCHAR2 constant 'ntaxnum' neither variable ntaxnum is used. However you call it correct. If so, there is no need for it in the second version.
oraclenub2006 wrote on Wed, 24 January 2007 21:48
The number entered by usr will come from an other app as a variable. This value is entered as 19 varchar after padding this number is 25 varchar2. I wanted to use a bind variable where the stored padded # will be hence the parameter in question: PADTAXNUM('NTAXNUM')

Do you want the stored pl/sql function to take the value of the variable from your sqlplus session (or wherever it is)? It is not possible, you have to get the desired value in your sqlplus session.
-- create a stored function (only once)
CREATE OR REPLACE FUNCTION PadTaxNum (par IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN SUBSTR(par, 1, 4)
||'-'||SUBSTR(par, 5,3)
||'.'||SUBSTR(par, 8,2)
||'-'||SUBSTR(par, 10,2)
||'.'||SUBSTR(par, 12,2)
||'-'||SUBSTR(par, 14,3)
||'.'||SUBSTR(par, 17);
END;
/

-- call user to enter desired value in sqlplus
accept u_string char prompt "Enter the value: "
-- or define its value directly
-- define u_string="0300074000500027000"
-- and call the stored procedure for the desired output
select padtaxnum(&u_string) from ttable where rownum <= 15;

-- if you want to pass it from another function, use a sqlplus bind variable
-- by the way you used this term, where did you get it?
variable u_string char(19)
execute :u_string := <other_app_call>
-- and call the stored procedure for the desired output
select padtaxnum(:u_string) from ttable where rownum <= 15;

Sorry for mixing these two approaches in my previous post (suppose you would complain of the result if you tried it). However if it does not satisfy your needs, start with studying documentation to write your own solution.
anacedent wrote on Thu, 25 January 2007 00:16
Rhetorical question - what is the difference between "u_string" in LINE#1 and LINE#4?
Maybe this causes the misunderstanding. So I add another: what is the difference between ntaxnum in line 4 and 'ntaxnum' passed as parameter to the function?
Re: SP2-0552 Bind variable not declared ERROR [message #216203 is a reply to message #216064] Thu, 25 January 2007 10:03 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Thank you Flyboy for the concise illustration. I know I am driving you (Experts) crazy, but I am in a tough spot here.
I decided to use the simpler approach which yields the result that I am looking for, since the number entered will vary.
Here it is:

SQL> accept usr_string char prompt " Enter T_Number : "
Enter T_Number : 01002150002003000
SQL> select padtaxnum(&usr_string) from ttable
2 where rownum <= 15;
old 1: select padtaxnum(&usr_string) from ttable
new 1: select padtaxnum(01002150002003000) from ttable

PADTAXNUM(01002150002003000)
-----------------------------------------------------------------
1002-150.20.03-000.
1002-150.20.03-000.
...
Technically I only need one padded # at a time. Here comes the hard part. That padded number is what what I will use in my main Sql qry to fetch 15 or less records associated with that number and return them to user.
The Q I have is can I then, in my where clause say:
select...and all my jiberish
where t_num = &usr_string
and ....

You guys have been great and Thnks for you patience.
Re: SP2-0552 Bind variable not declared ERROR [message #216253 is a reply to message #216203] Thu, 25 January 2007 14:29 Go to previous messageGo to next message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
It makes more sense - to filter table by a given value. Hope that 'associated' means equal with a column. If not watch the conditions and choose the proper one.
accept usr_string char prompt " Enter T_Number : "

SELECT <whatever>
FROM <whatever>
WHERE <whatever>
  AND rownum <= 15
  AND t_num = padtaxnum(&usr_string);

It only writes the resultset on the screen. To use it further you should study pl/sql.
Just of curiosity, does the user communicate in sqlplus session, just running scripts? No web application?
Re: SP2-0552 Bind variable not declared ERROR [message #216259 is a reply to message #216253] Thu, 25 January 2007 15:25 Go to previous messageGo to next message
oraclenub2006
Messages: 56
Registered: December 2006
Location: new york
Member
Thanks again flyboy. the answer to Qs are as follows:
1. Yes, associated w/ means it comes out of the t_num column in dbase.
2. the result on the screen is fine after the main qry takes place. The way it works is like an on line banking system except
I wrote the text data which is converted into voice data. Pretty slick uh!
3. The user connect thru odbc and have a legacy app (script) that it proprietary using session initiation protocol. In short
a network protocol layer used in VoIP.
Hope I answered your Qs.

Regards,

P.S. Perhaps I may need to use && w/ my '&&usr_sting' variable like so, since the same content of that var will be used twice. By that I mean I have 2 sets og Qry joined via union all. Based upon your above suggestion I'd have to call that function twice which is not a problem, but I dont want to prompt user twice for that same # within a session. Let me know what you think.
Thks again!
Re: SP2-0552 Bind variable not declared ERROR [message #216290 is a reply to message #216259] Fri, 26 January 2007 03:03 Go to previous message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
oraclenub2006 wrote on Thu, 25 January 2007 22:25
Perhaps I may need to use && w/ my '&&usr_sting' variable like so, since the same content of that var will be used twice. By that I mean I have 2 sets og Qry joined via union all. Based upon your above suggestion I'd have to call that function twice which is not a problem, but I dont want to prompt user twice for that same # within a session. Let me know what you think.

Since you define u_string, it may be used in the sqlplus session as many times as you want until you UNDEFINE it. As you see, no && was used, no need to enter u_string value since it was defined by ACCEPT till UNDEFINE.
SQL> set verify off
SQL> accept u_string char prompt "Enter the value: "
Enter the value: 0300074000500027000
SQL> select 'test '||&u_string col from dual;

COL
-----------------------
test 300074000500027000

SQL> select 'test '||&u_string col from dual union all
  2  select 'test '||&u_string col from dual;

COL
-----------------------
test 300074000500027000
test 300074000500027000

SQL> undefine u_string
SQL> select 'test '||&u_string col from dual;
Zadejte hodnotu pro u_string: 0300074000500027000

COL
-----------------------
test 300074000500027000

SQL> 

Next time try to make these testcases on your own.
Previous Topic: Getting ora-02085 fort Database Link
Next Topic: Last visit on location
Goto Forum:
  


Current Time: Sun Aug 31 05:32:31 CDT 2014

Total time taken to generate the page: 0.09125 seconds