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   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 ;

LOOP
	FETCH Vidr_cur INTO cPers  ;
	EXIT WHEN Vidr_cur%NOTFOUND ;
	END LOOP;
  DBMS_OUTPUT.put_line('ФИО  '|| cPers);

PROCEDURE Get_VidrFetch

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)  
  ----------------------------------