Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    5

    Unhappy Unanswered: table as arguments in PL/SQL

    Hi,

    I've a small PL/SQL problem.
    I made a kind of tool which aim is to clean a table given as parameter.

    Here follows the code:

    declare
    table_name char(05);
    date_pivot_char char(08);

    begin
    select count(*) into nbr_rec_purge from table_name where table_name.datcta < date_pivot_char;

    end;

    The returned message is:

    ERROR at line 13:
    ORA-06550: line 13, column 44:
    PLS-00201: identifier 'TABLE_NAME' must be declared
    ORA-06550: line 13, column 4:
    PL/SQL: SQL Statement ignored

    Thank you!

  2. #2
    Join Date
    Jan 2004
    Posts
    492

    Re: table as arguments in PL/SQL

    Originally posted by zenobe
    Hi,

    I've a small PL/SQL problem.
    I made a kind of tool which aim is to clean a table given as parameter.

    Here follows the code:

    declare
    table_name char(05);
    date_pivot_char char(08);

    begin
    select count(*) into nbr_rec_purge from table_name where table_name.datcta < date_pivot_char;

    end;

    The returned message is:

    ERROR at line 13:
    ORA-06550: line 13, column 44:
    PLS-00201: identifier 'TABLE_NAME' must be declared
    ORA-06550: line 13, column 4:
    PL/SQL: SQL Statement ignored

    Thank you!

    You havent declared nbr_rec_purge as a variable - So you're trying to select the count(*) into a variable which does not exist. Declare it instead of declaring table_name as a variable.

  3. #3
    Join Date
    Mar 2004
    Posts
    5

    Talking

    I have declared tnbr_rec_purge as a variable now, but it still doesn't works.
    So, I tried another way and it seems to work.
    I just hope the performances will be good... we'll see.

    Thanks!


    DECLARE

    table_name char(05) := 'CGD30';
    date_pivot_char char(08) := '20020101';
    nbr_rec_purge integer;
    v_statement varchar2(100);

    BEGIN

    v_statement := 'select count(*) from ' || table_name || ' where datcta <' || date_pivot_char;
    EXECUTE IMMEDIATE v_statement INTO nbr_rec_purge;
    DBMS_OUTPUT.PUT_LINE ('Nbre de records: ' || nbr_rec_purge);

    end;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •