6 Replies Latest reply on Jul 8, 2019 5:32 PM by TechGeek!

    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 ?  https://docs.oracle.com/database/121/LNPLS/formal_parameter.htm#GUID-5BA8E033-96B9-439A-A4FC-4844FEC14AD8__CJAJDGCC


          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.