CODEWITHSUNDEEP
oracle-databaseplsql
Asım Gündüz
Asım Gündüz

Reputation: 1297

Oracle Dynamic functions and parameter inputs

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!

Upvotes: 0

Views: 243

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26333

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 :)

Upvotes: 3

Related Questions