运行Proc时出现问题-Oracle PL/SQL [英] Issue in running a Proc - Oracle PL/SQL
本文介绍了运行Proc时出现问题-Oracle PL/SQL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我通过如下的sql查询在Oracle db中创建了一个proc:-
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');
我收到无效的SQL错误:-
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.
谢谢
推荐答案
我正在Oracle 11g XE中尝试此操作,但由于以下原因而无法编译:
1-最后一个参数后面有一个逗号".
2参数不能引用过程中的类型.
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.
注意:某些IDE不接受空行.
Note: some IDEs don't accept blank lines.
这篇关于运行Proc时出现问题-Oracle PL/SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文