|
안녕하세요.
동적SQL을 구현해 봤는데 에러가 납니다. 간단히 테스트 하려구
SQL*PLUS에서 패키지를 등록시킨후 다음과 같이 했는데...
---------------------------------------------------------------
SQL> execute packagebbs.IstRecords('php', '심', 'gate', '211','ht', 'te', 'co', '11', 'ph');
BEGIN packagebbs.IstRecords('php', '심', 'gate', '211','ht', 'te', 'co', '11', 'ph'); END;
*
1행에 오류:
ORA-06550: 줄 1, 열7:PLS-00306: 'ISTRECORDS' 호출 시 인수의 갯수나 유형이
잘못되었습니다
ORA-06550: 줄 1, 열7:PL/SQL: Statement ignored
----------------------------------------------------------------
그대로 복사한 겁니다.
테스트한 패키지는 아래와 같습니다. 복사해서 보세요.
테이명을 동적으로 할당받아 처리하는 겁니다.
----------------------------------------------------------------
CREATE OR REPLACE PACKAGE packageBbs
AS
TYPE cur IS REF CURSOR;
PROCEDURE IstRecords(p_fd_tbl_cd IN VARCHAR2,
p_fd_Name IN VARCHAR2,
p_fd_Email IN VARCHAR2,
p_fd_Ip IN VARCHAR2,
p_fd_URL IN VARCHAR2,
p_fd_Title IN VARCHAR2,
p_fd_Content IN VARCHAR2,
p_fd_Password IN VARCHAR2,
p_fd_tbl_nm IN VARCHAR2,
p_errorcode OUT NUMBER);
END packageBbs;
/
CREATE OR REPLACE PACKAGE BODY packageBbs
AS
PROCEDURE IstRecords(p_fd_tbl_cd IN VARCHAR2,
p_fd_Name IN VARCHAR2,
p_fd_Email IN VARCHAR2,
p_fd_Ip IN VARCHAR2,
p_fd_URL IN VARCHAR2,
p_fd_Title IN VARCHAR2,
p_fd_Content IN VARCHAR2,
p_fd_Password IN VARCHAR2,
p_fd_tbl_nm IN VARCHAR2,
p_errorcode OUT NUMBER)
IS
v_fd_Ref NUMBER(5);
v_Sql varchar2(200);
v_Cursor integer;
rows_processed integer;
BEGIN
p_errorcode := 0;
v_Cursor := DBMS_SQL.OPEN_CURSOR;
v_Sql := 'SELECT MAX(fd_Ref) FROM ' || p_fd_tbl_nm;
DBMS_SQL.PARSE(v_Cursor, v_Sql, DBMS_SQL.native);
DBMS_SQL.DEFINE_COLUMN(v_Cursor, 1, v_fd_Ref);
rows_processed := DBMS_SQL.EXECUTE(v_Cursor);
DBMS_SQL.COLUMN_VALUE(v_Cursor, 1, v_fd_Ref);
dbms_output.put_line(v_fd_Ref);
v_Sql := 'INSERT INTO ' || p_fd_tbl_nm || ' VALUES';
v_Sql := v_Sql || '(p_fd_tbl_cd,DECODE(v_fd_Ref, NULL, 1, v_fd_Ref + 1),0,0,';
v_Sql := v_Sql || 'p_fd_Name,p_fd_Email,p_fd_Ip,p_fd_URL,p_fd_Title,p_fd_Content,';
v_Sql := v_Sql || 'SYSDATE,p_fd_Password,0)';
DBMS_SQL.PARSE(v_Cursor, v_Sql, DBMS_SQL.native);
rows_processed := DBMS_SQL.EXECUTE(v_Cursor);
DBMS_SQL.CLOSE_CURSOR(v_Cursor);
EXCEPTION
WHEN OTHERS THEN
p_errorcode := SQLCODE;
END IstRecords;
END packageBbs;
/
|
