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!
Caruso_euAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Experiment with replacing this line:
as_pdf3.WRITE(c.job_id ||'  ' || initcap(C.manager_id) || '  ' || c.department_id || '  ' || c.DEPARTMENT_NAME,-1,-1);
   
and try some as_pdf3.put_txt calls.  It accepts some X and Y parameters.  I can only guess that this will allow you more exact text placement.
0
 
slightwv (䄆 Netminder) Commented:
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?
0
 
Caruso_euAuthor Commented:
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
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
slightwv (䄆 Netminder) Commented:
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?
0
 
Caruso_euAuthor Commented:
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.
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
Caruso_euAuthor Commented:
>>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.
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
slightwv (䄆 Netminder) Commented:
Personally, I would contact the author directly and ask them for the documentation or how to set the offsets:  http://technology.amis.nl/contact/
0
 
Caruso_euAuthor Commented:
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 :).
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
Caruso_euAuthor Commented:
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

0
 
Caruso_euAuthor Commented:
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.
0
All Courses

From novice to tech pro — start learning today.