Thanks for the question, J0Hn.

Asked: October 18, 2017 - 10:27 am UTC

Last updated: October 19, 2017 - 4:30 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi, I am trying to create a procedure that will display logs. It has an IN parameter which is the table nam, the cursor will SELECT data based on that parameter. I cannot compile my procedure. Hope you can help me. :)

CREATE OR REPLACE PROCEDURE pr_display_log ( pv_staging_table  VARCHAR2
                                           , pv_temp_table     VARCHAR2
                                           , pv_area           VARCHAR2
                                           , pv_rec_identifier VARCHAR2
                                           , pd_process_start  TIMESTAMP
                                           , pd_process_end    TIMESTAMP)
AS
  ln_total_count NUMBER;
  ln_valid_count NUMBER;
  ln_error_count NUMBER;
  ln_ctr NUMBER := 1;
  lv_rec_identifier VARCHAR2(30) := 'log_rec.'||pv_rec_identifier;

BEGIN
  -- Log Header
  DBMS_OUTPUT.PUT_LINE ('**Starts** '||pv_area||' - '||pd_process_start);
  DBMS_OUTPUT.PUT_LINE (LPAD('-', 150, '-'));
  DBMS_OUTPUT.PUT_LINE(RPAD('CTR', 8)||RPAD(pv_rec_identifier, 35)||RPAD('CONV_STATUS',15)||'CONVERSION REMARKS');
  DBMS_OUTPUT.PUT_LINE (LPAD('-', 150, '-'));

  -- Get Status of the records
  FOR log_rec IN (SELECT * FROM pv_temp_table)
  LOOP
    DBMS_OUTPUT.PUT_LINE(RPAD(ln_ctr, 8)
                         ||RPAD(lv_rec_identifier, 35)
                         ||RPAD(log_rec.conversion_status,15)
                         ||log_rec.error_message);
    ln_ctr := ln_ctr + 1;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE (LPAD('-', 150, '-'));
  DBMS_OUTPUT.PUT_LINE (CHR(10));

  -- Get count summary
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM :staging_table;'
     INTO ln_total_count
    USING pv_staging_table;

  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM :temp_table WHERE valid_flag = c_yes;'
     INTO ln_valid_count
    USING pv_temp_table;

  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM :temp_table WHERE valid_flag = c_no;'
     INTO ln_error_count
    USING pv_temp_table;

  -- Display the count summary
  DBMS_OUTPUT.PUT_LINE (LPAD('-', 150, '-'));
  DBMS_OUTPUT.PUT_LINE (pv_staging_table||' Summary');
  DBMS_OUTPUT.PUT_LINE (LPAD('-', 150, '-'));
  DBMS_OUTPUT.PUT_LINE (RPAD('Total No. of '||pv_area||' in the staging table :', 55)||ln_total_count);
  DBMS_OUTPUT.PUT_LINE (RPAD('No. of '||pv_area||' successfully validated :', 55)||ln_valid_count);
  DBMS_OUTPUT.PUT_LINE (RPAD('No. of '||pv_area||' with Error :', 55)||ln_error_count);
  DBMS_OUTPUT.PUT_LINE (LPAD('-', 150, '-'));
  DBMS_OUTPUT.PUT_LINE ('**Ends** '||pv_area||' - '||pd_process_end);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No records found in '||pv_temp_table||' table.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error encountered while generating the log file!');

END pr_display_log;

and Chris said...

It doesn't compile for me either:

LINE/COL ERROR
-------- -----------------------------------------------------------------
33/5     PL/SQL: SQL Statement ignored
34/12    PL/SQL: ORA-00942: table or view does not exist
36/5     PL/SQL: Statement ignored
38/13    PLS-00364: loop index variable 'LOG_REC' use is invalid

Which comes from this part of the code:

  for log_rec in (
    select *
    from   pv_temp_table
  ) loop

So, what is pv_temp_table? A table in your database? Or a parameter to the procedure? Or both?

Also, you have a lot of statements attempting to bind table names, like this:

  execute immediate 'SELECT COUNT(*) FROM :staging_table;' into
    ln_total_count
    using pv_staging_table;

Sadly you can't do this. You have to paste the table name into the text of the SQL. Making sure you defend against SQL injection attacks while doing so!

You can use DBMS_assert to do this. So the dynamic statements will become something like:

  execute immediate 'SELECT COUNT(*) FROM ' || 
   dbms_assert.sql_object_name(pv_staging_table)

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

John, October 18, 2017 - 11:33 pm UTC

HI Chris Saxon,

Thank you very much for your help!

pv_temp_table is a parameter in the procedure where a table name is being passed on. This is why I used:

FOR log_rec IN (SELECT * FROM pv_temp_table)

My goal here is to display the contents of a specific table.
e.g.

Display contents of EMP table --> Call this procedure and pass the 'EMP' to the parameter pv_temp_table.

Display contents of DEPT table --> Call this procedure and pass the 'DEPT' to the parameter pv_temp_table.

Display contents of SALGRADE table --> Call this procedure and the 'SALGRADE' to the parameter pv_temp_table.

Hope you can help me on this :)

Thank You!

October 19, 2017 - 2:24 am UTC

You'll need type dynamic SQL for that, eg this is out "pt.sql" script

set serverout on size 999999
set verify off
declare
    p_query varchar2(32767) := q'{&1}';

    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
    n number := 0;
  procedure p(msg varchar2) is
    l varchar2(4000) := msg;
  begin
    while length(l) > 0 loop
      dbms_output.put_line(substr(l,1,80));
      l := substr(l,81);
    end loop;
  end;
begin
    execute immediate
    'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

    dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
    dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

    for i in 1 .. l_colCnt loop
        dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
    end loop;

    l_status := dbms_sql.execute(l_theCursor);

    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            p( rpad( l_descTbl(i).col_name, 30 )
              || ': ' || 
              l_columnValue );
        end loop;
        dbms_output.put_line( '-----------------' );
        n := n + 1;
    end loop;
    if n = 0 then
      dbms_output.put_line( chr(10)||'No data found '||chr(10) );
    end if;
end;
/

set verify on

SQL> @pt "select * from scott.emp where rownum = 1"
EMPNO                         : 7369
ENAME                         : SMITH
JOB                           : CLERK
MGR                           : 7902
HIREDATE                      : 17-dec-1980 00:00:00
SAL                           : 800
COMM                          :
DEPTNO                        : 20
-----------------

PL/SQL procedure successfully completed.

SQL> @pt "select * from scott.dept where rownum = 1"
DEPTNO                        : 10
DNAME                         : ACCOUNTING
LOC                           : NEW YORK
-----------------

PL/SQL procedure successfully completed.

SQL>

Interesting

John, October 19, 2017 - 4:10 am UTC

Hi Thanks for your help!

But I need to tweak something to display like the following:

SQL> @pt "select * from scott.emp where rownum < 3"
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-dec-1980 800 20
XXXX JOHN IT XXXX 19-oct-1991 700 50

Hope you can help me with this.

Thanks!!!

October 19, 2017 - 4:30 am UTC

Yes - but *look* at the code we gave you. It shows

- how to parse the query
- how to get a list of the columns
- how to fetch the rows
- how to output the values for each column for each row

The rest is simply how *you* choose to format the output.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Interesting

More to Explore

PL/SQL demos

PL/SQL docs

Dynamic SQL in CURSOR

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Interesting

More to Explore

PL/SQL demos

PL/SQL docs