Results 1 to 6 of 6

Thread: begin-end block

  1. #1
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    Unanswered: begin-end block

    hello there,

    i'm trying to create a view within a begin end block. when i create the view by executing just the statement it is ok, but not within a begin-end block. does anyone know a workaround, because it has to be within a begin-end block, because it is called in an exception. see example:

    BEGIN
    SELECT * FROM ISH.SAPPLIC WHERE ( SAPPSQL >= 11096 AND SAPPUFMSQL < 11019 );
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    ish.applsp_AddRecordContextToUCT('UMM','Article', 4, 'Menu Artikel', 'Menu Article');
    ish.applsp_AddScreenToUCT('UMM', 'art04', 'Menu Artikelen', 'Menu Articles', 'UMM', 'Article', 'ArtId', 'D');
    ish.applsp_AddDefaultScreen('UMM','Article', 4, -1,'art04');

    CREATE OR REPLACE VIEW ish.applview_CateringList
    AS
    SELECT JobmJobId AS applviewFclJobId, JobmArtId AS applviewFclArtOrEqm, ArtDescr AS applviewFclOmschrijving, JobmDlvTime AS applviewFclDlvTime, JobmQty as applviewFclQty
    FROM ish.JobMat, ish.Article
    WHERE JobmArtId = ArtId AND JobmRecstatus >= 0
    UNION
    SELECT JobeqJobId, JobeqEqmId, EqmDescr, JobeqResFromDate, 1
    FROM ish.JobEquipment, ish.Equipment
    WHERE JobeqEqmId = EqmId AND JobeqRecstatus >= 0;
    /

    UPDATE ish.SAPPLIC SET SAPPUFMSQL = 11019;
    END;
    /

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: begin-end block

    Originally posted by thepercival
    hello there,

    i'm trying to create a view within a begin end block. when i create the view by executing just the statement it is ok, but not within a begin-end block. does anyone know a workaround, because it has to be within a begin-end block, because it is called in an exception. see example:

    BEGIN
    SELECT * FROM ISH.SAPPLIC WHERE ( SAPPSQL >= 11096 AND SAPPUFMSQL < 11019 );
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    ish.applsp_AddRecordContextToUCT('UMM','Article', 4, 'Menu Artikel', 'Menu Article');
    ish.applsp_AddScreenToUCT('UMM', 'art04', 'Menu Artikelen', 'Menu Articles', 'UMM', 'Article', 'ArtId', 'D');
    ish.applsp_AddDefaultScreen('UMM','Article', 4, -1,'art04');

    CREATE OR REPLACE VIEW ish.applview_CateringList
    AS
    SELECT JobmJobId AS applviewFclJobId, JobmArtId AS applviewFclArtOrEqm, ArtDescr AS applviewFclOmschrijving, JobmDlvTime AS applviewFclDlvTime, JobmQty as applviewFclQty
    FROM ish.JobMat, ish.Article
    WHERE JobmArtId = ArtId AND JobmRecstatus >= 0
    UNION
    SELECT JobeqJobId, JobeqEqmId, EqmDescr, JobeqResFromDate, 1
    FROM ish.JobEquipment, ish.Equipment
    WHERE JobeqEqmId = EqmId AND JobeqRecstatus >= 0;
    /

    UPDATE ish.SAPPLIC SET SAPPUFMSQL = 11019;
    END;
    /
    This a new one for me: try to select data from table T. If no data found then create view V... what on Earth is that all about?

    Anyway, to perform any kind of DDL (i.e. create, drop, alter etc.) in PL/SQL requires dynamic SQL, i.e. EXECUTE IMMEDIATE, with the DDL statement as a quoted string.

    Your SELECT is also wrong because it doesn't select INTO anything, and could also potentially raise TOO_MANY_ROWS.

    Finally, the SQL PLus "/" command is meaningless inside a PL/SQL block.

    The corrected version would be:

    Code:
    DECLARE
        x INTEGER;
    BEGIN
    	SELECT 1
    	INTO x
    	FROM ISH.SAPPLIC
    	WHERE ( SAPPSQL >= 11096 AND SAPPUFMSQL < 11019 )
     	AND ROWNUM=1;
    EXCEPTION
    	WHEN NO_DATA_FOUND THEN
    		ish.applsp_AddRecordContextToUCT('UMM','Article', 4, 'Menu Artikel', 'Menu Article');	
    		ish.applsp_AddScreenToUCT('UMM', 'art04', 'Menu Artikelen', 'Menu Articles', 'UMM', 'Article', 'ArtId', 'D');
    		ish.applsp_AddDefaultScreen('UMM','Article', 4, -1,'art04');
    
    		EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ish.applview_CateringList
    		AS
    			SELECT JobmJobId AS applviewFclJobId, JobmArtId AS applviewFclArtOrEqm, ArtDescr AS applviewFclOmschrijving, JobmDlvTime AS applviewFclDlvTime, JobmQty as applviewFclQty
    			FROM ish.JobMat, ish.Article
    			WHERE JobmArtId = ArtId AND JobmRecstatus >= 0 
    				UNION
    				SELECT JobeqJobId, JobeqEqmId, EqmDescr, JobeqResFromDate, 1 
    				FROM ish.JobEquipment, ish.Equipment
    				WHERE JobeqEqmId = EqmId AND JobeqRecstatus >= 0';
    	    
    		UPDATE ish.SAPPLIC SET SAPPUFMSQL = 11019;
    END;

  3. #3
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149
    I knew the syntax was not correct. Thanks anyway. But is was all about the DDL-statement within a BEGIN-END block. We tried the EXECUTE IMMEDIATE, but it can not contain more then 4000 characters. We have stored_procedures and triggers which are bigger and have to be placed within a BEGIN-END block. So is there another way.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by thepercival
    I knew the syntax was not correct. Thanks anyway. But is was all about the DDL-statement within a BEGIN-END block. We tried the EXECUTE IMMEDIATE, but it can not contain more then 4000 characters. We have stored_procedures and triggers which are bigger and have to be placed within a BEGIN-END block. So is there another way.
    Why would you want to create procedures and triggers using EXECUTE IMMEDIATE? Why not just create them?

  5. #5
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    updating

    We want create for example a stored procedure within a BEGIN-END block, because when out customer or our consultants want to update our program only a certain part of the script should be executed. so then we say if NUMBERX < X do something. So then we are in an pl/sql block and we can't create a trigger or stored procedure directly. We can use execute immediate, but then we are restricted to a max of 4000 characters. While we have bigger stored_procedure and triggers this is not a solution. That's why I asked if someone has a workaround or an idea.

    beforehand thanks

    CDK

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: updating

    Originally posted by thepercival
    We want create for example a stored procedure within a BEGIN-END block, because when out customer or our consultants want to update our program only a certain part of the script should be executed. so then we say if NUMBERX < X do something. So then we are in an pl/sql block and we can't create a trigger or stored procedure directly. We can use execute immediate, but then we are restricted to a max of 4000 characters. While we have bigger stored_procedure and triggers this is not a solution. That's why I asked if someone has a workaround or an idea.

    beforehand thanks

    CDK
    So you are trying to use PL/SQL to control an incremental upgrade of the application? I haven't seen that done before. More typically, a version control system like PVCS is used to determine which DDL scripts need to be run to upgrade from one version to another; the appropriate scripts are extracted and run.

    Perhaps rather than try to embed all your proecure definitions in text strings and use EXECUTE IMMEDIATE, you could have your process generate a controlling script to be run in SQL Plus - e.g.

    ...
    IF numberx < x THEN
    -- Need to recreate procedure PROC1
    DBMS_OUTPUT.PUT_LINE( '@proc1.sql' );
    END IF;
    ...

    This will generate output like:

    @proc1.sql
    @proc7.sql
    @package43.sql
    @trig99.sql

    ... which can then be run in SQL Plus, and will in turn run all the specified SQL scripts.

    Or perhaps someone else has some other ideas.

Posting Permissions

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