Aside from problems with repmcli dumping core when a store procedure is
too long, I have come across some other issues with using a ODBC
application to load a stored procedure.
As much as I would like to post the stored procedure in full, but it's
been rejected twice so I'l briefly reproduce it. :) :
CREATE DBPROC order_display(IN c_uname VARCHAR(20), IN c_passwd
VARCHAR(20),
OUT items FIXED(2),
OUT o_id FIXED(10), OUT c_fname VARCHAR(15), OUT c_lname VARCHAR(15),
OUT c_phone VARCHAR(16), OUT c_email VARCHAR(50), OUT o_date TIMESTAMP,
OUT o_sub_total FIXED(17, 2), OUT o_tax FIXED(17, 2), OUT o_total
FIXED(17, 2),
OUT o_ship_type VARCHAR(10), OUT o_ship_date TIMESTAMP,
OUT o_status VARCHAR(15),
OUT bill_addr_street1 VARCHAR(40), OUT bill_addr_street2 VARCHAR(40),
OUT bill_addr_city VARCHAR(30), OUT bill_addr_state VARCHAR(20),
OUT bill_addr_zip VARCHAR(10), OUT bill_co_name VARCHAR(50),
OUT ship_addr_street1 VARCHAR(40), OUT ship_addr_street2 VARCHAR(40),
OUT ship_addr_city VARCHAR(30), OUT ship_addr_state VARCHAR(20),
OUT ship_addr_zip VARCHAR(10), OUT ship_co_name VARCHAR(50),
OUT i_id1 FIXED(10), OUT i_title1 VARCHAR(60),
OUT i_publisher1 VARCHAR(60), OUT i_cost1 FIXED(17, 2),
OUT ol_qty1 FIXED(3), OUT ol_discount1 FIXED(17, 2),
OUT ol_comments1 VARCHAR(100),
...
OUT i_id100 FIXED(10), OUT i_title100 VARCHAR(60),
OUT i_publisher100 VARCHAR(60), OUT i_cost100 FIXED(17, 2),
OUT ol_qty100 FIXED(3), OUT ol_discount100 FIXED(17, 2),
OUT ol_comments100 VARCHAR(100),
OUT cx_type VARCHAR(10), OUT cx_auth_id CHAR(15)) AS
VAR c_id FIXED(10); o_bill_addr_id FIXED(10);
o_ship_addr_id FIXED(10);
BEGIN
SET i_id1 = 0;
SET i_title1 = '';
SET i_publisher1 = '';
SET i_cost1 = 0;
SET ol_qty1 = 0;
SET ol_discount1 = 0;
SET ol_comments1 = '';
...
SET i_id100 = 0;
SET i_title100 = '';
SET i_publisher100 = '';
SET i_cost100 = 0;
SET ol_qty100 = 0;
SET ol_discount100 = 0;
SET ol_comments100 = '';
SELECT c_id, c_fname, c_lname, c_phone, c_email
FROM tpcw.customer
WHERE c_uname = :c_uname
AND c_passwd = :c_passwd;
IF $rc = 0 THEN
BEGIN
FETCH INTO :c_id, :c_fname, :c_lname, :c_phone, :c_email;
SELECT o_id, o_date, o_sub_total, o_tax, o_total, o_ship_type,
o_ship_date,
o_status, o_bill_addr_id, o_ship_addr_id
FROM tpcw.orders, tpcw.customer
WHERE o_c_id = :c_id
ORDER BY o_date DESC;
IF $rc = 0 THEN
BEGIN
FETCH INTO :o_id, :o_date, :o_sub_total, :o_tax, :o_total,
:o_ship_type,
:o_ship_date, :o_status, :o_bill_addr_id,
:o_ship_addr_id;
SELECT cx_type, cx_auth_id
INTO :cx_type, :cx_auth_id
FROM tpcw.cc_xacts
WHERE cx_o_id = :o_id;
SELECT addr_street1, addr_street2, addr_city, addr_state, addr_zip,
co_name
INTO :bill_addr_street1, :bill_addr_street2, :bill_addr_city,
:bill_addr_state, :bill_addr_zip, :bill_co_name
FROM tpcw.address, tpcw.country
WHERE addr_id = :o_bill_addr_id
AND addr_co_id = co_id;
SELECT addr_street1, addr_street2, addr_city, addr_state, addr_zip,
co_name
INTO :ship_addr_street1, :ship_addr_street2, :ship_addr_city,
:ship_addr_state, :ship_addr_zip, :ship_co_name
FROM tpcw.address, tpcw.country
WHERE addr_id = :o_ship_addr_id
AND addr_co_id = co_id;
SELECT ol_i_id, i_title, i_publisher, i_cost, ol_qty, ol_discount,
ol_comments
FROM tpcw.item, tpcw.order_line
WHERE ol_o_id = :o_id
AND ol_i_id = i_id;
SET items = 0;
IF $rc = 0 THEN
BEGIN
FETCH INTO :i_id1, :i_title1, :i_publisher1, :i_cost1, :ol_qty1,
:ol_discount1, :ol_comments1;
SET items = items + 1;
END;
...
IF $rc = 0 THEN
BEGIN
FETCH INTO :i_id100, :i_title100, :i_publisher100, :i_cost100,
:ol_qty100, :ol_discount100, :ol_comments100;
SET items = items + 1;
END;
END;
Here is the only resulting SQL error:
SQLSTATE S1000
[SAP AG][LIBSQLOD SO][SAP DB]General error;-1110 POS(1) View definition
too long.
The full description is:
-1107: Too complicated SQL statement (too many internal commands)
Explanation: The SQL statement is so complicated that more internal
statements must be generated for it than can be administered for an SQL
statement. User Action: Simplify the SQL statement or split it into
several SQL statements.
I get the impression that SAP DB cannot handle a stored procedure this
large, even when increasing _PACKE_SIZE to the max of 131072 bytes and
_MAXTASK_STACK to 2048 KB. The file size of my stored procedure is
56762 bytes. Any help on how to reduce my procedure size, short of not
supporting 100 items (resulting in over 600 parameters), would be
greatly appreciated.
Thanks,
Mark
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
