13 Replies Latest reply on Apr 23, 2014 9:54 AM by 994122

    PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

    4lish4

      Hi All,


      I am trying to create stored procedures using inline subquery. when I run the query normally, but when the query is inserted into the cursor him get an error.


      cursor cashoutin is

      select name ,

       

      (select sum(amount) from ar_cash_receipts_all ac, ar_receivables_trx_all ar

      where ac.receivables_trx_id=ar.receivables_trx_id

      and ac.set_of_books_id=glsb.set_of_books_id

      and receipt_date between '01-MAR-14' and '31-MAR-14') kontribusi_prd,

       

      from gl_sets_of_books glsb

      where set_of_books_id > 470;

       

      v_cashoutin cashoutin%rowtype;



      can anybody help me


      Thanks

        • 2. Re: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
          994122

          Hi try this,

           

          cursor cashoutin

          is

          SELECT name,

                 (SELECT SUM(amount)

                  FROM   ar_cash_receipts_all ac,

                         ar_receivables_trx_all ar

                  WHERE  ac.receivables_trx_id = ar.receivables_trx_id

                         AND ac.set_of_books_id = glsb.set_of_books_id

                         AND receipt_date BETWEEN '01-MAR-14' AND '31-MAR-14') kontribusi_prd  -->Remove comma

          FROM   gl_sets_of_books glsb

          WHERE  set_of_books_id > 470;

           

          v_cashoutin cashoutin%rowtype;

          • 3. Re: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
            4lish4

            hai 994122,

             

            Thanks for your answer

            I try it without comma and get error above.

             

            can anybody help me


            Thanks

            • 4. Re: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
              994122

              4lish4 wrote:

               

              hai 994122,

               

              Thanks for your answer

              I try it without comma and get error above.

               

              can anybody help me


              Thanks

              Please provide me fully code ..

              • 5. Re: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
                4lish4

                This the fully code

                 

                CREATE OR REPLACE procedure CASHOUTIN

                (errbuf OUT char, retcode OUT char,

                 

                ) AS

                 

                cursor cashoutin is

                select haou.name,

                (select sum(apc.amount)

                from ap_checks_all apc, ap_invoice_payments_all aip

                where apc.check_id=aip.check_id and aip.org_id=haou.ORGANIZATION_ID

                and check_date between '01-MAR-14' and '31-MAR-14') cashout_period

                from hr_all_organization_units haou

                where haou.name like 'OU2012%';

                v_cashoutin cashoutin%rowtype;

                begin

                 

                 

                 

                open cashoutin;

                fetch cashoutin into v_cashoutin;

                FND_FILE.put_line(FND_FILE.output, 'Unit Kerja :' || v_cashoutin.sob_name);

                close cashoutin;

                for cash_rec in cashoutin

                LOOP

                FND_FILE.put_line(FND_FILE.output, 'TEST');

                END LOOP;

                 

                 

                 

                             errbuf := '';

                                retcode := '0';

                 

                 

                 

                exception

                               when others then

                               errbuf := sqlerrm;

                               retcode := '2';

                end;

                /

                • 6. Re: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
                  994122

                  Please check your column in the code. first time i got error. I have tested in the Oracle  10.1.0.4.2

                   

                   

                   

                  LINE/COL ERROR

                  -------- -----------------------------------------------------------------

                  20/5     PL/SQL: Statement ignored

                  20/70   PLS-00302: component 'SOB_NAME' must be declared

                  SQL> ed

                  Wrote file afiedt.buf

                   

                   

                    1  CREATE OR replace PROCEDURE Cashoutin(errbuf  OUT CHAR,

                    2                                        retcode OUT CHAR)

                    3  AS

                    4    CURSOR cashoutin IS

                    5      SELECT haou.name,

                    6             (SELECT SUM(apc.amount)

                    7              FROM   ap_checks_all apc,

                    8                     ap_invoice_payments_all aip

                    9              WHERE  apc.check_id = aip.check_id

                  10                     AND aip.org_id = haou.organization_id

                  11                     AND check_date BETWEEN '01-MAR-14' AND '31-MAR-14') cashout_period

                  12      FROM   hr_all_organization_units haou

                  13      WHERE  haou.name LIKE 'OU2012%';

                  14    v_cashoutin cashoutin%ROWTYPE;

                  15  BEGIN

                  16      OPEN cashoutin;

                  17      FETCH cashoutin INTO v_cashoutin;

                  18      fnd_file.Put_line(fnd_file.output, 'Unit Kerja :' || v_cashoutin.name);

                  19      CLOSE cashoutin;

                  20      FOR cash_rec IN cashoutin LOOP

                  21          fnd_file.Put_line(fnd_file.output, 'TEST');

                  22      END LOOP;

                  23      errbuf := '';

                  24      retcode := '0';

                  25  EXCEPTION

                  26    WHEN OTHERS THEN

                  27               errbuf := SQLERRM;

                  28               retcode := '2';

                  29* END;

                  30  /

                   

                   

                  Procedure created.

                  • 7. Re: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
                    4lish4

                    hai 994122,

                     

                    what you created in oracle 8i, because the error message isn't must be declared but

                     

                    Encountered the symbol "SELECT" when expecting one of the following:

                     

                    what is pl/sql oracle 8i support script above?

                    and can you give me solution?

                     

                     

                    Thanks

                    • 8. Re: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
                      4lish4

                      FYI,

                       

                      because i was try use oracle 9i is fine

                       

                      Thanks

                      • 10. Re: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
                        994122

                        What is the purpose of the for loop after the cursor closed.

                        • 11. Re: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
                          4lish4

                          994122 wrote:

                           

                          What is the purpose of the for loop after the cursor closed.

                          To retrieve data from database,

                          CREATE OR REPLACE procedure CASHOUTIN

                          (errbuf OUT char, retcode OUT char

                          ) AS

                          cursor cashoutin is

                          select haou.name,

                          (select sum(apc.amount)

                          from ap_checks_all apc, ap_invoice_payments_all aip

                          where apc.check_id=aip.check_id and aip.org_id=haou.ORGANIZATION_ID

                          and check_date between '01-MAR-14' and '31-MAR-14') cashout_prd,

                          (select sum(amount) from ar_cash_receipts_all ac, ar_receivables_trx_all ar

                          where ac.receivables_trx_id=ar.receivables_trx_id and ar.org_id=haou.organization_id

                          and receipt_date between '01-MAR-14' and '31-MAR-14') kontribusi_prd,

                          (select sum(apc.amount)

                          from ap_checks_all apc, ap_invoice_payments_all aip

                          where apc.check_id=aip.check_id and aip.org_id=haou.ORGANIZATION_ID

                          and check_date between '01-JAN-14' and '31-MAR-14') cashout_sd,

                          (select sum(amount) from ar_cash_receipts_all ac, ar_receivables_trx_all ar

                          where ac.receivables_trx_id=ar.receivables_trx_id and ar.org_id=haou.organization_id

                          and receipt_date between '01-JAN-14' and '31-MAR-14') kontribusi_sd,

                          (select sum(apsa.acctd_amount_due_remaining) from ar_payment_schedules_all apsa

                          where apsa.org_id=haou.organization_id) piutang

                          from hr_all_organization_units haou;

                          where haou.name like 'OU2012%';

                           

                          v_cashoutin cashoutin%rowtype;

                           

                          surplus number :=0;

                           

                          begin

                           

                          open cashoutin;

                          fetch cashoutin into v_cashoutin;

                          FND_FILE.put_line(FND_FILE.output, 'Unit Kerja :' || v_cashoutin.name);

                          FND_FILE.put_line(FND_FILE.output, 'UNIT KERJA;CASHOUT;KONTRIBUSI;CASHOUT SD; KONTRIBUSI SD; Defisit/Surplus;PIUTANG;');

                          close cashoutin;

                           

                          for cash_rec in cashoutin

                           

                          LOOP

                          surplus :=0;

                          surplus := cash_rec.kontribusi - cash_rec.cashout;

                          FND_FILE.put_line(FND_FILE.output, cash_rec.name||';'||

                          cash_rec.cashout_prd ||';'||

                          cash_rec.kontribusi_prd ||';'||

                          cash_rec.cashout_sd ||';'||

                          cash_rec.kontribusi_sd ||';'||

                          surplus ||';'||

                          cash_rec.piutang ||';');

                          END LOOP;

                           

                                       errbuf := '';

                                       retcode := '0';

                          exception

                                         when others then

                                         errbuf := sqlerrm;

                                         retcode := '2';

                          end;

                          /

                          any idea for my code in oracle 8i?

                           

                          Thanks

                          • 12. Re: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
                            994122

                            Can you please try this again...


                            CREATE OR replace PROCEDURE Cashoutin(errbuf  OUT CHAR,

                                                                  retcode OUT CHAR)

                            AS

                              CURSOR cashoutin IS

                                SELECT haou.name,

                                       (SELECT SUM(apc.amount)

                                        FROM   ap_checks_all apc,

                                               ap_invoice_payments_all aip

                                        WHERE  apc.check_id = aip.check_id

                                               AND aip.org_id = haou.organization_id

                                               AND check_date BETWEEN '01-MAR-14' AND '31-MAR-14') cashout_prd

                                                              ,

                                       (SELECT SUM(amount)

                                        FROM   ar_cash_receipts_all ac,

                                               ar_receivables_trx_all ar

                                        WHERE  ac.receivables_trx_id = ar.receivables_trx_id

                                               AND ar.org_id = haou.organization_id

                                               AND receipt_date BETWEEN '01-MAR-14' AND '31-MAR-14')  kontribusi_prd,

                                       (SELECT SUM(apc.amount)

                                        FROM   ap_checks_all apc,

                                               ap_invoice_payments_all aip

                                        WHERE  apc.check_id = aip.check_id

                                               AND aip.org_id = haou.organization_id

                                               AND check_date BETWEEN '01-JAN-14' AND '31-MAR-14') cashout_sd,

                                       (SELECT SUM(amount)

                                        FROM   ar_cash_receipts_all ac,

                                               ar_receivables_trx_all ar

                                        WHERE  ac.receivables_trx_id = ar.receivables_trx_id

                                               AND ar.org_id = haou.organization_id

                                               AND receipt_date BETWEEN '01-JAN-14' AND '31-MAR-14')   kontribusi_sd,

                                       (SELECT SUM(apsa.acctd_amount_due_remaining)

                                        FROM   ar_payment_schedules_all apsa

                                        WHERE  apsa.org_id = haou.organization_id)                   piutang

                                FROM   hr_all_organization_units haou

                                WHERE  haou.name LIKE 'OU2012%';

                              v_cashoutin cashoutin%ROWTYPE;

                              surplus     NUMBER := 0;

                            BEGIN

                                OPEN cashoutin;

                             

                                FETCH cashoutin INTO v_cashoutin;

                             

                                fnd_file.Put_line(fnd_file.output, 'Unit Kerja :' || v_cashoutin.name);

                             

                            fnd_file.Put_line(fnd_file.output, 'UNIT KERJA;CASHOUT;KONTRIBUSI;CASHOUT SD; KONTRIBUSI SD; Defisit/Surplus;PIUTANG;');

                             

                            CLOSE cashoutin;

                             

                            FOR cash_rec IN cashoutin LOOP

                                surplus := 0;

                             

                                surplus := cash_rec.kontribusi - cash_rec.cashout;

                             

                                fnd_file.Put_line(fnd_file.output, cash_rec.name ||';' || cash_rec.cashout_prd

                                                                                 ||';' || cash_rec.kontribusi_prd

                                                                                 ||';' || cash_rec.cashout_sd

                                                                                 ||';' || cash_rec.kontribusi_sd

                                                                                 ||';' || surplus

                                                                                 ||';' || cash_rec.piutang

                                                                                 ||';');

                            END LOOP;

                             

                            errbuf := '';

                             

                            retcode := '0';

                            EXCEPTION

                              WHEN OTHERS THEN

                                         errbuf := SQLERRM;

                             

                                         retcode := '2';

                            END;



                            I am getting 59/25    PLS-00302: component 'KONTRIBUSI' must be declared


                            And KONTRIBUSI where it coming from ?

                            • 13. Re: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
                              994122

                              HI 4lish4 ,

                               

                                   There are some many mistakes are there check again please and in my PC It is working fine now.

                               

                              SQL> CREATE OR replace PROCEDURE Cashoutin(errbuf  OUT CHAR,

                                2                                        retcode OUT CHAR)

                                3  AS

                                4    CURSOR cashoutin IS

                                5      SELECT haou.name,

                                6             (SELECT SUM(apc.amount)

                                7              FROM   ap_checks_all apc,

                                8                     ap_invoice_payments_all aip

                                9              WHERE  apc.check_id = aip.check_id

                              10                     AND aip.org_id = haou.organization_id

                              11                     AND check_date BETWEEN '01-MAR-14' AND '31-MAR-14')  cashout_prd

                              12                                    ,

                              13             (SELECT SUM(amount)

                              14              FROM   ar_cash_receipts_all ac,

                              15                     ar_receivables_trx_all ar

                              16              WHERE  ac.receivables_trx_id = ar.receivables_trx_id

                              17                     AND ar.org_id = haou.organization_id

                              18                     AND receipt_date BETWEEN '01-MAR-14' AND '31-MAR-14')  kontribusi_prd,

                              19             (SELECT SUM(apc.amount)

                              20              FROM   ap_checks_all apc,

                              21                     ap_invoice_payments_all aip

                              22              WHERE  apc.check_id = aip.check_id

                              23                     AND aip.org_id = haou.organization_id

                              24                     AND check_date BETWEEN '01-JAN-14' AND '31-MAR-14') cashout_sd,

                              25             (SELECT SUM(amount)

                              26              FROM   ar_cash_receipts_all ac,

                              27                     ar_receivables_trx_all ar

                              28              WHERE  ac.receivables_trx_id = ar.receivables_trx_id

                              29                     AND ar.org_id = haou.organization_id

                              30                     AND receipt_date BETWEEN '01-JAN-14' AND '31-MAR-14') kontribusi_sd,

                              31             (SELECT SUM(apsa.acctd_amount_due_remaining)

                              32              FROM   ar_payment_schedules_all apsa

                              33              WHERE  apsa.org_id = haou.organization_id)                   piutang

                              34      FROM   hr_all_organization_units haou

                              35      WHERE  haou.name LIKE 'OU2012%';

                              36    v_cashoutin cashoutin%ROWTYPE;

                              37    surplus     NUMBER := 0;

                              38  BEGIN

                              39      OPEN cashoutin;

                              40

                              41      FETCH cashoutin INTO v_cashoutin;

                              42

                              43      fnd_file.Put_line(fnd_file.output, 'Unit Kerja :' || v_cashoutin.name);

                              44

                              45      fnd_file.Put_line(fnd_file.output,

                              46  'UNIT KERJA;CASHOUT;KONTRIBUSI;CASHOUT SD; KONTRIBUSI SD; Defisit/Surplus;PIUTANG;'

                              47  );

                              48

                              49  CLOSE cashoutin;

                              50

                              51  FOR cash_rec IN cashoutin LOOP

                              52      surplus := 0;

                              53

                              54     surplus := cash_rec.kontribusi_sd - cash_rec.cashout_prd;

                              55

                              56      fnd_file.Put_line(fnd_file.output, cash_rec.name

                              57                                         ||';'

                              58                                         || cash_rec.cashout_prd

                              59                                         ||';'

                              60                                         || cash_rec.kontribusi_prd

                              61                                         ||';'

                              62                                         || cash_rec.cashout_sd

                              63                                         ||';'

                              64                                         || cash_rec.kontribusi_sd

                              65                                         ||';'

                              66                                         || surplus

                              67                                         ||';'

                              68                                         || cash_rec.piutang

                              69                                         ||';');

                              70  END LOOP;

                              71

                              72  errbuf := '';

                              73

                              74  retcode := '0';

                              75  EXCEPTION

                              76    WHEN OTHERS THEN

                              77               errbuf := SQLERRM;

                              78

                              79               retcode := '2';

                              80  END;

                              81  /

                               

                               

                              Procedure created.