Как создать параметр массива строк в хранимой процедуре и передавать его при вызове?

Question

Для начала я создал тип данных:

create or replace type string_array_for_create_table as table of varchar2(4000);

Затем создаю процедуру:

create or replace PROCEDURE DelCreateObject(ObjName varchar2, ObjType varchar2, arrParam in string_array_for_create_table)
IS
 v_counter number := 0;   
begin    
  if ObjType = 'TABLE' then
    select count(*) into v_counter from user_tables where table_name = upper(ObjName);
    if v_counter > 0 then          
      EXECUTE IMMEDIATE 'drop table ' || ObjName || ' cascade constraints'; 
      end if; 
       execute immediate  'create table ' || ObjName || ' ('|| arrparam || ')' ;  
  end if;
  EXCEPTION
    WHEN OTHERS THEN
    IF SQLCODE != -2289 THEN
      RAISE;
    END IF;
end;

Но получаю ошибку:

10/8 PL/SQL: Statement ignored 10/27 PLS-00306: ошибочно число или типы аргументов при обращении к '||'

Мне затем нужно вызвать эту процедуру так:

call DelCreateObject('COMPANY','TABLE',

'ID              NUMBER(19) not null
     primary key,
 ADDRESS            VARCHAR2(20 char),
 CONTACT_FIRST_NAME VARCHAR2(10 char),
 CONTACT_LAST_NAME  VARCHAR2(10 char),
 CONTACT_PHONE      VARCHAR2(20 char),
 NAME               VARCHAR2(12 char)
      constraint UKNIU8SFIL2GXYWCRU9AH3R4EC5
      unique,
 PHONE              VARCHAR2(20 char)'
);

Как это можно сделать?

Answer 1

create or replace PROCEDURE delAndCreateTable(ObjName varchar2, ObjType varchar2, arrParam varchar2)
IS
 v_counter number := 0;   
begin    
  if ObjType = 'TABLE' then
    select count(*) into v_counter from user_tables where table_name = upper(ObjName);
    if v_counter > 0 then          
      EXECUTE IMMEDIATE 'drop table ' || ObjName || ' cascade constraints'; 
      end if; 
     EXECUTE IMMEDIATE 'create table ' ||ObjName ||' ( ' ||arrParam||' )';
  end if;

  EXCEPTION
    WHEN OTHERS THEN
    IF SQLCODE != -2289 THEN
      RAISE;
    END IF;
end

вызов процедуры

call delAndCreateTable ('dump2', 'TABLE', 'ID NUMBER(19) not null  primary key, ADDRESS VARCHAR2(20 char), CONTACT_FIRST_NAME VARCHAR2(10 char),
 CONTACT_LAST_NAME  VARCHAR2(10 char),
 CONTACT_PHONE      VARCHAR2(20 char),
 NAME               VARCHAR2(12 char)
      constraint UKNIU8SFIL2GXYWCRU9AH3R4oC8
      unique,
 PHONE              VARCHAR2(20 char)');

Answer 2

Как изначально задумно в вопросе, тоже можно, но массив не строка, потому и ошибка:

create or replace type paramList is table of varchar2 (32767)
/
exec dbms_output.put_line ('array='||paramList ())
PLS-00306: wrong number or types of arguments in call to '||'

Надо только склеить элементы массива в одну строку. Вот посмотрите:

create or replace type paramList is table of varchar2 (32767)
/
create or replace procedure recreateTable (tableName varchar2, params paramList) is
    function join (p paramList) return varchar2 is
        ret varchar2 (32767); 
    begin 
        for i in 1..p.count loop ret := ret||p(i)||','||chr (10); end loop;
        return rtrim (ret, ','||chr (10));
    end;
begin    
    for r in (select 1 from user_tables where table_name=upper (tableName)) loop
        execute immediate 'drop table '||tableName||' cascade constraints purge'; 
    end loop;
    execute immediate 'create table '||tableName||' ('||join (params)||')';  
end;
/

И даже работает:

SQL> call recreateTable ('tab1', paramList (
    'id number (19) primary key',
    'address varchar2 (64)'
    )); 

SQL> desc tab1
Name    Null?    Type         
------- -------- ------------ 
ID      NOT NULL NUMBER(19)   
ADDRESS          VARCHAR2(64)

БЛОГ НА HUSL

Как создать параметр массива строк в хранимой процедуре и передавать его при вызове?

Ответы (2 шт):