Link to home
Start Free TrialLog in
Avatar of Meir Dinkels
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.
Avatar of HainKurt
HainKurt
Flag of Canada image

try

PROCEDURE get_docClob(OFFSET IN NUMBER, opcDoc OUT VARCHAR2)
   IS
BEGIN
    SELECT DBMS_LOB.substr( doc, 4000, OFFSET) INTO opcDoc
    FROM docClob
    WHERE ROWNUM = 1;
END ; 

Open in new window


also try a small number like 1000, 2000 to see if 4000 is the issue
Avatar of Meir Dinkels
Meir Dinkels

ASKER

I've tried that. It didn't work.
I've tried that. It didn't work.

meaning you get error/empty string?
Avatar of slightwv (䄆 Netminder)
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;
/
	

Open in new window

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.
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.
try these

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;

Open in new window



2.
PROCEDURE get_docClob(OFFSET IN NUMBER, opcDoc OUT VARCHAR2)
   IS
BEGIN
    SELECT 'HK - ' || ROWID INTO opcDoc
    FROM docClob
    WHERE ROWNUM = 1;
END;

Open in new window



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;

Open in new window



and lests see what it returns in each case...
ASKER CERTIFIED SOLUTION
Avatar of Meir Dinkels
Meir Dinkels

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
this is confusing...

WHERE ROWNUM <  2;

Open in new window


it should just be

WHERE ROWNUM =  1;

Open in new window

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.
You are right.
Thank you.
HainKurt, confusing ??


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;  

Open in new window

@geert

HainKurt, confusing ??

yes, why "rownum = 2" ? :)
It worked.