Home » SQL & PL/SQL » SQL & PL/SQL » ORACLE - SYS.DBMS_JOB causes error "ORA-01461 can bind a LONG value only for insert into a LONG colu (oracle 10)
ORACLE - SYS.DBMS_JOB causes error "ORA-01461 can bind a LONG value only for insert into a LONG colu [message #576171] Fri, 01 February 2013 02:33 Go to next message
amitms
Messages: 3
Registered: February 2013
Location: kathmandu
Junior Member
QUESTION2
-------------
query1 VARCHAR2(4000);
query2 CLOB;
vSQLString CLOB;

query1 := 'query statement';
query2 := 'query statement2';
query1=TO_CLOB(QUERY1);


vSQLString :='
DECLARE
...
BEGIN
EXECUTE IMMEDIATE'''||REPLACE(query1||query2,'''','''''')||''';
....
EXCEPTION WHEN OTHERS THEN
.....
END;';

DBMS_JOB.SUBMIT( JOB=>VJOBNUM2, WHAT=>vSQLString, INSTANCE=>1 );

---------------------------------------------------------------------
ERROR IS :
"ORA-01461 can bind a LONG value only for insert into a LONG column"

---------------------------------------------------------------------
This error also occurs when when vQLString in VARCHAR2 exceeds 4000;

Please someboady HELP on How to pass vSQLString in CLOB TO => WHAT
Re: ORACLE - SYS.DBMS_JOB causes error "ORA-01461 can bind a LONG value only for insert into a LONG colu [message #576172 is a reply to message #576171] Fri, 01 February 2013 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Nobody can debug a code that he does not see.
Use SQL*Plus and copy and paste your session, the WHOLE session.

Quote:
EXCEPTION WHEN OTHERS THEN


This is a bug, read WHEN OTHERS.

Regards
Michel
Re: ORACLE - SYS.DBMS_JOB causes error "ORA-01461 can bind a LONG value only for insert into a LONG colu [message #576174 is a reply to message #576171] Fri, 01 February 2013 03:20 Go to previous messageGo to next message
flyboy
Messages: 1776
Registered: November 2006
Senior Member
amitms wrote on Fri, 01 February 2013 09:33
Please someboady HELP on How to pass vSQLString in CLOB TO => WHAT

You cannot. Jobs are stored in ALL_JOBS view, which description is available in the documentation:
http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1086.htm#i1580430
Note the data type of WHAT column.

By the way, I see no reason for EXECUTE IMMEDIATE use as VSQLSTRING is already dynamically generated string. Additionally, also EXECUTE IMMEDIATE cannot run dynamic string having CLOB data type in 10gR2 too:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm#sthref2679
(although, it is probably limited - as PL/SQL VARCHAR2 data type - to 32767 characters)
Re: ORACLE - SYS.DBMS_JOB causes error "ORA-01461 can bind a LONG value only for insert into a LONG colu [message #576370 is a reply to message #576174] Mon, 04 February 2013 03:20 Go to previous messageGo to next message
amitms
Messages: 3
Registered: February 2013
Location: kathmandu
Junior Member
yes there is reason.. you can place multiple EXECUTE IMMEDIATE statements in WHAT jobs
So that you and place multiple jobs and execute statements in parallel
Re: ORACLE - SYS.DBMS_JOB causes error "ORA-01461 can bind a LONG value only for insert into a LONG colu [message #576379 is a reply to message #576370] Mon, 04 February 2013 05:05 Go to previous messageGo to next message
flyboy
Messages: 1776
Registered: November 2006
Senior Member
amitms wrote on Mon, 04 February 2013 10:20
yes there is reason.. you can place multiple EXECUTE IMMEDIATE statements in WHAT jobs
So that you and place multiple jobs and execute statements in parallel

If you are answering to the second paragraph, you probably missed my point. Why not simply run the statement(s) in block directly?
vSQLString :='
DECLARE
...
BEGIN
'||query1||query2||'
....
END;';

If there are multiple statements in one PL/SQL block, they are run in serial regardless of static/dynamic call. For parallel execution, you would have to SUBMIT multiple jobs, which would probably help you as each WHAT statement could be reduced to less than 4000 characters.
Re: ORACLE - SYS.DBMS_JOB causes error "ORA-01461 can bind a LONG value only for insert into a LONG colu [message #576429 is a reply to message #576379] Mon, 04 February 2013 21:27 Go to previous messageGo to next message
amitms
Messages: 3
Registered: February 2013
Location: kathmandu
Junior Member
One possible solution i tested myself is make a separate procedure.

-------------------

PROMPT CREATE OR REPLACE PROCEDURE sp_exec
CREATE OR REPLACE PROCEDURE sp_exec (vbquery1 VARCHAR2, vbquery2 VARCHAR2)
IS
BEGIN
DECLARE
RESULT clob;

BEGIN

EXECUTE IMMEDIATE vbquery1|| vbquery2 INTO RESULT ;
sp_viewquery(RESULT);
-- sp_viewquery(''''||REPLACE(vEXECString,'''','''''')||'''');
EXCEPTION WHEN OTHERS THEN
SP_VIEWQUERY(substr(SQLERRM, 1, 50));

END;
END;
/
---------then call from the begin end

DECLARE
vbquery VARCHAR2(4000):='SELECT TO_char(sysdate,''YYYY-MM'')';
vbquery1 VARCHAR2(4000):=' FROM DUAL';
vbquery2 CLOB;
vSQLString2 CLOB;
BEGIN
vbquery1:=TO_CLOB(vbquery1);
vbquery:=TO_CLOB(vbquery);
vbquery2:= vbquery||vbquery1 ;

SP_EXEC(vbquery,vbquery1);


END ;

-------------
anybody please test and post any enhancement on it
Re: ORACLE - SYS.DBMS_JOB causes error "ORA-01461 can bind a LONG value only for insert into a LONG colu [message #576430 is a reply to message #576429] Mon, 04 February 2013 22:29 Go to previous message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
why do you ignore previously offered advice?

http://www.orafaq.com/wiki/WHEN_OTHERS
Previous Topic: Oracle to SQL Server
Next Topic: please help me to implement this logic.
Goto Forum:
  


Current Time: Sat Oct 25 01:36:27 CDT 2014

Total time taken to generate the page: 0.13387 seconds