0
create or replace PROCEDURE CHECK_BLACKLIST 
(
  P_EMPLOYEE_USERNAME IN VARCHAR2 
, P_ROWCOUNT OUT NUMBER 
) AS 
BEGIN
  SELECT COUNT(*) INTO P_ROWCOUNT FROM boe_safegaurd WHERE UPPER(employee_username) = UPPER(P_EMPLOYEE_USERNAME);  
END CHECK_BLACKLIST;

Errors

I entered joe@example.com when I ran the procedure.

ORA-06550: line 5, column 26:
PLS-00352: Unable to access another database 'example.com'
ORA-06550: line 5, column 26:
PLS-00201: identifier 'joe@eample.com' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
Process exited.

when I enter "joe@example.com", I get a similar error

ORA-06550: line 5, column 27:
PLS-00201: identifier 'joe@eample.com' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
2

You're right. While the procedure executes correctly from SQL or PL/SQL code, somehow SQLDeveloper raises an exception when you invoke the procedure (via the green arrow) and pass anything as input that contains a "@" sign. It's either a peculiar behavior of SQL Developer or a bug. x But executing it directly works fine, also from SQL Developer: x

0
create or replace PROCEDURE CHECK_BLACKLIST
(
  P_EMPLOYEE_USERNAME IN VARCHAR2
, P_ROWCOUNT OUT NUMBER
) AS
BEGIN
  SELECT COUNT(*) INTO P_ROWCOUNT FROM all_users WHERE UPPER(username) = UPPER(P_EMPLOYEE_USERNAME);
END CHECK_BLACKLIST;
/

Procedure created.

set serveroutput on
declare
  r number;
begin
  CHECK_BLACKLIST('joe@example.com', r);
  dbms_output.put_line(r);
end;
/

0

PL/SQL procedure successfully completed.

The procedure works. The way you try to use it, does not. But we can not see that, because you did not post that.

SQL Developer:

enter image description here

This line is just wrong:

P_EMPLOYEE_USERNAME := joe@example.com;

If you try to set P_EMPLOYEE_USERNAME as 'joe@example.com', SQL Developer automatically adds extra quoting marks to both to the input value and the generated code, which is also wrong:

enter image description here

Just edit the code manually to P_EMPLOYEE_USERNAME := 'joe@example.com'; as below:

enter image description here

To be honest, I never use this feature of SQL Developer.

2
  • I pressed the play button in SQL Developer and entered an email as the username. That’s where these errors started appearing. It does compile – software is fun May 1 '20 at 18:58
  • Can you show a screenshot? I am getting errors. – software is fun May 1 '20 at 19:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.