Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2012
    Posts
    16

    Unanswered: Insert stored procedure by passing table name as a parameter

    Hello
    I have this query
    but it is not work properly
    pls check this and find a solution to this

    create or replace procedure bank_insert_sp
    (
    p_tablename in varchar2,
    p_bankcode in varchar2,
    p_bankname in varchar2,
    p_dist_code in number
    )
    as
    v_tem varchar2(5000);
    begin
    v_tem:='insert into ' || UPPER(p_tablename) || '
    (
    bankcode,
    bankname,
    dist_code
    )
    values
    (
    ' ||p_bankcode || ',
    ' ||p_bankname || ',
    ' ||p_dist_code || '
    )';
    execute immediate v_tem ;
    end bank_insert_sp;

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    what is the error it is generating? how do you know it's not working? did you commit after the insert? You need to use quotes around strings in the insert. ore information
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    The standard way for debugging dynamic SQL is: construct the statement (V_TEM variable in your case) and then print it - you may use e.g. DBMS_OUTPUT. Copy/paste it verbatim and try to run it statically. Examine the error. Examine the statement and fix the failure cause.

    After you will be able to spot the error, just stop hardcoding variable values into V_TEM, but bind them instead:
    http://www.akadia.com/services/ora_bind_variables.html

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    try

    Code:
    create or replace procedure bank_insert_sp
      (
     p_tablename in  varchar2,
     p_bankcode in varchar2,
     p_bankname in varchar2,
    p_dist_code in number
    )
      as
      v_tem  varchar2(5000);
     begin
    v_tem:='insert into ' || UPPER(p_tablename) || ' 
    (
    bankcode,
    bankname,
    dist_code
    )
    values
    (
    ''' ||p_bankcode || ''',
    ''' ||p_bankname || ''',
    ' ||p_dist_code || '
    )';
    execute  immediate v_tem ;
    end  bank_insert_sp;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by beilstwh View Post
    try
    What about being kind to your shared pool and CPU and not to propose non-scalable solution? There is more effective way described (with examples) in the link I posted...

    Also, be prepared for another tweaking of your code, just after the bank name will contain single quote (').

  6. #6
    Join Date
    Feb 2012
    Posts
    16

    Reply

    Thank you for your kind information regarding the stored procedure for the insert query
    I have more queries to clarify hope u will help me
    I paste the Query n below


    create or replace procedure bank_search_sp
    (
    p_tablename in varchar2,
    p_searchname in varchar2,
    p_bankcode out varchar2,
    p_bankname out varchar2,
    p_dist_code out number
    )
    as
    v_tem varchar2(5000);
    begin
    v_tem :='select bankcode,bankname,dist_code from ' || UPPER (p_tablename) || '
    where bankname like '''|| p_searchname||'';
    execute immediate v_tem into p_bankcode,p_bankname,p_dist_code using p_searchname ;
    end bank_search_sp;



    This is a stored procedure for searching data from table .Here the procedure is created but it will not work properly.I hope u will consider this also

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by kannama View Post
    I paste the Query n below
    I doubt it - the count of single quotes around P_SEARCHNAME is wrong. Have you not considered to bind that variable(s) instead? Why? You already use INTO clause in EXECUTE IMMEDIATE.
    Quote Originally Posted by kannama View Post
    Here the procedure is created but it will not work properly.I hope u will consider this also
    Alas! yet another mysterious "not work properly" Oracle behaviour. You are very unlucky to fall into such a dreadful situation; in fact I never faced it, Oracle did not document it anywhere, so I cannot assist more.
    Besides, without defining, what "work properly" means, especially when multiple rows satisfy that condition, it is impossible to suggest anything.

Posting Permissions

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