Dynamic cursor in PLSQL - Oracle-CodePudding

Is there any way to rename the table at this cursor.

DECLARE
   TYPE name_salary_rt IS RECORD (
      name     VARCHAR2 (1000),
      salary   NUMBER
   );         

   TYPE name_salary_aat IS TABLE OF name_salary_rt
      INDEX BY PLS_INTEGER;

   l_employees   name_salary_aat;   

   l_cursor   SYS_REFCURSOR;
   
   table_name = 'employees202110';
BEGIN
   OPEN l_cursor FOR 
      q'[select first_name || ' ' || last_name, salary 
           from employees202110
          order by salary desc]';

   FETCH l_cursor BULK COLLECT INTO l_employees;

   CLOSE l_cursor;

   FOR indx IN 1 .. l_employees.COUNT
   LOOP
      DBMS_OUTPUT.put_line (l_employees (indx).name);
   END LOOP;
END;

I want to run this cursor but with different table names

table_name = 'employees202110';

OPEN l_cursor FOR 
      q'[select first_name || ' ' || last_name, salary 
           from ]' || table_name || q'[
          order by salary desc]';

or in what way do you recommend me to make the cursor

CodePudding user response：

You're along the right tracks - as you're probably aware, you can't pass in identifier names as bind variables in native dynamic sql, so concatenating is the way forward.

However, you should check that what you're concatenating is actually an identifier name, otherwise you're leaving yourself wide open for SQL injection. This can be achieved using DBMS_ASSERT.

Depending on how you're passing the table_name in, you might want to use DBMS_ASSERT.SIMPLE_SQL_NAME (if you're not passing the schema name in as well as the table_name) or DBMS_ASSERT.QUALIFIED_SQL_NAME (if you're passing the schema name, or a db link, etc), e.g.:

OPEN l_cursor FOR 
      q'[select first_name || ' ' || last_name, salary 
           from ]' || DBMS_ASSERT.SIMPLE_SQL_NAME(table_name) || q'[
          order by salary desc]';

Tim Hall has a good article on using DBMS_ASSERT to sanitize your inputs for dynamic SQL.