While working on the Procedure and functions found some unique thing about them
Sharing the same--
I have created the procedure and the function -
My tables
desc test_suniti
Name Null Type
---- ---- --------------
TEST VARCHAR2(4000)
desc test_suniti_2
Name Null Type
----------- ---- --------------
AGENT_NM VARCHAR2(100)
RUN_TIME_DT DATE
LOG_MSG_TXT VARCHAR2(4000)
Column test does not exist in the table test_suniti_2 but still my procedure and function does not show any compile time error
But when I execute the procedure and function, I got the run time Error.
create or replace PROCEDURE test_pr
AS
BEGIN
DELETE from test_suniti
where test IN(SELECT test --column doesnot exist in test_suniti_2
FROM test_suniti_2
where agent_nm = 1);
END;
create or replace function test_fn return varchar2
is
BEGIN
DELETE from test_suniti
where test IN(SELECT test --column doesnot exist in test_suniti_2
FROM test_suniti_2
where agent_nm = 1);
return 'abc';
END;
When I run this statement outside the procedure and function, I got the error
Error starting at line 1 in command:
DELETE from test_suniti
where test IN(SELECT test --column doesnot exist in test_suniti_2
FROM test_suniti_2
where agent_nm = 1)
Error report:
SQL Error: ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause:
*Action:
Regards,
Suniti