CODEWITHSUNDEEP
oracle-databasesqlplus
Barry Prentiss
Barry Prentiss

Reputation: 209

How to assign a value to SQL*Plus variable with select statement in PL/SQL?

How do I assign a value to a variable using a select statement and use it in a SQL*Plus script, something like this?

VARIABLE FullCatCode VARCHAR2(7)

exec :FullCatCode := (SELECT CatCode from draw_catcodes where series = 123 and base = 158);

SELECT :FullCatCode || '-' || Other stuff... from table_name...

EDIT: @AlexPoole, Sorry for the imprecise problem statement. I am extracting 50 columns of data from a 280K record dataset. I am assigning sequence nextval's to each record based on a substring from a secondary table (draw_catcodes). I am running the SELECT statement in SQL*Plus, spooling to a CSV file. The SQL now looks like this (testing your second recommendation below):

COLUMN CatCode NEW_VALUE FullCatCode;

SELECT
(CASE d.image WHEN 0 THEN 'NoImage.pdf'
 ELSE lower(d.prefix || lpad(d.series,3,0) || lpad(d.base,3,0) || lpad(d.suffix,2,0) || lpad(d.rev,2,0) || '.pdf') END) as "Filename",
(SELECT EngDiscipline from draw_catcodes c where d.series = c.series and d.base = c.base) as "EngDiscipline",

(SELECT CatCode from draw_catcodes c where d.series = c.series and d.base = c.base),

SELECT &FullCatCode || '-' ||
lpad((CASE substr(&FullCatCode,0,3)
WHEN 'AEG' THEN (SELECT AEG_seq.NEXTVAL FROM DUAL)
WHEN 'ARY' THEN (SELECT ARY_seq.NEXTVAL FROM DUAL)
WHEN 'BBR' THEN (SELECT BBR_seq.NEXTVAL FROM DUAL)
ELSE 0
END),6,0) as "ItemID",
upper(d.prefix || '-' || lpad(d.series,3,0) || '-' || lpad(d.base,3,0) || '-' || lpad(d.suffix,2,0)) as "LegacyID",
...
from tablename where ...

I started with a Stored Function that 'Upserted' a table of sequences (can't perform DML from a Function), tried a Stored Procedure with an OUT variable (couldn't call the procedure from within the SQL SELECT), now trying sequences in a CASE statement (can't figure out how to use variables as described above)... Any suggestions would be much appreciated!

EDIT: @AlexPoole, Since the variables won't work in this context, I just SELECT the value I'm after directly, then use a CASE statement to specify the proper sequence. The syntax is not right though, as I am getting an ORA-00933: SQL command not properly ended error on the next line:

SELECT ((SELECT CatCode from draw_catcodes c where d.series = c.series and d.base = c.base) || '-' ||
lpad((CASE substr((SELECT CatCode from draw_catcodes c where d.series = c.series and d.base = c.base),0,3)
WHEN 'AEG' THEN AEG_seq.NEXTVAL
WHEN 'ARY' THEN ARY_seq.NEXTVAL
WHEN 'BBR' THEN BBR_seq.NEXTVAL
...
WHEN 'SPR' THEN SPR_seq.NEXTVAL
WHEN 'SRL' THEN SRL_seq.NEXTVAL
ELSE 0
END),6,0))  as "ItemID" FROM DUAL,
upper(d.prefix || '-' || lpad(d.series,3,0) || '-' || lpad(d.base,3,0) || '-' || lpad(d.suffix,2,0)) as "LegacyID",

EDIT: @AlexPoole, I added the JOIN and cleaned up the SELECT but now get an ORA-02287: sequence number not allowed here 

(c.CatCode || '-' || 
(SELECT lpad(
(CASE substr(c.CatCode,0,3)
WHEN 'AEG' THEN AEG_seq.NEXTVAL
WHEN 'ARY' THEN ARY_seq.NEXTVAL
WHEN 'BBR' THEN BBR_seq.NEXTVAL
WHEN 'BSY' THEN BSY_seq.NEXTVAL
...
WHEN 'SDR' THEN SDR_seq.NEXTVAL
WHEN 'SLC' THEN SLC_seq.NEXTVAL
WHEN 'SLD' THEN SLD_seq.NEXTVAL
WHEN 'SMS' THEN SMS_seq.NEXTVAL
WHEN 'SPP' THEN SPP_seq.NEXTVAL
WHEN 'SPR' THEN SPR_seq.NEXTVAL
WHEN 'SRL' THEN SRL_seq.NEXTVAL
ELSE 0 END ),6,0) FROM DUAL)) as "ItemID",
...
FROM md_draw d 
join draw_catcodes c on d.series = c.series and d.base = c.base
order by lpad(d.series,3,0), lpad(d.base,3,0), lpad(d.suffix,2,0);

Any suggestions?

EDIT: @AlexPoole, You're right, I removed the subquery containing the sequence call as you detailed it, but still get the ORA-02287: sequence number not allowed here error:

SELECT
(CASE d.image WHEN 0 THEN 'NoImage.pdf'
 ELSE lower(d.prefix || lpad(d.series,3,0) || lpad(d.base,3,0) || lpad(d.suffix,2,0) || lpad(d.rev,2,0) || '.pdf') END) as "Filename",
c.EngDiscipline as "EngDiscipline",
c.CatCode || '-' || lpad(CASE substr(c.CatCode,0,3)
WHEN 'AEG' THEN AEG_seq.NEXTVAL
WHEN 'ARY' THEN ARY_seq.NEXTVAL
WHEN 'BBR' THEN BBR_seq.NEXTVAL
...
WHEN 'SPP' THEN SPP_seq.NEXTVAL
WHEN 'SPR' THEN SPR_seq.NEXTVAL
WHEN 'SRL' THEN SRL_seq.NEXTVAL
ELSE 0 END,6,'0') as "ItemID",
upper(d.prefix || '-' || lpad(d.series,3,0) || '-' || lpad(d.base,3,0) || '-' || lpad(d.suffix,2,0)) as "LegacyID",
    ...
FROM md_draw SAMPLE (1) d 
join draw_catcodes c on d.series = c.series and d.base = c.base
order by c.ccProgram, lpad(d.series,3,0), lpad(d.base,3,0), lpad(d.suffix,2,0);

Upvotes: 6

Views: 20029

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

It is fairly easy. In PL/SQL a query either needs to be a cursor, or you have to select into something; in this, into your bind variable:

VARIABLE FullCatCode VARCHAR2(7)

exec SELECT CatCode into :FullCatCode from draw_catcodes where series = 123 and base = 158;

SELECT :FullCatCode || '-' || Other stuff... from table_name...

If you're only going to use it in later SQL statements you could also use a substitution variable, instead of a bind variable:

COLUMN CatCode NEW_VALUE FullCatCode

SELECT CatCode from draw_catcodes where series = 123 and base = 158;

SELECT &FullCatCode || '-' || Other stuff... from table_name...

With your modified question, this isn't what you're trying to do at all. You're trying to refer to a value from a subquery in another part of the same query, not a separate statement later in your script. And there is no PL/SQL involved.

You can't do that with either mechanism above; the first because there is no PL/SQL block to do the select ... into, and the second because substitution variables are evaluated and replaced before the statement is run - the new_value doesn't exist until after the query has run.

You don't want a subquery here though, you should be using a join, something like:

SELECT
  CASE d.image WHEN 0 THEN 'NoImage.pdf'
    ELSE lower(d.prefix || lpad(d.series,3,'0') || lpad(d.base,3,'0') || lpad(d.suffix,2,'0') || lpad(d.rev,2,'0') || '.pdf')
    END as "Filename",
  c.EngDiscipline as "EngDiscipline",
  c.CatCode,  -- not sure if you actually want this raw value?
  c.CatCode || '-' || lpad(CASE substr(c.CatCode,0,3)
    WHEN 'AEG' THEN AEG_seq.NEXTVAL
    WHEN 'ARY' THEN ARY_seq.NEXTVAL
    WHEN 'BBR' THEN BBR_seq.NEXTVAL
    ELSE 0
    END,6,'0') as "ItemID",
  upper(d.prefix || '-' || lpad(d.series,3,'0') || '-' || lpad(d.base,3,'0') || '-' || lpad(d.suffix,2,'0'))
    as "LegacyID",
  ...
from tablename d
join draw_catcodes c on d.series = c.series and d.base = c.base
where ...

You can refer directly to c.CatCode in the places you were trying to use &FullCatCode.

If the sequences only exist for this query and the ItemId has no wider significance, you could use an analytic function to generate the ItemIds instead:

  c.CatCode || '-' ||
    lpad(row_number() over (partition by substr(c.CatCode,0,3) order by null),6,'0')
    as "ItemID",

There will be no deterministic ordering of the ItemIds, but there wouldn't be with the sequence approach; and with this you could modify the windowing clause to specify the ordering if that was desirable.

Upvotes: 12

Related Questions