getting results like pipe function but in editor window

Toad for Oracle & DevOps Toolkit
1

Hi all,

quite new in toad & oracle. Until this time I had mostly work on ms-sql, but nowadays I need to work on oracle.

Here is my question:

I have managed to generate a pipelined function in oracle pl/sql which is like below and it works great.

FUNCTION get_trx_history (i_TRUCK_PLATE_NR varchar2,
i_date_START DATE DEFAULT SYSDATE - 1,
i_date_end DATE DEFAULT SYSDATE)
RETURN tbl_trx_history
PIPELINED
IS
r_pipe r_trx_history;
L_TRUCK_ID INTEGER;
BEGIN
SELECT ID
INTO L_TRUCK_ID
FROM VHCL100
WHERE IDENTIFIER = I_TRUCK_PLATE_NR;

FOR r
IN (SELECT a.*,
LAG (ODOMETER, 1) OVER (ORDER BY positiondate)
AS odometer_before,
“ODOMETER”

  • LAG (ODOMETER, 1) OVER (ORDER BY positiondate)
    AS delta_odometer,
    LAG (FUELLEVEL, 1) OVER (ORDER BY positiondate) / 10
    AS fuellevel_before,
    “FUELLEVEL” / 10
  • LAG (FUELLEVEL, 1) OVER (ORDER BY positiondate) / 10
    AS delta_fuellevel
    FROM asbotek.positionhistory a
    WHERE label = i_truck_plate_nr
    AND positiondate >= i_date_start
    AND positiondate <= i_date_end)
    LOOP
    R_pipe := NULL;
    r_pipe.trck100_id := L_TRUCK_ID;
    r_pipe.POSITIONNO := r.positionno;
    r_pipe.LABEL := r.label;
    r_pipe.POSITIONDATE := r.positiondate;
    r_pipe.UPDATETIME := r.updatetime;
    r_pipe.HUBREFERENCE := r.hubreference;
    r_pipe.LATITUDE := r.latitude;
    r_pipe.LONGITUDE := r.longitude;
    r_pipe.SPEED := r.speed;
    r_pipe.ODOMETER := r.odometer;
    r_pipe.TOTALFUEL := r.totalfuel;
    r_pipe.FUELLEVEL := r.fuellevel;
    r_pipe.ANGLE := r.angle;
    r_pipe.POSSRC := r.possrc;
    r_pipe.LOCATION := r.location;
    r_pipe.IGNITION := r.ignition;
    r_pipe.odometer_before := r.odometer_before;
    r_pipe.delta_odometer := r.delta_odometer;
    r_pipe.fuellevel_before := r.fuellevel_before;
    r_pipe.delta_fuellevel := r.delta_fuellevel;

PIPE ROW (r_pipe);
END LOOP;
END;
END;

my problem is; that I cannot make this work in edito. I managed to walk until:

declare
i_TRUCK_PLATE_NR varchar2(10) := ‘34 ff 444’;
i_date_START DATE := SYSDATE - 1;
i_date_end DATE := SYSDATE;
r_pipe r_trx_history table;
L_TRUCK_ID INTEGER := 0;
BEGIN
SELECT ID
INTO L_TRUCK_ID
FROM VHCL100
WHERE IDENTIFIER = I_TRUCK_PLATE_NR;

FOR r
IN (SELECT a.*,
LAG (ODOMETER, 1) OVER (ORDER BY positiondate)
AS odometer_before,
“ODOMETER”

  • LAG (ODOMETER, 1) OVER (ORDER BY positiondate)
    AS delta_odometer,
    LAG (FUELLEVEL, 1) OVER (ORDER BY positiondate) / 10
    AS fuellevel_before,
    “FUELLEVEL” / 10
  • LAG (FUELLEVEL, 1) OVER (ORDER BY positiondate) / 10
    AS delta_fuellevel
    FROM asbotek.positionhistory a
    WHERE label = i_truck_plate_nr
    AND positiondate >= i_date_start
    AND positiondate <= i_date_end)
    LOOP
    R_pipe := NULL;
    r_pipe.trck100_id := L_TRUCK_ID;
    r_pipe.POSITIONNO := r.positionno;
    r_pipe.LABEL := r.label;
    r_pipe.POSITIONDATE := r.positiondate;
    r_pipe.UPDATETIME := r.updatetime;
    r_pipe.HUBREFERENCE := r.hubreference;
    r_pipe.LATITUDE := r.latitude;
    r_pipe.LONGITUDE := r.longitude;
    r_pipe.SPEED := r.speed;
    r_pipe.ODOMETER := r.odometer;
    r_pipe.TOTALFUEL := r.totalfuel;
    r_pipe.FUELLEVEL := r.fuellevel;
    r_pipe.ANGLE := r.angle;
    r_pipe.POSSRC := r.possrc;
    r_pipe.LOCATION := r.location;
    r_pipe.IGNITION := r.ignition;
    r_pipe.odometer_before := r.odometer_before;
    r_pipe.delta_odometer := r.delta_odometer;
    r_pipe.fuellevel_before := r.fuellevel_before;
    r_pipe.delta_fuellevel := r.delta_fuellevel;

PIPE ROW (r_pipe);
END LOOP;
END;

but I got error on:

r_pipe r_trx_history table;

where I know there is an error but I do not know how to solve it.

This is just an example:

furthermore I need to be able to get the result table of any query in editor by using variables in it without creating a function.

Let’s say you have a table called phones with columns: owner, phone_type and phone_nr.

here is what I like to do:

declare

i_owner varchar2(10) := ‘some one’;

begin

select * from phones where owner = i_owner;

end;

should give me the results, but within editor pane without creating a function.

Thanks beforehand,

Murat KOÇAN