Dynamic query using Pl/SQL language

Question

How can I get date format in dd/mm/yyyy using dynamic PL/SQL query..?

I have tried so many options but nothing works. Can anybody suggest correct way get date of birth in below format (dd/mm/yyyy)

V_DYNAMICSQL := 'select to_char(a.dat_of_birth) Date_of_birth
from genmst_customer a'

Execute immediate v_dynamicsql;

Answer 1

to_char itself isn't enough - you should provide format model as well.

Sample table:

SQL> desc genmst_customer
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENAME                                              VARCHAR2(10)
 DAT_OF_BIRTH                                       DATE

SQL> select * from genmst_customer;

ENAME      DAT_OF_BI
---------- ---------
CLARK      09-JUN-81
KING       17-NOV-81
MILLER     23-JAN-82

PL/SQL procedure; note format model in line #5. I'm fetching only one row:

SQL> set serveroutput on
SQL> declare
  2    v_dynamicsql varchar2(500);
  3    result       varchar2(20);
  4  begin
  5    v_dynamicsql := q'[select to_char(a.dat_of_birth, 'dd/mm/yyyy')]' ||
  6                     ' from genmst_customer a where rownum = 1';
  7    execute immediate v_dynamicsql into result;
  8    dbms_output.put_line('Result = ' || result);
  9  end;
 10  /
Result = 09/06/1981

PL/SQL procedure successfully completed.

SQL>

---

[EDIT], based on your comment.

If you want to use alias and refcursor, no problem. Here's an example:

SQL> create or replace function f_test
  2    return sys_refcursor
  3  is
  4    rc sys_refcursor;
  5  begin
  6    open rc for select name, to_char(dat_of_birth, 'dd/mm/yyyy') as dob
  7                from genmst_customer
  8                where rownum <= 3;
  9    return rc;
 10  end;
 11  /

Function created.

SQL> select f_test from dual;

F_TEST
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

NAME       DOB
---------- ----------
SMITH      17/12/1980
ALLEN      20/02/1981
WARD       22/02/1981


SQL>

Answer 2

There is no need to use dynamic SQL because there is nothing dynamic about your query. You can just select the value as a DATE (which has no format) and then when you want to display it, then you can use TO_CHAR and provide a format model:

DECLARE
  v_date_of_birth GENMST_CUSTOMER.DAT_OF_BIRTH%TYPE;
BEGIN
  SELECT dat_of_birth
  INTO   v_date_of_birth
  FROM   genmst_customer
  WHERE  id = 1;

  DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_date_of_birth, 'dd/mm/yyyy'));
END;
/

Which, for the sample data:

CREATE TABLE genmst_customer (id, dat_of_birth) AS
SELECT 1, DATE '1970-01-01' FROM DUAL UNION ALL
SELECT 2, DATE '1980-01-01' FROM DUAL UNION ALL
SELECT 3, DATE '1990-01-01' FROM DUAL UNION ALL
SELECT 4, DATE '2000-01-01' FROM DUAL;

Outputs:

01/01/1970

fiddle