12 Replies Latest reply: Mar 15, 2012 4:13 AM by user11876003 RSS

    PL SQL Help

    user11876003
      I have to get details from my db to csv

      I want take data to first cursor and it has a USER CODE

      then I want to pass that to other query and want to get some details it has more than one row

      so how can i put loop for that with in main loop


      Please Help
        • 1. Re: PL SQL Help
          Marwim
          Hello,

          this is the forum {forum:id=260}. Please use {forum:id=75} instead.

          Regards
          Marcus
          • 2. Re: PL SQL Help
            806917
            Actually, he wants csv output, so this is a legitimate SQL Developer question.

            My recommendation, which you can do entirely in SQL Developer;
            1) Create a PL/SQL block that inserts rows into a table.
            2) Run a select statement against the data stored in the table.
            3) Use the extract functionality to dump your output to a csv file.

            Scot
            • 3. Re: PL SQL Help
              Marwim
              Hello Scot,
              Actually, he wants csv output, so this is a legitimate SQL Developer question.
              I beg to differ. If you use the export functionality of SQL Developer then it would be correct. But the OP wants to
              so how can i put loop for that with in main loop
              and as the thread title says he wants to do it in PL/SQL.

              It makes no sense to extract data in a loop and insert them in a table just to use SQL Developer's export functionality.

              Regards
              Marcus

              BTW: there are lots of examples how to create CSV from PL/SQL, e.g. {message:id=10121411}
              • 4. Re: PL SQL Help
                806917
                I told him how to do this w SQL Developer in the SQL Developer forum. Now he and anyone else who has the same question in a SQL Developer context has the benefit of my 17 years of Oracle experience.

                If he wants to do this using another technology, then he can repost elsewhere.
                • 5. Re: PL SQL Help
                  Marwim
                  Hello Scot,

                  I can't match the advice
                  1) Create a PL/SQL block that inserts rows into a table.
                  2) Run a select statement against the data stored in the table.
                  3) Use the extract functionality to dump your output to a csv file.
                  with this
                  my 17 years of Oracle experience
                  After working 17 years with Oracle you should know that it is usually a bad idea to copy data into a table just to select them afterwards for spooling. You might do so in SQL Server where locks are expensive and the reading might block others.

                  Regards
                  Marcus
                  • 6. Re: PL SQL Help
                    806917
                    I thought the purpose of the forums is to help people. Thus, I am helping.

                    You should consider reading this post - a great example of expertise, helping AND most importantly good attitude. Re: xhost  unable to open display
                    • 7. Re: PL SQL Help
                      Jim Smith
                      But giving bad advice is worse than no help at all.

                      And lecturing those who do help isn't particularly friendly.
                      • 8. Re: PL SQL Help
                        rp0428
                        >
                        I have to get details from my db to csv
                        >
                        When you ask a question always provide information about your 4 digit Oracle version and the OS and sqldeveloper versions that you are using.

                        You can export data in csv format a couple of different ways.
                        1. Right-click on a table in the connection navigator and select 'export data - csv'. Then enter the information on the format tab dialog for the format, delimiter and filename. If you select 'apply' this will export all data in the table. You can use the 'Columns' tab to select specific columns and the 'Where' tab to provide a WHERE clause to select specific records.

                        2. Enter a SQL SELECT statement in a SQl editor window and execute the query. This query can select data from any set of tables you have access to. Right-click in the Query Result window and select 'export data - csv' and follow the same instructions as in #1 above.

                        It sounds like you have data in a MASTER/DETAIL relationship such as the SCOTT schema DEPT and EMP tables.

                        Can you provide some sample data for each of your tables and show what data you want from each table?
                        This can probably be done with a simple, single query that returns a basic result set. You can then export this result set from sqldeveloper to a CSV file.

                        Don't focus on a particular solution such as 'cursors' or 'loops' until you have identified exactly what data you need.
                        • 9. Re: PL SQL Help
                          rp0428
                          >
                          I thought the purpose of the forums is to help people. Thus, I am helping.
                          >
                          Scot/Marwim/Jim - That is the purpose of the forums. The first step in helping is to make sure you understand the question and to get any missing information or clarification from the OP before trying to give advice or determine what forum is appropriate.

                          If help is needed in writing a complex query to get a result set then the SQL and PL/SQL forum would be the best place to get that help. If help is needed to get a result set to CSV then this is the right forum because sqldeveloper has easy to use functionality to do that - referring someone, especially a newbie, to another forum to use PL/SQL for that functionality isn't something I would do.

                          Most of this disagreement could have been avoided if OP has simply been asked to clarify what they were trying to do.

                          This OP has only posted 11 times and most of those have been in the Database-General and SQL and PL/SQl forums. I agree that the title and content makes things a little murky but that is all the more reason to get it clarified before trying to choose a solution or forum.

                          So who's turn is it to buy the next round?
                          • 10. Re: PL SQL Help
                            Marwim
                            Hello,
                            So who's turn is it to buy the next round?
                            I will ;-)

                            I accept, that the question might be answered within SQL Developer, so I will add my idea:

                            Create a master/detail report. In the master you select the USER CODE.
                            For each of the master records the detail will have the data you can export to csv with a simple right click.

                            Regards
                            Marcus

                            P.S.: If it is a job that has to be done regularily and there are more than a small number of files to be created, I would still try to do it in PL/SQL and use utl_file to write the files.
                            • 11. Re: PL SQL Help
                              Jim Smith
                              Mine's a G&T.

                              The definitive CSV solution...http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:68212348056
                              • 12. Re: PL SQL Help
                                user11876003
                                All are help to me solve