2 Replies Latest reply: Oct 5, 2009 6:30 PM by 726038 RSS

    ctx_oracle, stored procedures and cursors

    726038
      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.