# getting results like pipe function but in editor window

**URL:** <https://forums.toadworld.com/t/getting-results-like-pipe-function-but-in-editor-window/31837>\
**Category:** Toad for Oracle & DevOps Toolkit\
**Created:** [November 24, 2014, 2:11am UTC](https://forums.toadworld.com/t/getting-results-like-pipe-function-but-in-editor-window/31837 "2014-11-24T02:11:04Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![murathankocan](https://avatars.discourse-cdn.com/v4/letter/m/439d5e/32.png) [@murathankocan](https://forums.toadworld.com/u/murathankocan)\
**Post date:** [November 24, 2014, 2:11am UTC](https://forums.toadworld.com/t/getting-results-like-pipe-function-but-in-editor-window/31837/1 "2014-11-24T02:11:04Z")

</div>

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**
