1 2 Previous Next 26 Replies Latest reply on Mar 27, 2014 4:41 AM by 994122 Go to original post
      • 15. Re: PLS-00103: Encountered the symbol "PROCEDURE" when expecting one
        994122

        SQL> set serveroutput on

        SQL> set line 10000

        SQL> CREATE OR REPLACE PACKAGE body xx_extract

          2  AS

          3     CURSOR v_cursor

          4     IS

          5        SELECT empno, a.deptno,a.hiredate

          6          FROM emp a, dept d

          7         WHERE a.deptno = d.deptno;

          8 

          9     v_cursor_rec   v_cursor%ROWTYPE;

        10     v_in_number    emp.empno%TYPE;

        11     v_in_date      emp.hiredate%TYPE;

        12 

        13     PROCEDURE xxc_process

        14     IS

        15     BEGIN

        16 

        17        OPEN v_cursor;

        18 

        19        LOOP

        20           FETCH v_cursor

        21            INTO v_cursor_rec;

        22 

        23           EXIT WHEN v_cursor%NOTFOUND;

        24           xxc_get (v_cursor_rec.empno, v_cursor_rec.deptno);

        25        END LOOP;

        26 

        27        CLOSE v_cursor;

        28     END xxc_process;

        29 

        30     PROCEDURE xxc_get (in_id in emp.empno%TYPE, in_number in  emp.deptno%TYPE)

        31     IS

        32     BEGIN

        33        SELECT empno, hiredate

        34          INTO v_in_number, v_in_date

        35          FROM emp

        36         WHERE empno =in_id

        37         AND deptno = in_number;

        38         dbms_output.put_line('Number'||v_in_number||'   '||'Date'||v_in_date);

        39     EXCEPTION

        40        WHEN NO_DATA_FOUND

        41        THEN

        42           v_in_number := NULL;

        43           v_in_date := NULL;

        44     END xxc_get;

        45  END xx_extract;

        46  /

         

         

        Package body created.

        • 16. Re: PLS-00103: Encountered the symbol "PROCEDURE" when expecting one
          RogerT

          yes....and now

           

          begin

             xx_extract.xxc_process();

          end;

          /

           

          ?

          • 17. Re: PLS-00103: Encountered the symbol "PROCEDURE" when expecting one
            994122

            Ok fine..

             

            Number7839   Date17-NOV-81

            Number7698   Date01-MAY-81

            Number7782   Date09-JUN-81

            Number7566   Date02-APR-81

            Number7788   Date09-DEC-82

            Number7902   Date03-DEC-81

            Number7369   Date17-DEC-80

            Number7499   Date20-FEB-81

            Number7521   Date22-FEB-81

            Number7654   Date28-SEP-81

            Number7844   Date08-SEP-81

            Number7876   Date12-JAN-83

            Number7900   Date03-DEC-81

            Number7934   Date23-JAN-82

             

             

            PL/SQL procedure successfully completed.

             

            But why we need to call like below, why the package not displaying any values?

            begin

               xx_extract.xxc_process();

            end;

            • 18. Re: PLS-00103: Encountered the symbol "PROCEDURE" when expecting one
              Karthick2003

              What's the objective of the this package? The output of this package is same like of this simple query.

               

              select empno, hiredate from emp;

               

              What are you trying to achieve?

              • 19. Re: PLS-00103: Encountered the symbol "PROCEDURE" when expecting one
                RogerT

                A package is stored pl-sql code which needs to be called to be executed....it is not executed because you create it. ... pretty much like a car, the car does not drive aroud just because it exists....

                 

                hth

                • 20. Re: PLS-00103: Encountered the symbol "PROCEDURE" when expecting one
                  994122

                  Hi Karthick,

                   

                            Actually i have same requirement in the oracle  apps , i have written Package , but i am getting NULL Values when i ran the Backend Program in the oracle apps. I need to Display the Empno,Deptno.

                  • 21. Re: PLS-00103: Encountered the symbol "PROCEDURE" when expecting one
                    Karthick2003

                    DBMS_OUTPUT.PUT_LINE writes to a buffer. Clients like SQL Plus (when servereoutput is enabled) reads the buffer automatically when the execution of a PL/SQL code is complete, and print the buffer data to the I/O device. So your client being Oracle Apps you need to see what's need to be done to read the output buffer. Oracle provided API like DBMS_OUTPUT.GET_LINE to read the buffer. If necessary make use of it.

                    • 22. Re: PLS-00103: Encountered the symbol "PROCEDURE" when expecting one
                      994122

                      The Below code not displayed any values where i called the Procedure Before the Open stmt why? can u please explain.

                        PROCEDURE xxc_process

                         IS

                         BEGIN

                            xxc_get(v_cursor_rec.empno, v_cursor_rec.deptno);

                            OPEN v_cursor;

                       

                       

                      If i am calling in the Loop of the xxc_process it shows an out put (Fine).

                       

                      But as suggested as Roger why we called like Below ? I need the output when i ran the package and i don't want to call(run) like below

                       

                      begin

                         xx_extract.xxc_process();

                      end;

                      • 23. Re: PLS-00103: Encountered the symbol "PROCEDURE" when expecting one
                        RogerT

                        994122 wrote:

                         

                        The Below code not displayed any values where i called the Procedure Before the Open stmt why? can u please explain.

                          PROCEDURE xxc_process

                           IS

                           BEGIN

                              xxc_get(v_cursor_rec.empno, v_cursor_rec.deptno);

                              OPEN v_cursor;

                        because v_cursor_rec.empno and v_cursor_rec.deptno are empty at that time

                         

                        994122 wrote:

                         

                        But as suggested as Roger why we called like Below ? I need the output when i ran the package and i don't want to call(run) like below

                         

                        begin

                           xx_extract.xxc_process();

                        end;

                         

                        the begin ... end; block IS running the package, what you do with the create command is CREATING (storing) a  package.

                        • 24. Re: PLS-00103: Encountered the symbol "PROCEDURE" when expecting one
                          994122

                          Hi Roger sorry for that...

                           

                          because v_cursor_rec.empno and v_cursor_rec.deptno are empty at that time


                          1)How can you please explain...

                          2)And is this not possible to get an output  without call manually xx_extract.xxc_process();?


                          Package Spec:

                          CREATE OR REPLACE package xx_extract

                          as

                          procedure xxc_process;

                          procedure xxc_get(in_id         emp.empno%type,

                                                     in_number emp.deptno%type);

                          end ;

                          • 25. Re: PLS-00103: Encountered the symbol "PROCEDURE" when expecting one
                            RogerT

                            1) How can you please explain...

                             

                            Where does V_CURSOR_REC get filled?

                             

                            2) And is this not possible to get an output  without call manually xx_extract.xxc_process();?

                            Of course it is...you could call xx_extract.xxc_get with the right parameters...like with 7369, 20

                             

                            hth

                             

                            • 26. Re: PLS-00103: Encountered the symbol "PROCEDURE" when expecting one
                              994122

                              Roger wrote:

                               

                              1) How can you please explain...

                               

                              Where does V_CURSOR_REC get filled?

                               

                              From the v_cursor (Cursor Name).

                              1 2 Previous Next