find a string in atble [message #393645] |
Tue, 24 March 2009 00:10  |
phk.chaitanya
Messages: 7 Registered: March 2009
|
Junior Member |
|
|
Hai I am new pl/sql. this is my first program. I want to find a string in table. This is the program i tried to write. I got the following error.
Any help is appreciated.
CREATE PROCEDURE findStringInTable (stringToFind VARCHAR2)
IS
sqlCommand VARCHAR2(8000);
wCommand VARCHAR2(8000);
columnName VARCHAR2(30);
CURSOR MATCH IS SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'PPROFILES';
BEGIN
sqlCommand := 'SELECT * FROM PPROFILES WHERE';
wCommand := '';
OPEN MATCH;
FETCH MATCH INTO columnName;
WHILE MATCH%FOUND
LOOP
IF wCommand != '' THEN
wCommand := wCommand + ' OR';
END IF;
wCommand := wCommand + ' ' + columnName + ' LIKE ''' +stringToFind + '''';
FETCH MATCH INTO columnName;
END LOOP;
CLOSE MATCH;
sqlCommand := sqlCommand + wCommand;
EXECUTE IMMEDIATE (sqlCommand);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('No Data found');
END;
PL/SQL: numeric or value error: character to number conversion error
at "SCOTT.FINDSTRINGINTABLE", line 20
|
|
|
|
Re: find a string in atble [message #393649 is a reply to message #393646] |
Tue, 24 March 2009 00:19   |
phk.chaitanya
Messages: 7 Registered: March 2009
|
Junior Member |
|
|
The error is
PL/SQL: numeric or value error: character to number conversion error at "SCOTT.FINDSTRINGINTABLE", line 20
line 20 is wCommand := wCommand + ' ' + columnName + ' LIKE ''' +stringToFind + '''';
|
|
|
|
|
|
|
|
|
|
|
|
Re: find a string in atble [message #393685 is a reply to message #393645] |
Tue, 24 March 2009 01:43   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
What BlackSwan has been hinting about is to display the string that is executed as a query prior to the actual execution (sort of System.out.println or logger.debug)
To do this, use dbms_output.put_line(your_string)
Note that put_line can only handle strings <= 255 characters. If your string is larger, break it in portions.
In order to see the output, call your procedure from sqlplus, after you give the command
SET SERVEROUTPUT ON SIZE 1000000
Post back your results.
|
|
|
Re: find a string in atble [message #393726 is a reply to message #393649] |
Tue, 24 March 2009 03:17  |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
phk.chaitanya wrote on Tue, 24 March 2009 06:19 | The error is
PL/SQL: numeric or value error: character to number conversion error at "SCOTT.FINDSTRINGINTABLE", line 20
line 20 is wCommand := wCommand + ' ' + columnName + ' LIKE ''' +stringToFind + '''';
|
wCommand is declared as VARCHAR2(8000). In Oracle SQL, || is used as string concatenation operator, not +. So Oracle expects the expression as numeric addition and it fails to convert its part into numbers. There may be other errors though.
phk.chaitanya wrote on Tue, 24 March 2009 06:28 | i am java developer and I am asked to write a stored procedure. This is my first program in pl/sql. please bear with me
|
It is strange your company is using Oracle without having any Oracle developer. Good luck.
phk.chaitanya wrote on Tue, 24 March 2009 06:31 | later i modified it with probable pl/sql syntax
|
Maybe you shall use correct Oracle syntax instead of guessing. It is described SQL Reference and PL/SQL User's Guide and Reference. Both books are part of Oracle documentation, available e.g. online on http://tahiti.oracle.com/.
[Edit: Maybe it is possible in other DB to use + operator concatenate strings, but definitely not in Oracle. I put it more precisely in the first paragraph.]
[Updated on: Tue, 24 March 2009 03:31] Report message to a moderator
|
|
|