CODEWITHSUNDEEP
oracle-databaseplsql
Diogo dos Santos
Diogo dos Santos

Reputation: 176

Why am I getting this error: "ORA-00922: missing or invalid option" after running a PL/SQL procedure?

I created a table Patient with some attributes, like p_name, p_surname, p_number... I would like to create a procedure to transfer a patient from this table Patient to another table (Patient_backup), case his "p_number" attribute has received an input, deleting it from the first table and remaining only in the second. The second table has the same structure of the first one. I have coded the procedure like that.

CREATE TABLE patient (
p_number      VARCHAR2(10) NOT NULL,
p_name            VARCHAR2(15),
p_surname         VARCHAR2(15),
p_street            VARCHAR2(20),
p_city            VARCHAR2(15)
);

CREATE TABLE patient_backup (
p_number      VARCHAR2(10) NOT NULL,
p_name            VARCHAR2(15),
p_surname         VARCHAR2(15),
p_street            VARCHAR2(20),
p_city            VARCHAR2(15)
);

CREATE [OR REPLACE] PROCEDURE transfer (p_number VARCHAR2)
AS
CURSOR k1 IS SELECT p_number FROM patient;
BEGIN
OPEN k1;
LOOP
FETCH k1 INTO p_number;
IF p_number IS NULL THEN
dbms_output.put_line('empty');
ELSE
INSERT INTO patient_backup (SELECT * FROM patient);
Execute Immediate 'Drop Table patient;';
END IF;
END LOOP;
CLOSE k1;
END transfer;

But when I run it,I get the error "ORA-00922: missing or invalid option". Could you help me with that? I wonder if the code is correct. I have read a material about PL/SQL, but the concepts were not connected to each other, so I just tried to gather everything together, and I hope it is correct. Could you help me to correct this code and make it work?

Upvotes: 0

Views: 1509

Answers (3)

ᎮᏒᏗᏉᏋᏋᏁ
ᎮᏒᏗᏉᏋᏋᏁ

Reputation: 29

In Your procedure code have some error

1.P_NUMBER input parameter cannot be used into statment 2.don't use semicolon inside the EXECUTE IMMEDIATE string 3. in loop statement you should use exit otherwise it will run continuously

Here the code

CREATE OR REPLACE PROCEDURE TRANSFER (P_NUMBER IN VARCHAR2) AS
CURSOR K1 IS 
    SELECT P_NUMBER FROM PATIENT;
P_NUM PLS_INTEGER;    
BEGIN
    OPEN K1;
        LOOP
            FETCH K1 INTO P_NUM;
                IF P_NUM IS NULL THEN
                    DBMS_OUTPUT.PUT_LINE('EMPTY');
                ELSE
                    INSERT INTO PATIENT_BACKUP (SELECT * FROM PATIENT);
                    DELETE FROM PATIENT;
                END IF;
                EXIT WHEN P_NUM IS NULL;
        END LOOP;
CLOSE K1;
END TRANSFER;

Upvotes: 0

alexherm
alexherm

Reputation: 1362

I think you need to DECLARE your cursor before you define it.

Upvotes: 0

user330315
user330315

Reputation:

It's hard to tell where exactly the error is, but my guess is: remove the ; from inside the string for execute immediate.

But I think you want do not want to DROP the table - that removes the table completely from the database including all rows and its definition. It won't be accessible after that.

I think what you really want is to DELETE a row from that table, not remove the table completely.

Also: the whole loop is completely unnecessary (and inefficient). You can do that with two simple SQL statements:

insert into patient_backup
select *
from patient
where p_number = 42; --<< to pick one patient

delete from patient 
where p_number = 42;

Putting that into a procedure: 

CREATE PROCEDURE transfer (p_number_to_delete VARCHAR2)
AS
BEGIN
  insert into patient_backup
  select *
  from patient
  where p_number = p_number_to_delete;

  delete from patient 
  where p_number = p_number_to_delete;

END transfer;

It's highly recommended to not use the name of a column as the name of a parameter. That's why I named the parameter p_number_to_delete (but p_number is a bad name for a column that isn't a number to begin with - but that's a different discussion)

Upvotes: 5

Related Questions