How to use "Where" clause in Oracle database (stored procedures)-CodePudding

I am using the following stored procedures in an Oracle database to export the output of the query to a CSV file.

CREATE OR REPLACE PROCEDURE run_query(p_tbl_name IN VARCHAR2
                                            ) IS
         Select_Stmt VARCHAR2(100) := 'select * from '||p_tbl_name;
         p_dir VARCHAR2 (100) := 'DATA_PUMP_DIR';
         v_finaltxt  VARCHAR2(4000);
         v_v_val     VARCHAR2(4000);
         v_n_val     NUMBER;
         v_d_val     DATE;
         v_ret       NUMBER;
         c           NUMBER;
         d           NUMBER;
         col_cnt     INTEGER;
         f           BOOLEAN;
         rec_tab     DBMS_SQL.DESC_TAB;
         col_num     NUMBER;
         v_fh        UTL_FILE.FILE_TYPE;
       BEGIN
         c := DBMS_SQL.OPEN_CURSOR;
         DBMS_SQL.PARSE(c, Select_Stmt, DBMS_SQL.NATIVE);
         d := DBMS_SQL.EXECUTE(c);
         DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
         FOR j in 1..col_cnt
         LOOP
           CASE rec_tab(j).col_type
             WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
             WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
             WHEN 3 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
           ELSE
             DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,2000);
           END CASE;
         END LOOP; 
         -- This part outputs the HEADER
         v_fh := UTL_FILE.FOPEN(upper(p_dir),p_tbl_name||'.csv','w',32767);
         FOR j in 1..col_cnt
         LOOP
           v_finaltxt := ltrim(v_finaltxt||','||lower(rec_tab(j).col_name),',');
         END LOOP;
         UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
         -- This part outputs the DATA
         LOOP
           v_ret := DBMS_SQL.FETCH_ROWS(c);
           EXIT WHEN v_ret = 0;
           v_finaltxt := NULL;
           FOR j in 1..col_cnt
           LOOP
             CASE rec_tab(j).col_type
               WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
                           v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
               WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
                           v_finaltxt := ltrim(v_finaltxt||','||v_n_val,',');
               WHEN 3 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
                           v_finaltxt := ltrim(v_finaltxt||','||to_char(v_d_val,'DD/MM/YYYY HH24:MI:SS'),',');
             ELSE
               DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
               v_finaltxt := ltrim(v_finaltxt||',"'||v_v_val||'"',',');
             END CASE;
           END LOOP;
         --  DBMS_OUTPUT.PUT_LINE(v_finaltxt);
           UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
         END LOOP;
         UTL_FILE.FCLOSE(v_fh);
         DBMS_SQL.CLOSE_CURSOR(c);
       END;
       /

The above stored procedure is working perfectly and I got the output by using the following script to run this stored procedure

exec run_query person_list;

Here stored_procedure_name is run_query and the table_name is person_list.

Now my question is that how can I use the WHERE clause after the select statement on "line 3" -

Select_Stmt VARCHAR2(100) := 'select * from '||p_tbl_name;

or are there any other ways I can use the where clause.

Thanks in advance.

CodePudding user response：

 Select_Stmt VARCHAR2(100) := 'select * from '||p_tbl_name||' where name=''david''';

You have to have the character delimiters; and since you have it inside quotes you have to duplicate them.