Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Red face Unanswered: stored procedure works in NT and not in UNIX

    ------------------------------------------------------------------------
    this works in NT just fine, and in Unix, Oracle, this doesn't compile, citing a "PLS-00103" error, at the "OPEN recCursor FOR stringSQL;" line, namely the SQL string that contains the SQL command. Please help!

    -Anatoly

    P.S. - split is a custom function I wrote, it works fine, I've tested it before. It splits a delimited string to table of integers, which I parse and make a string out of.

    This is the procedure, with the pertinent declarations:
    ------------------------------------------------------------------------
    This is the procedure, with the pertinent declarations:
    TYPE T_CURSOR IS REF CURSOR;
    TYPE T_CHAR IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER;
    TYPE T_INT IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;

    PROCEDURE MULTI_TRAIN
    (
    User_String IN VARCHAR,
    Role_String IN VARCHAR,
    App_ID IN INTEGER,
    Train_Date IN VARCHAR,
    intReturn OUT INTEGER
    )
    IS
    userArray T_INT;
    roleArray T_INT;
    stringSQL VARCHAR2(300);
    recCursor T_CURSOR;
    curLastName VARCHAR2(50);
    curFirstName VARCHAR2(50);
    countInt INTEGER;
    stringInt VARCHAR2(30);
    BEGIN
    countInt := 0;
    roleArray := split(Role_String,',');
    stringSQL := 'SELECT First_Name,Last_Name FROM USER_PROFILE WHERE PROFILE_ID IN (' || User_String || ')';

    OPEN recCursor FOR stringSQL;
    LOOP
    FETCH recCursor INTO curFirstName, curLastName;
    EXIT WHEN recCursor%NOTFOUND;
    FORALL i IN roleArray.First..roleArray.Last
    INSERT INTO TRAINING_USERS (MT_ID,FIRST_NAME,LAST_NAME,ROLE_ID,APP_ID,TRAININ G_COMPLETED,TRAINING_DATE) VALUES (SEQ_MT.NEXTVAL, curFirstName, curLastName, roleArray(i), App_ID,'True',TO_DATE(Train_Date, 'MM-DD-YYYY hh:mi:sspm'));
    DELETE FROM TRAINING_USERS WHERE TRAINING_DATE = TO_DATE(Train_Date, 'MM-DD-YYYY hh:mi:sspm') AND ROLE_ID IS NULL;
    countInt := countInt + 1;
    END LOOP;
    intReturn := countInt;
    END MULTI_TRAIN;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: stored procedure works in NT and not in UNIX

    Originally posted by ageyfman
    ------------------------------------------------------------------------
    this works in NT just fine, and in Unix, Oracle, this doesn't compile, citing a "PLS-00103" error, at the "OPEN recCursor FOR stringSQL;" line, namely the SQL string that contains the SQL command. Please help!

    -Anatoly

    P.S. - split is a custom function I wrote, it works fine, I've tested it before. It splits a delimited string to table of integers, which I parse and make a string out of.

    This is the procedure, with the pertinent declarations:
    ------------------------------------------------------------------------
    This is the procedure, with the pertinent declarations:
    TYPE T_CURSOR IS REF CURSOR;
    TYPE T_CHAR IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER;
    TYPE T_INT IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;

    PROCEDURE MULTI_TRAIN
    (
    User_String IN VARCHAR,
    Role_String IN VARCHAR,
    App_ID IN INTEGER,
    Train_Date IN VARCHAR,
    intReturn OUT INTEGER
    )
    IS
    userArray T_INT;
    roleArray T_INT;
    stringSQL VARCHAR2(300);
    recCursor T_CURSOR;
    curLastName VARCHAR2(50);
    curFirstName VARCHAR2(50);
    countInt INTEGER;
    stringInt VARCHAR2(30);
    BEGIN
    countInt := 0;
    roleArray := split(Role_String,',');
    stringSQL := 'SELECT First_Name,Last_Name FROM USER_PROFILE WHERE PROFILE_ID IN (' || User_String || ')';

    OPEN recCursor FOR stringSQL;
    LOOP
    FETCH recCursor INTO curFirstName, curLastName;
    EXIT WHEN recCursor%NOTFOUND;
    FORALL i IN roleArray.First..roleArray.Last
    INSERT INTO TRAINING_USERS (MT_ID,FIRST_NAME,LAST_NAME,ROLE_ID,APP_ID,TRAININ G_COMPLETED,TRAINING_DATE) VALUES (SEQ_MT.NEXTVAL, curFirstName, curLastName, roleArray(i), App_ID,'True',TO_DATE(Train_Date, 'MM-DD-YYYY hh:mi:sspm'));
    DELETE FROM TRAINING_USERS WHERE TRAINING_DATE = TO_DATE(Train_Date, 'MM-DD-YYYY hh:mi:sspm') AND ROLE_ID IS NULL;
    countInt := countInt + 1;
    END LOOP;
    intReturn := countInt;
    END MULTI_TRAIN;
    Which verion(s) of Oracle?
    Which flavor of Unix & version?
    Which version of NT?
    Does the last line in the source file contain a single slash '/' character.

Posting Permissions

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