CODEWITHSUNDEEP
oracle-databaseplsql
New Coder
New Coder

Reputation: 3

Write a PL/SQL procedure with no parameters Ins_Job that inserts new record in table JOBS

CREATE OR REPLACE PROCEDURE Insert_Job AS
    v_job_id VARCHAR2(10) := 'HR_Assistant';
    v_job_title VARCHAR2(35) := 'HRessourcesAssistant';
    v_Min_Salary NUMBER(22):= 400;
    v_max_salary NUMBER (22):= 5000;
  
BEGIN
    INSERT INTO OEHR_JOBS (JOB_ID, JOB_TITLE,MIN_SALARY,MAX_SALARY)
    VALUES (v_job_id, v_job_title, v_Min_Salary, v_max_salary);
    dbms_output.put_line('Insertion OK');
End;

Write a PL/SQL procedure with no parameters Insert_Job that inserts a new record in the table JOBS: - Job Id: Assistant - Job title: HRessources Assistant - Min Salary: 400 - Max Salary: 5000 The procedure should print a message to the user to inform him about the insertion status (success/ Error). Call the procedure and take a screenshot of the output in both cases.

This is what I try and it doesn't work any help please? It created the procedure but no data into the table, an also when I call it it shows me an error. I'm usnig APEX ORACLE HERE IS THE ERROR MESSAGE BELOW

ORA-06550: line 2, column 1: PLS-00905: object WKSP_ELIASHOME.INSERT_JOB is invalid ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_200200", line 626 ORA-06550: line 2, column 1: PL/SQL: Statement ignored ORA-06512: at "SYS.DBMS_SYS_SQL", line 1658 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_200200", line 612 ORA-06512: at "APEX_200200.WWV_FLOW_DYNAMIC_EXEC", line 1749

Upvotes: 0

Views: 348

Answers (1)

Fauzi88
Fauzi88

Reputation: 713

When I read your error message: numeric or value error: character string buffer too small This mean your length is too small. In your code:

 v_job_id VARCHAR2(10) := 'HR_Assistant';

It was because length 'HR_Assistant' is longer then 10

Try to change lenght v_job_id into:

v_job_id VARCHAR2(12) := 'HR_Assistant';

Or you can remove some character, into:

v_job_id VARCHAR2(10) := 'Assistant';

Or, maybe you must check first length from column JOB_ID in table OEHR_JOBS

Sorry if my english bad and I don't really familiar with PLSQL.

Upvotes: 1

Related Questions