Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2013
    Posts
    19

    Unanswered: Calling procedure with inout parameters

    Hi guys,
    can you tell me why my following call function is not wrkng :

    Create procedure drop_schema123(INOUT name_schema varchar(20), INOUT table_schema varchar (30))
    Begin

    call sysproc.admin_drop_schema ('sagar123', NULL, name_schema, table_schema);
    CALL SYSPROC.ADMIN_CMD('DROP SCHEMA name_schema RESTRICT');

    END@


    call drop_schema123('ERRORSCHEMA','SCHEMADROPERR')@

    WRROR QL0443N Routine "*P_SCHEMA" (specific name "") has returned an error
    SQLSTATE with diagnostic text "sqlcode -601:CREATE TABLE
    "ERRORSCHEMA"."SCHEMADROPERR" (OB". SQLSTATE=38000


    i tried with IN parameters too, proc gets created but NOt getting CALLED................
    pls help...

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    This is the the third thread, I believe , you have started for the same issue, though you dress it up with different Subject lines ;-)

    First of all, why do you do a admin_drop_schema and also a drop schema. There is no point in dropping a schema if it has already been dropped.

    DROP SCHEMA is SQL. ADMIN_CMD is for issuing DB2 commands (not SQL). So will not work. In one of the earlier threads, I had mentioned about using dynamic SQL. Did you try it?

    ---
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    CALL SYSPROC.ADMIN_CMD('DROP SCHEMA name_schema RESTRICT');
    This means DROP SCHEMA of which the schema name was 'name_schema'(not the value of name_schema parameter).

    So, this might be nearer to your intention.
    EXECUTE IMMEDIATE 'DROP SCHEMA ' || name_schema || ' RESTRICT';

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It might also help if the documentation was read. I looked in V9.5, V9.7, and V10.1 documentation and neither states that the SYSPROC.ADMIN_CMD Stored Procedure can be used for the DROP SCHEMA command. This is unsupported.

    Like Tonkuma shows, just call the SQL ststement directly.

    Andy

  5. #5
    Join Date
    Mar 2013
    Posts
    19
    Quote Originally Posted by sagar.dafle View Post
    Hi guys,
    can you tell me why my following call function is not wrkng :

    Create procedure drop_schema123(INOUT name_schema varchar(20), INOUT table_schema varchar (30))
    Begin

    call sysproc.admin_drop_schema ('sagar123', NULL, name_schema, table_schema);
    CALL SYSPROC.ADMIN_CMD('DROP SCHEMA name_schema RESTRICT');

    END@


    call drop_schema123('ERRORSCHEMA','SCHEMADROPERR')@

    WRROR QL0443N Routine "*P_SCHEMA" (specific name "") has returned an error
    SQLSTATE with diagnostic text "sqlcode -601:CREATE TABLE
    "ERRORSCHEMA"."SCHEMADROPERR" (OB". SQLSTATE=38000


    i tried with IN parameters too, proc gets created but NOt getting CALLED................
    pls help...

    yes i know its 3rd time that i am asking a questn abt stored proc but this time
    i wanted to use admin_drop_schema and DROP SCHEMA inside stored procedure.....
    anyways, if i remember i told once that after admiN-drop_schema is executed , a schema named "ERRORSCHEMA" will be indirectly created in your database.
    so to drop that i use
    DROP SCHEMA ERRORSCHEMA RESTRICT.........

  6. #6
    Join Date
    Mar 2013
    Posts
    19
    Quote Originally Posted by ARWinner View Post
    It might also help if the documentation was read. I looked in V9.5, V9.7, and V10.1 documentation and neither states that the SYSPROC.ADMIN_CMD Stored Procedure can be used for the DROP SCHEMA command. This is unsupported.

    Like Tonkuma shows, just call the SQL ststement directly.

    Andy
    Thanks, yes you are right, it wont work,..........


    so i modified my Strd proc as

    Create or replace procedure drop_schema()
    LANGUAGE SQL
    Begin

    DECLARE vERRORSCHEMA VARCHAR(20);
    DECLARE vERRORTAB VARCHAR(20);
    DECLARE oRETURNCODE CHAR(1);
    set vERRORTAB ='SCHEMADROPERR';
    set vERRORSCHEMA='ERRORSCHEMA';
    call sysproc.admin_drop_schema ('SAGAR123, NULL, vERRORSCHEMA, vERRORTAB );
    END@

    It is created , but while calling i get :

    call drop_schema()
    SQL0443N Routine "ADMIN_DROP_SCHEMA" (specific name "ADMIN_DROP_SCHEMA") has
    returned an error SQLSTATE with diagnostic text "No schema for error table
    provided". SQLSTATE=38000

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    What version are you on?

    I tried this on DB2 10.1 FP2, and it works, with a minor change (a missing single-quote)
    Code:
    call sysproc.admin_drop_schema ('SAGAR123', NULL, vERRORSCHEMA, vERRORTAB );
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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