Oracle SQL


    Hi there,

                   Can anybody help me out in solving this issue.

    I use oracle SQL 11g.


    The issue is I'm not getting terminated out of the user promt while writing

    Code for creating procedure even though all syntax is proper...

    What should I do.?


    Screenshot of the issue:-


    Please guide me in finding the solution..

      • 1. Re: Oracle SQL

        Hi TechGeek,

        what say the documentation about parameters ?


        First: try create procedure data ( nm in varchar2(20))


        Second: a procedure or function or package is pl/sql.

        You need a BEGIN / END block and you need a construct where you can hold result of your select.

        Something like this

        create procedure data ( nm in varchar2(20))
          result_construct work%rowtype;
        select *
          into result_construct
          from work
          where name = nm;


        This may work, i can't test here at home.

        But it will only work if name is unique and you get only 1 row as the result.

        With 0 or 2 or more rows you get an exception. Better you use a cursor for such a select.

        What do you want to achieve with this procedure ?




        • 2. Re: Oracle SQL

          btw better move this thread to SQL & PL/SQL

          • 3. Re: Oracle SQL

            As KayK mentioned, your example you're testing is full of errors but first a few notes


            1. Avoid using Oracle keywords as names of your code (i.e. procedures/functions/packages/tables/views, etc...)
            2. The slash (aka /) is needed after your code when in the SQL* Plus command line to compile the procedure.
            3. The following errors in your code are:
              1. The syntax for parameter definition in a procedure/function is "parameter name, parameter mode [optional but by default is IN], parameter data type]"  You have the parameter mode first
              2. Then the minimum is a BEGIN followed by code, then END. You do not have a BEGIN
                1. What KayK put is also incorrect in that you only use the DECLARE if you are writing an "un-named procedure" (aka anonymous PL/SQL block)
                2. Your SELECT statement needs to "capture" the result set correctly.  As KayK mentioned, you need the "INTO" clause and code to capture 1 row or if more than 1 an array or an exception handler.
            • 4. Re: Oracle SQL

              with a real database at hand my example has to look like this:


              create or replace procedure prc_data ( nm in varchar2 )
                  result_construct emp%rowtype;
                 select *
                   into result_construct
                   from emp
                  where ename = nm;
              Procedure created.
              < scott:op57@unxsy078 > show err
              No errors.
              • 5. Re: Oracle SQL
                Choc Cac

                If you do not put the Slash (/), you will not leave



                create or replace PROCEDURE test_choc (p_ename in varchar2) IS

                CURSOR showx is

                select *

                from emp

                where ename=p_ename;



                FOR rec IN showx LOOP

                DBMS_OUTPUT.put_line (rec.ENAME);

                END loop;




                Copy and paste this code, on line 12 to exit, place /





                • 6. Re: Oracle SQL

                  I already told the OP about the slash.