Home » SQL & PL/SQL » SQL & PL/SQL » Can you tell me the error reason in the script
Can you tell me the error reason in the script [message #351892] Fri, 03 October 2008 11:24 Go to next message
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 #351898 is a reply to message #351892] Fri, 03 October 2008 12:10 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You need a format mask when using TO_DATE or TO_CHAR.
Re: Can you tell me the error reason in the script [message #351899 is a reply to message #351892] Fri, 03 October 2008 12:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read TO_DATE and follow the links.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
And always post your Oracle version (4 decimals).

Regards
Michel
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 Go to previous message
Frank
Messages: 7880
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:
a := 'Bob''s uncle'
Previous Topic: How to save output to a table
Next Topic: help query in needed
Goto Forum:
  


Current Time: Sun Dec 04 18:32:25 CST 2016

Total time taken to generate the page: 0.10724 seconds