This discussion is archived
2 Replies Latest reply: Oct 5, 2009 4:30 PM by 726038 RSS

ctx_oracle, stored procedures and cursors

726038 Newbie
Currently Being Moderated
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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points