APEX

Hi,

You can try using a "Named Column (row template)" report template as this allows you to layout the report as you like. I've described how to do that here: 9263057 (the layout is a simple one in that example, but it should give the idea about what to do!)

Andy

Hi Andy,

Thanks for the reply.

I will check that out.

Arif Khadas.

OK, Arif - update this thread if you have any questions

Andy

Hi Andy,

I created the template but I am not able to find the newly created Template to change the Report Template setting from Report Attributes page.

Template Property is set from Region Definition > User Interface > Template Property. Is that right?

I rechecked from Shared Components > Templates under Type Report the new template does exists there.

Am I missing something?

Thanks.

Arif Khadas

Edited by: Arif Khadas on Jan 12, 2011 9:13 AM

Hi,

As long as you followed the steps in that linked thread, the template should be there. If you filter the templates list to show only Report templates and check for any that show 0 References that should show you the ones that are not currently attached to a region. If you have multiple themes in your application, make sure that you viewing all themes.

Andy

I created one more template and this time I selected Region Template Type.

Now I can find this to be set for Template Property from Region Definition > User Interface > Template Property.

So this means that I was setting the Report Template Property for the wrong property.

From where exactly can I set Report Template Property.

Hi,

Region and Report are two different types of templates. The Region one determines the borders/title/buttons etc for the region regardless of what the region actually contains and the Report one determines the contents of a report and any pagination.

You have to use a Report template to set the layout you want.

Andy

Yes Andy I should set the Report Template But I am not able to find out from Report Attributes Tab, from where can I set the Report Template.

OK

Have you created a Report template?

If so, go to your page and click the "Report" link for your report region. This takes you to the Report Attributes settings. In the "Layout and Pagination" section on that page, there is a "Report Template" dropdown list. Find you new Report template on the list and select it. Click Apply Changes to save that

Andy

Thanks for your quick replies Andy.

Just to let you know once again I am using Application Express 4.0.1.00.03

From Page Rendering Tree Section I am Right clicking on Report > Edit Report Attributes.

I cannot find any section as Layout and Pagination.

There is a section called Pagination but without Report Template.

Additionally I have checked other sections tabs which are :

Column Attributes - Groups - Pagination - Sorting - Search Bar - Download - Link Column - Icon View - Detail View - Advance - Description

I have checked but cannot find Report Template.

OK - it sounds like you are using an Interactive Report? If that is the case, you must say so in your posts as these are different from "classic" reports. In Interactive Reports, you can not apply your own templates.

Andy

Sorry for that, I am too new to the New Version, also I have used older version rarely.

Interactive Report is the default type while creating a new Report.

Thanks a lot. Hope my problem will be solved now.

But I noticed something too good in the Interactive Report and that was Detail View that's almost what I want, but If I will want the detail to appear as required like on one line 3 Columns, on second 5 on third just one etc, is that possible.

Also I would like to know how can we display Image from database.

OK - use a "Classic" report which will allow you to create your own layout. Interactive Reports are useful because of all the functionalities you get in the tooblar at the top. But if you only want one record, then those don't really mean much!

The only way you could get an Interactive Report to display one record in anything other than a simple row would be to adjust your SQL to output more than one value in a cell. Something like:

SELECT EMPNO || '<br>' || ENAME as "No/Name",
...
FROM EMP

and change the column "Display Text As" setting to "Standard Report Column".

For images, see: http://apex.oracle.com/pls/otn/f?p=267:11 That's an example page, with instructions, on how to do that.

Andy

Thanks Andy for all your help.

The template is working fine and I can see as many rows as defined in the template.

Also I have found out how to display different columns on each row using the row and column HTML attributes.

Few more clarifications:

1. Is it necessary to add as many columns as in the Report to be displayed in the Template in the Row Template 1.

2. What about the Column headings. How to display column headings?

3. What are Row Template 2, Row Template 3 etc?

Edited by: Arif Khadas on Jan 12, 2011 10:24 AM

Hi,

You don't have to use all the report's columns in the template but you can not use ones that are not in the report! So, your template could show 323462168153128267801991895183035902825, 11 Jan 2011 08:30 pm and Jan 12 2011 as long as there are at least 6 columns in the report - items 2, 3, and 4 don't have to be used - but if you used ATD and there weren't 7 columns in your report, you would see ATD in the output.

Column headings can be defined in the "Before Rows" setting on the template. These, though, have to be entered as text. For example:

<table>
<tr>
<th>Heading 1</th>
<th>Heading 2</th>
</tr>

That would create two headings "Heading 1" and "Heading 2". You will have to ensure that you have one heading for each column

Andy

So we will need to create one Template each for each Report. Is it any more a Shared Component?
So this means we need to have Headings for each column, fine.

Image Related:

In the example Image of data type BLOB is used where as I am having a column of LONG RAW data type for Image.

So I am receiving "Query cannot be parsed, please check the syntax of your query. (ORA-00932: inconsistent datatypes: expected LONG BINARY got NUMBER)"
ERROR.

I cannot change the data type for the column so I need a way out to display LONG RAW images.

Thanks Once again.

Hi,

Yes, it would mean one template for each report (if they are different).

You can convert LONG to BLOB using TO_BLOB(). Have a look at: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:13213885403654

Andy

Generally named column report templates will be specific to one report, but notionally they are reusable for any report with the same number of columns referenced in the same order.
Column headings can be referenced in the Before Rows definition using positional syntax: 323462168153072657214289622240999418729, , . For example:

<tr><th>323462168153072657214289622240999418729</th><th> </th><th> </th><tr>

which provides for some flexibility in the reuse of the template for similar reports.

Conversion using TO_BLOB is not of much help for me, its like we can use TO_BLOB in an INSERT clause but not in a direct SELECT clause to fetch data.

So I will need to maintain one more table that I will not like to.

No way to fetch and display LONG RAW data in APEX.

Hi,

You could try using a collection as this has BLOB columns: http://download.oracle.com/docs/cd/B32472_01/doc/appdev.300/b32471/advnc.htm#CIHHCGGD

You could use CREATE_COLLECTION_FROM_QUERY to output the data into the collection and then use the collection to display the results. I've not tried it myself as I don't have a LONG RAW table. But it may save you having to create a separate table just to handle the output.

Andy

Thanks Paul, I'd not seen that.

I was going to suggest substitutions - &HEADING1., &HEADING2., etc

Andy

@Andy
>
You could try using a collection as this has BLOB columns: http://download.oracle.com/docs/cd/B32472_01/doc/appdev.300/b32471/advnc.htm#CIHHCGGD

You could use CREATE_COLLECTION_FROM_QUERY to output the data into the collection and then use the collection to display the results. I've not tried it myself as I don't have a LONG RAW table. But it may save you having to create a separate table just to handle the output.
>
Creative solution ;-), but unless there data set is very small there's going to be a major performance hit in pushing all that LOB info around...

@Arif
You really should change the column. LONGs are 15 years out of date. Doing almost anything else is likely to be a clunky, underforming workaround.

Well, yes, but it's A solution ;) As long as the collection is created in page chunks (say 15 records at a time max) then it should be ok - though that would have to be controlled using PL/SQL probably

But, I agree that the field type itself should be changed if possible

Andy

Hi Andy and Paul,

Thanks a lot for the instant, informative and useful comments.

First and foremost My objective was to obtain a Form Like Report which I feel at times is almost done and the again I face one or the other problem.

Now I can obtain column headings using <tr><th>323462168153280592455263338459048881001</th><th> </th><th> </th><tr> in Before Row.

But I just noticed that this will just be displayed at the top before all the rows.
This wont help me much as my objective will not be fulfilled by this as I want to create a page full of information and it will be just one record, and use the page completely to display loads of data in a way as a Master form can.

I even tried this before and I think instead of creating a report and template will it not be a better idea to create a Form displaying all this data.
I do not want to allow creating, updating or deleting records which I think should be possible, but how to fetch the data was my problem.
The page is just going to be informative as the System is developed using Oracle Forms.

The table related to Images is used in that system, so I will not like to alter it which will open doors for maintenance and changes in forms which even my Manager won't agree.

I will check out the link related to COLLECTION.

Thanks a lot.

Best Regards

Arif Khadas

Hi,

You don't have to use those headers if it not what is needed for your page.

For the headings - or do you mean "Labels" (like those that appear on forms?), you can enter these into the "Row Template 1" setting wherever you need them.

For example:

<tr>
<td>Heading 1</td><td>323462168152076502338927167801041529705</td>
</tr>

Which would add "Heading 1" next to the data for column 1 (323462168152076502338927167801041529705)

But a read-only form may be easier to create!

Andy

Please let me know how to fetch and display the data as soon as the page is executed in a read-only form and how to based the WHERE clause of the query on a page Item.

Hi,

The best, and easiest, way to create a form page is to use the Create page wizard as this will create most of the functionality you need.

Once you have a page created, you need to provide a means of setting the hidden primary key page item (for example, if this is page 2 and the form is created for the EMP table, you will have a hidden page item called P2_EMPNO). Typically, this is set by a link from a report - there are "Column Link" settings on a report column's Column Attributes page that allows you to set the value of P2_EMPNO with #EMPNO# (use the Name and Value settings to do this - the torch/flashlight icons next to these allow you to pick the names and values). Once that is done, clicking on the link automatically sets the P2_EMPNO item and the form page's "Fetch Row..." process will retrieve the values from the table for the selected record.

You just then need to go through the form's page items that are not "Hidden" and either set the "Read Only" option to "Always" or change the item's Display As type to "Display Only"

Andy

Thanks once again for quick reply.
I need the functionality as :

This will be the first page so I cannot create a link on other page as described above, so is there any way to assign the value from the Login Page. So as soon as User logs in, the P2_EMPNO will be set and the data fetched and displayed.
My objective is like as soon as Employee logs in only his details should be displayed.

Also I will have the value stored in Page 0 Item (P0_EMPNO) so that I can use this in all the pages.

Hi,

Does the table contain the user's login ID?

if so, you can create a Page Item Computation on the page that runs "Before Header" and is conditional on the P2_EMPNO (or whatever it is) item being NULL. The computation would set the value of P2_EMPNO using a SQL query that does something like:

SELECT v('APP_USER') FROM DUAL

If the table doesn't contain the ID but contains, for example, a number for that employee, you can retrieve that number using the above method but with a SQL statement of something like:

SELECT USER_ID
FROM EMP
WHERE LOGIN_ID = v('APP_USER')

Once the computation has run, the "Fetch Row..." process will use that value to retrieve the rest of the data

Andy

Thanks Andy for the constant help and support. I appreciate it a lot.

I am not yet sure about the login process, it has to be decided at a later stage along with my manager and Client.

Actually we have Student Management System.

Now we are trying to develop Web Pages through APEX from which user can access and only view the complete Student Profile with the numerous Exam and Payment details.

So each student will be provided with a log in credentials and hence only his information will be visible.

I have temporarily created Zero Page Item, I am passing the Student No from this Item.

So "SELECT v('P0_SSTD_NUM') FROM DUAL" worked for me.

Cheers!

Hi,

That's fine - there are several ways to get to the student's own data - my post gave two typical methods. When you're ready with the table structures, logins and so on, you should be able to update your page to get the right information

Good luck with your app!

Andy

Hi Andy,

Good Morning to You.

I am having a problem related to collection.

I am trying to create a collection as

BEGIN
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
    p_collection_name => 'SM_STUDENT_IMAGE_COLL',
    p_query           => 'SELECT    SIMG_NUM, SIMG_START_DATE, SIMG_END_DATE, SIMG_IMAGE, SIMG_SSTD_NUM
                          FROM      SM_PHOTOS_VEW',
    p_generate_md5    => 'NO');
END;

I am getting the following error:

ORA-20104: create_collection_from_query Error:ORA-20001: Invalid parsing schema for current workspace ID
ORA-06512: at "APEX_040000.WWV_FLOW_COLLECTION", line 914
ORA-06512: at line 2

Thanks & Regards

Arif Khadas

Hi,

Where are you running that statement? It sounds like you are not within an Apex application?

Also, ensure that the SELECT statement is on one line

Andy

I was running that statement as a procedure in the Oracle Database Schema from TOAD.

I am only an entry level APEX Programmer so please have patience with me and if I will know the details steps then better for me.

Now I executed the same with SQL query on one line from APEX > SQL Workshop > SQL Commands and I got the following error.

ORA-20104: create_collection_from_query Error:ORA-20104: create_collection_from_query ExecErr:ORA-00932: inconsistent datatypes: expected NUMBER got LONG BINARY

Edited by: Arif Khadas on Jan 13, 2011 10:06 AM

OK

Is SIMG_IMAGE a BLOB?

Andy

Arif
I've reviewed the discussion from yesterday, the Ask Tom thread, and the SQL docs and spotted that we missed something. The restriction referred to relates to <tt>TO_LOB</tt>, not <tt>TO_BLOB</tt>, which can be used in PL/SQL:

SQL*Plus: Release 9.2.0.2.0 - Production on Thu Jan 13 08:08:18 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning option

SQL> set serveroutput on
SQL> create table lrt (
  2    n integer,
  3    img long raw);

Table created.

SQL> insert into lrt
  2  select rownum n, hextoraw(lpad(rownum, 100, '0')) from dual;

1 row created.

SQL> declare
  2    r long raw;
  3    b blob;
  4  begin
  5    select img into r from lrt where n = 1;
  6    b := to_blob(r);
  7    dbms_output.put_line('BLOB size: ' || dbms_lob.getlength(b));
  8  end;
  9  .
SQL> /
BLOB size: 50

PL/SQL procedure successfully completed.

Still can't be used directly in a query, but it means you can handle this in PL/SQL.

Sorry for being late.
Its a LONG RAW column.

OK

Firstly, you would need to convert it to a BLOB and secondly, a collection does have a blob column but it needs to be specifically targeted. Unfortunately, it's column 57 (I think - check: http://download.oracle.com/docs/cd/E17556_01/doc/apirefs.40/e15519/apex_collection.htm#insertedID1 ), so your SQL would either need to use NULL values for the fields to be skipped, or you would have to remove the item from the query and the update each collection member individually to set the blob contents using PL/SQL. Otherwise, perhaps, you could ignore the blob in the query and perform a join to the actual table for the report. But either way, it has to be a BLOB

Andy

Please correct me If I am wrong: This will be by TO_BLOB (SIMG_IMAGE)
I checked it from TOAD and I found it to be 54th column with the first two being COLLECTION_NAME and SEQ_ID.
I tried th follwoing but same error ORA-00932: inconsistent datatypes: expected BINARY got LONG BINARY

BEGIN
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
    p_collection_name => 'SM_STUDENT_IMAGE_COLL',
    p_query           => 'SELECT SIMG_NUM, SIMG_START_DATE, SIMG_END_DATE, SIMG_SSTD_NUM, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, TO_BLOB (SIMG_IMAGE) FROM SM_PHOTOS_VEW',
    p_generate_md5    => 'NO');
END;

Too many problems.

Seems like Changing the data type is the best option. Hopefully I will be permitted for it.

Cheers. I have changed the data type and it did not create any problems with my old Forms. I should had tried this before.

Thanks alot ANDY.

Best Regards

Arif Khadas
Arif

Edited by: Arif Khadas on Jan 13, 2011 12:15 PM

Ummm..

There are 50 VARCHAR2 fields, 5 NUMBER fields, 1 CLOB field and then 1 BLOB field - I make that 57??

Andy

Andy Please help me once again.

In the procedure DISPLAY_IMAGE, you have

 
SELECT MIME_TYPE, BLOB_CONTENT, FILE_NAME, DBMS_LOB.GETLENGTH(BLOB_CONTENT)
    INTO vMIME, vBLOB, vFILENAME, vLENGTH
  FROM A_IMAGES

Now I do not have MIME_TYPE and FILE_NAME columns in my table.
So what should I do? What can I have default value as MIME_TYPE.
Whereas FILE_NAME is not being used, its just being fetched. Right?

I don't think there is a default image mime-type.

You can try one of:

image/gif
image/jpeg
image/png
image/tiff

there are others, but those are the common ones.

File name is not used for the displaying of images - I have included it in my report for info only.

If you don't have the file name and don't know the file type (and, therefore, the mime type), you may have problems displaying the images

If the images have been uploaded through Apex, a record may still exist on APEX_APPLICATION_FILES??

Andy

The System Application has been developed by using Forms 6i and that is what is being used for data entry.
As for now we are using APEX just as a Reporting tool to display data to Students through a Web Page.

I have created the procedure as well as the Report region.

I am getting an output like the following on the page for IMAGE:

<img src="DEV.DISPLAY_SM_PHOTOS_VEW_IMAGE?P_SIMG_NUM=370" height="50" width="50" />

Edited by: Arif Khadas on Jan 13, 2011 1:25 PM

Change the column Display As setting to "Standard Report Column" and try again

Andy

I did change the Report column type, now I cant see anything, seems its because of what you had mentioned earlier.

Hi,

If you know what type of images at least some of them are, you could hardcode the mime-type in the SELECT statement - then, at least, some of the images should show. But if they are different image types, you do need to know either the filename extension or the mime-type.

Andy

I tried adding one file of JPEG type file name Web - GalleryPlayer.jpg and i have used MIME_TYPE as 'image/jpeg' in select clause, even this image is not visible.

There are some jpeg files that are slightly different - try: image/pjpeg

Andy

Thanks Andy for your constant help, but seems the Images does not want to appear. :)

Even after changing its not appearing, amusing thing is that I have around 500 Images even then not even a single is visible. I wonder why? Is there any other problem.

My Procedure :

CREATE OR REPLACE PROCEDURE DISPLAY_SM_PHOTOS_VEW_IMAGE (P_SIMG_NUM NUMBER) IS
  
  V_MIME     VARCHAR2(48);
  V_LENGTH   NUMBER;
  V_FILENAME VARCHAR2(2000);
  V_BLOB     BLOB;
  
BEGIN
  
  SELECT    'image/pjpeg',   SIMG_IMAGE, 'FILE_NAME',  DBMS_LOB.GETLENGTH (SIMG_IMAGE)
  INTO      V_MIME,         V_BLOB,     V_FILENAME,   V_LENGTH
  FROM      SM_PHOTOS_VEW
  WHERE     SIMG_NUM = P_SIMG_NUM;
  
  OWA_UTIL.MIME_HEADER (NVL (V_MIME, 'application/octet'), FALSE);
  
  HTP.P ('Content-length: ' || V_LENGTH);
  OWA_UTIL.HTTP_HEADER_CLOSE;
  WPG_DOCLOAD.DOWNLOAD_FILE (V_BLOB);
  
END;

Region Source

SELECT SIMG_NUM,
'<img src="#OWNER#.DISPLAY_SM_PHOTOS_VEW_IMAGE?P_SIMG_NUM=' || NVL(SIMG_NUM, 0) || '" height="50" width="50">' IMAGE
FROM SM_PHOTOS_VEW