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.