Home » SQL & PL/SQL » SQL & PL/SQL » Where like variable for varchar2 (10g)
Where like variable for varchar2 [message #405935] Mon, 01 June 2009 00:40 Go to next message
McLan
Messages: 36
Registered: April 2008
Member
I have the issue with the below procedure.
Unable to compile successfully for where clause for string.

Below is the error:
ORA-01722: invalid number
ORA-06512: at GET_STARTING_BATCH, line 9


Table description:
sql>DESC BATCH_INFO
BATCH_NUM	VARCHAR2(6);
SOURCE	VARCHAR2(15);


code for the function

create or replace FUNCTION get_starting_batch(
	source batch_info.SOURCE%TYPE)
RETURN VARCHAR2
AS
  V_batch_num batch_info.SOURCE%TYPE;
BEGIN
	SELECT MIN(bi.batch_num) into v_batch_num
	FROM batch_info bi
	WHERE
	 bi.source like @source+'%';
	RETURN v_batch_num.
END get_starting_batch;
/


Alos tried with below :

like &source&i
like '@source'+'%';
but of no use.
Re: Where like variable for varchar2 [message #405938 is a reply to message #405935] Mon, 01 June 2009 01:06 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Syntax is
like source||'%'
or
like concat(source,'%')

It is basic elements of Oracle SQL, please read/search in SQL Reference before posting.

Regards
Michel

[Updated on: Mon, 01 June 2009 01:07]

Report message to a moderator

Previous Topic: spool output to a file in dbms_job.submit
Next Topic: How to find the trace user activity like logs in pl/sql ?
Goto Forum:
  


Current Time: Mon Dec 05 10:43:18 CST 2016

Total time taken to generate the page: 0.05002 seconds