Results 1 to 10 of 10

Thread: open for..

  1. #1
    Join Date
    Nov 2003
    Posts
    87

    Thumbs down Unanswered: open for..

    HI,

    1 CREATE OR REPLACE PROCEDURE CHK (
    2 TABLE_NAME IN VARCHAR2, AS_DATE DATE, INST_CODE VARCHAR2)
    3 is
    4 type col_type is REF CURSOR;
    5 col_rt col_type;
    6 IT_DESCR VARCHAR2(40);
    7 AMT NUMBER(15,2);
    8 begin
    9 open col_rt for
    10 ' SELECT ITEM_DESCRIPTION,' ||TABLE_NAME|| '.AMOUNT
    11 FROM TBAS_MBR_TRN_HDR, '||TABLE_NAME||
    12 WHERE A.MT_CR_TRN_ID = ' ||TABLE_NAME|| '.MT_CR_TRN_ID;
    13 LOOP
    14 fetch col_rt into IT_DESCR,AMT;
    15 exit when col_rt%NOTFOUND;
    16 DBMS_OUTPUT.PUT_LINE(IT_DESCR || ' ' || AMT);
    17 END LOOP;
    18* END;
    SQL> /

    Warning: Procedure created with compilation errors.

    SQL> SHO ERR
    Errors for PROCEDURE CHK:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    12/14 PLS-00103: Encountered the symbol "WHERE" when expecting one of
    the following:
    ( - + case mod new null <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> avg
    count current max min prior sql stddev sum variance execute
    forall merge time timestamp interval date
    <a string literal with character set specification>
    <a number> <a single-quoted SQL string> pipe

    13/13 PLS-00103: Encountered the symbol "LOOP"

    im stuck in this err.
    can anybody help??
    thanx.

  2. #2
    Join Date
    Dec 2003
    Posts
    74
    10 ' SELECT ITEM_DESCRIPTION,' ||TABLE_NAME|| '.AMOUNT
    11 FROM TBAS_MBR_TRN_HDR, '||TABLE_NAME||
    12 'WHERE A.MT_CR_TRN_ID = ' ||TABLE_NAME|| '.MT_CR_TRN_ID';


    REFER TO THE LINE IN BOLD ABOVE
    Last edited by edwinjames; 01-08-04 at 06:16.

  3. #3
    Join Date
    Jan 2004
    Location
    India
    Posts
    62

    Thumbs up

    ' SELECT ITEM_DESCRIPTION, '
    ||TABLE_NAME||
    '.AMOUNT FROM TBAS_MBR_TRN_HDR, '
    ||TABLE_NAME||
    ' WHERE A.MT_CR_TRN_ID = '
    ||TABLE_NAME||
    '.MT_CR_TRN_ID ;'
    13 LOOP
    14 fetch col_rt into IT_DESCR,AMT;
    15 exit when col_rt%NOTFOUND;
    16 DBMS_OUTPUT.PUT_LINE(IT_DESCR || ' ' || AMT);
    17 END LOOP;

    Hi!
    Please check the ' sign where you concatinate the variables.
    I think you are missing one ' before where clause.

    where clause must be attached to the variable tablename . column name .
    Check it.
    Regards,

    Rushi

  4. #4
    Join Date
    Nov 2003
    Posts
    87
    HI THANX.

    AGAIN IM GETTING ERROR WHILE EXECUTING.

    SQL> CREATE OR REPLACE PROCEDURE CHK (
    2 TABLE_NAME IN VARCHAR2, AS_DATE DATE, INST_CODE VARCHAR2)
    3 is
    4 type col_type is REF CURSOR;
    5 col_rt col_type;
    6 IT_DESCR TBAS_MBR_TRN_HDR.ITEM_DESCRIPTION%TYPE;
    7 AMT NUMBER(15,2);
    8 begin
    9 open col_rt for
    10 ' SELECT ITEM_DESCRIPTION,' ||TABLE_NAME|| '.AMOUNT
    11 FROM TBAS_MBR_TRN_HDR, '||TABLE_NAME||
    12 ' WHERE TBAS_MBR_TRN_HDR.MT_CR_TRN_ID = '||TABLE_NAME||'.MT_CR_TRN_ID
    13 AND TBAS_MBR_TRN_HDR.AS_AT_DATE ='||'AS_DATE' ||
    14 'AND TBAS_MBR_TRN_HDR.INST_CODE = '||'INST_CODE';
    15 LOOP
    16 fetch col_rt into IT_DESCR,AMT;
    17 exit when col_rt%NOTFOUND;
    18 END LOOP;
    19 END;
    20 /

    Procedure created.

    SQL> EXEC CHK('TBAS_MBR300','16-NOV-03','00002');
    BEGIN CHK('TBAS_MBR300','16-NOV-03','00002'); END;

    *
    ERROR at line 1:
    ORA-00933: SQL command not properly ended
    ORA-06512: at "BSDMASTER.CHK", line 9
    ORA-06512: at line 1


    HELP??

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It's always a good idea to use DBMS_OUTPUT.PUT_LINE to see the SQL statement you have built and eyeball it to see if it is OK. I can see a missing space here:

    13 AND TBAS_MBR_TRN_HDR.AS_AT_DATE ='||'AS_DATE' ||
    14 'AND TBAS_MBR_TRN_HDR.INST_CODE = '||'INST_CODE';

    so that the SQL says:

    AND TBAS_MBR_TRN_HDR.AS_AT_DATE =AS_DATEAND TBAS_MBR_TRN_HDR.INST_CODE = INST_CODE

    which is clearly wrong!

    BTW, I don't suppose you really wanted 'AS_DATE' and 'INST_CODE' to be treated the way they are either did you? Otherwise you wouldn't have bothered with the concatenations. You meant to use the values of those parameters right? If so you should use bind variables like this:

    Code:
    open col_rt for
      ' SELECT ITEM_DESCRIPTION,' ||TABLE_NAME|| '.AMOUNT 
       FROM TBAS_MBR_TRN_HDR, '||TABLE_NAME||
       ' WHERE TBAS_MBR_TRN_HDR.MT_CR_TRN_ID = '||TABLE_NAME||'.MT_CR_TRN_ID
     AND TBAS_MBR_TRN_HDR.AS_AT_DATE =:B1
     AND TBAS_MBR_TRN_HDR.INST_CODE = :B2'
      using AS_DATE, INST_CODE;

  6. #6
    Join Date
    Nov 2003
    Posts
    87
    again :-((


    1 CREATE OR REPLACE PROCEDURE CHK (
    2 TABLE_NAME IN VARCHAR2, AS_DATE DATE, INST_CODE VARCHAR2)
    3 is
    4 type col_type is REF CURSOR;
    5 col_rt col_type;
    6 IT_DESCR TBAS_MBR_TRN_HDR.ITEM_DESCRIPTION%TYPE;
    7 AMT NUMBER(15,2);
    8 begin
    9 open col_rt for
    10 'SELECT ITEM_DESCRIPTION,' ||TABLE_NAME|| '.AMOUNT
    11 FROM TBAS_MBR_TRN_HDR, '||TABLE_NAME||
    12 'WHERE TBAS_MBR_TRN_HDR.MT_CR_TRN_ID = '||TABLE_NAME||'.MT_CR_TRN_ID
    13 AND TBAS_MBR_TRN_HDR.AS_AT_DATE = :b1
    14 AND TBAS_MBR_TRN_HDR.INST_CODE = :b2';
    15 LOOP
    16 fetch col_rt into IT_DESCR,AMT;
    17 exit when col_rt%NOTFOUND;
    18 END LOOP;
    19* END;
    SQL> exec chk('tbas_mbr300','16-nov-03','00002');
    BEGIN chk('tbas_mbr300','16-nov-03','00002'); END;

    *
    ERROR at line 1:
    ORA-00933: SQL command not properly ended
    ORA-06512: at "BSDMASTER.CHK", line 9
    ORA-06512: at line 1



    ??

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If you used DBMS_OUTPUT like I suggested you might be able to debug your own code instead of asking others to do it for you:

    (With TABLE_NAME set to 'EMP' as an example):

    SELECT ITEM_DESCRIPTION,EMP.AMOUNT
    FROM TBAS_MBR_TRN_HDR, EMPWHERE TBAS_MBR_TRN_HDR.MT_CR_TRN_ID = EMP.MT_CR_TRN_ID
    AND TBAS_MBR_TRN_HDR.AS_AT_DATE = :b1
    AND TBAS_MBR_TRN_HDR.INST_CODE = :b2

    And you are missing the USING clause.

  8. #8
    Join Date
    Nov 2003
    Posts
    87
    HOPE IT WONT B DISTURBING U..

    1 CREATE OR REPLACE PROCEDURE CHK (
    2 TABLE_NAME IN VARCHAR2, AS_DATE DATE, INST_CODE VARCHAR2)
    3 is
    4 type col_type is REF CURSOR;
    5 col_rt col_type;
    6 IT_DESCR TBAS_MBR_TRN_HDR.ITEM_DESCRIPTION%TYPE;
    7 AMT NUMBER(15,2);
    8 sQUERY VARCHAR2(2000) :=
    9 'SELECT ITEM_DESCRIPTION,' ||TABLE_NAME|| '.AMOUNT
    10 FROM TBAS_MBR_TRN_HDR, '||TABLE_NAME||
    11 ' WHERE TBAS_MBR_TRN_HDR.MT_CR_TRN_ID = '||TABLE_NAME||'.MT_CR_TRN_ID
    12 AND TBAS_MBR_TRN_HDR.AS_AT_DATE = :b1
    13 AND TBAS_MBR_TRN_HDR.INST_CODE = :b2';
    14 begin
    15 open col_rt for sQUERY;
    16 DBMS_OUTPUT.PUT_LINE('------------------');
    17 DBMS_OUTPUT.PUT_LINE(SQUERY);
    18 LOOP
    19 fetch col_rt into IT_DESCR,AMT;
    20 exit when col_rt%NOTFOUND;
    21 END LOOP;
    22* END;
    SQL> /

    Procedure created.

    SQL> EXEC CHK('TBAS_MBR300','16-NOV-03','00002');
    BEGIN CHK('TBAS_MBR300','16-NOV-03','00002'); END;

    *
    ERROR at line 1:
    ORA-01008: not all variables bound
    ORA-06512: at "BSDMASTER.CHK", line 15
    ORA-06512: at line 1

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You still need that USING clause to bind the parameter values to the query:

    open col_rt for sQUERY using AS_DATE, INST_CODE;

  10. #10
    Join Date
    Nov 2003
    Posts
    87
    thanx a lot andrew.
    :-))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •