13 Replies Latest reply: Apr 23, 2014 4:54 AM by 994122 RSS

    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

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

          FYI,

           

          I am using oracle 8i

           

          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.