OraFAQ Forum: SQL & PL/SQL » ORACLE - SYS.DBMS_JOB causes error "ORA-01461 can bind a LONG value only for insert into a LONG colu

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)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

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

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

Report message to a moderator

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

Michel Cadot
Messages: 54719
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

Report message to a moderator

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

flyboy
Messages: 1673
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)

Report message to a moderator

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

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

Report message to a moderator

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

flyboy
Messages: 1673
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.

Report message to a moderator

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

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

Report message to a moderator

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

BlackSwan
Messages: 20247
Registered: January 2009

Senior Member

why do you ignore previously offered advice?

http://www.orafaq.com/wiki/WHEN_OTHERS

Report message to a moderator

Previous Topic:	Oracle to SQL Server
Next Topic:	please help me to implement this logic.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 20 02:17:31 CDT 2013

Total time taken to generate the page: 0.12224 seconds