CODEWITHSUNDEEP
blobattachmentpeoplesoftsqr
Jason Rupp
Jason Rupp

Reputation: 1

I need to write an sqr that uses data from a file that is attached to a Peoplesoft page

I have tried several things that I found online but not really sure what to do with them:

     SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(file_data,1000,1))
     FROM psfile_attdet
     WHERE ATTACHSYSFILENAME = $sysfilename

This returns data but I don't see how it would be in a file format. The sqr runs to success

    SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(UTL_COMPRESS.LZ_UNCOMPRESS(file_data),2000,1))
    FROM psfile_attdet
     WHERE ATTACHSYSFILENAME = $sysfilename

I get the following error:

          (SQR 5528) ORACLE OCIStmtExecute error 29294 in cursor 2:  

          ORA-29294: A data error occurred during compression or uncompression. 

          ORA-06512: at "SYS.UTL_SYS_COMPRESS", line 60 

          ORA-06512: at "SYS.UTL_SYS_COMPRESS", line 230 

          ORA-06512: at "SYS.UTL_COMPRESS", line 89

I looked the errors up online but did not really understand it as I am an application developer and not a DBA.`

Is anyone able to tell me how I can access an attached file in Peoplesoft and use the data in an SQR?`

 <Snippet>
 Begin-Procedure download-file

 Begin-Select
 !utl_raw.cast_to_varchar2(dbms_lob.substr(file_data,1000,1)) &file

UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(UTL_COMPRESS.LZ_UNCOMPRESS(file_data),2000,1)) &file

do get-file-invoices
let $PSfile = &file

FROM psfile_attdet 
 WHERE ATTACHSYSFILENAME = $sysfilename

  End-Select

 End-procedure !download-file\`

I don't feel like this is even close to correct but it is an attempt.

Upvotes: 0

Views: 136

Answers (1)

Guillermo-Santos
Guillermo-Santos

Reputation: 1

I think the easiest solution would be to create a job, it should first execute an application engine that gets the attachment and place it on some predetermined directory, then save the path on a table that you can access from your SQR (never touched SQR so really do not know how you do file processing there).

Upvotes: 0

Related Questions