Using PL/SQL Debuger with OracleDB 23.5 within Docker on a Mac with an Apple Silicon Chip

README.md

Using PL/SQL Debuger with OracleDB 23.5 within Docker on a Mac with an Apple Silicon Chip

1. Create the Oracle Database

# create container
docker run -d \
    --name oracledb \
    -p 1523:1521 \
    -v oradata:/opt/oracle/oradata \
    phsalvisberg/oracle-database-free:23.5-arm

# change password
docker exec -it oracledb ./setPassword.sh oracle

2. Create User

sql sys/oracle@127.0.0.1:1523/freepdb1 as sysdba <<EOF
create user if not exists xyz identified by xyz
   default tablespace users
   quota 1g on users;
   
grant db_developer_role to xyz;
grant debug connect session to xyz;
grant debug connect any to xyz;
grant debug any procedure to xyz;

begin
   dbms_network_acl_admin.append_host_ace(
      host => '*',
      ace  => sys.xs\$ace_type(
                 privilege_list => sys.xs\$name_list('jdwp') ,
                 principal_name => 'XYZ',
                 principal_type => sys.xs_acl.ptype_db
              )
   );
end;
/
EOF

3. Demo PL/SQL procedure

sql xyz/xyz@127.0.0.1:1523/freepdb1 <<EOF
alter session set  plsql_optimize_level = 1;
create or replace procedure p1 is
   l_value integer := 1;
begin
   dbms_output.put_line(l_value);
   l_value := l_value + 1;
   dbms_output.put_line(l_value);
end;
/
EOF

4. Debug

• Open VS Code
• Create a connection for XYZ
• Open procedure P1
• Set a breakpoint on line 5
• Start the debugger and enter the IP address of your computer in the local network, e.g. 192.168.106.1
• Important: 127.0.0.1 cannot work, because we need to communicate from the OracleDB in the docker container to the client running the debugger in VS Code. So the OracleDB needs the IP address of the client.

5. More Information

• Video Using the PL/SQL Debugger APEX@Home 2020
• Slides