SQL Plus variable string

Question

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

You indicate your table has rows a,b,c,d but your assignment statement treats then as columns (actually here as substitution variables). These are entirely different things, So which are they? The solution will be totally different depending on that. @Littlefoot takes them as substitution variables. — Belayer
– Belayer, Commented Jan 18, 2021 at 18:39

Littlefoot · Accepted Answer · 2021-01-16 18:46:35Z

2

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?

answered Jan 16, 2021 at 18:46

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Plus variable string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related