Oracle存储过程帮助 [英] Oracle Stored procedure help

问题描述

我编写了一个oracle存储过程。该过程已成功编译。



但是当我使用参数值执行该存储过程时，它在查询中给出了错误。
下面的
是我的存储过程查询----

I have written one oracle stored procedure.The procedure is compiled sucessfuly.

But when i am executing that stored procedure with paramater value it is giving error in query.
below is my stored procedure query----

CREATE OR REPLACE PROCEDURE test123(p_Le in varchar2,p_CHK in varchar2,p_Ty in varchar2,p_Po in varchar2,
p_Cl in varchar2,p_CA in varchar2,p_From_Date in date,p_To_Date in date,p_Number in varchar2,
p_recordset OUT SYS_REFCURSOR) as

sqlquery  varchar2(30000);

BEGIN

IF  p_Le = 'SUB' THEN

     IF  p_CHK ='TRUE' THEN

       sqlquery:= ( ' select type, sub_type, amount from (select rank() over(order by count(distinct(id)) desc) r,');

    ELSIF p_CHK='FALSE' THEN

        sqlquery:=('select');

     END IF;
         IF  p_Po='ALL' THEN

             IF   p_Cl = 'ALL' THEN

                   sqlquery:= sqlquery || 't.type, t.sub_type, count(distinct(id)) as amount from logged l, type t where t.category = '''|| p_CA ||''' and l.ftr_type_id = t.type_id and trunc(date) >=  '''|| p_From_Date ||'''  and trunc(date) <=   '''|| p_To_Date ||''' and t.type = '''|| p_Ty ||''' and substr(l.msisdn,1,4) in ('|| p_Number ||') and t.type <> ''Call'' group by t.type,t.sub_type order by t.type, t.sub_type';

OPEN p_recordset FOR sqlquery;

             END IF;

        END IF;

END IF;

END test123;
/

请帮助

Please help

推荐答案

BETWEEN TO_DATE（'''|| parm1 || '''，''dd / MM / yy''）这只是在动态查询中使用parametr的一个例子....

BETWEEN TO_DATE('''||parm1||''',''dd/MM/yy'') this is just an example of using parametr inside dynamic query....

只是一个猜测，但这可能是吗？



在行中：

选择类型，子类型，金额来自（选择等级（）结束（按计数排序（不同（ftr_id）） ）desc）r，'）;



类型是关键字。您应该使用表限定类型列或用引号括起来（TYPE）

请参阅此处的说明。 [ ^ ]



它编译因为sqlquery是正确的字符串，但是当调用打开游标时，它失败了。您应该将sqlquery输出到控制台，然后单独检查它。这是所有动态SQL查询的好习惯。



如果这有帮助，请花些时间接受解决方案。谢谢。

Just a guess, but could this be it?

In the line:
select type, sub_type, amount from (select rank() over(order by count(distinct(ftr_id)) desc) r,');

Type is a keyword. You should qualify type column with the table or surround it with quotes ("TYPE")
See the explanation here.[^]

It compiles because sqlquery is correct string, but when called to open a cursor it fails. You should output your sqlquery to console and then check it separately. This is good practice for all dynamic sql queries.

If this helps, please take time to accept the solution. Thank you.

这篇关于Oracle存储过程帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！