1 2 Previous Next 15 Replies Latest reply: Mar 19, 2013 5:24 AM by padders RSS

    dbms_sql

    francy77
      Hi all,
      i'm reading documentation about dbms_sql pakage at:
      http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm#LNPLS01108


      reading that:
      You must use the DBMS_SQL package to execute a dynamic SQL statement when you don't know either of the following until run-time:
      
          SELECT list
      
          What placeholders in a SELECT or DML statement must be bound
      now i'm asking if someone knows an easy example in which i have to use dbms_sql instead of execute immediate, i cannot immagine it.

      thanks
      Francesco
        • 1. Re: dbms_sql
          6363
          Both examples below on that page would require the use of DBMS_SQL to bind the variables.

          http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm#g1777282

          http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm#BHCHJBHJ
          • 2. Re: dbms_sql
            BluShadow
            francy77 wrote:
            Hi all,
            i'm reading documentation about dbms_sql pakage at:
            http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm#LNPLS01108


            reading that:
            You must use the DBMS_SQL package to execute a dynamic SQL statement when you don't know either of the following until run-time:
            
            SELECT list
            
            What placeholders in a SELECT or DML statement must be bound
            now i'm asking if someone knows an easy example in which i have to use dbms_sql instead of execute immediate, i cannot immagine it.
            Where you don't know the columns names or projection being returned by the query.

            Example (from my library of examples) of some code that can take a query and output it as CSV...

            -----
            As sys user:
            CREATE OR REPLACE DIRECTORY TEST_DIR AS '\tmp\myfiles'
            /
            GRANT READ, WRITE ON DIRECTORY TEST_DIR TO myuser
            /
            As myuser:
            CREATE OR REPLACE PROCEDURE run_query(p_sql IN VARCHAR2
                                                 ,p_dir IN VARCHAR2
                                                 ,p_header_file IN VARCHAR2
                                                 ,p_data_file IN VARCHAR2 := NULL) IS
              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;
              v_samefile  BOOLEAN := (NVL(p_data_file,p_header_file) = p_header_file);
            BEGIN
              c := DBMS_SQL.OPEN_CURSOR;
              DBMS_SQL.PARSE(c, p_sql, 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 12 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_header_file,'w',32767);
              FOR j in 1..col_cnt
              LOOP
                v_finaltxt := ltrim(v_finaltxt||','||lower(rec_tab(j).col_name),',');
              END LOOP;
              --  DBMS_OUTPUT.PUT_LINE(v_finaltxt);
              UTL_FILE.PUT_LINE(v_fh, v_finaltxt);
              IF NOT v_samefile THEN
                UTL_FILE.FCLOSE(v_fh);
              END IF;
              --
              -- This part outputs the DATA
              IF NOT v_samefile THEN
                v_fh := UTL_FILE.FOPEN(upper(p_dir),p_data_file,'w',32767);
              END IF;
              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 12 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;
            This allows for the header row and the data to be written to seperate files if required.

            e.g.
            SQL> exec run_query('select * from emp','TEST_DIR','output.txt');
             
            PL/SQL procedure successfully completed.
            Output.txt file contains:
            empno,ename,job,mgr,hiredate,sal,comm,deptno
            7369,"SMITH","CLERK",7902,17/12/1980 00:00:00,800,,20
            7499,"ALLEN","SALESMAN",7698,20/02/1981 00:00:00,1600,300,30
            7521,"WARD","SALESMAN",7698,22/02/1981 00:00:00,1250,500,30
            7566,"JONES","MANAGER",7839,02/04/1981 00:00:00,2975,,20
            7654,"MARTIN","SALESMAN",7698,28/09/1981 00:00:00,1250,1400,30
            7698,"BLAKE","MANAGER",7839,01/05/1981 00:00:00,2850,,30
            7782,"CLARK","MANAGER",7839,09/06/1981 00:00:00,2450,,10
            7788,"SCOTT","ANALYST",7566,19/04/1987 00:00:00,3000,,20
            7839,"KING","PRESIDENT",,17/11/1981 00:00:00,5000,,10
            7844,"TURNER","SALESMAN",7698,08/09/1981 00:00:00,1500,0,30
            7876,"ADAMS","CLERK",7788,23/05/1987 00:00:00,1100,,20
            7900,"JAMES","CLERK",7698,03/12/1981 00:00:00,950,,30
            7902,"FORD","ANALYST",7566,03/12/1981 00:00:00,3000,,20
            7934,"MILLER","CLERK",7782,23/01/1982 00:00:00,1300,,10
            The procedure allows for the header and data to go to seperate files if required. Just specifying the "header" filename will put the header and data in the one file.

            Adapt to output different datatypes and styles are required.
            -----
            • 3. Re: dbms_sql
              francy77
              Hi,
              may be i said i understand but indeed isn't true. i've try to call the procedure
              do_query_1(placeholder => placeholder,
                           bindvars => bindvars,
                           sql_stmt => :sql_stmt);
              but i'm not able to find out right value for placeholder, bindvars and sql_stmt, just to see an example.

              thanks
              F

              Edited by: francy77 on Mar 14, 2013 11:10 AM
              • 4. Re: dbms_sql
                BluShadow
                So, what is "do_query_1"?
                • 5. Re: dbms_sql
                  francy77
                  It is a procedure, that receive tre parameters
                  1)placeholder that is a collection of varchar2,
                  2)bindvars that is also a collection of varchar2
                  3)sql_stmt that should be a string that is a sql expression. This string should be executed only via dbms_sql.

                  But unfortunately I' m not able to figure out sone value for this parameters.

                  In other words i just want to call the procedure passing to it some value, to understand what the procedure actually do.It is possible?

                  thanks
                  F.
                  • 6. Re: dbms_sql
                    padders
                    It's one of the examples at http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm#LNPLS01108.

                    I can't actually test that call since I don't have 11g here but it should be something like this...
                    BEGIN
                       do_query_1 (
                          placeholder => vc_array ('P_JOB', 'P_ENAME'),
                          bindvars => vc_array ('MANAGER', 'A%'),
                          sql_stmt => 'SELECT e.empno, e.deptno' || 
                                      'FROM   emp e ' || 
                                      'WHERE  e.job = :p_job ' || 
                                      'AND    e.ename LIKE :p_ename');
                    END;
                    /
                    • 7. Re: dbms_sql
                      BluShadow
                      padders wrote:
                      It's one of the examples at http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm#LNPLS01108.
                      Ah, right, it's example code in the documentation. And there was me thinking I was supposed to magically know what do_query_1 actually does. LOL!
                      • 8. Re: dbms_sql
                        francy77
                        I've changed a little bit the procedure as follow (adding val collection in the last fetch and dbms_output statement):
                        CREATE OR REPLACE PROCEDURE DO_QUERY_1(PLACEHOLDER VC_ARRAY
                                                              ,BINDVARS    VC_ARRAY
                                                              ,SQL_STMT    VARCHAR2) IS
                          TYPE CURTYPE IS REF CURSOR;
                          SRC_CUR   CURTYPE;
                          CURID     NUMBER;
                          BINDNAMES VC_ARRAY;
                          EMPNOS    NUMLIST;
                          DEPTS     NUMLIST;
                          RET       NUMBER;
                          ISOPEN    BOOLEAN;
                          val VC_ARRAY;
                        BEGIN
                          -- Open SQL cursor number:
                          CURID := DBMS_SQL.OPEN_CURSOR;
                        
                          -- Parse SQL cursor number:
                          DBMS_SQL.PARSE(CURID, SQL_STMT, DBMS_SQL.NATIVE);
                          BINDNAMES := PLACEHOLDER;
                        
                          -- Bind arguments:
                          
                          FOR I IN 1 .. BINDNAMES.COUNT
                          LOOP
                            DBMS_SQL.BIND_VARIABLE(CURID, BINDNAMES(I), BINDVARS(I));
                          END LOOP;
                        
                        
                          -- Execute SQL cursor number:
                          RET := DBMS_SQL.EXECUTE(CURID);
                        
                          -- Switch from DBMS_SQL to native dynamic SQL:
                          SRC_CUR := DBMS_SQL.TO_REFCURSOR(CURID);
                        
                          FETCH SRC_CUR BULK COLLECT
                            INTO EMPNOS
                                ,DEPTS,val;
                        
                        dbms_output.put_line('the value in the collection EMPNOS '||EMPNOS(1)||' and '||EMPNOS(2));
                        dbms_output.put_line('the value in the collection DEPTS ' ||DEPTS(1)||' and '||DEPTS(2));
                        dbms_output.put_line('the value in the collection val '||val(1)||' and '||val(2));
                        
                          -- This would cause an error because curid was converted to a REF CURSOR:
                          -- isopen := DBMS_SQL.IS_OPEN(curid);
                        
                          CLOSE SRC_CUR;
                        END;
                        calling it from:
                        declare
                          -- Non-scalar parameters require additional processing 
                          placeholder vc_array;
                          bindvars vc_array;
                          sql_stmt varchar2(500);
                        begin
                          placeholder:=vc_array('P_JOB', 'P_ENAME','AAA');
                          bindvars:=vc_array('AD_VP', '%','e.last_name');
                          
                          sql_stmt := 'SELECT e.EMPLOYEE_ID, e.DEPARTMENT_ID, :aaa ' || 
                                          'FROM   emp e  ' || 
                                          'WHERE  e.job_id = :p_job ' || 
                                          'AND    e.last_name LIKE :p_ename';
                          -- Call the procedure
                          do_query_1(placeholder => placeholder,
                                     bindvars => bindvars,
                                     sql_stmt => sql_stmt);
                        end;
                        and this is the result of dbms_output:

                        the value in the collection EMPNOS 101 and 102
                        the value in the collection DEPTS 90 and 90
                        the value in the collection val e.last_name and e.last_name

                        The EMPNOS and DEPTS collection are retrived in right way, but the val collection not it contain the value last_name while i'm waiting the real name (Kochhar
                        and De Haan), it is an oracle bug?
                        • 9. Re: dbms_sql
                          JustinCave
                          No, that's not an Oracle bug.

                          You cannot use a bind variable (:aaa) for a column name (or for a table name). If you want to reference the column name, you'd need to do so when you built the SQL statement. As written, you are binding the literal string 'e.last_name' so that's what you would get returned in every row.

                          Justin
                          • 10. Re: dbms_sql
                            francy77
                            please could you tell me how i can do it, i want to change the column name at run time, both using dbms_sql and execute immediate.

                            thanks so much
                            F.
                            • 11. Re: dbms_sql
                              JustinCave
                              You'd need to build the SQL statement. Something like
                              declare
                                -- Non-scalar parameters require additional processing 
                                placeholder vc_array;
                                bindvars vc_array;
                                sql_stmt varchar2(500);
                                column_name varchar2(30) := 'e.last_name';
                              begin
                                placeholder:=vc_array('P_JOB', 'P_ENAME');
                                bindvars:=vc_array('AD_VP', '%');
                                
                                sql_stmt := 'SELECT e.EMPLOYEE_ID, e.DEPARTMENT_ID,  ' || column_name || ' ' ||
                                                'FROM   emp e  ' || 
                                                'WHERE  e.job_id = :p_job ' || 
                                                'AND    e.last_name LIKE :p_ename';
                                -- Call the procedure
                                do_query_1(placeholder => placeholder,
                                           bindvars => bindvars,
                                           sql_stmt => sql_stmt);
                              end;
                              would work.

                              Justin
                              • 12. Re: dbms_sql
                                francy77
                                I also solve the matter in the following way:
                                set serveroutput on
                                declare
                                  -- Non-scalar parameters require additional processing 
                                  placeholder vc_array;
                                  bindvars vc_array;
                                  sql_stmt varchar2(500);
                                  column_name varchar2(30) := 'e.last_name';
                                   EMPNOS      NUMLIST;
                                   DEPTS       NUMLIST;
                                   VAR         vc_array;
                                begin
                                  placeholder:=vc_array('P_JOB', 'P_ENAME');
                                  bindvars:=vc_array('AD_VP', '%');
                                  
                                  sql_stmt := 'SELECT e.EMPLOYEE_ID, e.DEPARTMENT_ID,  ' || column_name || ' ' ||
                                                  'FROM   emp e  ' || 
                                                  'WHERE  e.job_id = :p_job ' || 
                                                  'AND    e.last_name LIKE :p_ename';
                                  /*
                                  -- Call the procedure
                                  do_query_1(placeholder => placeholder,
                                             bindvars => bindvars,
                                             sql_stmt => sql_stmt);
                                 */
                                
                                
                                execute immediate sql_stmt bulk collect into EMPNOS, DEPTS,VAR using IN bindvars(1),IN bindvars(2); 
                                
                                dbms_output.put_line(EMPNOS(1)||':'||EMPNOS(2));
                                dbms_output.put_line(DEPTS(1)||':'||DEPTS(2));
                                dbms_output.put_line(VAR(1)||':'||VAR(2));
                                end;
                                /
                                I've just used execute immediate and i've get the same result(with just one line of code instead of the query_1 procedure).

                                On oracle documentation i've read that some situation require to use only dbms_sql, that i cannot do the task with Natively dynamic sql (execute immediate), so i'm just try to find an example in which i cannot use execute immediate to make a dynamic query.

                                thanks so much
                                F.

                                Edited by: francy77 on Mar 18, 2013 4:49 PM
                                • 13. Re: dbms_sql
                                  padders
                                  I've just used execute immediate and i've get the same result
                                  Yes, but EXECUTE IMMEDIATE doesn't support a situation where the number of placeholders is unknown at compile time.
                                  • 14. Re: dbms_sql
                                    francy77
                                    Please an example, please again, do a query with dbms_sql that i cannot modifiy to execute via "execute immediate".

                                    thanks
                                    F
                                    1 2 Previous Next