将变量从Access(前端)传递到Oracle(后端)存储过程

Question

我正在将csv文件导入oracle表，我用pl/sql编写过程，该过程有一个输入参数，该参数将文件名传递给过程。我想从ms访问（前端）中给出IN参数值，我通过传递查询调用该过程，在该过程中我编写了

call EXT_TABLE()，我怎么能在这里传递IN参数。。我的plsql代码写在下面

CREATE OR REPLACE PROCEDURE EXT_TABLE (file_name in varchar2)
AS 
L_QUERY VARCHAR2(1000) := NULL;
L_DROP VARCHAR2(10000) := NULL;
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE IMPORT_TEST
   ( EMP_ID NUMBER (10)
    )
  ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY IMPORT
      ACCESS PARAMETERS 
        ( RECORDS DELIMITED BY NEWLINE 
          FIELDS  TERMINATED BY '',''
          MISSING FIELD VALUES ARE NULL
        )
          LOCATION ('''||file_name ||''')
        )reject limit unlimited';
L_QUERY:= 'INSERT INTO MPRN SELECT * FROM IMPORT_TEST';
EXECUTE IMMEDIATE L_QUERY;
L_DROP := 'drop table IMPORT_TEST ';
execute immediate L_DROP; 
 commit;
END EXT_TABLE;

Import是目录，mprn是我的文件名，它只有一列。我想要MS ACCESS中的文件名，我可以将其传递给过程并导入

Answer 1

不要过度使用动态SQL，请尝试以下操作：

CREATE TABLE IMPORT_TEST(
    EMP_ID NUMBER (10)
)
ORGANIZATION EXTERNAL 
( TYPE ORACLE_LOADER
  DEFAULT DIRECTORY IMPORT
  ACCESS PARAMETERS 
( RECORDS DELIMITED BY NEWLINE 
  FIELDS  TERMINATED BY '',''
  MISSING FIELD VALUES ARE NULL
)
  LOCATION ('some_default_filename')
)reject limit unlimited
;
CREATE OR REPLACE PROCEDURE EXT_TABLE(file_name in varchar2)
AS 
BEGIN
    EXECUTE IMMEDIATE 'ALTER TABLE IMPORT_TEST LOCATION ('''||file_name ||''')';
    INSERT INTO MPRN SELECT * FROM IMPORT_TEST;
END EXT_TABLE;

要运行此过程，请运行：execute EXT_TABLE(file_name => 'my_new_file_name');

要从MS ACCESS运行此过程，您需要使用VBA

1. 设置ODBC连接
2. 创建一个将执行Oracle存储过程的过程