Executing a SQL query in string format

Question

I have a query in quotes(' '). I want Oracle SQL to not to treat as string but as a query and store the data from the query in a variable.

Here I am trying to store the date in array - example:

[10-JAN-22 01.47,10-JAN-22 01.47]

which is a string by the way

Code is here

DECLARE qms varchar(400);

BEGIN
    qms := 'select ''['' || TO_CHAR(CURRENT_TIMESTAMP, ''DD-MON-RR HH.MI'') || '',''|| TO_CHAR(CURRENT_TIMESTAMP, ''DD-MON-RR HH.MI'') || '']'' as customdate from dual';
    dbms_output.put_line('value of varl is ' ||qms);
END;

Here is the output

value of varl is select '[' || TO_CHAR(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI') || ','|| TO_CHAR(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI') || ']' as customdate from dual

I need to get the data from query but here the query is getting printed.

I am newbie and just learning SQL - any help would be appreciated

As a side observation, why why why why why WHY are you using 2-digt years? All you are doing is re-creating the Y2K issue. And the RR mask was meant to be a temporary band-aid solution to buy some time back in 1998, 1999. All it really did was kick the can down the road, as a lot of people discovered - to their dismay - around 1-JAN-2020. — EdStevens
– EdStevens, Commented Jan 10, 2022 at 16:00

Littlefoot · Accepted Answer · 2022-01-10 09:13:22Z

1

You'll need dynamic SQL for that:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     qms    VARCHAR (400);
  3     l_res  VARCHAR2 (50);
  4  BEGIN
  5     qms :=
  6        'select ''['' || TO_CHAR(CURRENT_TIMESTAMP, ''DD-MON-RR HH.MI'') || '',''|| TO_CHAR(CURRENT_TIMESTAMP, ''DD-MON-RR HH.MI'') || '']'' as customdate from dual';
  7
  8     EXECUTE IMMEDIATE qms
  9        INTO l_res;
 10
 11     DBMS_OUTPUT.put_line ('value of varl is ' || l_res);
 12  END;
 13  /
value of varl is [10-SIJ-22 10.12,10-SIJ-22 10.12]

PL/SQL procedure successfully completed.

SQL>

answered Jan 10, 2022 at 9:13

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Executing a SQL query in string format

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related