CODEWITHSUNDEEP
sqloracle-databaseplsql
danny schult
danny schult

Reputation: 19

Declaring a constant in PLSQL

I am having trouble declaring a constant and using it in a simple query.

I've tried many variations of declaring a constant in PLSQL that I've seen online but I keep getting errors. What am I doing wrong? 

DECLARE 
    cobdate CONSTANT NUMBER(10) := 420181109;
BEGIN
    SELECT *
    FROM   THIS_TABLE dex
    WHERE  dex.close_of_business_key = &cobdate
    AND    dex.scenario_type_id = 'xxxx'
    AND    dex.s_counterparty_id = 'xxxx'
    AND    dex.run_type = 'xxxx'
    AND    dex.s_credit_line_type_id = 'xxxx'
END;

Upvotes: 1

Views: 322

Answers (1)

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181300

You are not supposed to use & as a prefix to your constant name.

DECLARE 
   cobdate CONSTANT NUMBER(10) := 420181109;
   v_this_table this_table%rowtype;
BEGIN
   SELECT * into v_this_table
     FROM THIS_TABLE dex
    WHERE dex.close_of_business_key = cobdate
      AND dex.scenario_type_id = 'xxxx'
      AND dex.s_counterparty_id = 'xxxx'
      AND dex.run_type = 'xxxx'
      AND dex.s_credit_line_type_id = 'xxxx'
END;

Also, in PL/SQL you need to specify where are you going to store your result set values (in my example, v_this_table).

If your query returns more than one row you will need a cursor to iterate it.

Upvotes: 4

Related Questions