14 Replies Latest reply: Jul 30, 2013 10:08 AM by BluShadow RSS

    how to execute .sql file in Stored Procedure?

    newmind

      Hi,

       

      I have an urgent requirement, where i have to execute .sql file form Stored Procedure.

      This .sql file will have set of update statement. I need to pass value to this update statement.

      Kindly please help me.

       

      Regards,

      Irfan

        • 1. Re: how to execute .sql file in Stored Procedure?
          BluShadow

          Urgent requirements indicate that a live system has failed or that people's lives are at risk.  In such cases you should raise a top priority service request with Oracle Support using your support identifier, and Oracle will assist you immediately.

          Otherwise, recognize that this is a forum of volunteers with their own jobs to do, and also other members who would also like their questions answering just as soon as possible.  As such, it's considered rude to demand 'urgent' attention from the volunteers or to suggest that your issue is more important than the other people who have politely asked for help.

           

          See the forum FAQ: Re: 2. How do I ask a question on the forums?

           

          What you are trying to do is not good practice.

          Yes, you could call SQL*Plus using something like the DBMS_SCHEDULER package and get SQL*Plus to execute the script on your server, or even use a Java Stored Procedure to call out the the SQL*Plus executable, but what's the point in that.

          Structured programming would have the update statement(s) as part of another stored procedure of function into which you would pass the necessary parameters.

           

          Explain why you are trying to do this, and provide full details as requested in the FAQ.

          • 2. Re: how to execute .sql file in Stored Procedure?
            newmind

            This is required as part of Data Migration where  i have to do 100 of table's update. Each time update table will defer, so its better to have in separate script file (.sql). Can u paste some sample/syntax to exceute .sql file from stored procedure. I am new to this PL/SQL.

            • 3. Re: how to execute .sql file in Stored Procedure?
              BluShadow

              This is required as part of Data Migration where  i have to do 100 of table's update. Each time update table will defer, so its better to have in separate script file (.sql). Can u paste some sample/syntax to exceute .sql file from stored procedure. I am new to this PL/SQL.

               

              How have you determined that it's "better" to have seperate scripts?  I assume you mean the table name will "differ" (and not "defer" - I assume that's just because English isn't your first language? no problem - I think I understand what you're asking).

               

              So what I think you're asking is that you have dynamic table names but each table needs to be updated in the same way?

              Question: Why do you have tables with different names that all need the same process doing to them?

               

              Assuming it's a valid requirement (and 99% of the time doing dynamic coding implies it's not).... you could use dynamic code, rather than 'scripts'...

               

              e.g.

               

              create procedure update_table(tbl_name varchar2) is

              begin

                execute immediate 'update '||tbl_name||' set lastupdate = null';

              end;

               

              As you haven't bothered to provide a database version, any example code/data or explanation of what you're actually doing, you're not going to get any detailed answer.  Please do take the time to read the FAQ and post appropriate details so people can help you.

              • 4. Re: how to execute .sql file in Stored Procedure?
                newmind

                Thanks for the reply. But can u please paste some sample/syntax to exceute .sql file from stored procedure?

                Assume i have update_data.sql file  and it contains assume 5 update statement like:

                 

                UPDATE homepage_inv_details SET payer = ? WHERE legacyId= ?;

                UPDATE homepage_inv_details SET billto = ? WHERE legacyId= ?;

                 

                How do i invoke update_data.sql file from Stored Procedure and pass parameter's required to this update statement.

                Note: Parameter values to set and where condition is similar for all the update statement.

                • 5. Re: how to execute .sql file in Stored Procedure?
                  BluShadow

                  As said previously, this is a stupid way of doing things.

                  The example you've provided does not require any dynamic coding.

                  Passing parameters to external scripts executed through the DBMS_SCHEDULER package is going to be far more messy than just creating procedures and using parameters.

                   

                  It took me a matter of seconds using google to find out how to call a shell script from DBMS_SCHEDULER... e.g. just one result...

                   

                  Calling Shell Script from Oracle Procedure

                   

                  and there are others, and you could also search these forums for the same.  Nobody wants to spend time giving you an example for something that is already answered many times and easily available via a simple search.

                   

                  You still haven't provided any reasonable justification for why this needs to be in sql scripts rather than in procedures.  Your "Note" says that the parameter values to set and the where condition is similar for all update statements, so that suggests even more that there is no need for dynamic coding.

                  • 6. Re: how to execute .sql file in Stored Procedure?
                    BluShadow

                    Manik wrote:

                     

                    One option can be create an external table on that file (update_data.sql), and write an oracle proc to read through the lines and execute them one by one (using execute immediate).

                    Cheers,

                    Manik.

                     

                    Ouch.  That's just messy.  If you're going to go to that trouble you may as well just open up the files and copy/paste them into a procedure and call them from there.

                    • 7. Re: how to execute .sql file in Stored Procedure?
                      newmind

                      I want to do in the above said way, that is my requirement. Could please help in that way? Sample code/syntax will be very much helpful.

                      • 8. Re: how to execute .sql file in Stored Procedure?
                        Manik

                        Yes you are right...!

                        Not sure what I was writing in the forum..

                        I understand what you really mean.

                         

                        Cheers,

                        Manik.

                        • 9. Re: how to execute .sql file in Stored Procedure?
                          newmind

                          Still i haven't received the answer to my question/requirement. Please help me.

                          • 10. Re: how to execute .sql file in Stored Procedure?
                            Sven W.

                            Please formulate your question in a way that helps us to answer it. Read the FAQ for getting a better idea what is missing.

                             

                             

                            Your question sounds to us similiar to the following analogy

                            Question: "I want to drop a ball into the basket. Please tell how I can drop this while flying on a plane with 350 mph and having the window open."

                             

                            Answer: "Get off the plane, stand still and drop the ball directly."

                            Doing this from a plane is way more difficult and way more dangerous. Therefore we won't advise doing it in such a way.


                            • 11. Re: how to execute .sql file in Stored Procedure?
                              BluShadow

                              newmind wrote:

                               

                              I want to do in the above said way, that is my requirement. Could please help in that way? Sample code/syntax will be very much helpful.

                               

                              Requirements specify what business 'input' is provided and what business 'output' should occur from it.  How that is technically implemented is not a business requirement.

                              As the technical developer it is your job to determine the best way to implement a business requirement, which may mean creating a technical requirement, but that technical requirement should examine all the possibilities.  If you have been provided with a technical requirement from someone else and that requirement appears to be flawed or incorrect, then this should be fed back to the original person who created it to either get them to explain the real reason why this method has been chosen above others, or to get them to correct it for a more appropriate solution.

                              The experts here are seemingly in agreement that you are trying to do a simple task in almost the most complicated way possible, and a way that is flawed.  Yet you insist that those same experts should show you how to go about implementing this flawed solution.  That's just wrong.

                              • 12. Re: how to execute .sql file in Stored Procedure?
                                EdStevens

                                newmind wrote:

                                 

                                Still i haven't received the answer to my question/requirement. Please help me.

                                You are driving down a road.  People on both sides of the road are screaming at you, telling you there is a bridge out just around the next bend in the road.  You keep asking how to make your car go faster ....

                                • 13. Re: how to execute .sql file in Stored Procedure?
                                  michaelrozar17

                                  Can't we just tell the OP that what ever approach he/she wants is impossible to do in PL/SQL? Until now we ask him to take different approach but we did not mention whether its possible or not to implement the way he/she wants in PL/SQL. If the answer is clear-cut i believe OP would not ask again..as..

                                  I want to do in the above said way, that is my requirement. Could please help in that way? Sample code/syntax will be very much helpful.

                                  • 14. Re: how to execute .sql file in Stored Procedure?
                                    BluShadow

                                    But it's not impossible... it's just bloomin' stupid.

                                    I'm not in the business of teaching people stupid practices, so if someone else wants to do that then sure, let them.  Otherwise the OP is going to have to realize that the lack of answer is because it's not going to be answered the way they want, when there are far better technical solutions to be used.