Forum Stats

  • 3,768,544 Users
  • 2,252,807 Discussions
  • 7,874,616 Comments

Discussions

ctx_oracle, stored procedures and cursors

726038
726038 Member Posts: 3
edited Oct 5, 2009 7:30PM in Python
Hello.

I have as stored procedure which is defined as follows:

V_PAGENUM NUMBER IN
V_CITY CHAR IN
V_STATE CHAR IN
V_MILES NUMBER IN
V_MAX_PRICE NUMBER IN
V_MIN_PRICE NUMBER IN
V_NUM_BED NUMBER IN
V_PROPTYPE NUMBER IN
V_ORDERFLAG CHAR IN
V_PARTNERID NUMBER IN
V_SID NUMBER OUT
CV_1 REF CURSOR IN/OUT

I execute this from sqlplus as follows:

set serveroutput on;
set linesize 10000;
set trimspool on;
declare
v_sid number;
cv_1 SYS_REFCURSOR;

c_propid hctempsearch.propid%TYPE;
c_city hcprops.city%TYPE;
c_state hcprops.state%TYPE;
c_urlprefix HCPROP_SOURCES.urlprefix%TYPE;
c_propurl1 HCPROPMASTER.propurl1%TYPE;
c_propurl2 HCPROPMASTER.propurl2%TYPE;
c_price hcprops.prop_price%TYPE;
c_proptypecode hcprops.proptype%TYPE;
c_num_bed hcprops.num_bedrooms%TYPE;
c_num_bath hcprops.num_baths%TYPE;
c_neighborhood HCPROPMASTER.neighborhood%TYPE;
c_sourcename HCPROP_SOURCES.sourcename%TYPE;
c_homeurl HCPROP_SOURCES.homeurl%TYPE;
c_extensive varchar2(1);
c_fsbo varchar2(1);
c_newconst varchar2(1);
c_reduceprice HCPROPMASTER.reducedprice%TYPE;

begin
HC_CONTSEARCH(0,'Colorado Springs','CO',87,500000,100000,1,32,'d',0,v_sid,cv_1);
LOOP
FETCH cv_1 INTO
c_propid,
c_city,
c_state,
c_urlprefix,
c_propurl1,
c_propurl2,
c_price,
c_proptypecode,
c_num_bed,
c_num_bath,
c_neighborhood,
c_sourcename,
c_homeurl,
c_extensive,
c_fsbo,
c_newconst,
c_reduceprice;
EXIT WHEN cv_1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(c_propid || '|' ||
ltrim(rtrim(c_city)) || '|' ||
c_state || '|' ||
c_urlprefix || '|' ||
c_propurl1 || '|' ||
c_propurl2 || '|' ||
c_price || '|' ||
c_proptypecode || '|' ||
c_num_bed || '|' ||
c_num_bath || '|' ||
c_neighborhood || '|' ||
ltrim(rtrim(c_sourcename)) || '|' ||
c_homeurl || '|' ||
c_extensive || '|' ||
c_fsbo || '|' ||
c_newconst || '|' ||
c_reduceprice);

END LOOP;

end;
/

I it returns results as follows:

8000000029323407|XXXXXXXXXXXXX|CO|www.xxxxxx.com/homescout/mylistings/show_listing?|listing_uid=113620&view_mode=1||499900|32|5|4|810 Broadview Pl.|xxxxxx Homes for Sale|www.homegain.com/homescout/mylistings/show_listing?|F|F|F|F

I need to call this stored procedure from a python script and pass the same variables to display the same results. Can anyone tell me how to do that?

Thanks.

Answers

  • 390020
    390020 Member Posts: 446
    It's easy, just define two cursors, one for the proc to execute and one for the ref cursor. Here an example (not tested but more or less that's the way).

    v_sid = 1
    cur1 = conn.cursor() # assuming conn is a valid oracle connection
    cur2 = conn.cursor()
    cur1.callproc("HC_CONTSEARC", (0,'Colorado Springs','CO',87,500000,100000,1,32,'d',0,v_sid,cur2))
    print cur2.fetchall()
  • 726038
    726038 Member Posts: 3
    Thanks Mariano.

    That worked! The syntax was a little tricky.
This discussion has been closed.