OraFAQ Forum: SQL & PL/SQL » Reading BLOB field to a REF CURSOR (3 merged by CM)

Home » SQL & PL/SQL » SQL & PL/SQL » Reading BLOB field to a REF CURSOR (3 merged by CM)

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

Reading BLOB field to a REF CURSOR (3 merged by CM) [message #509395]

Fri, 27 May 2011 08:37

infinitenumbers
Messages: 33
Registered: November 2010

Member

Hello All,

I have a procedure which will be called my front end team get the reports stored in the database as BLOB. These reports are mostly PDF files.

I need to read this BLOB field as a part of OUT PARAMETER REF CURSOR which also contains other two columns.

Please help me with this.

Thanks in advance for the help! Really appreciate it.

Report message to a moderator

Re: Reading BLOB field to a REF CURSOR (3 merged by CM) [message #509400 is a reply to message #509395]

Fri, 27 May 2011 08:47

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Have you tried it?
Did you get an error?

Report message to a moderator

Re: Reading BLOB field to a REF CURSOR (3 merged by CM) [message #509404 is a reply to message #509400]

Fri, 27 May 2011 08:56

infinitenumbers
Messages: 33
Registered: November 2010

Member

Yes, I tried it. I got the following error.

'inconsitent datatypes expected-got BLOB'

Report message to a moderator

Re: Reading BLOB field to a REF CURSOR (3 merged by CM) [message #509410 is a reply to message #509404]

Fri, 27 May 2011 09:13

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Want to show us the code you used?

Report message to a moderator

Re: Reading BLOB field to a REF CURSOR (3 merged by CM) [message #509412 is a reply to message #509410]

Fri, 27 May 2011 09:18

infinitenumbers
Messages: 33
Registered: November 2010

Member

Sure. Here is the code. the field REPORT is the BLOB type.

PROCEDURE get_svr_template
(
rset IN OUT ref_cursor,
p_get_all_yn IN VARCHAR2,
p_err_nbr OUT NUMBER,
p_err_msg OUT LOAD_ACTIVITY.message%TYPE
) AS

v_login_user VARCHAR2(30) := SYS_CONTEXT('userenv','SESSION_USER');
v_count NUMBER;

BEGIN

IF UPPER(p_get_all_yn) = 'Y'
THEN

OPEN rset FOR
SELECT study_id,
visit_id,
report
FROM svr_templates
WHERE study_id IS NULL

UNION

SELECT study_id,
visit_id,
report
FROM svr_templates st
WHERE st.study_id IS NOT NULL
AND st.study_id IN (SELECT DISTINCT ur.study_id
FROM user_roles your
WHERE ur.study_id = st.study_id
AND ur.user_id = v_login_user);

DELETE
FROM svr_templates_replicated
WHERE user_id = v_login_user;

ELSIF UPPER(p_get_all_yn) = 'N'
THEN

OPEN rset FOR
SELECT st.study_id,
st.visit_id,
st.report
FROM svr_templates st,
svr_templates_replicated str
WHERE st.study_id IS NULL
AND str.study_id IS NULL
AND st.visit_id = str.visit_id
AND st.dt_modified <> str.report_template_date
UNION
SELECT st.study_id,
st.visit_id,
st.report
FROM svr_templates st,
svr_templates_replicated str
WHERE st.study_id IS NOT NULL
AND str.study_id IS NOT NULL
AND st.study_id = str.study_id
AND st.visit_id = str.visit_id
AND st.dt_modified <> str.report_template_date
AND st.study_id IN (SELECT DISTINCT ur.study_id
FROM user_roles your
WHERE ur.study_id = st.study_id
AND ur.user_id = v_login_user);

DELETE
FROM svr_templates_replicated
WHERE user_id = v_login_user
AND report_template_date > replicated_date;

COMMIT;

END IF;

EXCEPTION
WHEN OTHERS
THEN
p_err_nbr := SQLCODE;
p_err_msg := SUBSTR(SQLERRM,1,250);
END get_svr_template ;

Report message to a moderator

Re: Reading BLOB field to a REF CURSOR (3 merged by CM) [message #509415 is a reply to message #509412]

Fri, 27 May 2011 09:24

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

And the code that calls the procedure?

Report message to a moderator

Re: Reading BLOB field to a REF CURSOR (3 merged by CM) [message #509416 is a reply to message #509415]

Fri, 27 May 2011 09:25

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

And in future can you use [code] tags for your code as demonstrated here

Report message to a moderator

Re: Reading BLOB field to a REF CURSOR (3 merged by CM) [message #509419 is a reply to message #509416]

Fri, 27 May 2011 09:36

infinitenumbers
Messages: 33
Registered: November 2010

Member

The code that calls the procedure is below. It successfully ran. but Now I am wondering how do I really print this REF CURSOR 'rset' so that I can unit test my code if it getting the correct REPORT and in correct FORMAT.

declare
TYPE ref_cursor is ref cursor;
rset ref_cursor;
 p_err_nbr    NUMBER;
p_err_msg    LOAD_ACTIVITY.message%TYPE;
TYPE t_rec is RECORD
(
study_id studies.study_id%TYPE,
visit_id site_visit_types.visit_id%TYPE,
report svr_templates.report%TYPE
);
TYPE t_tab IS TABLE OF t_rec;
t_output t_tab;
begin
pkg_cm_replication.get_svr_template(rset, 'Y', p_err_nbr, p_err_msg);
end;

Report message to a moderator

Re: Reading BLOB field to a REF CURSOR (3 merged by CM) [message #509422 is a reply to message #509419]

Fri, 27 May 2011 09:39

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

By using a front end that can actually handle blobs.
SQLplus for example can't:

SQL> create table bob (a number, b blob);

Table created.

SQL> 
SQL> create function bob_func return sys_refcursor as
  2  
  3  l_ref sys_refcursor;
  4  
  5  begin
  6  
  7  open l_ref for select * from bob;
  8  
  9  return l_ref;
 10  
 11  end;
 12  /

Function created.

SQL> 
SQL> insert into bob (a) values (1);

1 row created.

SQL> 
SQL> var x refcursor;
SQL> 
SQL> exec :x := bob_func;

PL/SQL procedure successfully completed.

SQL> print :x
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
SP2-0625: Error printing variable "x"
SQL>

Presumably you have a UI somewhere that can actually handle blobs?

Report message to a moderator