Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f4SChIA99020 for ; Mon, 28 May 2001 08:43:18 -0400 (EDT) (envelope-from pgsql-general-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4OCRnA18099; Thu, 24 May 2001 08:27:49 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f4OCRjY31126; Thu, 24 May 2001 08:27:45 -0400 (EDT) (envelope-from news) From: alla@sergey.com (Alla) X-Newsgroups: comp.databases.postgresql.sql, comp.databases.postgresql.general, comp.databases.postgresql.questions Subject: Re: Return cursor Date: 24 May 2001 05:27:27 -0700 Organization: http://groups.google.com/ Lines: 77 Message-ID: <9275d56e.0105240427.2fa28c31@posting.google.com> References: <9275d56e.0105231020.6bc24751@posting.google.com> <9ehged$k4f$1@news.tht.net> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: groups-support@google.com To: pgsql-general@postgresql.org.pgsql-sql@postgresql.org X-Archive-Number: 200105/1095 X-Sequence-Number: 10133 Alexander Dederer wrote in message news:<9ehged$k4f$1@news.tht.net>... > Can you send PL/SQL code and back-end code used this PL/SQL code? > Myself trubles with CURSOR I resolve use LIMIT ... OFFSET ... It would look something like this: create or replace package my_package AS type gc_cursor is ref cursor; procedure load_users (pp_user_base out gc_cursor); end my_package; / create or replace package body my_package as procedure load_users (pp_user_base out gc_cursor) as begin open pp_user_base for select column1, column2, column3 from my_table order by 1; exception when others then raise_application_error(-20100, 'Error while trying to load user base ' || sqlerrm); end load_users; end my_package; / C program would look something like this: void load_user_base() { EXEC SQL BEGIN DECLARE SECTION; sql_cursor cUserBase; EXEC SQL END DECLARE SECTION; struct USER_PROFILE { ....... } user_profile[ARRAY_LENGTH]; struct USER_PROFILE_IND { ....... } user_profile_ind[ARRAY_LENGTH]; EXEC SQL ALLOCATE :cUserBase; /* allocate the cursor variable */ EXEC SQL EXECUTE BEGIN my_package.load_users(:cUserBase); END; END-EXEC; EXEC SQL WHENEVER NOT FOUND DO break; for (;;) { EXEC SQL FOR :i FETCH :cUserBase INTO :user_profile:user_profile_ind; if (sqlca.sqlcode != 0) { fprintf(stderr, "Fetching users %s\n", sqlca.sqlerrm.sqlerrmc); break; } } ........ ........ Thanks for your response Alla