I have two arrays where I have stored some valid values for a table and I'm passing them in a SELECT statement. I'm doing it so that array a and b values enter where condition at correspondingly.
I am using EXECUTE IMMEDIATE to print the SELECT statement, I have tried inserting the value of the SELECT statement in a variable and printing it via dbms_output.put_line but it gave an error.
And my code doesn't print anything, it just shows procedure completed.
DECLARE
dest temp_1.destination%type;
type arr1 IS VARRAY(4) OF VARCHAR2(50);
sd arr1;
type arr2 IS VARRAY(4) OF VARCHAR2(50);
sid1 arr2;
total integer;
BEGIN
sd := street_directional('a','b','c','d');
sid1 := street_direction('1','2','3','4');
total := sd.count;
FOR i in 1 .. total LOOP
execute immediate q'[select destination from temp_1 where cond1 =(:var1) and cond2 = (:var2) ]' using sd(i),sid1(i);
END LOOP;
END;
This doesn't give any output, it just shows the procedure mybpcreditcard complete.
I tried doing this:
execute immediate q'[select destination from temp_1 where cond1 =(:var1) and cond2 = (:var2) into dest]' using sd(i),sid1(i);
return dest;
-- or printline
dbms_output.put_line(dest);
but it gave an error. I have kept the serveroutput on and I'm running this on oracle engine.
If there is some easy way of getting the result other than using PL/SQL, please let me know.