Hi there's this oracle Procedure:
SP_DELETE_FROM_TABLE(pTableName in VARCHAR2, pFieldName in VARCHAR2,pFieldValue in VARCHAR2,pFieldType in VARCHAR2)
What I would like to know is that what is the fieldtype parameter?
I mean I could
queryString := 'DELETE FROM' ||pTableName|| 'WHERE pFieldName = pFieldValue' ;
EXECUTE IMMEDIATE queryString USING pFieldName, pFieldValue;
right? What am I missing here? Can anyone Help? Thanks!
The table name can't be a parameter, so you've got to substitute it in as a string. That's just like your example, except you need to put spaces around it as @are noted:
queryString := 'DELETE FROM ' || pTableName || ' WHERE...
^ space here ^ and here
The column name can't be a parameter either.
The field value you're substituting is a parameter, so it needs (1) a name and (2) a colon in front of it. I'll name it thing here so the name will be obviously different than the proc's pFieldValue parameter:
queryString := 'DELETE FROM ' || pTableName || ' WHERE ' || pFieldName || ' = :thing';
^ parameter
The USING part of EXECUTE IMMEDIATE is for providing parameter values, period. The query has one parameter, so the USING supplies one value:
EXECUTE IMMEDIATE queryString USING pFieldValue;
The first parameter (thing) is given the value of pFieldValue.
One more thing: Many (including me) think a generic "delete a row" procedure is bad practice. Instead of a compiler-checked statement like this in your code...
DELETE FROM myTable WHERE myField = pFieldValue;
... you have something not checkable like this - if it's wrong it'll fail at runtime:
SP_DELETE_FROM_TABLE('myTable', 'myField', 'badidea');
And what if you have to do this?
DELETE FROM myTable WHERE myValue BETWEEN 10 AND 12;
Do you extend the SP_DELETE_FROM_TABLE procedure, or write another one? This procedure is a good exercise, but please think twice before using it in a real application.
That said, I get that sometimes you have to use EXECUTE IMMEDIATE for operations like this (for example if the table is accessed via a DB link). Just use it wisely :)
WHERE clause? For the most part you'd be OK without using it - for example, if the column is negative and pFieldValue is the string '123' Oracle will cast for you. If the column is date then Oracle will cast only if (1) the string is in formatted as specified in your NLS_DATE_FORMAT session setting or (2) it's a valid ANSI date string - for example the ANSI date string for today (December 24, 2015) is DATE '2015-12-24'. The word DATE is part of the string.
fromand beforewhere. it can be issue when you execute the script. then...pFieldNameyou cannot use it this way. finally it should be smth likequeryString := 'DELETE FROM ' ||pTableName|| ' WHERE '||pFieldName ||' = :pFieldValue' ;