CODEWITHSUNDEEP
sqloracle-database
Didu
Didu

Reputation: 79

Pass column name as a parameter

I need to pass a column name from front end to back end in my code. i'm using c# with oracle and when i pass the column name as a parameter, it gives an error and it's because the column name is used as a string in here and i need to know how to fix this. here is my code,

PROCEDURE PR_GETCLIENTCONTRACTDATA(INSTRFIELD IN VARCHAR2,INSTRCONTRACTNO IN VARCHAR2,CUR_OUTPUT OUT T_CURSOR)--ADDED BY DIDULA 25/10/2017 
IS
    BEGIN
        OPEN CUR_OUTPUT FOR
SELECT c.con_no,
       DECODE (a.clm_cori,
               '1', a.clm_cltitle || ' ' || a.clm_initialsfull || ' '
                || a.clm_name,
               a.clm_name
              ) cliname,
       a.clm_code,
       (   a.clm_permaddline1
        || '|'
        || a.clm_permaddline2
        || '|'
        || COALESCE (a.clm_permaddline3, a.clm_permaddline4)
        || '|'
        || NULLIF ((a.clm_permaddline4),
                   COALESCE (a.clm_permaddline3, a.clm_permaddline4)
                  )
       ) address
  FROM leaseinfo.tblcontracts c, corpinfo.tblclientmain a
 WHERE a.clm_code = c.con_clmcode
   AND INSTRFIELD = INSTRCONTRACTNO; ***here INSTRFIELD is the column name 
                                        that i need to pass***

END PR_GETCLIENTCONTRACTDATA;

Upvotes: 0

Views: 1014

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

When you use OPEN cur FOR ... you can pass a string, i.e.

PROCEDURE PR_GETCLIENTCONTRACTDATA(INSTRFIELD IN VARCHAR2,INSTRCONTRACTNO IN VARCHAR2,CUR_OUTPUT OUT T_CURSOR)
IS
 BEGIN
    OPEN CUR_OUTPUT FOR
      'SELECT c.con_no,
       DECODE (a.clm_cori,
               ''1'', a.clm_cltitle || '' '' || a.clm_initialsfull || '' ''
                || a.clm_name,
               a.clm_name
              ) cliname,
       a.clm_code,
       (   a.clm_permaddline1
        || ''|''
        || a.clm_permaddline2
        || ''|''
        || COALESCE (a.clm_permaddline3, a.clm_permaddline4)
        || ''|''
        || NULLIF ((a.clm_permaddline4),
                   COALESCE (a.clm_permaddline3, a.clm_permaddline4)
                  )
       ) address
       FROM leaseinfo.tblcontracts c
           JOIN corpinfo.tblclientmain a ON a.clm_code = c.con_clmcode
       WHERE '||DBMS_ASSERT.SIMPLE_SQL_NAME(INSTRFIELD)||' = :INSTRCONTRACTNO)' 
   USING INSTRCONTRACTNO;

END PR_GETCLIENTCONTRACTDATA;

Upvotes: 0

MT0
MT0

Reputation: 167832

Whitelist the column names:

PROCEDURE PR_GETCLIENTCONTRACTDATA(
  INSTRFIELD      IN  VARCHAR2,
  INSTRCONTRACTNO IN  VARCHAR2,
  CUR_OUTPUT      OUT T_CURSOR
)
IS
BEGIN
  OPEN CUR_OUTPUT FOR
    SELECT -- your select clauses
    FROM   leaseinfo.tblcontracts c,
           INNER JOIN corpinfo.tblclientmain a -- ANSI join syntax
           ON a.clm_code = c.con_clmcode
    WHERE  CASE INSTRFIELD 
           WHEN 'COLUMNA' THEN ColumnA
           WHEN 'COLUMNB' THEN ColumnB
           WHEN 'COLUMNC' THEN ColumnC
           END = INSTRCONTRACTNO;
END PR_GETCLIENTCONTRACTDATA;
/

Upvotes: 1

Related Questions