• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

something to deal with Long Data type

Hi,
   I have a table where we have a column with Long data type and we usually store the ules which are SQL statements in that column. say for eg: the column would hold data like this...

insert into tab1(col1,col2)
select 100,colx from tab2
where coly is not null
/

In my procedure I have to execute these rules and I get an error saying that my SQL syntax is not correct.
I have trid running the same SQL in SQL plus and it works. I think it has got something to do with the Long data type becuase, I created an other table with varchar2(2000) instead of long and it worked fine.

procedure test_commit is
v_sql validate_rules.validate_code%type;
begin
select validate_code into v_sql from validate_rules where validate_id = 1001;
dbms_output.put_line(v_SQL);
execute immediate v_SQL;
end;

ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "test_SCHEMA.TEST_COMMIT", line 7
ORA-06512: at line 1


Any ideas on the why long cannot be used here???

ukorep
0
ukorep
Asked:
ukorep
  • 7
  • 4
1 Solution
 
andrewstCommented:
I don't think there is any problem with using LONG, provided that the SQL statements do not exceed 32767 characters in length - that is the limit for a PL/SQL long variable.

What does the PUT_LINE show?
0
 
ukorepAuthor Commented:
This is the outcome when I run my procedure

INSERT INTO ERROR_LOG (LINE_NUMBER, BATCH_ID, ERROR_NUM
SELECT LINE_NUMBER, BATCH_ID,
'1001'
FROM TEMP_WATER_LVL
WHERE WELL_NUM NOT IN (SELECT WELL_NUM
FROM WELL)

/
BEGIN test_commit; END;

*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "WQD_SCHEMA.TEST_COMMIT", line 7
ORA-06512: at line 1
0
 
ukorepAuthor Commented:
This is the outcome when I run my procedure

INSERT INTO ERROR_LOG (LINE_NUMBER, BATCH_ID, ERROR_NUM
SELECT LINE_NUMBER, BATCH_ID,
'1001'
FROM TEMP_WATER_LVL
WHERE WELL_NUM NOT IN (SELECT WELL_NUM
FROM WELL)

/
BEGIN test_commit; END;

*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "WQD_SCHEMA.TEST_COMMIT", line 7
ORA-06512: at line 1
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
ukorepAuthor Commented:
This is the outcome when I run my procedure

INSERT INTO ERROR_LOG (LINE_NUMBER, BATCH_ID, ERROR_NUM
SELECT LINE_NUMBER, BATCH_ID,
'1001'
FROM TEMP_WATER_LVL
WHERE WELL_NUM NOT IN (SELECT WELL_NUM
FROM WELL)

/
BEGIN test_commit; END;

*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "WQD_SCHEMA.TEST_COMMIT", line 7
ORA-06512: at line 1
0
 
andrewstCommented:
Your SQL is missing a bracket after ERROR_NUM:

INSERT INTO ERROR_LOG (LINE_NUMBER, BATCH_ID, ERROR_NUM)
SELECT ...

0
 
ukorepAuthor Commented:
Oh.. I am sorry after I submitted, I refreshed  the page and it started displaying that many number of times. I didn't mean to post my outcome three times.
0
 
ukorepAuthor Commented:
sorry that is my fault that I didn;t copy from sql plus properly. Here you go

SQL> exec test_commit;
INSERT INTO ERROR_LOG (LINE_NUMBER, BATCH_ID, ERROR_NUMBER)
SELECT LINE_NUMBER, BATCH_ID,
'1001'
FROM TEMP_WATER_LEVEL
WHERE DEQ_WELL_NUM NOT IN (SELECT DEQ_WELL_NUM
FROM WELL)

/
BEGIN test_commit; END;

*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "WQD_SCHEMA.TEST_COMMIT", line 7
ORA-06512: at line 1
0
 
andrewstCommented:
Is that blank line and / after the INSERT statement actually stored in the validate_code column?  It should not be.

What is the output of:

select validate_code from validate_rules where validate_id = 1001;

?
0
 
ukorepAuthor Commented:
Even I thought that would be the issue and changed it. Hereis the outcome..
SQL> exec test_commit;
INSERT INTO ERROR_LOG (LINE_NUMBER, BATCH_ID, ERROR_NUMBER)
SELECT LINE_NUMBER, BATCH_ID, 1001 FROM
TEMP_WATER_LEVEL
WHERE DEQ_WELL_NUM NOT IN (SELECT DEQ_WELL_NUM FROM WELL)
/
BEGIN test_commit; END;

*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "WQD_SCHEMA.TEST_COMMIT", line 7
ORA-06512: at line 1


Also, I ran the SQL that you would like to see the outcome for...and here is the output.

VALIDATE_CODE
--------------------------------------------------------------------------------
INSERT INTO ERROR_LOG (LINE_NUMBER, BATCH_ID, ERROR_NUMBER)
SELECT LINE_NUMBER, BATCH_ID, 1001 FROM TEMP_WATER_LEVEL
WHERE DEQ_WELL_NUM NOT IN (SELECT DEQ_WELL_NUM FROM WELL)
/
0
 
andrewstCommented:
Remove that trailing / from the validate_code value, and it should work.
0
 
ukorepAuthor Commented:
hey, You are right!!! Thank you so much. I did so much research and did bother to remove that little "/" from there.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now