CODEWITHSUNDEEP
sqloracle-databaseplsql
user3790692
user3790692

Reputation: 72

ERROR at line 10: PL/SQL: Statement ignored

I'm trying to create a proceure on oracle pl/sql, but i'm getting error.

My procedure's code is:

CREATE OR REPLACE PROCEDURE PR_LISTA_CLI (cpf IN VARCHAR2, p_rc OUT SYS_REFCURSOR)
IS
  checkCpf NUMBER; 
BEGIN   

    SELECT COUNT(CPF_CLIENTE) INTO checkCpf FROM CLIENTE WHERE CPF_CLIENTE = cpf;

    IF (checkCpf > 0) THEN
      open p_rc
       for
        select 
            com.data_compra,
            lst.qde_produto, 
            prd.nome_produto, 
            prd.valor_produto * lst.qde_produto
        from cliente cli 
            inner join compra com on cli.cod_cliente = com.cod_cliente 
            inner join lista_produto lst on com.cod_compra = lst.cod_compra
            inner join produto prd on lst.cod_produto = prd.cod_produto
        WHERE cli.cpf_cliente = cpf
        order by com.data_compra asc;
    ELSE
        RAISE_APPLICATION_ERROR(-20999,'ERRO! Cpf não cadastrado!', FALSE);
    END IF;
END;

The procedure is created with sucess, no errors.

But i got the error after calling it - CALL PR_LISTA_CLI(111111111);

ORA-00911: invalid character

How to fix it, pleasse?

Thank you!!!

Upvotes: 0

Views: 836

Answers (1)

Justin Cave
Justin Cave

Reputation: 231851

Your compilation error is at line 10 which is where you are doing your SELECT. Unless you are using 12.1 or later and hoping to use the new implicit statement results functionality, you cannot have a SELECT statement in a PL/SQL block unless there is something to fetch the results into (e.g. a [BULK COLLECT] INTO clause) or you are using the SELECT statement to open a cursor. Assuming that your query returns multiple rows and that your intention is to return a cursor to the caller, you could do something like

CREATE OR REPLACE PROCEDURE PR_LISTA_CLI (cpf IN VARCHAR2, p_rc OUT SYS_REFCURSOR)
IS
  vEXCEPTION EXCEPTION;
  checkCpf NUMBER; 
BEGIN   

    SELECT COUNT(CPF_CLIENTE) INTO checkCpf FROM CLIENTE WHERE CPF_CLIENTE = cpf;

    IF (checkCpf > 0) THEN
      open p_rc
       for
        select 
            com.data_compra,
            lst.qde_produto, 
            prd.nome_produto, 
            prd.valor_produto * lst.qde_produto
        from cliente cli 
            inner join compra com on cli.cod_cliente = com.cod_cliente 
            inner join lista_produto lst on com.cod_compra = lst.cod_compra
            inner join produto prd on lst.cod_produto = prd.cod_produto
        WHERE cli.cpf_cliente = cpf
        order by com.data_compra asc
    ELSE
        RAISE vEXCEPTION;
    END IF;
    EXCEPTION
    WHEN vEXCEPTION THEN
        RAISE_APPLICATION_ERROR(-20999,'ERRO! Cpf não cadastrado!', FALSE);
END;

Now, if that's what you're doing

  • It would make more sense to create a function that returns a sys_refcursor since you're not modifying any database state.
  • The exception that you've defined doesn't appear to be useful. You're raising an exception and then catching that same exception only to throw a different exception. Cut out the middle man, remove the exception handler and the vException variable and just call RAISE_APPLICATION_ERROR in your ELSE clause (assuming it really makes sense to throw an exception in that case in the first place).

Upvotes: 2

Related Questions