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

    Oracle SQL

    TechGeek!

      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:-

      IMG_20190707_192054016.jpg

      Please guide me in finding the solution..

        • 1. Re: Oracle SQL
          KayK

          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))
          as
          declare
            result_construct work%rowtype;
          begin
          select *
            into result_construct
            from work
            where name = nm;
          end;
          /
          

           

          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 ?

           

          regards

          Kay

          • 2. Re: Oracle SQL
            KayK

            btw better move this thread to SQL & PL/SQL

            • 3. Re: Oracle SQL
              jaramill

              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
                KayK

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

                 

                create or replace procedure prc_data ( nm in varchar2 )
                 as
                    result_construct emp%rowtype;
                 begin
                   select *
                     into result_construct
                     from emp
                    where ename = nm;
                 end;
                /
                
                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;

                   

                  BEGIN

                  FOR rec IN showx LOOP

                  DBMS_OUTPUT.put_line (rec.ENAME);

                  END loop;

                  END;

                   

                   

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

                   

                   

                  Regards!

                  Choc

                  • 6. Re: Oracle SQL
                    jaramill

                    I already told the OP about the slash.