Hi

I am completely new to Oracle PL/SQL. I have a simple question, probably someone here can answer.

I need to execute two SQL queries which look like :

SELECT userID FROM USER where name like '%John%';

SELECT birthdate from USERDETAILS where userID IN (a,b,c,d...);

The values a,b,c,d etc should be got from the first query. I do not want to do a join on the two tables and the process should be done in two seperate queries(strictly).. So, basically, I need to execute the first one, store the results in an array in PL/SQL and then use the array to run the second query. Searched on the internet for examples, but cudn't find any. Can anyone please give me an outline? First of all, Can an array be used as condition in a SQL statement? If so, How?

Thanks in advance
-Chinna

Oracle supports the neeto subquery syntax so you can combine the queries, with no joins, like this

SELECT birthdate from USERDETAILS
where userID IN (SELECT userID FROM USER where name like '%John%');

The other option is to use a temporary table, and do a join on that table. I'm not sure I understand why you don't want to use a join. Otherwise, you'll want to create a stored procedure... this would look something like this :

CREATE OR REPLACE
PROCEDURE get_birthdate (name_match IN VARCHAR(255)) IS
Emp_id NUMBER;
birthdate VARCHAR(255);
CURSOR c1 (match VARCHAR(255)) IS
SELECT id FROM users
WHERE name like match;
CURSOR c2 (birthdate VARCHAR(255)) IS
SELECT birthdate FROM userDetails
WHERE id=Emp_id;

BEGIN
OPEN c1(name_match);
LOOP
FETCH c1 INTO Emp_id;
OPEN c2(Emp_id);
LOOP
FETCH c2 INTO birthdate;
DBMS_OUTPUT.PUT_LINE(birthdate);
EXIT WHEN C2%NOTFOUND
END LOOP
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE c1;
END;

Then you would make a call like select get_birthdate('%John%');

Hope that helps

Chris
Come see the BiteSize SQL Tutorial
http://www.bitesizeinc.net