Re: Any suggestions with this updated code????

I am unsure of the answers to your questions. This Oracle sproc is so
dang foreign compared to SQLServer sproc's, I simply cannot make sense
of what you are asking! Sorry for not totally understanding/answering
your questions (which I know are to help get this thing working),
perhaps someone can suggest the answers by looking at what is in the
contents of this oracle package....

j

I was provided 2 things: "prop1.sql" and "declare2.txt" ----

***** this is the contents of prop1.sql ******

--Create a package to declare a ref cursor globally
create or
replace package property1 as

type results_record_type is record (
locn property.locn%type,
locs property.locs%type,
name1 property.name1%type,
id property.id%type);

type props_ref_type is ref cursor return results_record_type;

--This proc will open the refcursor
procedure open_prop(rc_prop in out props_ref_type,
street_no in varchar2, street_name in varchar2);

procedure close_prop(rc_prop in out props_ref_type);

end property1;
/
show errors

create or replace package body property1 as
procedure open_prop(rc_prop in out props_ref_type,
street_no in varchar2, street_name in varchar2)
is
v_str varchar2(23):='%'||street_name ||'%';
v_no varchar2(6):= '%'|| street_no ||'%';

begin
open rc_prop for
select locn, locs, name1, property.id from property
where locn = v_no and locs = v_str;
end open_prop;

procedure close_prop(rc_prop in out props_ref_type)
is
begin
close rc_prop;
end close_prop;

end property1;
/
show errors

***********


**** this is the contents of declare2.txt ****
declare
v_property property1.results_record_type;
props_cursor property1.props_ref_type;
begin
property1.open_prop(props_cursor,'6000','AIRPO');
loop
fetch props_cursor into v_property;
exit when props_cursor%NOTFOUND;
-- look at or process the fields in v_prop_record
end loop;
property1.close_prop(props_cursor);
end;
/
******

.

Relevant Pages

  • Re: Help trying to debug this DUMB pl/sql code
    ... I have a package I'm trying to run through a debugger and find some ... The package uses a defined type VARRAY: ... p_save_out OUT VARCHAR2, ... Puget Sound Oracle Users Group ...
    (comp.databases.oracle.server)
  • Re: jdbc + CallableStatement +Incorrectly registered parameters
    ... > Oracle and java. ... > I've created a package with a function to find buss connection ... > FUNCTION getdirect(from_city varchar2, to_city varchar2) RETURN ... > CallableStatement stmt = db.prepareCall; ...
    (comp.lang.java.databases)
  • Re: Oracle <= 9i / 10g File System Access via utl_file Exploit
    ... I don't think so its any new vulnerability or exploit ... Traversal via this package ... -- for any special privileges (CONNECT and RESOURCE ... in varchar2, p_filename in varchar2) as ...
    (Bugtraq)
  • How to reverse an LDAP query?
    ... address from their username in an LDAP server. ... Below is the query and the package. ... a_host varchar2, ... -- Loop through the entries ...
    (comp.databases.oracle.server)
  • Re: Help trying to debug this DUMB pl/sql code
    ... I have a package I'm trying to run through a debugger and find some ... The package uses a defined type VARRAY: ... p_save_out OUT VARCHAR2, ... If I try to debug using SQL Developer, ...
    (comp.databases.oracle.server)