Invoke Built-in Functions in PL/SQL in Oracle

Q

How To Invoke Built-in Functions in PL/SQL in Oracle?

✍: FYIcenter.com

A

Of course, you can invoke SQL functions in SQL statements. But many SQL functions can also be executed in regular PL/SQL statements, as shown in the following sample script:

DECLARE
  now DATE;
  id NUMBER;
  str VARCHAR2(40);
BEGIN
  now := SYSDATE;
  DBMS_OUTPUT.PUT_LINE('Time #1 = ' ||
    TO_CHAR(now,'HH24:MI:SS'));
  SELECT SYSDATE INTO now FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('Time #2 = ' ||
    TO_CHAR(now,'HH24:MI:SS'));

  id := UID;
  DBMS_OUTPUT.PUT_LINE('User id #2 = ' || TO_CHAR(id));
  SELECT UID INTO id FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('User id #2 = ' || TO_CHAR(id));
  
  str := CHR(70)||CHR(89)||CHR(73);
  DBMS_OUTPUT.PUT_LINE('String #1 = ' || str);
  SELECT CHR(70)||CHR(89)||CHR(73) INTO str FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('String #2 = ' || str);
END;
/
Time #1 = 21:41:16
Time #2 = 21:41:16
User id #2 = 33
User id #2 = 33
String #1 = FYI
String #2 = FYI

 

Working with Database Objects in Oracle PL/SQL

⇒⇒Oracle Database Tutorials

2016-10-17, 216👍, 0💬