Meir Dinkels
asked on
Can't get an oracle procedure to work
I have a pl/sql procedure this is the code:
PROCEDURE get_docClob(OFFSET IN NUMBER, opcDoc OUT VARCHAR2)
IS
d VARCHAR2(5000);
BEGIN
SELECT DBMS_LOB.substr( doc, 4000, OFFSET) INTO d
FROM docClob
WHERE ROWNUM < 2;
opcDoc := d;
END ;
I've tested it pl/sql developer with offset = 1 and doesn't return any thing and doesn't raise any errors.
I tested it in sql using this code:
SELECT DBMS_LOB.substr( doc, 4000,1)
FROM docClob
WHERE ROWNUM = 1;
and it returns the first 4000 characters of the clob.
PROCEDURE get_docClob(OFFSET IN NUMBER, opcDoc OUT VARCHAR2)
IS
d VARCHAR2(5000);
BEGIN
SELECT DBMS_LOB.substr( doc, 4000, OFFSET) INTO d
FROM docClob
WHERE ROWNUM < 2;
opcDoc := d;
END ;
I've tested it pl/sql developer with offset = 1 and doesn't return any thing and doesn't raise any errors.
I tested it in sql using this code:
SELECT DBMS_LOB.substr( doc, 4000,1)
FROM docClob
WHERE ROWNUM = 1;
and it returns the first 4000 characters of the clob.
ASKER
I've tried that. It didn't work.
I've tried that. It didn't work.
meaning you get error/empty string?
This works for me:
/*
create table docclob(doc clob);
insert into docclob values('Hello');
commit;
*/
create or replace PROCEDURE get_docClob(OFFSET IN NUMBER, opcDoc OUT VARCHAR2)
IS
BEGIN
SELECT DBMS_LOB.substr( doc, 4000, offset) INTO opcDoc
FROM docClob
WHERE ROWNUM<2;
END ;
/
show errors
declare
result varchar2(4000);
begin
get_docclob(1,result);
dbms_output.put_line('Got: ' || result);
end;
/
ASKER
No rows were returned. No errors.
Are you running your original code or my modified code? I made a few changes since you were doing some things you didn't need to do.
If you are running my code then, if you aren't getting any rows returned then either the table doesn't have any rows or you aren't selecting form the table you think you are.
If you are running my code then, if you aren't getting any rows returned then either the table doesn't have any rows or you aren't selecting form the table you think you are.
How are you calling or running your procedure? The suggestion from slightwv works for me. And, as he suggested, you don't need to declare a local variable to fetch the value into, then copy that value to your "out" parameter. You can simply select into the "out" parameter.
Mark's post just made me think of another potential reason why it might not appear to be working.
If you insert data and do not commit it, you can query it from one session and see it fine. If you would run the stored procedure from a different session, the data would not be available because it was never committed.
If you insert data and do not commit it, you can query it from one session and see it fine. If you would run the stored procedure from a different session, the data would not be available because it was never committed.
try these
1.
2.
3.
and lests see what it returns in each case...
1.
PROCEDURE get_docClob(OFFSET IN NUMBER, opcDoc OUT VARCHAR2)
IS
BEGIN
SELECT 'HK-' || DBMS_LOB.substr( doc, 1000, OFFSET) INTO opcDoc
FROM docClob
WHERE ROWNUM = 1;
END;
2.
PROCEDURE get_docClob(OFFSET IN NUMBER, opcDoc OUT VARCHAR2)
IS
BEGIN
SELECT 'HK - ' || ROWID INTO opcDoc
FROM docClob
WHERE ROWNUM = 1;
END;
3.
PROCEDURE get_docClob(OFFSET IN NUMBER, opcDoc OUT VARCHAR2)
IS
BEGIN
SELECT DBMS_LOB.substr( doc, 1000, OFFSET) INTO opcDoc
FROM docClob
WHERE ROWNUM = 1;
EXCEPTION
WHEN OTHERS THEN
opcDoc := 'HK - Error';
END;
and lests see what it returns in each case...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
this is confusing...
it should just be
WHERE ROWNUM < 2;
it should just be
WHERE ROWNUM = 1;
I would not say that "WHERE ROWNUM < 2;" is confusing. That syntax is legal, and likely as efficient as: "WHERE ROWNUM = 1;". For performance reasons, I try to avoid using rownum to limit the number of rows returned. I prefer to use other conditions if possible. But this should work.
ASKER
You are right.
Thank you.
Thank you.
HainKurt, confusing ??
try this:
try this:
with x as (
select 'a' y from dual
union all select 'b' from dual
union all select 'c' from dual)
select * from x
where rownum = 2
order by y desc;
@geert
yes, why "rownum = 2" ? :)
HainKurt, confusing ??
yes, why "rownum = 2" ? :)
ASKER
It worked.
Open in new window
also try a small number like 1000, 2000 to see if 4000 is the issue