oracle testing function that contains cursor with sql developer

Question

I wrote a function in oracle but I cannot test it. It gives type error but I can call it from java. I want to test it in sql developer tool.I checked all parameter types but they are correct.

My function is like this:

create or replace
FUNCTION           GET_STUDENT_SCORE_FN(
p_no               IN  VARCHAR2,
p_date             IN  DATE,
p_periods          IN  NUMBER,
p_cycle            OUT VARCHAR2,
p_return_code      OUT INTEGER,
p_return_desc      OUT VARCHAR2)
RETURN SYS_REFCURSOR   

AS
cursor_response_score   SYS_REFCURSOR;
def_refcur                  SYS_REFCURSOR;
BEGIN

 IF p_no IS NULL
 THEN
    p_cycle := NULL;
    p_return_code := -1;
    p_return_desc := ' no can not be null!';
    RETURN def_refcur;
 END IF;
OPEN cursor_response_score FOR
    SELECT * from students;
return cursor_response_score;
END

I want to test my function.I wrote some codes like this

DECLARE
P_NO VARCHAR2(200);
P_DATE DATE;
P_PERIODS NUMBER;
P_CYCLE VARCHAR2(200);
P_RETURN_CODE NUMBER;
P_RETURN_DESC VARCHAR2(200);
v_Return SYS_REFCURSOR;

BEGIN
 P_NO := '5325551374';
 P_DATE := to_date('1002019','ddmmyyyy');
 P_PERIODS := null;

v_Return := GET_STUDENT_SCORE_FN(
P_NO => P_NO,
P_DATE => P_DATE,
P_PERIODS => P_PERIODS,
P_CYCLE => P_CYCLE,
P_RETURN_CODE => P_RETURN_CODE,
P_RETURN_DESC => P_RETURN_DESC
);

/* Legacy output: 
DBMS_OUTPUT.PUT_LINE('P_CYCLE = ' || P_CYCLE);
*/ 
:P_CYCLE := P_CYCLE;  
/* Legacy output: 
DBMS_OUTPUT.PUT_LINE('P_RETURN_CODE = ' || P_RETURN_CODE);
*/ 
:P_RETURN_CODE := P_RETURN_CODE;
/* Legacy output: 
DBMS_OUTPUT.PUT_LINE('P_RETURN_DESC = ' || P_RETURN_DESC);
*/ 
:P_RETURN_DESC := P_RETURN_DESC;
/* Legacy output: 
DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
*/ 
:v_Return := v_Return; --<-- Cursor
END;

But it gives error like this when i run above query on sql developer( click: run)

ORA-06550: line 25, column 20:
PLS-00382: expression is of wrong type
ORA-06550: line 25, column 4:
PL/SQL: Statement ignored
ORA-06550: line 37, column 16:
PLS-00382: expression is of wrong type
ORA-06550: line 37, column 3:
PL/SQL: Statement ignored

I run above query in on sql page it gives

Bind Variable "P_CYCLE" is NOT DECLARED
anonymous block completed

Alex Poole · Accepted Answer · 2021-03-01 16:33:49Z

You can test it from a SQL worksheet using variable and print:

var P_NO VARCHAR2(200);
-- has to be a string, unfortunately; var doesn't support dates directly
var P_DATE VARCHAR2(10);
var P_PERIODS NUMBER;
var P_CYCLE VARCHAR2(200);
var P_RETURN_CODE NUMBER;
var P_RETURN_DESC VARCHAR2(200);
var V_RETURN REFCURSOR;


BEGIN
 :P_NO := '5325551374';
 :P_DATE := '2019-02-10';
 :P_PERIODS := null;

 :V_RETURN := GET_STUDENT_SCORE_FN(
    P_NO => :P_NO,
    P_DATE => to_date(:P_DATE, 'YYYY-MM-DD'),
    P_PERIODS => :P_PERIODS,
    P_CYCLE => :P_CYCLE,
    P_RETURN_CODE => :P_RETURN_CODE,
    P_RETURN_DESC => :P_RETURN_DESC
  );
END;
/

print P_CYCLE;
print P_RETURN_CODE;
print P_RETURN_DESC;

print V_RETURN;

Notice that the var and print commands don't have colons; but within the PL/SQL block (or just within SQL) they are treated as bind variables, so they do have them there.

The IN parameters don't necessarily have to be bound, so you could simplify slightly to:

var P_CYCLE VARCHAR2(200);
var P_RETURN_CODE NUMBER;
var P_RETURN_DESC VARCHAR2(200);
var V_RETURN REFCURSOR;


BEGIN
 :V_RETURN := GET_STUDENT_SCORE_FN(
    P_NO => '5325551374',
    P_DATE => date '2019-02-10',
    P_PERIODS => null,
    P_CYCLE => :P_CYCLE,
    P_RETURN_CODE => :P_RETURN_CODE,
    P_RETURN_DESC => :P_RETURN_DESC
  );
END;
/

print P_CYCLE;
print P_RETURN_CODE;
print P_RETURN_DESC;

print V_RETURN;

Collectives™ on Stack Overflow

oracle testing function that contains cursor with sql developer

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related