Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Problems running script in SQL Plus.

    We are trying to execute the following script in an sql file through SQL Plus command line (extra code ommitted):
    Code:
    UPDATE TICKLER
    SET ASSIGNEDTO = 'PublicInquiriesAssistant'
    WHERE ASSIGNEDTO = 'PublicInquiresAssistant';
    COMMIT;
    
    CREATE OR REPLACE  PACKAGE BODY "MCCH"."PKG_CASE"  AS
    
      PROCEDURE P_U_LEADSMISSINGCASES (V_HORIZONDATE IN DATE) IS
      BEGIN
        INSERT INTO TICKLER
               (REFERENCEID,
               REFERENCETABLE,
    	   .
    	   .
    	   .
               STATUS,
               ASSIGNEDTO)
        SELECT CASEID AS REFERENCEID,
               'CASE',
               'LEADSUpdate' AS REFERENCETYPE,
               CASENUMBER AS REFERENCEDESCRIPTION,
               SYSDATE AS REMINDERDATE,
               1 AS SYSTEMGENERATED,
               SYSDATE AS DUEDATE,
               'Verify case ' || CASENUMBER || ': Missing from LEADS on ' || SYSDATE ||'.' AS TITLE,
               'Case is missing from LEADS on ' || SYSDATE || '.' AS DESCRIPTION,
               'Open',
               'PublicInquiriesAssistant'
       FROM    CASE
       WHERE   (LEADSVERIFIEDDATE < V_HORIZONDATE OR LEADSVERIFIEDDATE IS NULL)
               AND LEADSMISSINGDATE IS NULL
               AND STATUS IN('PrivateOpen', 'PublicOpen', 'Active', 'Inactive');
    
       UPDATE  CASE
       SET     LEADSMISSINGDATE = SYSTIMESTAMP
       WHERE   (LEADSVERIFIEDDATE < V_HORIZONDATE OR LEADSVERIFIEDDATE IS NULL)
               AND LEADSMISSINGDATE IS NULL
               AND STATUS IN('PrivateOpen', 'PublicOpen', 'Active', 'Inactive');
    
      COMMIT;
    
      END P_U_LEADSMISSINGCASES;
    
      PROCEDURE P_S_CASESEARCH
            (V_VICTIM IN NUMBER DEFAULT 0,
            V_COMPANION IN NUMBER DEFAULT 0,
            V_SUSPECT IN NUMBER DEFAULT 0,
    	.
    	.
    	.
            V_VIN IN VARCHAR2 DEFAULT NULL,
            V_LICENSE IN VARCHAR2 DEFAULT NULL,
            O_CASECURSOR OUT C_CASE) IS
      BEGIN
    
        OPEN O_CASECURSOR FOR
            SELECT  DISTINCT CASE.CASEID
            FROM    CASE
                    LEFT OUTER JOIN PERSON ON CASE.CASEID = PERSON.CASEID
                    LEFT OUTER JOIN VEHICLE ON CASE.CASEID = VEHICLE.CASEID
            WHERE   ((V_VICTIM = 1 AND PERSON.ROLE = 'VICTIM')
                        OR (V_COMPANION = 1 AND PERSON.ROLE = 'COMPANION')
                        OR (V_SUSPECT = 1 AND PERSON.ROLE = 'SUSPECT')
                        OR (COALESCE(V_VICTIM, 0) + COALESCE(V_COMPANION, 0) + COALESCE(V_SUSPECT, 0) = 0))
                    AND (UPPER(CASE.CASENUMBER) = UPPER(V_CASENUMBER) OR V_CASENUMBER IS NULL)
                    AND (CASE.STATUS = V_CASESTATUS OR V_CASESTATUS IS NULL)
    		.
    		.
    		.
                    AND (VEHICLE.VIN LIKE '%' || V_VIN || '%' OR V_VIN IS NULL)
                    AND (VEHICLE.LICENSE LIKE '%' || V_LICENSE || '%' OR V_LICENSE IS NULL);
      END P_S_CASESEARCH;
    END PKG_CASE;
    The first part, the UPDATE statement, executes correctly indicating rows updated and commit complete. The second part, replacing the package body, does not execute, and SQL Plus just returns a prompt for the next line number. We have tried finishing the script with backslash (/), commit;, disconnect, exit, stop, and quit, but nothing seems to get this portion of the script to execute.
    Anyone know why this may be happening?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Hm ... you are talking about a "backslash" and typing a "slash" sign (/). Which one did you use? Slash should be OK - /.

    Does package specification exist? It should - you can not compile package body without its specification.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, used slash (/), not backslash.
    Yes. Package already exists. We are just changing a hard-coded value in it.

    Also, the script ran succesfully and without errors using SQL Worksheet. It is only through SQL Plus that we cannot get it to work. We have a client that insists (for some reason I cannot fathom) on running all scripts through SQL Plus command line.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Honestly, I have no idea ... actually, an idea or two more: what happens if you rename the package body - instead of "MCCH"."PKG_CASE", use only PKG_CASE. Furthermore, what happens if you omit UPDATE statement that is run before package body recreation? Just leave the package in the script file (and end it with the slash!).

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, tried running the Update and package parts independently. Update completes, but the package part returns to a command line prompt that we can't even exit out of. We end up having to close the DOS window to get out of it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    try adding SET DEFINE OFF at the top?
    -cf

  7. #7
    Join Date
    Mar 2004
    Posts
    370
    Hi,
    Sorry if this is dumb try! First of all I interested in:
    Code:
    ...
    O_CASECURSOR OUT C_CASE) IS
      BEGIN
    
        OPEN O_CASECURSOR FOR
            SELECT  DISTINCT CASE.CASEID
            FROM    CASE
                    LEFT OUTER JOIN P....
    Is that C_CASE a public type in your specification?Just curious!

    Second: since your code is running successfuly under SQL Worksheet it seems that there is a problem with SQL*Plus only.I have seen only once that the chr(0) in source caused a strange error in SQL*Plus and it was just because that this product is written in C and chr(0) has meaning for C! I found this in asktom.Maybe this can help

Posting Permissions

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