This is the first time I have tried writing an Oracle procedure and I am getting an error (shown in the question title) centering on the DBMS_OUTPUT.PUT_LINE
line.
I read online that it can only hand back VARCAHR2
columns, so I have cast the only two non VARCHAR2
columns that are being accessed, but I am still getting the error. This error is happening when I try to run it directly in the oracle SQL Developer.
CREATE OR REPLACE PROCEDURE LOGGING_PRC
(
STARTDATE_IN IN VARCHAR2,
ENDDATE_IN IN VARCHAR2,
NAMES_IN IN VARCHAR2,
MODS_IN IN VARCHAR2,
LOGS_IN IN VARCHAR2,
ID_OUT OUT VARCHAR2,
NAME_OUT OUT VARCHAR2,
MODULE_OUT OUT VARCHAR2,
ENTRYDATE_OUT OUT VARCHAR2,
STATUS_OUT OUT VARCHAR2,
TYPE_OUT OUT VARCHAR2
)
AS
BEGIN
SELECT
CAST(ID_LOG AS VARCHAR2(16)),
APNAME,
APPMOD,
CAST(ENTRYDATE AS VARCHAR2(30)),
APPSTATUS,
LOGTYPE
INTO
ID_OUT,
NAME_OUT,
MODULE_OUT,
ENTRYDATE_OUT,
STATUS_OUT,
TYPE_OUT
FROM
BASE
WHERE
ENTRYDATE > STARTDATE_IN AND
ENTRYDATE < ENDDATE_IN AND
(NAMES = NAMES_IN OR NAMES_IN IS NULL) AND
(MODS = MODS_IN OR MODS_IN IS NULL) AND
(LOGS = LOGS_IN OR LOGS_IN IS NULL);
RETURN;
DBMS_OUTPUT.PUT_LINE(ID_OUT, NAME_OUT, MODULE_OUT, ENTRYDATE_OUT, STATUS_OUT, TYPE_OUT);
END LOGGING_PRC;
Does someone see where I have the incorrect code?
DBMS_OUTPUT.PUT_LINE is defined as follows
procedure put_line(a varchar2);
so, it only accepts one input parameter.
If you need to print the values of more than one field, you need to concatenate them in a single varchar2
; you can try with :
DBMS_OUTPUT.PUT_LINE(ID_OUT || ',' || NAME_OUT || ',' || MODULE_OUT || ',' || ENTRYDATE_OUT || ',' || STATUS_OUT || ',' || TYPE_OUT);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments