1

I created a proc in Oracle db from a sql query as below:-

CREATE OR REPLACE PROCEDURE getTableDetails(
       p_tablename IN table_info.table_name%TYPE,
       o_owner OUT table_info.owner%TYPE,
       o_table_name OUT  table_info.table_name%TYPE,
       o_num_rows OUT table_info.num_rows%TYPE,
       o_num_cols OUT table_info.num_cols%TYPE,
       o_columnlist OUT table_info.columnlist%TYPE,
       o_keycolumns OUT table_info.keycolumns%TYPE,
       )
IS
BEGIN

with tableinfo as
(select 
    t.owner, 
    t.table_name, 
    t.num_rows,
    count(*) as num_cols,
    listagg(c.column_name, ',') within group (order by c.column_name) as columnlist
  from all_tables t
  join all_tab_columns c on c.owner = t.owner and c.table_name = t.table_name
  group by t.owner, t.table_name, t.num_rows
)
, pkinfo as
(
  select
    c.owner,
    c.table_name,
    listagg(cc.column_name, ',') within group (order by cc.position) as keycolumns
  from all_constraints c
  join all_cons_columns cc on cc.owner = c.owner and cc.constraint_name = c.constraint_name   
  where c.constraint_type = 'P'
  group by c.owner, c.table_name
)
select owner,table_name,num_rows,num_cols,columnlist,keycolumns
into o_owner,o_table_name,o_num_rows,o_num_cols,o_columnlist,o_keycolumns
from tableinfo t
left join pkinfo pk using (owner, table_name)
where table_name = p_tablename
order by t.num_rows desc;

END;
/

When I try to execute this:-

EXECUTE getTableDetails('ABC_TABLE');

I am getting an invalid SQL error:-

 [Error Code: 900, SQL State: 42000]  ORA-00900: invalid SQL statement

Could you please help me troubleshoot this? The query for this is giving me results.

Thanks,

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
RLearner
  • 65
  • 5
  • Sorry, I've never heard of this. In http://www.dba-oracle.com/sf_ora_00900_invalid_sql_statement.htm they explain, that `CREATE OR REPLACE PROCEDURE` could fail with this error, but you say you created the procedure and the error is when you are trying to execute it. What tool do you use to run `EXECUTE getTableDetails('ABC_TABLE');`? – Thorsten Kettner Feb 21 '20 at 22:04
  • Have you looked up the error on Stackoverflow? I've just found this: https://stackoverflow.com/questions/13722307/ora-00900-invalid-sql-statement-when-run-a-procedure-in-oracle-10g – Thorsten Kettner Feb 21 '20 at 22:07
  • I used DBVisualizer Pro. Even the below is giving an error:- begin getTableDetails('ABC_TABLE'); end; [Error Code: 6550, SQL State: 65000] ORA-06550: line 2, column : PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: := . ( % ; – RLearner Feb 21 '20 at 22:52
  • 2
    @RLearner - not familiar with that tool (but as you've gathered, it seems to not recognise the `execute` shorthand from SQL\*Plus, SQL Developer and SQLcl); might it be expecting a `/` after the anonymous begin/end block? – Alex Poole Feb 21 '20 at 23:02

1 Answers1

0

I'm trying this in Oracle 11g XE and it doesn't compile because:
1-You have a "comma" after the last parameter.
2-Parameters cann't refer to types inside procedure.

Note: some IDEs don't accept blank lines.

alvalongo
  • 523
  • 3
  • 11