CODEWITHSUNDEEP
oracle-databaseplsqlsqlplus
fd2812
fd2812

Reputation: 23

SQL Plus variable string

I have PLSQL block and a table. I need to write an expression from that tables rows. Lets say my tables rows be like a,b,c,d and my description should be like;

desc_ VARCHAR2(32000) := &a || &b || &c || &d

Some of my variables depends on conditions. For example if &d is Null I need to write "UNDEF" if its not null need to write "something".

My question is how can I applied this to the variables?

Thank you

Upvotes: 0

Views: 471

Answers (1)

Littlefoot
Littlefoot

Reputation: 142710

Use CASE. For example:

SQL> set serveroutput on
SQL> set ver off
SQL>
SQL> declare
  2    desc_ varchar2(200);
  3  begin
  4    desc_ := '&a' ||
  5             case when '&b' is null then 'UNDEF'
  6                  else 'SOMETHING'
  7             end  ||
  8             '&c';
  9    dbms_output.put_line(desc_);
 10  end;
 11  /
Enter value for a: This is A-
Enter value for b: ???
Enter value for c: -This is B
This is A-SOMETHING-This is B

PL/SQL procedure successfully completed.

SQL>

Though, as you use PL/SQL why wouldn't you create a stored procedure and pass those values as parameters to it?

Upvotes: 2

Related Questions