Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    11
    Rep Power
    0

    Table as parameter in plsql


    Hi
    I'm a new to plsql, I have been trying to use table as parameter in store procedure using following code.

    CREATE or replace PROCEDURE moePAPPTGRP
    (
    XFILE IN OUT VARCHAR2
    ) as

    BEGIN
    execute immediate 'update ' || xfile || ' set apptgrp=01';
    END MOEPAPPTGRP;
    /

    I save as apptgrp.sql and execute it. There is no error. But when I run in command window like that
    SQL> exec moepapptgrp('payhr'), I got an error as below.

    begin moepapptgrp('payhr'); end;

    ORA-06550: line 1, column 19:
    PLS-00363: expression 'payhr' cannot be used as an assignment target
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored


    Please advise how can I solve this dynamic issue.

    Thank you in advance
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,378
    Rep Power
    391
    It is beacuse the parameter XFILE is specified as IN OUT. It is sufficient that it is an IN parameter only.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    11
    Rep Power
    0

    Thank you


    Originally Posted by swampBoogie
    It is beacuse the parameter XFILE is specified as IN OUT. It is sufficient that it is an IN parameter only.
    Thank you so much, it did work. I'm so very happy as this is my first script.
    Thanks again
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    11
    Rep Power
    0
    Sorry to start this problem again. I'm trying to use bind variable in procedure as I have an error using ie fpt<>P
    here is my procedure . Could you please help me to correct this issue using mfpt?
    Thanks you

    Here is error message and procedure
    ORA-06550: line 1, column 7:
    PLS-00905: object TEST.OMBAPPTGRP is invalid
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored


    CREATE OR REPLACE PROCEDURE OMBAPPTGRP
    (
    XFILE IN VARCHAR2
    ) as
    BEGIN

    variable mfpt varchar(1);
    exec :mfpt := 'P';

    execute immediate 'update ' || xfile || ' set apptgrp=46' || 'where reptrc=6269' || 'and og=trim(0||6)' || 'and trim(a_jc)=2876';

    execute immediate 'update ' || xfile || ' set apptgrp=41' || 'where reptrc=6110' || 'and og=trim(0||6)' || 'and substr(a_jc,1,1)=1' ||'and fpt<>':mfpt'';
    commit;
    END OMBAPPTGRP;
    /
  8. #5
  9. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    848
    Rep Power
    387

    Cool


    variable mfpt varchar(1); is not a valid pl/sql statement.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    11
    Rep Power
    0
    Originally Posted by LKBrwn_DBA
    variable mfpt varchar(1); is not a valid pl/sql statement.
    Then , how can I declare the mfpt in this procedure? please advise.
    Thank you
  12. #7
  13. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    848
    Rep Power
    387

    Cool


    Code:
    CREATE OR REPLACE PROCEDURE OMBAPPTGRP
    (
    XFILE IN VARCHAR2
    ) as
    mfpt varchar(1);
    
    BEGIN
    
    mfpt := 'P';
    
    execute immediate . . . . . . .
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    11
    Rep Power
    0
    Originally Posted by LKBrwn_DBA
    Code:
    CREATE OR REPLACE PROCEDURE OMBAPPTGRP
    (
    XFILE IN VARCHAR2
    ) as
    mfpt varchar(1);
    
    BEGIN
    
    mfpt := 'P';
    
    execute immediate . . . . . . .
    Hi
    I tried following quote, still doesn't work
    CREATE OR REPLACE PROCEDURE OMBAPPTGRP
    (
    XFILE IN VARCHAR2
    ) as
    mftp varchar(1);
    BEGIN
    mfpt:='P';

    I tried 3 ways in here , all get error message, if I leave the mfpt criteria, it works well up to a_jc

    1. execute immediate 'update ' || xfile || ' set apptgrp=41' || 'where reptrc=6110' || 'and og=trim(0||6)' || 'and substr(a_jc,1,1)=1' || 'and fpt<>mfpt';

    2. execute immediate 'update ' || xfile || ' set apptgrp=41' || 'where reptrc=6110' || 'and og=trim(0||6)' || 'and substr(a_jc,1,1)=1' || 'and fpt<>:mfpt';

    3. execute immediate 'update ' || xfile || ' set apptgrp=41' || 'where reptrc=6110' || 'and og=trim(0||6)' || 'and substr(a_jc,1,1)=1' || 'and fpt<>mfpt' using mfpt;

    commit;
    END OMBAPPTGRP;

    Thank you
  16. #9
  17. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    848
    Rep Power
    387

    Cool


    You need to watch out for spaces between the statement words.
    Code:
    execute immediate 'update ' || xfile 
                   || '   set apptgrp=41 ' 
                   || ' where reptrc=6110' 
                   || '   and og=trim(0||6)' 
                   || '   and substr(a_jc,1,1)=1' 
                   || '   and fpt<> :1 '
                using mfpt;
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    11
    Rep Power
    0
    mfpt is still doesn't work for me. So I changed to xfpt and work well. Thank you so much for your help. Otherwise, I won't know what to do.

    CREATE OR REPLACE PROCEDURE OMBAPPTGRP
    (
    XFILE IN VARCHAR2
    ) as
    xfpt varchar2(1);

    BEGIN

    xfpt:='P';

    execute immediate 'update ' || xfile || ' set apptgrp=41 '|| 'where reptrc=6110'|| ' and og=trim(0||6)' || ' and substr(a_jc,1,1)=1' || ' and fpt!= :xfpt ' using xfpt;
    commit;
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    11
    Rep Power
    0

    Question


    Originally Posted by pusuma
    mfpt is still doesn't work for me. So I changed to xfpt and work well. Thank you so much for your help. Otherwise, I won't know what to do.

    CREATE OR REPLACE PROCEDURE OMBAPPTGRP
    (
    XFILE IN VARCHAR2
    ) as
    xfpt varchar2(1);

    BEGIN

    xfpt:='P';

    execute immediate 'update ' || xfile || ' set apptgrp=41 '|| 'where reptrc=6110'|| ' and og=trim(0||6)' || ' and substr(a_jc,1,1)=1' || ' and fpt!= :xfpt ' using xfpt;
    commit;

    I want to start this again. what if I want to populate only full time people how can I filter out?

    I tried this, but didn't work, please help me again, Thank you

    CREATE OR REPLACE PROCEDURE OMBAPPTGRP
    (
    XFILE IN VARCHAR2
    c1 out TYPES.REF_CURSOR
    ) as

    BEGIN

    open c1 for select * from payhr where fpt="F";

    execute immediate 'update ' || xfile || ' set apptgrp=26' || 'where reptrc=2700'|| 'and og=trim(0||6)' || 'and a_jc=trim(1813)' || 'and dept='2000';
    execute immediate 'update ' || xfile || ' set apptgrp=26' || 'where reptrc=2700'|| 'and og=trim(0||7)' || 'and a_jc=trim(1813)' || 'and dept='2000';

    commit;
    end ombapptgrp;
    /
  22. #12
  23. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    848
    Rep Power
    387
    And I said:
    You need to watch out for spaces between the statement words.
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    11
    Rep Power
    0
    Originally Posted by LKBrwn_DBA
    And I said:
    Hi

    I think problem is not the spaces, the cursor I used. I'm not sure I can use like this or not?
    If I take out the cusor command, my script works fine. Successfully executed.
    If I add the cusor command I got error as below

    begin TAPPOINT('PAYHR'); end;

    ORA-06550: line 1, column 7:
    PLS-00905: object TEST.TAPPOINT is invalid
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    Below is my script

    CREATE OR REPLACE PROCEDURE tAPPOINT
    (
    XFILE IN VARCHAR2, c1 out ref_cursor
    ) as


    BEGIN

    open c1 for ' select * from ' || xfile || ' WHERE FPT=F';

    execute immediate 'update ' || xfile || ' set apptgrp=null';
    commit;
    execute immediate 'update ' || xfile || ' set apptgrp=46' || 'where reptrc=6269' || 'and og=trim(0||6)' || 'and trim(a_jc)=2876';
    commit;
    end tAPPOINT;
    /
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2004
    Posts
    15
    Rep Power
    0
    What is the datatype of the column FPT in your table? Where you have this line:
    open c1 for ' select * from ' || xfile || ' WHERE FPT=F';
    I'm assuming this is a character-type of datatype column (i.e. CHAR or VARCHAR or VARCHAR2). With that said then you need to escape the single quote so that the SQL engine understands it.

    your line should be

    OPEN c1 FOR 'SELECT * FROM ' || xfile || ' WHERE fpt = ''F''';
    There is no space in between the single quote and the letter "S" in the SELECT.
    There are 2 single quotes before the string "F", followed by 3 single quotes after it.

    Try that.
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    11
    Rep Power
    0
    Originally Posted by jaramill
    What is the datatype of the column FPT in your table? Where you have this line:
    I'm assuming this is a character-type of datatype column (i.e. CHAR or VARCHAR or VARCHAR2). With that said then you need to escape the single quote so that the SQL engine understands it.

    your line should be



    There is no space in between the single quote and the letter "S" in the SELECT.
    There are 2 single quotes before the string "F", followed by 3 single quotes after it.

    Try that.
    I did try but still doesn't work for me. Yes, fpt is Varchar2

    I changed my code as below



    CREATE OR REPLACE PROCEDURE OMBAPPTGRP
    (
    XFILE IN VARCHAR2
    ) is

    TYPE curtype IS REF CURSOR;
    appt_cur curtype;
    xfpt varchar2(1);


    begin
    xfpt:='F';

    --OPEN c1 FOR 'SELECT * FROM ' || xfile || ' WHERE fpt = ''F''';

    open appt_cur for 'SELECT * FROM ' || xfile || ' where fpt!= :xfpt ' using xfpt;

    execute immediate 'update' || xfile || 'set apptgrp=26' || 'where reptrc=2700'|| 'and og=trim(0||6)' || 'and a_jc=trim(1813)' || 'and dept=2000';
    execute immediate 'update' || xfile || 'set apptgrp=26' || 'where reptrc=2700'|| 'and og=trim(0||7)' || 'and a_jc=trim(1813)' || 'and dept=2000';

    commit;
    end ombapptgrp;
    /

    Here is my errors
    SQL> alter procedure test.ombapptgrp recompile;

    alter procedure test.ombapptgrp recompile

    ORA-00922: missing or invalid option

    SQL> show errors
    No errors for PROCEDURE TEST.OMBAPPTGRP

    SQL> exec OMBAPPTGRP('payhr');

    begin OMBAPPTGRP('payhr'); end;

    ORA-00900: invalid SQL statement
    ORA-06512: at "TEST.OMBAPPTGRP", line 18
    ORA-06512: at line 1

    Thank you
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo