Forum Stats

  • 3,838,564 Users
  • 2,262,383 Discussions
  • 7,900,687 Comments

Discussions

I wanted to know after creating a table using "ADM_DDL.DO_DDL" can we use "sql%rowcount" ?

I wanted to know if there is any way to get the row count immediately after creating a table using "ADM_DDL.DO_DDL" ?

Tagged:

Best Answer

  • BEDE
    BEDE Oracle Developer Member Posts: 2,454 Gold Trophy
    Answer ✓

    sql%rowcount is a variable in PL/SQL, while create table is DDL, which may be executed in PL/SQL only dynamically, that is via execute immediate. That's why I think this won't work.

    Normally, after an insert select or update or delete or merge sql%rowcount shows the number of rows inserted/updated/deleted/merged.

    Until now it never crossed my mind to use that after create table because in PL/SQL blocks you seldom create tables.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,243 Red Diamond

    Hi, @User_5U12Z

    I wanted to know if there is any way to get the row count immediately after creating a table using "ADM_DDL.DO_DDL" ?

    That depends on what adm_ddl.do_ddl does. Where is it documented? What happens when you try it?

    SQL%ROWCOUNT returns the number of rows affected by the most recent statement of certain kinds, including CREATE TABLE statements, but it only works for the most recent statement.

  • BEDE
    BEDE Oracle Developer Member Posts: 2,454 Gold Trophy
    Answer ✓

    sql%rowcount is a variable in PL/SQL, while create table is DDL, which may be executed in PL/SQL only dynamically, that is via execute immediate. That's why I think this won't work.

    Normally, after an insert select or update or delete or merge sql%rowcount shows the number of rows inserted/updated/deleted/merged.

    Until now it never crossed my mind to use that after create table because in PL/SQL blocks you seldom create tables.

  • BEDE
    BEDE Oracle Developer Member Posts: 2,454 Gold Trophy

    Apparently it does work.

    I tested like below:

    begin
    dbms_output.enable(1000000);
    execute immediate 'create table zzzt as select * from employees';
    dbms_output.put_line('>'||sql%rowcount||'< after create table as select');
    end;
    
  • User_4ZB42
    User_4ZB42 Member Posts: 5 Green Ribbon

    Code is perfectly work, You can try once more I am sure it will be work

    begin
    dbms_output.enable(1000000);
    execute immediate 'create table zzzt as select * from employees';
    dbms_output.put_line('>'||sql%rowcount||'< after create table as select');
    end;
    

    Best Regards

    Johnsons Ellen