sql exec stored procedure - JointLantic - Program Language about Peoplesoft, Peoplecode, People Tool , SQL, Application Engine (AE), XML / BI Publisher

oracle sql exec stored procedure

This syntax sql for execute store procedure in oracle

Procedure Name: JS_WS_GET_ABS_TYPE

create or replace procedure "JS_WS_GET_ABS_TYPE"
(p_EMPID IN VARCHAR2
 pcursor OUT SYS_REFCURSOR
)
AS
.....

create or replace procedure "JS_WS_GET_ABS_TYPE"

(p_EMPID IN VARCHAR2

pcursor OUT SYS_REFCURSOR

)

.....

How to execute prodecure and print output in sql

Set serveroutput on;

DECLARE
v_cur SYS_REFCURSOR;
v_1 VARCHAR2(254);
v_2 VARCHAR2(254);
v_3 VARCHAR2(254);
v_4 VARCHAR2(254);
v_5 VARCHAR2(254);
v_6 VARCHAR2(254);
v_7 VARCHAR2(254);
v_8 VARCHAR2(254);
v_9 VARCHAR2(254);
v_10 VARCHAR2(254);
BEGIN
JS_WS_GET_ABS_TYPE('0003',v_cur);

LOOP
FETCH v_cur INTO v_1, v_2, v_3, v_4, v_5, v_6, v_7, v_8, v_9, v_10;
EXIT WHEN v_cur%NOTFOUND;
dbms_output.put_line(v_1 || ', ' || v_2 || ', ' || v_3 || ', ' || v_4 || ', ' || v_5 || ', ' || v_6 
|| ', ' || v_7 || ', ' || v_8 || ', ' || v_9 || ', ' || v_10);
END LOOP;
CLOSE v_cur;
END;

Set serveroutput on;

DECLARE

v_cur SYS_REFCURSOR;

v_1 VARCHAR2(254);

v_2 VARCHAR2(254);

v_3 VARCHAR2(254);

v_4 VARCHAR2(254);

v_5 VARCHAR2(254);

v_6 VARCHAR2(254);

v_7 VARCHAR2(254);

v_8 VARCHAR2(254);

v_9 VARCHAR2(254);

v_10 VARCHAR2(254);

BEGIN

JS_WS_GET_ABS_TYPE('0003',v_cur);

LOOP

FETCH v_cur INTO v_1, v_2, v_3, v_4, v_5, v_6, v_7, v_8, v_9, v_10;

EXIT WHEN v_cur%NOTFOUND;

dbms_output.put_line(v_1 || ', ' || v_2 || ', ' || v_3 || ', ' || v_4 || ', ' || v_5 || ', ' || v_6

|| ', ' || v_7 || ', ' || v_8 || ', ' || v_9 || ', ' || v_10);

END LOOP;

CLOSE v_cur;

END;

Syntax sql for select store procedure in oracle

SELECT Text FROM User_Source WHERE Name ='JS_WS_GET_ABS_TYPE' ORDER BY Line

1	SELECT Text FROM User_Source WHERE Name ='JS_WS_GET_ABS_TYPE' ORDER BY Line