6

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);
Improve this question
4
  • You only have a single statement, and you're trying to use the value of CatCode from the subquery later in the same statement? Commented Mar 24, 2016 at 15:50
  • I don't get a notification when you edit the question, I just happened to come back. Why do you still have a subquery - that wasn't in my answer? That is what is causing that new error. And did you see the edit about using an analytic function instead of the sequences - maybe even more useful if you're using more than you originally showed? Commented Mar 24, 2016 at 19:05
  • @AlexPoole, removed subquery, still get same error... Commented Mar 24, 2016 at 20:53
  • And all the extra parentheses? I ran a version with my own table and it was fine with the subquery removed, but i did take the brackets out too, and I'm not 100% sure which cleared the ORA-02287. Commented Mar 24, 2016 at 20:57

1 Answer 1

Reset to default
12

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.

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

6 Comments

Thx Alex! After altering the SELECT I get the following error: (EXEC SELECT CatCode into :FullCatCode from draw_catcodes c where d.series = c.series and d.base = c.base) * ERROR at line 20: ORA-00907: missing right parenthesis This statement is within a larger Select statement, does this 'Into' have to be in the outermost statement?
@BarryPrentiss - not quite sure what sure what you mean. exec is a SQL*Plus command, not part of a SQL statement, so that doesn't belong there. And the into needs to be in the outermost statement, yes. But it isn't clear where that snippet/subquery fits in to a bigger one though - is it a select list expression, an inline view, a join condition? Or something I haven't thought of...?
@BarryPrentiss - updated based on what I think you're trying to do. Are the ItemIDs supposed to start from 1 each time you run this, or each run supposed to start where the last one left off? I'm trying to figure out if you really need the sequences, or if you just want a sequence-like suffix that's unique within the query.
I am assigning CatCodes and sequential ItemIDs to the records upon select based on the unique series/base pairs, so each of the CatCodes will have its own sequential number set. I'll try the join and see if I can get it to work... Thx for the pointer!
Yes, but are they only relevant within this query? The sequence value (i.e. ItemID) each row gets is only in the file, not stored in your database. I suppose another way of looking at it is - have you created the sequences just for this query, and if you run it again will you reset the sequences to they start again at 1? (All I'm trying to do is see if you can get the result you want without the sequences)
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.