pl/sql interview questions 1

How to find dependencies referenced Inside PL/SQL Store Procedure

The Oracle data dictionary tracks the object types referenced in PL/SQL with the dba_dependencies view. To track the dependency among packages and tables, try this dictionary query:

select
  referenced_owner,
  referenced_name,
  referenced_type
from
  DBA_DEPENDENCIES
where
  name= 'YOUR_PROCEDURE or PACKAGE NAME' and   owner = 'SCOTT'
order by
  referenced_owner, referenced_name, referenced_type;

Finding PL/SQL compilation errors

PL/SQL does not always tell you about compilation errors.

SQL>SHOW ERRORS
No errors.

But how to see the error?

You have to give the full command as follows:

SQL>SHOW ERROR PROCEDURE 


Show Errors syntax:

Usage: SHOW ERRORS [{ FUNCTION | PROCEDURE | PACKAGE |
   PACKAGE BODY | TRIGGER | VIEW
   | TYPE | TYPE BODY | DIMENSION
   | JAVA SOURCE | JAVA CLASS } [schema.]name]

 
Finding Record count of all tables?

   PROMPT
     PROMPT ROWCOUNT for Table &Table_Name.

     SET FEEDBACK OFF
     SET SERVEROUTPUT ON
     DECLARE N NU MBER ;
     V VARCHAR2(100) ;
     BEGIN
     V := 'SELECT COUNT(*) FROM ' || UPPER('&Table_Name.') ;
     EXECUTE IMMEDIATE V INTO N ;
     DBMS_OUTPUT.PUT_LINE (N);
     END;
     /

SQL>SELECT COUNT(*) FROM EMP;
14 Records. 
SQL>SELECT COUNT(*) FROM DEPT;
4 Records. 
...
...

How do you find when an object was created , last modified date?

SELECT OWNER, OBJECT_NAME, CREATED, LAST_DDL_TIME
FROM DBA_OBJECTS
WHERE OBJECT_NAME = 'enter_object_name_here';
 
 
Sending E-Mail in PL/SQL

Oracle allows us to send email from PL/SQL. You can create a procedure to send mail as follows:

CREATE OR REPLACE PROCEDURE SEND_MAIL (
    msg_from     VARCHAR2 := 'sender@domain.com'
  , msg_to       VARCHAR
  , msg_subject  VARCHAR2 := 'E-Mail message from your database'
  , msg_text     VARCHAR2 := ''
)
IS
  c   UTL_TCP.CONNECTION;
  rc  INTEGER;
BEGIN
  c  := UTL_TCP.OPEN_CONNECTION('localhost', 25);       -- open the SMTP port 25 on local machine
  rc := UTL_TCP.WRITE_LINE(c, 'HELO localhost');
  rc := UTL_TCP.WRITE_LINE(c, 'MAIL FROM: '||msg_from);
  rc := UTL_TCP.WRITE_LINE(c, 'RCPT TO: '||msg_to);
  rc := UTL_TCP.WRITE_LINE(c, 'DATA');                  -- Start message body
  rc := UTL_TCP.WRITE_LINE(c, 'Subject: '||msg_subject);
  rc := UTL_TCP.WRITE_LINE(c, '');
  rc := UTL_TCP.WRITE_LINE(c, msg_text);
  rc := UTL_TCP.WRITE_LINE(c, '.');                     -- End of message body
  rc := UTL_TCP.WRITE_LINE(c, 'QUIT');
  UTL_TCP.CLOSE_CONNECTION(c);                          -- Close the connection
EXCEPTION
  WHEN others THEN
    RAISE_APPLICATION_ERROR(-20000, 'Unable to send e-mail message from PL/SQL routine.');
END;

SQL>send_mail('sender@domain.com','recipient@domain.com','Test message from first PLSQL','your message here ');