CODEWITHSUNDEEP
sqloracle-databaseplsqldynamic-sql
amar2108
amar2108

Reputation: 323

How to print select statment in a plsql script?

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 procedure 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 im running this on oracle engine.

If there is some easy way of getting the result other than using PL/SQL, please let me know.

Upvotes: 3

Views: 7087

Answers (3)

Alex Poole
Alex Poole

Reputation: 191245

Even with dynamic SQL you need to select into something, which has to be outside the dynamic part of the statement. In fact, with dynamic SQL, if you don't then the query is parsed but not executed. (Other DML and DDL behave differently of course.) Your second block has into dest but inside the query string, which isn't doing anything; and you can't return a value from an anonymous block.

execute immediate q'[select destination from temp_1 where cond1 =(:var1) and cond2 = (:var2)]'
  into dest
  using sd(i),sid1(i); 

dbms_output.put_line(dest);

db<>fiddle

You still need to have set serveroutput on in your client, or equivalent, to see the dest output. And if there is no matching row in temp_1 for any of the pairs of condition values then you'll get a no-data-found exception, so you'll need to decide if that's possible and what you want to do if it happens.

It doesn't look like this really needs to be dynamic - I included a static version in the fiddle using:

select destination into dest from temp_1 where cond1 = sd(i) and cond2 = sid1(i);

dbms_output.put_line(dest);

... or even really PL/SQL, but presumably is an exercise.

Upvotes: 2

S S
S S

Reputation: 101

why are you using Execute IMMEDIATE? You can print select statement in a plsql as below.

    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 := arr1('a','b','c','d');
 sid1 := arr2('1','2','3','4');
   total := sd.count;
   FOR i in 1 .. total LOOP 
      select destination into dest from temp_1  where cond1 =sd(i) and cond2 = sid1(i) ;
      dbms_output.put_line(dest);  
   END LOOP; 
END;

Upvotes: 1

i100
i100

Reputation: 4666

Try setting dbms_output.enable(null) like:

execute immediate q'[select destination from temp_1 where cond1 =(:var1) and cond2 = (:var2)]'
  into dest
  using sd(i),sid1(i); 

dbms_output.enable(null);
dbms_output.put_line(dest);

or enable spooling to a file.

spool on to 'output.txt';
execute immediate q'[select destination from temp_1 where cond1 =(:var1) and cond2 = (:var2)]'
  into dest
  using sd(i),sid1(i); 

spool off;

Check also ask tom

Upvotes: 0

Related Questions