oracle PLS-00306：调用“”中的参数数量或类型错误||'在动态查询中

2uluyalo 于 2023-08-03 发布在 Oracle

关注(0)|答案(2)|浏览(288)

我遇到了施工动态查询问题。

Error report -
ORA-06550: line 10, column 25:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 10, column 1:
PL/SQL: Statement ignored
ORA-06550: line 16, column 17:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 16, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

declare
  v_column_order View_Columns.Column_order%TYPE;

   column_order_sql_qry     VARCHAR2 (150);
   insert_query    Varchar2(300);
  CURSOR view_ids IS select view_id from Views where template_name in ('mydhli_containers', 'demo_mydhli_containers', 'mydhli_shipments', 'demo_mydhli_shipments', 'mydhli_shipments_gogreen', 'demo_mydhli_shipments_gogreen');
begin

FOR v_view_id IN view_ids LOOP
column_order_sql_qry := 'select max(column_order)  from view_columns vc inner join views vi on vc.view_id = vi.view_id where vc.view_id = ' || v_view_id;

EXECUTE IMMEDIATE column_order_sql_qry into v_column_order;

v_column_order := v_column_order + 1;

insert_query := 'insert into VIEW_COLUMNS(ID, COLUMN_ORDER, NAME, VIEW_ID) VALUES (view_column_id_seq.nextval, ' || v_column_order || ',''commercialInvoiceNumbers'',' || v_view_id || ')';

EXECUTE IMMEDIATE insert_query;

END LOOP;

end;

字符串
我不知道我是否应该将数字参数转换为char或该怎么做。看来，nocmal concatination与||不起作用，谢谢你的帮助

oracle

来源：https://stackoverflow.com/questions/76712651/pls-00306-wrong-number-or-types-of-arguments-in-call-to-in-dynamic-query

2条答案

按热度按时间

1cosmwyk1#

v_view_id是行变量。您需要指定要使用光标返回的字段中的哪个字段。所以就像

column_order_sql_qry := 'select max(column_order)  from view_columns vc inner join views vi on vc.view_id = vi.view_id where vc.view_id = ' || v_view_id.view_id;

字符串

赞(0）回复(0）举报 2023-08-03

omtl5h9j2#

我没有你的表，所以我创建了虚拟的，只是为了使代码编译。

SQL> CREATE TABLE views
  2  AS
  3     SELECT 'mydhli_containers' template_name, 1 view_id FROM DUAL;

Table created.

SQL> CREATE TABLE view_columns
  2  AS
  3     SELECT 1 view_id, 2 column_order FROM DUAL;

Table created.

SQL> SET SERVEROUTPUT ON

字符串
光标FOR循环：

SQL> DECLARE
  2     v_column_order        View_Columns.Column_order%TYPE;
  3
  4     column_order_sql_qry  VARCHAR2 (150);
  5     insert_query          VARCHAR2 (300);
  6  BEGIN
  7     FOR cur_r in (
  8        SELECT view_id
  9          FROM Views
 10         WHERE template_name IN ('mydhli_containers',
 11                                 'demo_mydhli_containers',
 12                                 'mydhli_shipments',
 13                                 'demo_mydhli_shipments',
 14                                 'mydhli_shipments_gogreen',
 15                                 'demo_mydhli_shipments_gogreen'))
 16     LOOP
 17        column_order_sql_qry :=
 18              'select max(column_order)  from view_columns vc inner join views vi on vc.view_id = vi.view_id where vc.view_id = '
 19           || cur_r.view_id;
 20
 21        EXECUTE IMMEDIATE column_order_sql_qry
 22           INTO v_column_order;
 23
 24        v_column_order := v_column_order + 1;
 25
 26        insert_query :=
 27              'insert into VIEW_COLUMNS(ID, COLUMN_ORDER, NAME, VIEW_ID) VALUES (view_column_id_seq.nextval, '
 28           || v_column_order
 29           || ',''commercialInvoiceNumbers'','
 30           || cur_r.view_id
 31           || ')';
 32
 33        DBMS_OUTPUT.put_line (insert_query);
 34        -- EXECUTE IMMEDIATE insert_query;
 35     END LOOP;
 36  END;
 37  /

型
测试结果：

insert into VIEW_COLUMNS(ID, COLUMN_ORDER, NAME, VIEW_ID) VALUES
(view_column_id_seq.nextval, 3,'commercialInvoiceNumbers',1)

PL/SQL procedure successfully completed.

SQL>

型

赞(0）回复(0）举报 2023-08-03

我来回答

oracle PLS-00306：调用“”中的参数数量或类型错误||'在动态查询中

2条答案

相关问题

热门标签

最新问答