CODEWITHSUNDEEP
oracle-databaseplsqloracle10g
ziggy
ziggy

Reputation: 15876

Execute immediate run from a PL/SQL package

I have the following lines of code in a PL/SQL package

l_sql := 'alter table LOG_FILES location ('||chr(39)|| filename || to_char(sysdate,'DDMMYY') ||chr(39)||'))';
execute immediate l_sql;

The pl/sql package compiles fine but i dont see the change. The alter command has no effect at all. Is there any reason for this?

Note: The table I am trying to alter is an Oracle External table.

Upvotes: 1

Views: 1972

Answers (2)

APC
APC

Reputation: 146349

The filename is a string and needs to be bracketed with single quotes in the final statement. In order to acheive that you need to escape the single quote. One way is to use two single quotes. So, your assignment needs to look something like this:

l_sql := 'alter table LOG_FILES location ('''|| filename || to_char(sysdate,'DDMMYY') ||''')';

Upvotes: 5

Gerrat
Gerrat

Reputation: 29740

You have an extra parentheses at the end of your statement

Upvotes: 4

Related Questions