10 Replies Latest reply: May 3, 2012 1:24 AM by William Robertson RSS

    Please help a debug

    684323
      Hello,

      make a pl/sql, get an error

      --first, create a function to find days intervals between dates

      create or replace function test_schema.find_interval(from_date in date, to_date in date)
      return number as
      begin
      return abs(trunc(to_date) - trunc(from_date));
      end;
      /

      (function created)

      --then

      SYS@xxx> declare
      2 V_insert_date date;
      3 v_sysdate date :=sysdate;
      4 v_retention_num number :=21;
      5 v_extended_retention number :=0;
      6 purge_nbr Number := 0;
      7 v_interval_days number :=0;
      8 begin
      9
      10 select to_char(max(INSERT_DT), 'DD-Mon-YYYY')
      11 INTO V_insert_date
      12 FROM test_schema.STAT_CD;
      13
      14
      15 IF v_insert_date > sysdate THEN
      16
      17 select test_schema.find_interval(v_insert_date,to_date(sysdate, 'mm/dd/yyyy')) into v_interval_days from dual;
      18 v_extended_retention := v_retention_num + v_interval_days;
      19 select ltrim(to_char(sysdate - v_extended_retention,'ddd'),'0') into purge_nbr from dual;
      20 DBMS_OUTPUT.PUT_LINE('truncated partition' ||purge_nbr);
      21
      22 ELSE
      23
      24 select ltrim(to_char(sysdate - v_retention_num,'ddd'),'0') into purge_nbr from dual;
      25 DBMS_OUTPUT.PUT_LINE('truncated partition' || purge_nbr);
      26
      END IF;
      27 28 end;
      29 /
      declare
      *
      ERROR at line 1:
      ORA-01858: a non-numeric character was found where a numeric was expected
      ORA-06512: at line 17


      why...?

      thank you
        • 1. Re: Please help a debug
          indra budiantho
          select test_schema.find_interval(to_date(v_insert_date, 'mm/dd/yyyy'),to_date(sysdate, 'mm/dd/yyyy')) into v_interval_days from dual;
          • 2. Re: Please help a debug
            Hoek
            SYSDATE is already of DATE datatype, so change
            select test_schema.find_interval(v_insert_date,to_date(sysdate, 'mm/dd/yyyy')) into v_interval_days from dual;
            into
            select test_schema.find_interval(v_insert_date, sysdate) into v_interval_days from dual;
            or
            select test_schema.find_interval(v_insert_date, trunc(sysdate)) into v_interval_days from dual;
            Also, reconsider putting a simple subtraction into a PL/SQL function and calling that from a SQL statement might make you suffer from context switching, see: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:60122715103602
            • 3. Re: Please help a debug
              William Robertson
              Also,
              10 select to_char(max(INSERT_DT), 'DD-Mon-YYYY')
              11 INTO V_insert_date
              12 FROM test_schema.STAT_CD;
              The INSERT_DT column is a date, v_insert_date is a date, the from_date in parameter is a date. Why you are converting to character string?
              • 4. Re: Please help a debug
                684323
                yes, my bad.

                all fixed.

                thanks a lot for all answers!!!!
                • 5. Re: Please help a debug
                  684323
                  one more question:

                  when run this


                  declare
                  V_insert_date date;
                  v_sysdate date :=sysdate;
                  v_retention_num number :=21;
                  v_extended_retention number :=0;
                  purge_nbr Number := 0;
                  v_interval_days number :=0;
                  begin

                  select max(INSERT_DT)
                  INTO V_insert_date
                  FROM test_schema.STAT_CD;


                  IF v_insert_date > sysdate THEN

                  select test_schema.find_interval(v_insert_date,sysdate) into v_interval_days from dual;
                  v_extended_retention := v_retention_num + v_interval_days;
                  select ltrim(to_char(sysdate - v_extended_retention,'ddd'),'0') into purge_nbr from dual;
                  DBMS_OUTPUT.PUT_LINE('truncated partition' ||purge_nbr);

                  ELSE

                  select ltrim(to_char(sysdate - v_retention_num,'ddd'),'0') into purge_nbr from dual;
                  DBMS_OUTPUT.PUT_LINE('truncated partition' || purge_nbr);

                  END IF;
                  end;
                  /


                  truncated partition101

                  PL/SQL procedure successfully completed.



                  --then, want to give this PL/SQL a name to create a named procedure, and add two parameters  (one is table name, the other is retention days number), do like


                  CREATE OR REPLACE procedure test_schema.truncate_partition
                  (p_table_name IN VARCHAR2 ,
                  p_retention_num IN NUMBER)
                  IS
                  V_insert_date date;
                  v_sysdate date :=sysdate;
                  v_retention_num number := p_retention_num;
                  v_extended_retention number :=0;
                  purge_nbr Number := 0;
                  v_interval_days number :=0;
                  begin

                  select max(INSERT_DT)INTO V_insert_date
                  FROM p_table_name;


                  IF v_insert_date > sysdate THEN

                  select test_schema.find_interval(v_insert_date,sysdate) into v_interval_days from dual;
                  v_extended_retention := v_retention_num + v_interval_days;
                  select ltrim(to_char(sysdate - v_extended_retention,'ddd'),'0') into purge_nbr from dual;
                  DBMS_OUTPUT.PUT_LINE('truncated partition' ||purge_nbr);

                  ELSE

                  select ltrim(to_char(sysdate - v_retention_num,'ddd'),'0') into purge_nbr from dual;
                  DBMS_OUTPUT.PUT_LINE('truncated partition' || purge_nbr);

                  END IF;
                  END;
                  end truncate_partition;
                  /

                  Warning: Procedure created with compilation errors.

                  then debug,
                  Errors for PROCEDURE TEST_SCHEMA.TRUNCATE_PARTITION:

                  LINE/COL ERROR
                  -------- -----------------------------------------------------------------
                  13/1 PL/SQL: SQL Statement ignored
                  14/6 PL/SQL: ORA-00942: table or view does not exist

                  seems like problem is here..

                  select max(INSERT_DT)INTO V_insert_date
                  FROM p_table_name;


                  my question is how to overcome?

                  thank you

                  Edited by: ROY123 on May 2, 2012 1:12 PM
                  • 6. Re: Please help a debug
                    sb92075
                    ROY123 wrote:

                    LINE/COL ERROR
                    -------- -----------------------------------------------------------------
                    13/1 PL/SQL: SQL Statement ignored
                    14/6 PL/SQL: ORA-00942: table or view does not exist

                    seems like problem is here..

                    select max(INSERT_DT)INTO V_insert_date
                    FROM p_table_name;
                    privileges acquired via ROLE do NOT apply within named PL/SQL procedures.
                    • 7. Re: Please help a debug
                      684323
                      ...those tables (as parameter p_table_name) are owned by schema TEST_SCHEMA, I guess it has privilege to run DML against those tables..
                      by the way, how to overcome if I am wrong?

                      thank you
                      • 8. Re: Please help a debug
                        sb92075
                        ROY123 wrote:
                        ...those tables (as parameter p_table_name) are owned by schema TEST_SCHEMA, I guess it has privilege to run DML against those tables..
                        by the way, how to overcome if I am wrong?

                        thank you
                        issue explicit GRANT on object to USER
                        • 9. Re: Please help a debug
                          684323
                          if I do like this,


                          CREATE OR REPLACE procedure TEST_SCHEMA.TRUNCATE_PARTITION
                          (p_retention_num IN NUMBER)
                          IS
                          begin
                          declare
                          V_insert_date date;
                          v_sysdate date :=sysdate;
                          v_retention_num number := p_retention_num;
                          v_extended_retention number :=0;
                          purge_nbr Number := 0;
                          v_interval_days number :=0;
                          begin
                          select max(INSERT_DT)
                          INTO V_insert_date
                          FROM TEST_SCHEMA.STAT_CD;

                          IF v_insert_date > sysdate THEN
                          select TEST_SCHEMA.find_interval(v_insert_date,sysdate) into v_interval_days from dual;
                          v_extended_retention := v_retention_num + v_interval_days;
                          select ltrim(to_char(sysdate - v_extended_retention,'ddd'),'0') into purge_nbr from dual;
                          DBMS_OUTPUT.PUT_LINE('truncated partition' ||purge_nbr);
                          ELSE
                          select ltrim(to_char(sysdate - v_retention_num,'ddd'),'0') into purge_nbr from dual;
                          DBMS_OUTPUT.PUT_LINE('truncated partition' || purge_nbr);
                          END IF;
                          END;
                          end truncate_partition;
                          /

                          Procedure created.

                          --if add a table name parameter for this procedure, this make it invaild.

                          CREATE OR REPLACE procedure TEST_SCHEMA.TRUNCATE_PARTITION
                          (p_table_name IN VARCHAR2(40), p_retention_num IN NUMBER)
                          IS
                          begin
                          declare
                          V_insert_date date;
                          v_sysdate date :=sysdate;
                          v_retention_num number := p_retention_num;
                          v_extended_retention number :=0;
                          purge_nbr Number := 0;
                          v_interval_days number :=0;
                          begin
                          select max(INSERT_DT)
                          INTO V_insert_date
                          FROM P_TABLE_NAME;
                          IF v_insert_date > sysdate THEN
                          select TEST_SCHEMA.find_interval(v_insert_date,sysdate) into v_interval_days from dual;
                          v_extended_retention := v_retention_num + v_interval_days;
                          select ltrim(to_char(sysdate - v_extended_retention,'ddd'),'0') into purge_nbr from dual;
                          DBMS_OUTPUT.PUT_LINE('truncated partition' ||purge_nbr);
                          ELSE
                          select ltrim(to_char(sysdate - v_retention_num,'ddd'),'0') into purge_nbr from dual;
                          DBMS_OUTPUT.PUT_LINE('truncated partition' || purge_nbr);
                          END IF;
                          END;
                          end truncate_partition;
                          /

                          Warning: Procedure created with compilation errors.

                          --while, how can I put a table name parameter in this procedure to make it universal while still valid?

                          thank you

                          Edited by: ROY123 on May 2, 2012 2:41 PM
                          • 10. Re: Please help a debug
                            William Robertson
                            You would need to use dynamic SQL for this. Have a look at EXECUTE IMMEDIATE.