Home » SQL & PL/SQL » SQL & PL/SQL » Procedure to fetch values from a select (Oracle RDBMS 10.2.0.4 Win2003 x64)
Procedure to fetch values from a select [message #363195] Fri, 05 December 2008 06:10 Go to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

Im want to create a procedure that selects values from a view (that checks spilled messages for streams or maybe I can just use the select I used to create the view as below?). I will then use the procedure to populate a diagram (well, first a table in Oracle) in Excel to get information over time about the amount of spilled messages.
And I know I need to use sysdate as well, but I just want to get started with something small.

CREATE PROCEDURE streams_spilled_msgs
IS
CURSOR spill_cur IS
SELECT a.cnum_msgs "Capture queue messages",
       a.cspill_msgs "Capture queue spill",
       b.cnum_msgs "Apply queue messages", b.cspill_msgs "Apply queue spill" FROM v$buffered_queues a, v$buffered_queues@DatabaseLink b
 WHERE a.queue_name = 'DatabaseLink_CAPTURE_Q'
   AND b.queue_name = 'MY_APPLY';
spill_rec spill_cur%rowtype;
BEGIN
FOR spill_rec in spill_cur
LOOP
INSERT INTO T1
END LOOP;
END;


I know that this is not working, but I need some pointers...or a lot of them.

Regards
Hristo
Re: Procedure to fetch values from a select [message #363209 is a reply to message #363195] Fri, 05 December 2008 06:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Pointer> Skip the whole row by row approach and just ineert the data:
CREATE PROCEDURE streams_spilled_msgs IS
BEGIN
  INSERT INTO T1 (col_1,col_2,col_3,col_4,date_column)
  SELECT a.cnum_msgs
        ,a.cspill_msgs
        ,b.cnum_msgs
        ,b.cspill_msgs
        ,sysdate
  FROM   v$buffered_queues a
        ,v$buffered_queues@DatabaseLink b
  WHERE  a.queue_name = 'DatabaseLink_CAPTURE_Q'
  AND    b.queue_name = 'MY_APPLY';

END;
Re: Procedure to fetch values from a select [message #363230 is a reply to message #363195] Fri, 05 December 2008 07:42 Go to previous messageGo to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi again!

Find some info else where and this is what I did:

CREATE PROCEDURE streams_spilled_msgs
IS
v_cnum_msgs NUMBER;
v_cspill_msgs NUMBER;
v_cnum_msgs2 NUMBER;
v_cspill_msgs2 NUMBER;
BEGIN
SELECT a.cnum_msgs , a.cspill_msgs ,b.cnum_msgs, b.cspill_msgs INTO v_cnum_msgs, v_cspill_msgs, v_cnum_msgs2, v_cspill_msgs2  
FROM v$buffered_queues a, v$buffered_queues@DatabaseLink b
 WHERE a.queue_name = 'PLWEB_CAPTURE_Q'
   AND b.queue_name = 'Streams_QUEUE_ANY';
INSERT INTO strmadmin.T1 VALUES (v_cnum_msgs,v_cspill_msgs,v_cnum_msgs2,v_cspill_msgs2);
COMMIT;
END;
/


Regards
Hristo
Re: Procedure to fetch values from a select [message #363234 is a reply to message #363230] Fri, 05 December 2008 08:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why do it in two steps?
This will be quicker:
CREATE PROCEDURE streams_spilled_msgs
IS
BEGIN
INSERT INTO strmadmin.T1 SELECT a.cnum_msgs , a.cspill_msgs ,b.cnum_msgs, b.cspill_msgs INTO v_cnum_msgs, v_cspill_msgs, v_cnum_msgs2, v_cspill_msgs2  
FROM v$buffered_queues a, v$buffered_queues@DatabaseLink b
 WHERE a.queue_name = 'PLWEB_CAPTURE_Q'
   AND b.queue_name = 'Streams_QUEUE_ANY';
COMMIT;
END;


Also - are you sure you want the commit there. It's normal to commit once at the end of a whole transaction, rather than after each insert.
Re: Procedure to fetch values from a select [message #363240 is a reply to message #363234] Fri, 05 December 2008 09:04 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I think copy/paste gotcha.

Should omit:

INTO v_cnum_msgs, v_cspill_msgs, v_cnum_msgs2, v_cspill_msgs2
Re: Procedure to fetch values from a select [message #363258 is a reply to message #363240] Fri, 05 December 2008 10:01 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
D'oh!

Embarassed
Previous Topic: how to create a view of table in diff schemas (merged a 3rd time)
Next Topic: temporary table
Goto Forum:
  


Current Time: Sun Dec 04 02:32:12 CST 2016

Total time taken to generate the page: 0.12000 seconds