Can you tell me the error reason in the script [message #351892] |
Fri, 03 October 2008 11:24  |
sg52
Messages: 7 Registered: October 2008
|
Junior Member |
|
|
Hi guys,
I am working on following code:
This code has been developed by someone else.
DECLARE
sql_stmt varchar2(4000);
-- 1 is trade date and 2 is trd date minus 1
dt_diff number := to_date('&1') - to_date('&2'); --LINE 4
BEGIN
sql_stmt := 'CREATE TABLE ad_hoc';
sql_stmt := sql_stmt || 'PARALLEL(DEGREE 4 INSTANCES 1) NOLOGGING PCTFREE 0 AS ';
sql_stmt := sql_stmt || 'SELECT E.ACCT, ';
sql_stmt := sql_stmt || ' E.MSG, ';
sql_stmt := sql_stmt || ' E.MSG_D, ';
sql_stmt := sql_stmt || ' C.Code, ';
sql_stmt := sql_stmt || ' C.t_date, ';
sql_stmt := sql_stmt || ' C.s_date, ';
sql_stmt := sql_stmt || ' FROM (SELECT ACCT, ';
sql_stmt := sql_stmt || ' MSG, ';
sql_stmt := sql_stmt || ' MSG_D ';
sql_stmt := sql_stmt || ' FROM ACCT_FACT ';
sql_stmt := sql_stmt || ' WHERE CODE IN (''S21'',''S12'',''F20'') ';
sql_stmt := sql_stmt || ' ) E, ';
sql_stmt := sql_stmt || ' TXN_FT C ';
sql_stmt := sql_stmt || ' WHERE E.ACCT = C.ACCT ';
sql_stmt := sql_stmt || ' AND C.TXN_CANCN_C = '' '' ';
if dt_diff > 1 then
sql_stmt := sql_stmt || ' AND C.TXN_PSTG_D BETWEEN to_date(''&2'') and to_date(''&1'') ';
else
sql_stmt := sql_stmt || ' AND C.TXN_PSTG_D = to_date(''&1'') ';
end if;
EXECUTE IMMEDIATE sql_stmt;
EXCEPTION
WHEN OTHERS THEN
raise;
RETURN;
END;
but it is giving following error:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 4
I cant understand y is this happening..
and also.. do we use single qoutes twice while specifying a condition for dynamic sql??
i mean lets see this from the code:
WHERE CODE IN (''S21'',''S12'',''F20'')
in generel we use:
WHERE CODE IN ('S21','S12','F20')
so y we r using above notation here??
Kindly help me on this!!
|
|
|
|
|
Re: Can you tell me the error reason in the script [message #351970 is a reply to message #351892] |
Sat, 04 October 2008 01:29  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Oh boy, does this look bad..
sg52 wrote on Fri, 03 October 2008 18:24 | sql_stmt := 'CREATE TABLE ad_hoc';
sql_stmt := sql_stmt || 'PARALLEL(DEGREE 4 INSTANCES 1) NOLOGGING PCTFREE 0 AS ';
|
Never create 'ad-hoc' tables at runtime in Oracle. It's a Sybase/MS SQLServer thing to do, not the Oracle way.
Quote: | EXCEPTION
WHEN OTHERS THEN
raise;
RETURN;
END;
|
Bad coding. The raise does nothing but obfuscating the linenumber where the actual error was raised. The return-statement is never reached.
Remove the entire exception handler.
As for your other question:
If you need a quote within a quoted value, you need to use two single quotes. For example:
|
|
|