1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Create Or Replace Package only if condition matches

Discussion in 'SQL PL/SQL' started by LenaMi, Apr 8, 2011.

  1. LenaMi

    LenaMi Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Dears,

    i am new member here so, Hello World! :eek:)
    My question is PL/SQL related.
    I have a file packageX.spc and inside code to 'create or replace package X as ...'
    I run this file through sqlplus after connecting to proper db_schema/password@db_service as:
    SQL>@packageX.spc

    the purpose though is to create this package in the database schema, only if a condition is satisfied.

    i have tried to do it as below but 'exit' or 'quit' give error, whereas 'return' just exits the begin - end block.

    file packageX.spc
    -------------------

    begin
    if condition is TRUE then
    return; --- here i want it to exit whole file during execution, not the block, since then it will still try to create the package
    end if;
    end;
    /

    create or replace package X as...

    < code... >

    end;
    /

    Any ideas please?
    thanks,
    LeMi
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Use raise_application_error to exit the executable block and transfer control to the exception handler. Code a null exception handler and you should be done.
     
  3. LenaMi

    LenaMi Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi! thanks for reply

    can you please give me example, based on my file code?

    thanks
     
  4. LenaMi

    LenaMi Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi again.

    u mean something like this?
    will that return out of the file but i do nt wish to get any errors at command prompt.

    In fact i am calling in a script, a number of packages to be deployed, among them i want this package to be deployed only if condition matches, but i do not wish to stop the whole script execution that applies rest packages.
    I just want it to return from the packageX file and continue with rest.



    file packageX.spc
    -------------------

    begin
    if condition is TRUE then
    raise_application_error(0, 'Stop execution');
    end if;

    EXCEPTION
    WHEN OTHERS THEN null;

    end;
    /

    create or replace package X as...

    < code... >

    end;
    /
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Apparently I didn't fully understand your situation as that won't stop packageX from being created. You'll need to go outside of PL/SQL to control when this package is created such as using a shell script to drive package creation; you can query the database and return the condition into shell variable then use that in a shell script if construct to either call the package creation script or pass it by.
     
  6. debasisdas

    debasisdas Active Member

    Messages:
    46
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    Bangalore, India
    try modifying this sample for your own use.

    Code (Text):
    REM ======================================
    REM    Version notes
    REM ======================================
    REM 28-DEC-2010 - Das- Developed for test
           


    @echo off

    set nls_lang=american_america.al32utf8

    set install_file=INSTALL.LOG
    set timestamp=%DATE% %TIME%

    CLS

    REM ======================================
    REM    Report header
    REM ======================================
    echo        =============================================================          
    echo                     Execution of test configuration      
    echo        =============================================================          

    echo.

    :app_CONN
    set /P app_user=       Please enter app Account Name     :
    set /P app_pass=       Please enter app Account Password :
    set /P app_conn=       Please enter app Connect String   :

    sqlplus -S -L %app_user%/%app_pass%@%app_conn% @test_check_connection.sql | FIND /C /I "connection successful" > check
    set /P md_conn_status=<check

    if %md_conn_status% NEQ 0 (
          echo.
          echo        Connection to app schema SUCCESS
          echo.
          echo.
    ) else (
          echo.
          echo        Connection to app schema FAIL.
          echo        Please repeat entering app connection details
          echo.        
          goto app_CONN
       )
    )

    sqlplus -S %app_user%/%app_pass%@%app_conn% @test_Dis_Del.sql
    sqlplus -S %app_user%/%app_pass%@%app_conn% @test_Dist_val.sql
    sqlplus -S %app_user%/%app_pass%@%app_conn% @test_Dist_Inf_proc.sql


    set /P H_load=       Enter P for production envrionment T for Testing (P/T)     :

    if %H_load% EQU P (

     (
     echo conn %app_user%/%app_pass%@%app_conn% ;
     echo exec proc_company_unit_insert(%'%%H_load%'%^^^);
     ) | sqlplus -s /nolog
     
       

    )

    if %H_load% EQU p (

     
     (
     echo conn %app_user%/%app_pass%@%app_conn% ;
     echo exec proc_company_unit_insert('%H_load%'^^^);
     ) | sqlplus -s /nolog
     
     

    )


    if %H_load% EQU T (

     (
     echo conn %app_user%/%app_pass%@%app_conn% ;
     echo exec proc_company_unit_insert('%H_load%'^^^);
     ) | sqlplus -s /nolog
     
       

    )

    if %H_load% EQU t (

     (
     echo conn %app_user%/%app_pass%@%app_conn% ;
     echo exec proc_company_unit_insert('%H_load%'^^^);
     ) | sqlplus -s /nolog
     
       

    )

    (
     echo conn %app_user%/%app_pass%@%app_conn% ;
     echo execute execute immediate 'drop procedure proc_insert';
     ) | sqlplus -s /nolog


    :END
    DEL check
    pause
    add the above code to a batch file and run.