0

I have this statement:

SELECT parameter,
       VALUE
  FROM TABLE(my_db.workplan.get_pp_params(in_pa_nummer => 'NR04640992', in_par_typ => NULL))

this is what I get back from the statement:

enter image description here

So far so good. But I want to get all of these parameters into variables in a PLSQL Procedure, but I don't know exactly how I can achieve this.

Normally I would do a SELECT INTO statement but it doesn't work here so could anyone give me a tip?` I want to put the variables into the statements

v_arbeitsanweisung
v_maschinengruppe
v_max_anz_ic_pro_tape
.
.
.
1
  • use a collection Commented Jun 20, 2024 at 12:45

1 Answer 1

0

Your query returns a table of two columns and an unknown number of rows. Select these into a collection of a two-column record. Instead of a mere INTO you need BULK COLLECT INTO for this operation.

Once you are there, you can fill your separate variables in a loop:

DECLARE
  TYPE t_record IS RECORD (parameter VARCHAR2(100), value VARCHAR2(100));
  TYPE t_table IS TABLE OF t_record;
  v_table t_table;
BEGIN
  SELECT parameter, value
  BULK COLLECT INTO v_table
  FROM TABLE(my_db.workplan.get_pp_params(in_pa_nummer => 'NR04640992', in_par_typ => NULL));

  -- do something with that result, e.g.
  FOR i IN 1 .. v_table.COUNT LOOP 
    CASE v_table(i).parameter 
      WHEN 'Arbeitsanweisung'      THEN v_arbeitsanweisung    := v_table(i).value;
      WHEN 'Maschinengruppe'       THEN v_maschinengruppe     := v_table(i).value;
      WHEN 'Max. Anz. IC pro Tape' THEN v_max_anz_ic_pro_tape := v_table(i).value;
      ...
    END CASE;
  END LOOP;
END;

Another option: pivot your data to turn your rows into columns. With conditional aggregation:

BEGIN
  SELECT 
    MAX(CASE WHEN parameter = 'Arbeitsanweisung' THEN value END),
    MAX(CASE WHEN parameter = 'Maschinengruppe' THEN value END),
    MAX(CASE WHEN parameter = 'Max. Anz. IC pro Tape' THEN value END),
  INTO v_arbeitsanweisung
     , v_maschinengruppe
     , v_max_anz_ic_pro_tape
  FROM TABLE(my_db.workplan.get_pp_params(in_pa_nummer => 'NR04640992', in_par_typ => NULL));
END;
Sign up to request clarification or add additional context in comments.

Comments

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.