Home » SQL & PL/SQL » SQL & PL/SQL » find a string in atble (Oracle SQl)
find a string in atble [message #393645] Tue, 24 March 2009 00:10 Go to next message
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 #393646 is a reply to message #393645] Tue, 24 March 2009 00:13 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>I got the following error.
ERROR? What Error. I do not see any error.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: find a string in atble [message #393649 is a reply to message #393646] Tue, 24 March 2009 00:19 Go to previous messageGo to next message
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 #393652 is a reply to message #393646] Tue, 24 March 2009 00:20 Go to previous messageGo to next message
phk.chaitanya
Messages: 7
Registered: March 2009
Junior Member
Can anybody write a stored procedure to find a string in a table. please

any help is appreciated
Re: find a string in atble [message #393655 is a reply to message #393645] Tue, 24 March 2009 00:26 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
What is the actual command being executed at line 20?

>Can anybody write a stored procedure to find a string in a table.
I can & I am sure others can also.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: find a string in atble [message #393656 is a reply to message #393655] Tue, 24 March 2009 00:27 Go to previous messageGo to next message
phk.chaitanya
Messages: 7
Registered: March 2009
Junior Member
wCommand := wCommand + ' ' + columnName + ' LIKE ''' +stringToFind + '''';
Re: find a string in atble [message #393659 is a reply to message #393645] Tue, 24 March 2009 00:28 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>wCommand := wCommand + ' ' + columnName + ' LIKE ''' +stringToFind + '''';
Is NOT a valid SQL statement!
What is the resultant string in "wCommand"?

[Updated on: Tue, 24 March 2009 00:29]

Report message to a moderator

Re: find a string in atble [message #393660 is a reply to message #393655] Tue, 24 March 2009 00:28 Go to previous messageGo to next message
phk.chaitanya
Messages: 7
Registered: March 2009
Junior Member
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
Re: find a string in atble [message #393661 is a reply to message #393659] Tue, 24 March 2009 00:29 Go to previous messageGo to next message
phk.chaitanya
Messages: 7
Registered: March 2009
Junior Member
Can you fix the error.
Re: find a string in atble [message #393662 is a reply to message #393659] Tue, 24 March 2009 00:31 Go to previous messageGo to next message
phk.chaitanya
Messages: 7
Registered: March 2009
Junior Member
To be frank I got this program on this site.

http://www.mssqltips.com/tip.asp?tip=1522

later i modified it with probable pl/sql syntax
Re: find a string in atble [message #393664 is a reply to message #393645] Tue, 24 March 2009 00:36 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
On a scale on 1 (ignorant) to 10 (expert)
how do you rate your expertise on SQL and PL/SQL?
Re: find a string in atble [message #393665 is a reply to message #393645] Tue, 24 March 2009 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Go to AskTom site and search for "search_string" function.

Regards
Michel
Re: find a string in atble [message #393685 is a reply to message #393645] Tue, 24 March 2009 01:43 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous message
flyboy
Messages: 1832
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

Previous Topic: difference between 'H' and '''H'''
Next Topic: how to get the column value of previous row to current row? (merged 3)
Goto Forum:
  


Current Time: Tue Dec 06 16:22:47 CST 2016

Total time taken to generate the page: 0.07811 seconds