How to output information in Oracle db using in other pl/sql block?

Question

I have following code:

SET SERVEROUTPUT ON
ACCEPT empno PROMPT 'Enter empno: '
ACCEPT ename PROMPT 'Enter ename: '
ACCEPT job PROMPT 'Enter job: '
ACCEPT mgr PROMPT 'Enter mgr: '
ACCEPT sal PROMPT 'Tneter sal: '
ACCEPT deptno PROMPT 'Enter deptno: '
DECLARE
v_empno NUMBER := '&empno';
v_ename VARCHAR(255) := '&ename';
v_job VARCHAR(255) := '&job';
v_mgr NUMBER := '&mgr';
v_hire DATE := Sysdate;
v_sal NUMBER := '&sal';
v_comm NUMBER := null;
v_deptno NUMBER := '&deptno';
BEGIN
    INSERT INTO EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (v_empno, v_ename, v_job, v_mgr, v_hire, v_sal, v_comm, v_deptno);
    EXCEPTION
    WHEN value_error THEN
    DBMS_OUTPUT.Put_line('Error inserting data');
END;
/
ACCEPT vote PROMPT 'Commit changes?'
DECLARE
vote VARCHAR(10) := '&vote';
BEGIN
    IF vote = 'yes' THEN
    COMMIT COMMENT 'CHANGES COMMITED' WRITE IMMEDIATE NOWAIT;
    DBMS_OUTPUT.Put_line('New employee with empno ' || v_empno || ', name:' || v_ename|| ', job:' || v_job || ', his manager is ' || v_mgr + ' and salary is ' || v_sal);
    ELSE
    ROLLBACK;
    END IF;
END;

This code inserting data to table. It is working. But it doesnt outputs result from

DBMS_OUTPUT.Put_line('New employee with empno ' || v_empno || ', name:' || v_ename|| ', job:' || v_job || ', his manager is ' || v_mgr + ' and salary is ' || v_sal);

And I am receving error that v_empno must be declared. What should I do?

question from:https://stackoverflow.com/questions/65831737/how-to-output-information-in-oracle-db-using-in-other-pl-sql-block

Answer 1

Variables are restricted to the scope of their declaration. You have defined v_empno in one anonymous PL/SQL block: you cannot reference it in the second.

What you could do is reference the substitution variables instead:

DBMS_OUTPUT.Put_line('New employee with empno &&empno , name: &&ename, job:&&job , his manager is &&mgr and salary is &&sal');

Note the double ampersands. This notation indicates that the variable is populated from the previous instance of that named variable. It avoids the need to prompt the user each time.

But the underlying problem is that PL/SQL is not really intended for user-interaction. You're trying to built a UI using a language intended for autonomic data processing.