Re: Creating Datawindows in PB based on Oracle Stored Procedures
Date: 1998/06/17
Message-ID: <35875BB1.A8914299_at_netvision.net.il>
From my personal experience follows that you need to create a package using pbor7cat.SQL file ( use PowerBuilder database administrator, but don't forget to change the delimiter from ";" to "`" )
BEGIN PBOR7CAT.SQL : create or replace package pbdbms as
procedure disable;
procedure put(a varchar2); procedure put(a number); procedure put(a date); procedure put_line(a varchar2); procedure put_line(a number); procedure put_line(a date);
procedure new_line;
procedure get_line(line out varchar2, status out integer); type chararr is table of varchar2(255) index by binary_integer; procedure get_lines(lines out chararr, numlines in out integer); end;
`
create or replace package body pbdbms as
enabled boolean := TRUE; buf_size binary_integer; tmpbuf varchar2(500) := ''; putidx binary_integer := 1; amtleft binary_integer := 0; getidx binary_integer := 2; getpos binary_integer := 1; get_in_progress boolean := TRUE; type char_arr is table of varchar2(512) index by binary_integer; buf char_arr; idxlimit binary_integer;
procedure enable (buffer_size in integer default 20000) is lstatus integer;
lockid integer;
begin
enabled := TRUE;
if buffer_size < 2000 then
buf_size := 2000;
elsif buffer_size > 1000000 then
buf_size := 1000000;
else
buf_size := buffer_size;
end if;
idxlimit := trunc((buf_size+499) / 500); end;
procedure disable is
begin
enabled := FALSE;
end;
procedure put(a varchar2) is
begin
if enabled then
tmpbuf := tmpbuf || a;
end if;
end;
procedure put(a number) is
begin
if enabled then
tmpbuf := tmpbuf || to_char(a);
end if;
end;
procedure put(a date) is
begin
if enabled then
tmpbuf := tmpbuf || to_char(a);
end if;
end;
procedure put_line(a varchar2) is
begin
if enabled then
tmpbuf := tmpbuf || a;
new_line;
end if;
end;
procedure put_line(a number) is
begin
if enabled then
tmpbuf := tmpbuf || to_char(a);
new_line;
end if;
end;
procedure put_line(a date) is
begin
if enabled then
tmpbuf := tmpbuf || to_char(a);
new_line;
end if;
end;
procedure new_line is
strlen binary_integer;
begin
if enabled then
if get_in_progress then
get_in_progress := FALSE;
putidx := 1;
amtleft := 500;
buf(putidx) := '';
end if;
strlen := lengthb(tmpbuf);
if strlen > 255 then
tmpbuf := '';
raise_application_error(-20000, 'ORU-10028: line length overflow, '
||
'limit of 255 bytes per line');
end if;
if strlen > amtleft then
if putidx >= idxlimit then
tmpbuf := '';
raise_application_error(-20000, 'ORU-10027: buffer overflow, ' ||
'limit of ' || to_char(buf_size) || ' bytes');
end if;
buf(putidx) := buf(putidx) || ' -1';
putidx := putidx + 1;
amtleft := 500;
buf(putidx) := '';
end if;
buf(putidx) := buf(putidx) || to_char(strlen,'999') || tmpbuf;
amtleft := amtleft - strlen - 4;
tmpbuf := '';
end if;
end;
procedure get_line(line out varchar2, status out integer) is strlen binary_integer;
begin
if not enabled then
status := 1;
return;
end if;
if not get_in_progress then
buf(putidx) := buf(putidx) || ' -1';
putidx := putidx + 1;
get_in_progress := TRUE;
getidx := 1;
getpos := 1;
tmpbuf := '';
end if;
while getidx < putidx loop
strlen := to_number(substrb(buf(getidx),getpos,4));
if strlen >= 0 then
line := substrb(buf(getidx), getpos+4, strlen);
getpos := getpos + strlen + 4;
status := 0;
return;
else
getidx := getidx + 1;
getpos := 1;
end if;
end loop;
status := 1;
return;
end;
procedure get_lines(lines out chararr, numlines in out integer) is linecnt integer := 1;
s integer;
begin
if not enabled then
numlines := 0;
return;
end if;
while linecnt <= numlines loop
get_line(lines(linecnt), s);
if s = 1 then
numlines := linecnt - 1;
return;
end if;
linecnt := linecnt + 1;
end loop;
numlines := linecnt - 1;
return;
end;
end;
`
drop public synonym pbdbms `
create public synonym pbdbms for pbdbms ` grant execute on pbdbms to public `
END PBOR7CAT.SQL After that create your stored procedure as needed, using "PBDBMS.PUT_LINE ( )" function for result select. Note that string you put in this function must not be longer 255 char.
Example :
CREATE PROCEDURE <NAME> ( in_dwg_id in number ) /* this input parameter will be an retrieval argument for DW */
< Your Stored procedure statements ....
....
....
>
pbdbms.put_line ( ' select < columns > from < tables > ' ); pbdbms.put_line ( ' where <some key> = ' ); pbdbms.put_line ( in_dwg_id ) ; /* note that this argument is out withoutquotes */
pbdbms.put_line ( ' ORDER BY <some order statement>' ); end <procedure name> ;
Try this, it really works
Good luck !!
vyeddanapalli_at_my-dejanews.com wrote:
> I need to create a datawindow in Powerbuilder that would show a resultset
> from a stored procedure in Oracle with parameters being passed from
> powerbuilder as retrieval argument.
>
> We have created a Package and a stored
> procedure in Oracle so that the
> result set can be viewed in a DataWindow in
> Powerbuilder. We created an
> Oracle Package first; a result set structure
> is defined in the package, as well
> as the Reference Cursor, which is set to
> return a result set of the type defined
> in the same package. The stored
> procedure uses the reference cursor as an
> IN OUT, as well as an IN prameter
> that is being passed to the procedure.
> When I execute the procedure from
> SQLPLUS I get an error message
> saying:
> "Wrong number or types of arguments
> in call to the procedure" .
> I'm not sure what the problem is. I
> followed the structure for
> creating the package and the stored procedure as
> suggested by the
> Powersoft faxline document ID = 44553.
>
> The Package and
> Procedure code is as follows:
>
> Create OR Replace Package PACK_MYPORT_OFFICIER
> IS
> TYPE R_MYPORT_OFFICIER IS RECORD(
> company account.company%type,
> city
> account.city%type,
> postal account.postal%type,
> cust_overall_stat
> account_shadow.cust_overall_stat%type,
> comp_indiv_indicator
> account_shadow.comp_indiv_indicator%type,
> address_1 account.address_1%type,
> state_prov account.state_prov%type,
> phone_num phone.phone_num%type,
> sales_vol account.sales_vol%type,
> last_call call_item.call_date%type,
> next_call call_item_shadow.follow_up_date%type,
> name employee.name%type );
>
> TYPE C_MYPORT_OFFICIER IS REF CURSOR
> RETURN R_MYPORT_OFFICIER ;
>
> PROCEDURE
> P_MYPORT_OFFICIER (EMP_NUM IN employee.employee%type, OUTREC IN OUT
> C_MYPORT_OFFICIER);
>
> END PACK_MYPORT_OFFICIER;
> /
> CREATE OR REPLACE PROCEDURE
> P_MYPORT_OFFICIER(
> EMP_NUM IN employee.employee%type,
> OUTREC IN OUT
> PACK_MYPORT_OFFICIER.C_MYPORT_OFFICIER )
> AS
> BEGIN
> OPEN OUTREC FOR
> Select
> ...........;
>
> END P_MYPORT_OFFICIER ;
> /
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Wed Jun 17 1998 - 00:00:00 CEST