EXEC_SQL.EXECUTE_AND_FETCH returns 0 rows when in a PL/SQL Libary in Oracel Reports

From: Aaron Jackson <aaron.jackson_at_non.hp.com>
Date: 10 Jul 2002 12:48:06 -0700
Message-ID: <b852e646.0207101148.4bd97883_at_posting.google.com>

I am using EXEC_SQL.EXECUTE_AND_FETCH in a PL/SQL libaray in Report Builder 6.0.8.11.3. I want to call a function from my PL/SQL library in the "BeforeReport" trigger in each of my reports. The function is called correctly, but EXEC_SQL.EXECUTE_AND_FETCH always returns 0 rows when the function is in my PL/SQL library. If I copy the code from the library and place it in the "BeforeReport" trigger directly then the result set is returned correcly from EXEC_SQL.EXCUTE_AND_FETCH. See the snippets below.

BeforeReport Trigger:

function BeforeReport return boolean is
BEGIN
  SETRPTPARAMS( :username, :area, :grp, :cust );   return (true);
END; PL/SQL Library procedure:  

PROCEDURE SetRptParams ( pusername IN VARCHAR2, area_out IN OUT VARCHAR2, grp_out IN OUT VARCHAR2, cust_out IN OUT VARCHAR2 ) IS /*
Purpose: Obtain the GROUP_ID, AREA_ID, and CUST_ID from the ODS and assign

         the values to the user parameters :area, :grp, and :cust when not

         in drill down mode.
*/
  connection_id EXEC_SQL.CONNTYPE;
  cursorID EXEC_SQL.CURSTYPE;

  sqlstr VARCHAR2(1000); 			--SQL query
  loc_areaid VARCHAR2(70);			--Local copy of area id.
  loc_groupid VARCHAR2(32000); 			--Local copy of group id.
  rtn_grp VARCHAR2(32000);			--Local variable for string manipulation
of groups.
  nIgn PLS_INTEGER; 			        --Return value for the the

EXEC_SQL.EXECUTE_AND_FETCH
  connect_str VARCHAR2(1000) := 'xxxxx/xxxxxx_at_xxxxx'; --Connection string to the DB(xxx for usenet posting)

  out_file TEXT_IO.File_Type; 		--debug file 
  CUSTID_COL CONSTANT NUMBER := 2; 	--Index of the CUST_ID column in
the DB.
  AREA_COL CONSTANT NUMBER := 3; 	--Index of the AREA_ID column in the
DB.
  GROUP_COL CONSTANT NUMBER := 4; 	--Index of GROUP_ID column in the

DB.   

BEGIN
--Open a connection and cursor to the ODS.
 connection_id := EXEC_SQL.OPEN_CONNECTION(connect_str);  cursorID := EXEC_SQL.OPEN_CURSOR(connection_id);         

--Format and execute the SQL statement
 sqlstr := 'select * from security where user_id = ''' || pusername ||'''';
 EXEC_SQL.PARSE(connection_id, cursorID, sqlstr, exec_sql.v7);  EXEC_SQL.DEFINE_COLUMN(connection_id, cursorID, AREA_COL, loc_areaid, 70);
 EXEC_SQL.DEFINE_COLUMN(connection_id, cursorID, GROUP_COL, loc_groupid, 70);
 nIgn := EXEC_SQL.EXECUTE_AND_FETCH (connection_id, cursorID);

--Retrieve data from returned row set

 IF (nIgn = 0 ) THEN
   --Execution always end up here when in a PL/SQL libary    TEXT_IO.PUT_LINE (out_file,'There is not a user with ID = ' || pusername);
 ELSIF (nIgn = 1) THEN
   TEXT_IO.PUT_LINE (out_file,'Found a user with ID ' || pusername);           

• obtain the values in this row EXEC_SQL.column_value(connection_id, cursorID, AREA_COL, loc_areaid); EXEC_SQL.column_value(connection_id, cursorID, GROUP_COL, loc_groupid); END IF;

--Clean up cursor and connection

 EXEC_SQL.CLOSE_CURSOR(connection_id, cursorID);  EXEC_SQL.CLOSE_CONNECTION(connection_id); EXCEPTION
  WHEN EXEC_SQL.PACKAGE_ERROR THEN
    TEXT_IO.PUT_LINE('ERROR (' ||
TO_CHAR(EXEC_SQL.LAST_ERROR_CODE(connection_id)) || '): '

||	EXEC_SQL.LAST_ERROR_MESG(connection_id));
    IF EXEC_SQL.IS_CONNECTED(connection_id)	THEN
      IF EXEC_SQL.IS_OPEN(connection_id, cursorID) THEN
	EXEC_SQL.CLOSE_CURSOR(connection_id, cursorID);
      END IF;
      EXEC_SQL.CLOSE_CONNECTION(connection_id);

    END IF;
END; Received on Wed Jul 10 2002 - 21:48:06 CEST