asked on

Sorting feched data (oracle)

Hello,
I have a question about selecting data from a table.
What i curently try to achieve is fetch data and send .pdf file directly from database. Problem is i dont know how to define lenght of each cell or column so i could sort data a bit.

At the moment i am getting data in format like this:
001 Some text 2312321.232 EUR
002 Some text longer 21231.22 eur
003 Some reallllyyyyyyyyyyyy long ext 3243243243243232423.212 eur

And i would like to have it like this:
001 Some text 2312321.232 EUR
002 Some text longer 21231.22 eur
003 Some reallllyyyyyyyyyyyy long ext 3243243243243232423.212 eur

Thank you for your help!

slightwv (䄆 Netminder)

I'm not sure I understand the question here.

If you are looking to align the columns, you can pad them with the RPAD function in Oracle's SQL.

If you are looking to sort them, why not add an 'ORDER BY' to the select and have them come from the database already sorted?

Caruso_eu

ASKER

Hmm it is hard to explain this for me with my poor English.

Let me try, I am running a procedure that is fetching data from database and sending it via email in PDF file format. This is done automatically within the database (no other 3rd party programs are used).

So after my query i get data in my PDF file in format like in attached fie EXAMPLE_1. But i want it sorted like in file EXAMPLE_2 with columns separated.
EXAMPLE-1.pdf
EXAMPLE-2.pdf

slightwv (䄆 Netminder)

This is a spacing issue not a sorting issue.

Unless the font you are using is fixed space, padding will not work for you.

You need to set up tab spaces or some other column settings in the PDF itself to get the text to line up.

How are you building the PDF?

Caruso_eu

ASKER

No no we are still not on the same page here. I am using AS_PDF3 package that generates PDF file automatically.

AS_PDF3 - PACKAGE

But the data I get in the pdf is like in my EXAMPLE_1 file.

I know i have to solve this out in my p/sql procedure i just dont know how to do that.

slightwv (䄆 Netminder)

>>I am using AS_PDF3 package that generates PDF file automatically.

I'm not familiar with that package so I don't know how you are calling it.

>>No no we are still not on the same page here

You need the data all lined up in the same columns, correct? In other words, no matter how 'loooonnnnggggg' the text is, all the EUR columns line up vertically.

If this is not correct, please clarify.

If this is correct, then however you call the AS_PDF3 package with the data, you need to tell it you have 4 columns in your data and what the offsets are.

I assume this is done with the parameters of as_pdf3.put_txt but I'm not sure. You will need to see if there is any documentation for as_pdf3 that tells you what the parameters are and how to do offsets.

Caruso_eu

ASKER

>>You need the data all lined up in the same columns, correct? In other words, no matter how 'loooonnnnggggg' the text is, all the EUR columns line up vertically.<<

Exactly!

>>If this is correct, then however you call the AS_PDF3 package with the data, you need to tell it you have 4 columns in your data and what the offsets are.<<

Alright. Yes i understand you can help me more since this package has absolutely ZERO documentation and is just trial and error until you get it working.

slightwv (䄆 Netminder)

Can you show me the code you currently have? Not the A3 package itself, the code you have that selects the data and calls the a3_pdf3 code.

slightwv (䄆 Netminder)

Personally, I would contact the author directly and ask them for the documentation or how to set the offsets: http://technology.amis.nl/contact/

Caruso_eu

ASKER

I will post the code tomorrow when i will be at work (i don't have access to my PC at the moment).

I think he doesn't have any documentation on the package. I have tried to read the code and decipher what each procedure does but so far no luck.

I have one "dumb" solution in my mind to just loop trough each column in cursor and manually put it together on the list ... but i would hate my self by doing that :).

slightwv (䄆 Netminder)

I did scan the packave and saw a reference to a procedure that takes a ref cursor and generates a PDF table. That will definitly line everything up for you.

Caruso_eu

ASKER

OK here is my code. I haven't tried with that procedure yet.

DECLARE
    v_pdf     BLOB;
    v_message CLOB := 'E-mail!';
 
    cursor c1 is 
    
          SELECT
              job_id,
			  manager_id,
			  department_id,
			  DEPARTMENT_NAME
		  from EMP_DETAILS_VIEW;
          
          
BEGIN
    as_pdf3.init;


    as_pdf3.set_page_orientation('PORTRAIT');
    
  
   as_pdf3.set_font( 'times', 'b' ,16);
   as_pdf3.WRITE('Title',180,700); 
   as_pdf3.horizontal_line( 180, 695, 305, 2);
   as_pdf3.WRITE('from: ' || to_char(SYSDATE,'dd.mm.yyyy') || ' to: ' || to_char(SYSDATE,'dd.mm.yyyy'),190,680);
   as_pdf3.horizontal_line( 180,675, 300, 2);
   
   as_pdf3.set_font( 'times', 'b' ,10); 
  
   
   as_pdf3.put_image( 'logo_dir', 'logo.jpg', 200, 700);  
   

    FOR c IN c1 loop
    BEGIN
    
      as_pdf3.set_font( 'times', 'N' ,10);  
      as_pdf3.WRITE(c.job_id ||'  ' || initcap(C.manager_id) || '  ' || c.department_id || '  ' || c.DEPARTMENT_NAME,-1,-1);
   
   
    END;
    END LOOP;
    

    v_pdf := as_pdf3.get_pdf;

    sdsemail.send_attach_blob(
        p_sender          => 'no-replay@domain.com',
        p_recipients      =>  'example@example@mail.com', --mail of recipient
        p_subject         => 'Subject' || sysdate,
        p_message         => v_message,
        p_attachment      => v_pdf,
        p_att_inline      => FALSE,
        p_att_mime_type   => 'application/pdf',
        p_att_filename    => 'report_' || to_char(SYSDATE,'DD_MM_YYYY') || '.pdf'
    );
    
END;

Open in new window

Caruso_eu

ASKER

Still no luck. I spotted one procedure in package called table2query.

type tp_col_widths is table of number;
type tp_headers is table of varchar2(32767);

  procedure query2table
    ( p_query varchar2
    , p_widths tp_col_widths := null
    , p_headers tp_headers := null
    );

Open in new window

It takes 3 parameters:

1 is query (varchar2)

2nd and 3rd are type.

Since i am new to oracle i have no idea how to use them or better pass them to procedure as parameters. Anything i set as parameter i get
"PLS-00306: wrong number or types of arguments in call to 'QUERY2TABLE'" error.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial