Well, yes - those "translators" don't always do what they are supposed to.
This is how your code should look like:
- use
like, not=in thewhereclause - in PL/SQL, you have to put the result of the
selectstatementintosomething - for example, locally declared variables (as my example shows).
So:
DECLARE
v_SearchObj VARCHAR2 (100) := '%aldbrough%';
--
v_obj_id t_object.obj_id%TYPE;
v_name t_object.name%TYPE;
v_description t_object.description%TYPE;
BEGIN
SELECT obj_id, NAME, DESCRIPTION
INTO v_obj_id, v_name, v_description
FROM agnis.t_object
WHERE LOWER (NAME) LIKE v_searchobj;
END;
If such a code returns an error - too_many_rows (and yes, it does), then one option is to loop through rows and do something (such as display those values):
DECLARE
v_SearchObj VARCHAR2 (100) := '%aldbrough%';
BEGIN
FOR cur_r IN (SELECT obj_id, NAME, DESCRIPTION
FROM agnis.t_object
WHERE LOWER (NAME) LIKE v_searchobj)
LOOP
DBMS_OUTPUT.put_line (
'Name = ' || cur_r.name || ', description = ' || cur_r.description);
END LOOP;
END;
Ok I see that I have to declare a variable for each data item returned from my query but yes, in this case, using your good code is giving me an error for returning multiple rows
Exactly, just like @APC has said. I added some more code which shows how you might "fix" that, but - the final result depends on what you want to do. This just displays values in a tool that supports DBMS_OUTPUT (such as SQLPlus or SQL Developer).
I am now using this code DECLARE v_SearchObj VARCHAR2 (100) := '%aldbrough%'; TYPE v_obj_id IS TABLE OF NUMBER; TYPE v_name IS TABLE OF VARCHAR(200); TYPE v_description IS TABLE OF VARCHAR(200); BEGIN SELECT obj_id, NAME, DESCRIPTION BULK COLLECT INTO v_obj_id, v_name, v_description FROM agnis.t_object WHERE LOWER (NAME) LIKE v_searchobj; END; but I get this error Error report - ORA-06550: line 9, column 24: PLS-00321: expression 'V_OBJ_ID' is inappropriate as the left hand side of an assignment statement
is it possible to format code in these comments ?
You can't format code in comments; it is better to edit the original question you posted. Anyway, you can't bulk collect 3 columns into 3 arrays. Create one array which will contain all 3 columns.