CODEWITHSUNDEEP
sqloracle-databaseplsql
user2521079
user2521079

Reputation: 33

Oracle PLSQL script for string concatenation

I have a database of SQL queries, procedures etc.

Now I have following SQL statement with concatenation operators.

'INSERT INTO TEST_TABLE(ID, NAME)' || 'SELECT ID, NAME FROM ' || ' MY_TABLE'

I want that when I get SQL back it return

INSERT INTO TEST_TABLE(ID, NAME) SELECT ID, NAME FROM MY_TABLE

This SQL is just for example but basically I want to remove concatenations and return simple SQL.

Thanks

Upvotes: 1

Views: 2689

Answers (1)

haki
haki

Reputation: 9759

If i got the question right your looking for something like an eval to make oracle evaluate the string with the concatenation marks.

Try something like this 

declare
  concatenetaed_sql  varchar2(32000);
  result_Sql varchar2(32000);
begin
  concatenetaed_sql  := '''INSERT INTO TEST_TABLE(ID, NAME)'' || ''SELECT ID, NAME FROM '' || '' MY_TABLE''';
  execute immediate 'select ' || concatenetaed_sql  || ' from dual'
     into result_sql;
  dbms_output.put_line(result_sql);
end;

And the result

INSERT INTO TEST_TABLE(ID, NAME)SELECT ID, NAME FROM  MY_TABLE

PL/SQL procedure successfully completed.

Upvotes: 3

Related Questions