tag:blogger.com,1999:blog-25366171.post839570681843844070..comments2023-07-20T08:35:13.315-07:00Comments on Sanjeev' Oracle World: Sanjeev Saprehttp://www.blogger.com/profile/03452338244966094046noreply@blogger.comBlogger116125tag:blogger.com,1999:blog-25366171.post-49230233874054123902020-01-27T02:56:05.267-08:002020-01-27T02:56:05.267-08:00very nice and provide me informative content thank...very nice and provide me informative content thanks for sharing for more information about how to <a href="http://excelvbaautomation.com/create-new-workbook/" rel="nofollow">Create new Workbook</a>Krishna Optifhttps://www.blogger.com/profile/01239512147287638641noreply@blogger.comtag:blogger.com,1999:blog-25366171.post-28006142859546514292018-06-30T20:27:29.894-07:002018-06-30T20:27:29.894-07:00Hello,
I am trying to execute your procedure by Ap...Hello,<br />I am trying to execute your procedure by Applications EBS, but I do not know how to make it output in concurrent, it gives a structure error in XML.<br /><br />Att<br />VanderleiAnonymoushttps://www.blogger.com/profile/16975551680213050911noreply@blogger.comtag:blogger.com,1999:blog-25366171.post-62677405126467035022016-01-28T05:15:42.364-08:002016-01-28T05:15:42.364-08:00Hi,
i have one very similar code to generate the ...Hi,<br /><br />i have one very similar code to generate the excel file from pl/sql code.<br /><br />Though this was fully working in IE-8 browser but when i try to run the same code through IE-11 then suddenly it changes the behavior.<br /><br />It gives the error in exporting the excel file that 'This File Couldn't be downloaded' and when i RETRY to download the same then it downloads successfully.<br /><br />could you please derive me what is going wrong with it ?<br /><br />Thanks in advance,<br />BhuminAnonymoushttps://www.blogger.com/profile/12652313071263797422noreply@blogger.comtag:blogger.com,1999:blog-25366171.post-74711400711049925122015-02-17T12:44:30.307-08:002015-02-17T12:44:30.307-08:00I have recently enhanced this package to include (...I have recently enhanced this package to include (1) filter (2) pane (3) subtotal (4) comma and $ format. If anyone is interested please let me know.Anonymoushttps://www.blogger.com/profile/04375701316766244142noreply@blogger.comtag:blogger.com,1999:blog-25366171.post-75950535010770304652014-11-17T13:59:18.636-08:002014-11-17T13:59:18.636-08:00sorry about create_style procedure i can't put...sorry about create_style procedure i can't put the text here because it contains some HTML tags. If anyone have an idea how can i attach ite file gloirehttps://www.blogger.com/profile/06050856531674593199noreply@blogger.comtag:blogger.com,1999:blog-25366171.post-83279572167335339002014-11-17T13:45:33.152-08:002014-11-17T13:45:33.152-08:00PROCEDURE write_cell_date(p_row NUMBER, p_column N...PROCEDURE write_cell_date(p_row NUMBER, p_column NUMBER, p_worksheet_name IN VARCHAR2, p_value IN VARCHAR2, p_style IN VARCHAR2 DEFAULT NULL ) IS<br />l_ws_exist BOOLEAN ;<br />l_worksheet VARCHAR2(2000) ;<br />BEGIN<br /><br /> -- CHECK IF this cell has been used previously.<br /> IF cell_used( p_row , p_column , p_worksheet_name ) THEN<br /> RAISE_application_error( -20001 , 'The cell ( Row: '||p_row ||' Column:'||p_column ||' Worksheet:'||p_worksheet_name ||') is already used.Check if you have missed to increment row number in your code. ');<br /> END IF;<br /><br />-- IF worksheet NAME IS NOT passed THEN use first USER created sheet ELSE use DEFAULT sheet<br />-- this PROCEDURE just adds the data INTO the g_cells TABLE<br /> g_cell_count := g_cell_count + 1 ;<br /> g_cells( g_cell_count ).r := p_row ;<br /> g_cells( g_cell_count ).c := p_column ;<br /> g_cells( g_cell_count ).v := p_value ;<br /> g_cells( g_cell_count ).w := p_worksheet_name ;<br /> g_cells( g_cell_count ).s := p_style ;<br /> g_cells( g_cell_count ).dt := 'DateTime' ;<br /><br />END ;gloirehttps://www.blogger.com/profile/06050856531674593199noreply@blogger.comtag:blogger.com,1999:blog-25366171.post-83250529626377475012014-11-17T13:44:44.224-08:002014-11-17T13:44:44.224-08:00hi i used the package and i update it to support d...hi i used the package and i update it to support date form. i added write_cell_date function <br />and updated create_style function. <br />------------------------------------------------------------------------------<br />How to use :<br />Create new style : create_style(‘style name’, ‘Short Date’);<br />Create cell: write_cell_date(p_row, p_column, p_worksheet_name ,to_char(‘your date’, ‘yyyy-mm-dd’) , ‘style name’);gloirehttps://www.blogger.com/profile/06050856531674593199noreply@blogger.comtag:blogger.com,1999:blog-25366171.post-77045913130203574252014-09-22T13:24:00.947-07:002014-09-22T13:24:00.947-07:00Your code is cool and simple to apply. Great Thank...Your code is cool and simple to apply. Great Thanks. I used to create csv files but is trying to create excel this time because I want to put a company logo on the top of the report. However, I cannot figure out a way to put an image (either from a jpg file or from a BLOB column in a table) into your code to make it work. I think I can pre-create a excel with that logo header and open it for append in your code. But since our database server is not in the application server, this is not easy to do. Can you give some advise? Thank you.Anonymoushttps://www.blogger.com/profile/04375701316766244142noreply@blogger.comtag:blogger.com,1999:blog-25366171.post-20867400957589316132014-07-18T08:17:28.542-07:002014-07-18T08:17:28.542-07:00Love ur code.. many thnx!!Love ur code.. many thnx!!MChttps://www.blogger.com/profile/11884716222969601209noreply@blogger.comtag:blogger.com,1999:blog-25366171.post-1461765787926572272014-03-31T11:42:41.946-07:002014-03-31T11:42:41.946-07:00I am using this code in an application.
I am usin...I am using this code in an application.<br /><br />I am using this code in the page:-<br /><br />DECLARE <br />r NUMBER := 0 ;<br />BEGIN <br /> -- gen_xl_xml.create_excel('UTL_DIR', 'employees.xls') ;<br /> gen_xl_xml.create_worksheet( 'sheet1');<br /> gen_xl_xml.create_excel_apps ;<br /> gen_xl_xml.create_style( 'sgs1' , 'Courier', 'red',16, TRUE , p_backcolor => 'LightGray', P_underline => 'Single' );<br /> gen_xl_xml.create_style( 'sgs2' , 'Courier', 'blue',12,NULL );<br /> gen_xl_xml.create_style( 'sgs3' , 'Courier', 'green',14,TRUE );<br /><br /> -- increase width OF colum b that IS no 2<br /> gen_xl_xml.set_column_width( 1, 140, 'sheet1' );<br /> gen_xl_xml.set_column_width( 2, 145 , 'sheet1' );<br /> gen_xl_xml.set_column_width( 3, 145, 'sheet1' );<br /> gen_xl_xml.set_row_height( 1, 30 ,'sheet1' );<br /><br />-- writing the headers<br /> r := r+1 ;<br /> gen_xl_xml.write_cell_char( r,1, 'sheet1', 'Employee id' ,'sgs1' );<br /> gen_xl_xml.write_cell_char( r,2, 'sheet1', 'First Name' ,'sgs1' );<br /> gen_xl_xml.write_cell_char( r,3, 'sheet1', 'Last Name', 'sgs1' );<br />-- gen_xl_xml.write_cell_char( r,3, 'Last Name', 'sgs1' );<br />--<br /> FOR y IN (SELECT 1 OEM_CODE, OEM_NAME , OEM_USER_CREATED FROM OEMS where ROWNUM < 10 ) LOOP <br /> <br /> r := r+1 ;<br /> gen_xl_xml.write_cell_num( r,1, 'sheet1' , y.OEM_CODE, 'sgs3' );<br /> gen_xl_xml.write_cell_char( r,2, 'sheet1' , y.OEM_NAME, 'sgs2' );<br /> gen_xl_xml.write_cell_char( r,3, 'sheet1' , y.OEM_USER_CREATED, 'sgs2');<br /><br /> END LOOP ;<br /><br /> gen_xl_xml.close_file ;<br /><br />END ;<br /><br />But got this error :-<br />ORA-20008: No worksheets have been created, this version does not support automatic worksheet creation.<br /><br />Can you please tell me what code should i <br />use in the page?i want excel output of a sql query with a custom header row.<br /><br />Thanks<br />SubhojitAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-25366171.post-49584389332830906102014-03-07T14:07:03.422-08:002014-03-07T14:07:03.422-08:00Thank you for your code. I'am impatient to try...Thank you for your code. I'am impatient to try it out! <br /><br />wolfAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-25366171.post-75096509453524467392014-03-03T01:45:47.542-08:002014-03-03T01:45:47.542-08:00Great script Sanjeev.
I am trying to create excel...Great script Sanjeev.<br /><br />I am trying to create excel using 10000 records, its too slow.<br /><br />As suggested by you in one of others post,I have commented the cell_used function in write cell procedure. Still no luck. Any help on this?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-25366171.post-3352753830950521352014-03-02T22:56:33.404-08:002014-03-02T22:56:33.404-08:00Hi Sanjeev,
I went thru your blog and took the c...Hi Sanjeev,<br /><br /><br />I went thru your blog and took the code for "create excel by p/sql".<br /><br /><br />It runs fine but when I retreive "date" it gives me an error and worksheet is blank. I know you have mentioned that currently it doesn't support DATE. Any alternative you can suggest ?<br /><br /><br />I tried converting it to char but no luck..Please do revert asap as this is an urgent deliverable for me..Anonymoushttps://www.blogger.com/profile/01865341841555537797noreply@blogger.comtag:blogger.com,1999:blog-25366171.post-64381804145135266452014-02-26T23:25:55.936-08:002014-02-26T23:25:55.936-08:00how can we generate xlsx file instead of xls?Pleas...how can we generate xlsx file instead of xls?Please adviceAnonymoushttps://www.blogger.com/profile/13696773997343103166noreply@blogger.comtag:blogger.com,1999:blog-25366171.post-33461428871029691362014-02-22T08:42:03.703-08:002014-02-22T08:42:03.703-08:00Great job Sanjeev, are there any recent update to ...Great job Sanjeev, are there any recent update to your package, like cell alignement.<br />Again thank you a lot.<br />AmilkarVivenciashttps://www.blogger.com/profile/09599671526498479904noreply@blogger.comtag:blogger.com,1999:blog-25366171.post-60084368511558601102013-12-12T06:21:33.391-08:002013-12-12T06:21:33.391-08:00Dear Sanjeev,
I have a requirement to read a cell...Dear Sanjeev,<br /><br />I have a requirement to read a cell value from excel as IN variable and write the OUT variables to the adjacent cell for Eg: if am reading from A1 then I have to write in A2,A3 can this package be rewritten in such way. Can you please send it to my mail id: aravind.psg@gmail.comAnonymoushttps://www.blogger.com/profile/04681216850132400931noreply@blogger.comtag:blogger.com,1999:blog-25366171.post-30769954086620904192013-07-04T22:19:06.593-07:002013-07-04T22:19:06.593-07:00Hi Sanjeev,
I want to upload excel sheet through ...Hi Sanjeev,<br /><br />I want to upload excel sheet through my JDBC driver. Can you help me in identifying correct tool which hit my JDBC and insert records in the tablesbalahttps://www.blogger.com/profile/10772434052989648420noreply@blogger.comtag:blogger.com,1999:blog-25366171.post-7370786149403133942013-05-10T22:04:49.956-07:002013-05-10T22:04:49.956-07:00Hi sanjeev
I have developed and package to genera...Hi sanjeev <br />I have developed and package to generate excel the same package is working in one instance and on other instance the excel is not opening giving problems during load..<br />can you please help?Dipalihttps://www.blogger.com/profile/04734336538035916135noreply@blogger.comtag:blogger.com,1999:blog-25366171.post-80155601603680441142013-03-21T21:48:58.106-07:002013-03-21T21:48:58.106-07:00Hi Sanjeev,
Nice solution!
I have succesfully use...Hi Sanjeev,<br />Nice solution! <br />I have succesfully used formulas as well.<br /><br />Usmannoreply@blogger.comtag:blogger.com,1999:blog-25366171.post-80997296100500618742012-08-28T03:25:26.007-07:002012-08-28T03:25:26.007-07:00Hi Sanjeev,
Thanks for the great code.
I get an ...Hi Sanjeev,<br /><br />Thanks for the great code.<br /><br />I get an error on the following line: <br />l_font := ' <Font ';<br /><br />The error message is:<br />Error(174,5): PLS-00201: identifier 'L_FONT' must be declared<br /><br />Could you please assist with this.<br /><br />Many thanks<br />JackAnonymoushttps://www.blogger.com/profile/15018996993635277010noreply@blogger.comtag:blogger.com,1999:blog-25366171.post-46110841512144049722012-08-27T22:05:49.081-07:002012-08-27T22:05:49.081-07:00Hi Sanjeev,
I have created the file successfully....Hi Sanjeev,<br /><br />I have created the file successfully. However, I can't view the formatting when I open on iPad or Android device.<br /><br />Can you advise how to view the file on iPad/Android?<br /><br />Thanks<br />ChiragAnonymoushttps://www.blogger.com/profile/14062347348871735017noreply@blogger.comtag:blogger.com,1999:blog-25366171.post-62404500887941583542012-08-08T23:26:30.869-07:002012-08-08T23:26:30.869-07:00Hi Sanjeev,
We are facing a problem when the data...Hi Sanjeev,<br /><br />We are facing a problem when the data is converted to excel format using through PL/SQL. It gives us the below erorr. This is due to security feature in MS Office 2007 and above. Could you please suggest any workaround for this through PL/SQL. I do not want to change registry setting to overcome this issue. <br /><br />"The file you are trying to open is in a different format than specified by the file extension."<br /><br />Regards,<br />SunilAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-25366171.post-42145903393004836022012-06-18T03:35:29.738-07:002012-06-18T03:35:29.738-07:00can we edit existing excel file using this method?...can we edit existing excel file using this method?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-25366171.post-37324076334166781692012-01-27T18:01:19.072-08:002012-01-27T18:01:19.072-08:00Hi Sanjeev,
Is there any update for Date format an...Hi Sanjeev,<br />Is there any update for Date format and applying borders to ach cell?cnrajahttps://www.blogger.com/profile/03277468142304616288noreply@blogger.comtag:blogger.com,1999:blog-25366171.post-35034227505106144022012-01-05T20:54:53.454-08:002012-01-05T20:54:53.454-08:00When I open a excel file it is giving as error as ...When I open a excel file it is giving as error as below for more then 43 rows.<br /><br />XML PARSE ERROR: Missing end-tag<br /> Error occurs at or below this element stack:<br /> <br /> <br /> <br /> <br /> <br /> <br /><br />What can we do this?Praveenahttps://www.blogger.com/profile/17136816656679031144noreply@blogger.com