CREATE OR REPLACE PACKAGE FM_pkg AS
      TYPE RefCursor IS REF CURSOR ;
     PROCEDURE Get_VidrFetch
	  (
	  choice IN VIDR.id%TYPE,
	  Vidr_cur OUT RefCursor
	  );
   END ;
/

CREATE OR REPLACE PACKAGE BODY FM_pkg AS
       PROCEDURE Get_VidrFetch
	  (
	  choice IN VIDR.id%TYPE,
	  Vidr_cur OUT RefCursor
	  )
	   IS
	    cPers VARCHAR2( 100 );
       BEGIN
	   DBMS_OUTPUT.Enable( 100000 );
       OPEN Vidr_cur FOR
	SELECT c.fio ||' '|| c.name ||' '|| c.otch AS Pers
	   FROM VIDR a, RVD b, fxb.TFV_Karta c
	   WHERE a.WHERETO = b.id
	   AND a.person = c.tn  AND a.WHERETO = choice;
	LOOP
	FETCH Vidr_cur INTO cPers  ;
	EXIT WHEN Vidr_cur%NOTFOUND ;
	END LOOP;
  DBMS_OUTPUT.put_line('ФИО  '|| cPers);

-----	   close Vidr_cur;
     END ;
   END ;
/

declare
somecur invent.fm_pkg.RefCursor ;
code integer := 17 ;
 begin
  invent.fm_pkg.Get_VidrFetch (code, somecur);
 end;

ФИО Иванов Петр Васильевич

noVal1 = ''
 cJobNo =  20  
    
  nSQLResult = SQLExec(nConnHandle, "{call Invent.FM_pkg.Get_VidrFetch (?cJobNo, ?noVal1)}","MyCursor")

nSQLResult = SQLExec(nConnHandle, "{call Invent.FM_pkg.Get_VidrFetch (?cJobNo)}","MyCursor")

cSql = 'declare somecur invent.fm_pkg.RefCursor ; code integer :=17; begin invent.fm_pkg.Get_VidrFetch (code, somecur); end;'
 
 WAIT WINDOW cSql

nSQLResult = SQLExec(nConnHandle, cSql,'MyCursor')

----------------------------------
Timestamp:  12 : 26 : 21 . 872 

declare somecur invent.fm_pkg.RefCursor ; code integer := 17 ; begin
invent.fm_pkg.Get_VidrFetch (code, somecur); end;

----------------------------------

 SELECT   MyCursor
  brow

ERROR N13
Псевдоним MyCursor не найден....

SELECT c.fio ||' '|| c.name ||' '|| c.otch AS Pers
	   FROM VIDR a, RVD b, fxb.TFV_Karta c
	   WHERE a.WHERETO = b.id
	   AND a.person = c.tn  AND a.WHERETO =  17  ;

PROCEDURE Get_VidrFetch
	  (
	  choice IN VIDR.id%TYPE,
	  Vidr_cur OUT RefCursor
	  )
	   IS
	    cPers VARCHAR2( 100 );
       BEGIN
	   DBMS_OUTPUT.Enable( 100000 );
       OPEN Vidr_cur FOR
	SELECT c.fio ||' '|| c.name ||' '|| c.otch AS Pers
	   FROM VIDR a, RVD b, fxb.TFV_Karta c
	   WHERE a.WHERETO = b.id
	   AND a.person = c.tn  AND a.WHERETO = choice;
     END ;

CONNECT invent/**** ;

CREATE OR REPLACE PACKAGE my_pkg AS
      TYPE curTyp IS REF CURSOR;
      PROCEDURE open_my_cur
	  (
	  choice IN INTEGER,
	  my_cur OUT curTyp
      );
   END ;
/

CREATE OR REPLACE PACKAGE BODY my_pkg AS
     PROCEDURE open_my_cur (choice IN INTEGER, my_cur OUT curTyp
                             ) IS
       BEGIN
       OPEN my_cur FOR
	   SELECT a.OutDATE, a.BackDATE, c.fio ||' '|| c.name ||' '|| c.otch AS Pers, a.AIM, a.REPORT, b.CODE, b.NAME
	   FROM VIDR a, RVD b, fxb.TFV_Karta c
	   WHERE a.WHERETO = b.id
	   AND a.person = c.tn  AND a.WHERETO >= choice;
     END open_my_cur;
   END my_pkg;
/

nWhereTo =  20 
 noVal1 = ' '
 
 nSQLResult = SQLEXEC(nConnHandle,"{call invent.my_pkg.open_my_cur(?nWhereTo, ?noVal1)}","fmc")
    
  SELECT fmc
  brow

 ----------------------------------  
  Timestamp:  12 : 02 : 19 . 172   
  BEGIN invent.my_pkg.open_my_cur(: 1  , : 2  ); END;  
  : 1  =  20 

GRANT execute on my_pkg to user02;

CONNECT user02/**** ;

declare
cCursor invent.my_pkg.curTyp ;
nGo INTEGER := 10 ;

begin

invent.my_pkg.open_my_cur (nGo, cCursor);

end;
/

PL/SQL procedure successfully completed

CONNECT user02/**** ;

declare
cCursor invent.my_pkg.curTyp ;
nGo INTEGER := 10 ;
cpers VARCHAR2( 50 );
d1 DATE;
d2 DATE;
caim VARCHAR2( 200 );
cRep VARCHAR2( 1000 );
cCode VARCHAR2( 10 );
cNm VARCHAR2( 50 );

begin

invent.my_pkg.open_my_cur (nGo, cCursor);

LOOP
FETCH cCursor  INTO d1, d2, cpers,  caim, cRep, cCode, cNm  ;
EXIT WHEN cCursor%NOTFOUND ;
DBMS_OUTPUT.put_line(': ' || cpers || ' - ' || cCode || ' - '|| cNm);
END LOOP;
end;
/

PL/SQL procedure successfully completed

: Иванов Петр Васильевич -  2222  - Директор
: Безымянная валентина Николаевна -  21006  - Главный бухгалтер

nWhereTo =  20 
 noVal1 = ' '
 
 nSQLResult = SQLEXEC(nConnHandle,"{call invent.my_pkg.open_my_cur(?nWhereTo, ?noVal1)}","fmc")
    
  SELECT fmc
  brow

----------------------------------  
  Timestamp:  12 : 13 : 47 . 223   
    
  BEGIN invent.my_pkg.open_my_cur(: 1  , : 2  ); END;  
    
  : 1  =  20   
  : 2  = ''  
  Oracle error occurred:  6550  (ORA- 06550 : 
  PLS- 00306 : неверное количество или тип аргументов  'OPEN_MY_CUR'  
  PL/SQL: Statement ignored)  
  ----------------------------------