10 Replies Latest reply: May 7, 2008 12:20 PM by DBelt RSS

    Using continuation character in EXECUTE command

    DBelt
      In SQL*Plus, the following sample EXECUTE command works fine

      EXECUTE MyProcedure(arg1, arg2)

      But if I try to break it into two lines:

      EXECUTE MyProcedure(arg1, -
      arg2)

      I get a character to number conversion error ORA-06502

      Both of the arguments are of VARCHAR2 type.

      I thought the hyphen character was supposed to let you continue a command on more than one line. How can I break up this command?
        • 1. Re: Using continuation character in EXECUTE command
          Madhu BR
          Why would want to "break" the command with a hyphen ?
          what do you want to achieve ?
          • 2. Re: Using continuation character in EXECUTE command
            2889
            from the docs :
            ===============================================

            EXEC[UTE] statement

            where statement represents a PL/SQL statement.

            Executes a single PL/SQL statement. The EXECUTE command is often useful when you want to execute a PL/SQL statement that references a stored procedure. For more information on PL/SQL, see your Oracle Database PL/SQL Language Reference.

            Usage

            If your EXECUTE command cannot fit on one line because of the PL/SQL statement, use the SQL*Plus continuation character (a hyphen).

            The length of the command and the PL/SQL statement cannot exceed the length defined by SET LINESIZE.

            You can suppress printing of the message "PL/SQL procedure successfully completed" with SET FEEDBACK OFF.

            =======================================

            may be your real life PL/SQL statement exceeds linesize, otherwise you have hit a bug
            • 3. Re: Using continuation character in EXECUTE command
              DBelt
              In this case I am putting the command in a .sql command file and the line is too long to fit onto a single printed line.

              I know that I could just use an anonymous PL/SQL block and it looks like I may have to since the EXECUTE command seems to be broken.
              • 4. Re: Using continuation character in EXECUTE command
                DBelt
                The line is in a command file. What is LINESIZE when a command file is being executed; is it that defined in the session or some other default. My development environment right now is SQLDeveloper and it just skips all references to LINESIZE.

                The real line I am trying to run is 81 or 82 characters including the EXECUTE keyword -- I wouldn't think this would cause a LINESIZE overflow.

                It does seem, though, that the problem goes away when the script is run from PL*SQL -- I can't seem to run the script from inside SQLDeveloper beacuse it is too stupid (or I am too stupid to figure out how) to run scripts with @ that reside outside the default directory.
                • 5. Re: Using continuation character in EXECUTE command
                  70824
                  Forget the continuation mark:

                  begin
                  do_something (arg1
                  , arg2
                  , arg3
                  );
                  end;
                  /
                  works fine; exec is for lazy programmers, and is expanded to:
                  begin do_something(....); end;
                  • 6. Re: Using continuation character in EXECUTE command
                    DBelt
                    This was exactly my conclusion. Thanks.

                    And sometimes I am a lazy programmer but not always :)

                    Message was edited by:
                    user556531
                    • 7. Re: Using continuation character in EXECUTE command
                      2889
                      just for the record
                      EXECUTE MyProcedure(arg1, -
                                          arg2)
                      [pre]
                      does NOT work in :
                      [pre]
                      [ ] Sql*Plus
                      [ ] SqlDeveloper
                      ?
                      • 8. Re: Using continuation character in EXECUTE command
                        545555
                        SQL>create procedure myprocedure( arg1 varchar2, arg2 varchar2 ) as begin null; end;
                          2  /

                        Procedure created.

                        SQL>execute myprocedure('somearg',-
                        'someotherarg')
                        PL/SQL procedure successfully completed.

                        SQL>
                        .
                        or via a script...
                        .
                        SQL>$
                        copy con c:\script.sql
                        execute myprocedure('somearg',-
                                            'someotherarg')
                        ^Z
                        exit

                        SQL>@c:\script.sql

                        PL/SQL procedure successfully completed.
                        .
                        What's inside your procedure? and what exactly are you typing on the command line? What version of SQL*Plus are you using? What is your platform?
                        • 9. Re: Using continuation character in EXECUTE command
                          2889
                          <<
                          What's inside your procedure? and what exactly are you typing on the command line?
                          What version of SQL*Plus are you using? What is your platform?
                          >>


                          that's exactly what I'm trying to find out from OP user556531
                          • 10. Re: Using continuation character in EXECUTE command
                            DBelt
                            The EXECUTE works with SQL*Plus and does not work from within SqlDeveloper

                            The contents of the procedure, which is part of a package, seems to be irrelevant since the problem occurs even with an empty procedure body (just a NULL; statement).

                            My platform is Windows2000. Database version is 11g. SQLDeveloper version is 1.2.1

                            To reiterate, when I type on command line in SqlPlus there is no problem, when I run it from a script file from within SqlPlus there is not problem. The problem occurs only from within SqlDeveloper. Replacing the EXECUTE command with an anonymous block does work in either environment.

                            It is difficult for me to show exactly what is in the called procedure since the machine it is running on is not connected to the internet and would require retyping the whole procedure out here -- that's why I tried the experiment with the NULL body with the exact same results as with the non NULL body.

                            Message was edited by:
                            user556531