Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2011
    Posts
    22

    Unanswered: Error on every line?!

    I wrote a script in MS SQL, and am trying to port it over to Oracle with no luck. I'm using Oracle SQL Developer to execute the script.

    Code:
    set serveroutput on
    LinkRef_List VARCHAR2(4000);
    LinkRef      VARCHAR2(10);
    LinkRef_List       := '8371|223312|53298|213234|END';
    WHILE LinkRef_List <> 'END'
    LOOP
      LinkRef := SUBSTR(LinkRef_List,1,(INSTR(LinkRef_List,'|')-1));
      IF (SELECT DISTINCT COUNT(*) FROM ms.obj o WHERE o.origobjectid = LinkRef) > 0
      THEN
        dbms_output.put_line(LinkRef + ' exists within the database');
      END IF;
      LinkRef_List := REPLACE(LinkRef_List,LinkRef + '|','');
    END LOOP;
    /
    This is what is belched out in the Statement Output. It apparently didn't like a single thing, so I must be miles away from what it should be.

    Error starting at line 2 in command:
    LinkRef_List VARCHAR2(4000)
    Error report:
    Unknown Command

    Error starting at line 3 in command:
    LinkRef VARCHAR2(10)
    Error report:
    Unknown Command

    Error starting at line 4 in command:
    LinkRef_List := '8371|223312|53298|213234|END'
    Error report:
    Unknown Command

    Error starting at line 5 in command:
    WHILE LinkRef_List <> 'END'
    Error report:
    Unknown Command

    Error starting at line 6 in command:
    LOOP
    Error report:
    Unknown Command

    Error starting at line 7 in command:
    LinkRef := SUBSTR(LinkRef_List,1,(INSTR(LinkRef_List,'|')-1))
    Error report:
    Unknown Command

    Error starting at line 8 in command:
    IF (SELECT DISTINCT COUNT(*) FROM ms.obj o WHERE o.origobjectid = LinkRef) > 0
    Error report:
    Unknown Command

    Error starting at line 9 in command:
    THEN
    Error report:
    Unknown Command

    Error starting at line 10 in command:
    dbms_output.put_line(LinkRef + ' exists within the database')
    Error report:
    Unknown Command

    Error starting at line 11 in command:
    END IF
    Error report:
    Unknown Command

    Error starting at line 12 in command:
    LinkRef_List := REPLACE(LinkRef_List,LinkRef + '|','')
    Error report:
    Unknown Command

    Error starting at line 13 in command:
    END LOOP
    Error report:
    Unknown Command

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    set serveroutput ON
    DECLARE
        linkref_list VARCHAR2(4000);
        linkref      VARCHAR2(10);
    BEGIN
        linkref_list := '8371|223312|53298|213234|END';
    
        WHILE linkref_list <> 'END' LOOP
            linkref := Substr(linkref_list, 1, ( Instr(linkref_list, '|') - 1 ));
    
            IF (SELECT DISTINCT COUNT(*)
                FROM   ms.obj o
                WHERE  o.origobjectid = linkref) > 0 THEN
              dbms_output.Put_line(linkref + ' exists within the database');
            END IF;
    
            linkref_list := Replace(linkref_list, linkref + '|', '');
        END LOOP;
    END;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2011
    Posts
    22
    Thank you for the reply.

    When I attempt to run that script I get the response below. Line 17 looks fine, I tried to enclose the linkref + '|' string in (), but that didn't change anything.

    Error starting at line 17 in command:
    DECLARE
    linkref_list VARCHAR2(4000);
    linkref VARCHAR2(10);
    BEGIN
    linkref_list := '8371|223312|53298|213234|END';

    WHILE linkref_list <> 'END' LOOP
    linkref := Substr(linkref_list, 1, ( Instr(linkref_list, '|') - 1 ));
    IF (SELECT DISTINCT COUNT(*)
    FROM ms.obj o
    WHERE o.origobjectid = linkref) > 0 THEN
    dbms_output.Put_line(linkref + ' exists within the database');
    END IF;

    linkref_list := Replace(linkref_list, linkref + '|', '');
    END LOOP;
    END;
    Error report:
    ORA-06550: line 10, column 13:
    PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

    ( - + case mod new not null others <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> avg
    count current exists 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
    <an alternatively-quoted string literal with character set specification>
    <an alternativ
    ORA-06550: line 12, column 44:
    PLS-00103: Encountered the symbol ")" when expecting one of the following:

    . ( * @ % & - + ; / at for mod remainder rem
    <an exponent (**)> and or group having intersect minus order
    start union where connect || multiset
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:


    I then went with the following
    Code:
    set serveroutput ON
    DECLARE
        linkref_list VARCHAR2(4000);
        linkref      VARCHAR2(10);
        cnt          INT;
    BEGIN
        linkref_list := '8371|223312|53298|213234|END';
    
        WHILE linkref_list <> 'END' LOOP
            linkref := Substr(linkref_list, 1, ( Instr(linkref_list, '|') - 1 ));
            cnt :=SELECT DISTINCT COUNT(*) FROM   ms.obj o WHERE  (o.origobjectid = linkref);
            IF cnt > 0 
            THEN
              dbms_output.Put_line(linkref + ' exists within the database');
            END IF;
    
            linkref_list := Replace(linkref_list, linkref + '|', '');
        END LOOP;
    END;
    Got the following output.
    Error starting at line 2 in command:
    DECLARE
    linkref_list VARCHAR2(4000);
    linkref VARCHAR2(10);
    cnt INT;
    BEGIN
    linkref_list := '8371|223312|53298|213234|END';

    WHILE linkref_list <> 'END' LOOP
    linkref := Substr(linkref_list, 1, ( Instr(linkref_list, '|') - 1 ));
    cnt :=SELECT DISTINCT COUNT(*) FROM ms.obj o WHERE (o.origobjectid = linkref);
    IF cnt > 0
    THEN
    dbms_output.Put_line(linkref + ' exists within the database');
    END IF;

    linkref_list := Replace(linkref_list, linkref + '|', '');
    END LOOP;
    END;
    Error report:
    ORA-06550: line 10, column 15:
    PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

    ( - + case mod new not null <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> avg
    count current exists 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
    <an alternatively-quoted string literal with character set specification>
    <an alternatively-quo
    ORA-06550: line 11, column 9:
    PLS-00103: Encountered the symbol "IF"
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You did not follow the example. you can't do

    cnt :=SELECT DISTINCT COUNT(*) FROM ms.obj o WHERE (o.origobjectid = linkref);

    you have to do

    SELECT count( DISTINCT *)
    into cnt
    FROM ms.obj o
    WHERE (o.origobjectid = linkref);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jul 2011
    Posts
    22
    Quote Originally Posted by beilstwh View Post
    You did not follow the example. you can't do

    cnt :=SELECT DISTINCT COUNT(*) FROM ms.obj o WHERE (o.origobjectid = linkref);

    you have to do

    SELECT count( DISTINCT *)
    into cnt
    FROM ms.obj o
    WHERE (o.origobjectid = linkref);
    anacedent's example didn't have a SELECT INTO.

    I copied and pasted anacedent example, and it provided the first error I provided. The second error is from my attempt to deal with the "PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:" error.

    I believe I followed your direction and executed:

    Code:
    set serveroutput ON
    DECLARE
        linkref_list VARCHAR2(4000);
        linkref      VARCHAR2(10);
        cnt          INTEGER;
    BEGIN
        linkref_list := '8371|223312|53298|213234|END';
    
        WHILE linkref_list <> 'END' LOOP
            linkref := Substr(linkref_list, 1, ( Instr(linkref_list, '|') - 1 ));
            SELECT COUNT(DISTINCT *) INTO cnt FROM ms.obj o WHERE  (o.origobjectid = linkref);
            IF cnt > 0 
            THEN
              dbms_output.Put_line(linkref + ' exists within the database');
            END IF;
    
            linkref_list := Replace(linkref_list, linkref + '|', '');
        END LOOP;
    END;
    It returned the following error:

    Code:
    Error starting at line 2 in command:
    DECLARE
        linkref_list VARCHAR2(4000);
        linkref      VARCHAR2(10);
        cnt          INTEGER;
    BEGIN
        linkref_list := '8371|223312|53298|213234|END';
    
        WHILE linkref_list <> 'END' LOOP
            linkref := Substr(linkref_list, 1, ( Instr(linkref_list, '|') - 1 ));
            SELECT COUNT(DISTINCT *) INTO cnt FROM ms.obj o WHERE  (o.origobjectid = linkref);
            IF cnt > 0 
            THEN
              dbms_output.Put_line(linkref + ' exists within the database');
            END IF;
    
            linkref_list := Replace(linkref_list, linkref + '|', '');
        END LOOP;
    END;
    Error report:
    ORA-06550: line 10, column 31:
    PL/SQL: ORA-00936: missing expression
    ORA-06550: line 10, column 9:
    PL/SQL: SQL Statement ignored
    06550. 00000 -  "line %s, column %s:\n%s"
    *Cause:    Usually a PL/SQL compilation error.
    *Action:
    Am I not allowed to do linkref := either?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    consider building procedure 1 line at a time so you can learn about correct syntax by trial & error
    instead of actually Reading The Fine Manual .

    Contents

    Ask Tom Home contain many fine coding examples.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You are, but not like that. In Oracle, strings are "appended" one to another using the concatenation operator - a double pipe sign -> ||.

    So, try
    Code:
    linref_list := replace(linkref_list, linkref || '|', '');

  8. #8
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Littlefoot View Post
    In Oracle, strings are "appended" one to another using the concatenation operator - a double pipe sign -> ||
    Just as in any other standard compliant DBMS

  9. #9
    Join Date
    Jul 2011
    Posts
    22
    Thank you Littlefoot. There are still other things wrong with the script. I'll try to work them out, after I get done RTFM.

Posting Permissions

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