Stack Exchange
How to analyze metadata of given schema name and table name dynamically in Oracle?
I am trying to write a dynamic oracle plsql script that takes schema_name and table_name for parameter and returns Primaty Key Column names,Index flag(if table contains index returns 1 else 0),data volume for size of table in db,row count and column count of selected schema and table.Here is my code
DECLARE
  p_table_name      VARCHAR2(1000);
  p_owner_name      VARCHAR2(1000);
  v_pk_columns_name VARCHAR2(1000);
  v_ind_exists_flg  VARCHAR2(1000);
  v_data_volume     VARCHAR2(1000);
  v_row_cnt         VARCHAR2(1000);
  v_column_cnt      VARCHAR2(1000);
  v_flag_one        NUMBER;
  v_flag_zero       NUMBER;
  v_constraint_type VARCHAR2(100);
BEGIN
  v_flag_one        := 1;
  v_flag_zero       := 0;
  v_constraint_type := 'P';
  p_owner_name      := '0DS03';
  p_table_name      := 'ODS_SALES';
  v_data_volume     := 'SELECT SUM(bytes)/1024/1024 INTO v_db_size 
                          FROM dba_segments 
                         WHERE owner = ' || p_owner_name ||
                          'AND segment_name = ' || p_table_name;

  EXECUTE IMMEDIATE v_data_volume;

  v_row_cnt := 'SELECT COUNT(*) FROM ' || p_owner_name;
  EXECUTE IMMEDIATE v_row_cnt;

  v_ind_exists_flg := 'SELECT CASE WHEN (index_name) <> null THEN ' || v_flag_one || '
                                   ELSE ' || v_flag_zero || '
                                   END AS flag
                         FROM dba_ind_columns
                        WHERE table_owner =' || p_owner_name ||
                         'AND table_name =' || p_table_name;
  EXECUTE IMMEDIATE v_ind_exists_flg;

  v_column_cnt := 'SELECT COUNT(column_name) 
                     FROM all_tab_columns
                    WHERE table_name =' || p_table_name || 
                     'AND owner = ' ||p_owner_name;
  EXECUTE IMMEDIATE v_column_cnt;

  v_pk_columns_name := 'SELECT cols.column_name
                          FROM all_constraints cons, all_cons_columns cols
                         WHERE cons.constraint_type = ' || v_constraint_type ||
                          'AND cols.owner = ' || p_owner_name ||
                          'AND cons.table_name = ' || p_table_name ||
                          'AND cons.constraint_name = cols.constraint_name
                           AND cons.owner = cols.owner';
  EXECUTE IMMEDIATE v_pk_columns_name;

END;

It gives me these errors :
ORA-00933: SQL command not properly ended
ORA-06512: at line 23
00933. 00000 -  "SQL command not properly ended"

How can i fix these errors ? What is worng with my syntax ? Thanks for your help.

';' is missing.

Line 18 is p_table_name : = 'ODS_SALES'.  Column 14 is the space between the ':and the=.  Remove that.  It appears that you wrote the entire procedure at once rather than building it up (i.e. write and test the code to do one thing then extend it) because there are a number of additional syntax errors that you'll get when you correct the first one.  And then there are problems with most if not all the SQL statements you've dynamically assembled.  Print out/ log the statements before you execute them so that you can debug the errors you'll get when you call execute immediate`.

If you use EXECUTE IMMEDIATE with a statement that returns a value, use EXECUTE IMMEDIATE <your statement> INTO <a variable>

It seems to be a lot more complicated than it needs to be because it uses dynamic SQL. Make it all normal static SQL (apart from counting the rows in the table) and it will be easier to work with.

Updating the question with the new error as we go through each error is not particularly efficient-- that's why you want to break things down.  The dba_segments query should be static SQL not dynamic SQL.  If you want to use dynamic SQL, you should use bind variables, don't concatenate the owner and table_name in the SQL statement.  Take the into clause out of the string you're building if you're using dynamic SQL and make that part of the execute immediate call.

select sum(bytes)/1024/1024    into v_db_size   from dba_segments   where owner = p_owner_name    and segment_name = p_table_name; will fix the current issue.  Then you'll get a new syntax error on the next line because you've built an invalid SQL statement

And it seems like you were shown exactly how to do that in your prior question https://stackoverflow.com/questions/68884984/how-to-pass-schema-name-and-table-name-as-input-then-get-memory-size-and-row-cou from a week or so ago

Working version:
declare
    p_owner_name   all_tables.owner%type := 'ODS03';
    p_table_name   all_tables.table_name%type := 'ODS_SALES';

    v_size_mb      number(8,1);
    v_pk_columns   varchar2(1000);
    v_index_cnt    number;
    v_row_cnt_sql  varchar2(1000) := 'select count(*) from ' || p_owner_name||'.'||p_table_name;
    v_row_cnt      integer;
    v_column_cnt   number;
begin
    select sum(bytes)/power(1024,2) into v_size_mb
    from   dba_segments
    where  owner = p_owner_name and segment_name = p_table_name;

    execute immediate v_row_cnt_sql into v_row_cnt;

    select count(*) into v_column_cnt
    from   all_tab_columns
    where  table_name = p_table_name and owner = p_owner_name;

    select count(*) into v_index_cnt
    from   all_indexes i
    where  i.owner = p_owner_name
    and    i.table_name = p_table_name;

    select listagg(cols.column_name, ', ') within group (order by cols.position)
    into   v_pk_columns
    from   all_constraints cons
           join all_cons_columns cols
                on  cols.owner = cons.owner
                and cols.table_name = cons.table_name
                and cols.constraint_name = cons.constraint_name
    where  cons.owner = cols.owner
    and    cons.table_name = p_table_name
    and    cons.constraint_type = 'P';

    dbms_output.put_line('Table: '||p_owner_name||'.'||p_table_name||':');
    dbms_output.put_line('Size (MB): '||round(v_size_mb,1));
    dbms_output.put_line('Rows: '||v_row_cnt);
    dbms_output.put_line('Columns: '||v_column_cnt);
    dbms_output.put_line('Indexes: '||v_index_cnt);
    dbms_output.put_line('PK columns: '||v_pk_columns);
end;

The only part that needs to be dynamic is counting the rows.
In many systems there will be tables so large that counting the rows could take many hours, so you might want to consider the requirement for this. Would an approximate count be acceptable? Will statistics (all_tables.num_rows) be close enough? If you really need an exact count, you might consider parallel query (requires Enterprise Edition).

Your approach in not optimal. You can not use static SQL to select from parametrized table, but you can use static SQL to select from the database metadata for any table.
One possible approach is to define a view that selects the dictionary data (this is not necessary, but will keep your procedure simpler).
create or replace  view my_tab_meta as
with pk as (
SELECT COLS.OWNER,CONS.TABLE_NAME, listagg(cols.column_name,'.') within group (order by cols.POSITION) as pk_cols
FROM ALL_CONSTRAINTS CONS, ALL_CONS_COLUMNS COLS
WHERE CONS.CONSTRAINT_TYPE = 'P' 
AND CONS.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME
AND CONS.OWNER = COLS.OWNER
group by COLS.OWNER,CONS.TABLE_NAME),
idx as (
select distinct TABLE_OWNER, TABLE_NAME from all_indexes) 
select tab.OWNER, tab.TABLE_NAME, 
tab.NUM_ROWS, 
tab.BLOCKS * (select VALUE from v$parameter where name = 'db_block_size') /1024/1024 db_size,
case when idx.TABLE_OWNER is not null then 1 else 0 end as idx_flag,
pk.pk_cols
from all_tables tab
left outer join idx
on tab.owner = idx.TABLE_OWNER and tab.TABLE_NAME = idx.TABLE_NAME
left outer join pk
on tab.owner = pk.OWNER and tab.TABLE_NAME = pk.TABLE_NAME;

Some discussion

you should decide to use either DBA views or ALL view - and not to combine them.

you will need access rights to the dictionary view to be able to create the view

I'm taking size and row count from dictionary as well. This is more preformant but requires up to date object statistics.


Below is the frame for your PL/SQL block
declare
p_table_name varchar2(128) := 'TAB1';
p_owner_name  varchar2(128) := 'REPORTER';
v_PK_COLUMNS_NAME varchar2(4000);
v_IND_EXISTS_FLG varchar2(1);
v_DATA_VOLUME number;
v_ROW_CNT number;
v_COLUMN_CNT number; 
begin
  select 
    NUM_ROWS, DB_SIZE, IDX_FLAG, PK_COLS 
  into v_ROW_CNT, v_DATA_VOLUME, v_IND_EXISTS_FLG, v_PK_COLUMNS_NAME
  from MY_TAB_META 
  where owner = p_owner_name and table_name = p_table_name;
end;
/

Note also the usage of propper data types, e.g. row count is number.
