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!

Procedure.,

Discussion in 'SQL PL/SQL' started by Vicky, Feb 20, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    How to execute this procedure, where I'm passing table name as input?

    Create or replace procedure p1( emp_name in varchar2, table_name in varchar2, emp_id in number, o out varchar2)
    as
    begin
    execute immediate 'select'|| emp_name ||'into'|| o ||'from'|| table_name ||'where emp_id='|| emp_id ;
    dbms_output.put_line(o);
    end;

    I can compile this procedure, But while executing, I'm getting error as below:

    declare
    x varchar2(50);
    begin
    p1('emp', 'Vicky',5, x);
    dbms_output.put_line(x);
    end;


    Error report:
    ORA-00900: invalid SQL statement
    ORA-06512: at "PORTALADMIN.P1", line 4
    ORA-06512: at line 4
    00900. 00000 - "invalid SQL statement"
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Problem with Execute immediate.

    So you can use like this....

    Create or replace procedure p1( emp_name in varchar2, table_name in varchar2, emp_id in number, o out varchar2)
    as
    begin
    o:= execute immediate 'select'|| emp_name ||'from'|| table_name ||'where emp_id='|| emp_id ;
    dbms_output.put_line(o);
    end;
     
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Tnx dude., The procedure is not compliling.,.,

    Error(5,13): PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following: . ( * @ % & = - + ; < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset The symbol "( was inserted before "IMMEDIATE" to continue.

    Error(5,92): PLS-00103: Encountered the symbol ";" when expecting one of the following: . ( ) , * @ % & = - + < / > at in is mod remainder not rem => <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec as between from using || member submultiset The symbol ")" was substituted for ";" to continue.
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi vicky,

    i am sorry,

    Remove execute immediate in that.

    Create or replace procedure p1( emp_name in varchar2, table_name in varchar2, emp_id in number, o out varchar2)
    as
    begin
    o:= 'select'|| emp_name ||'from'|| table_name ||'where emp_id='|| emp_id ;
    dbms_output.put_line(o);
    end;
     
  5. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Dude I got the o/p as:

    declare
    x varchar2(200);
    begin
    p1('emp_name', 'emp', 1, x);
    dbms_output.put_line(x);
    end;


    anonymous block completed
    selectemp_namefromempwhere emp_id=1
    selectemp_namefromempwhere emp_id=1

    All I need is to create a procedure for the basic sql queries like select, Insert, update & delete.,,.
     
  6. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    And also I've get table name and column name as inputs.,.,
     
  7. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Vky,

    Try with the below Code.

    Code (SQL):

    CREATE OR REPLACE PROCEDURE p1( emp_name IN varchar2, TABLE_NAME IN varchar2, emp_id IN NUMBER, o OUT varchar2)
    AS

    sqlstmt VARCHAR2(300);

    BEGIN

      sqlstmt := ' select '|| emp_name ||' from '|| TABLE_NAME ||' where emp_id = '|| emp_id ;
     
     EXECUTE IMMEDIATE sqlstmt INTO o ;


    dbms_output.put_line(o);


    END;
    /
     

    Regds
    Sambasiva Reddy.K
     
    Vicky likes this.
  8. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Tnk UU so much Sambasiva Reddy., It works.,.:)
     
  9. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Can you do an another help, I need to know what is Dynamic SQL means.,?!
     
  10. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Oh! that's great samba.
     
  11. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Because I've used Dbms_output twice.,.
     
  12. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    How can I execute this procedure, If I use sys_refsursor to return more than 1 row,.?

    CREATE OR REPLACE PROCEDURE p1( emp_name IN varchar2, TABLE_NAME IN varchar2, o OUT sys_refcursor)
    AS

    sqlstmt VARCHAR2(300);

    BEGIN

    sqlstmt := ' select '|| emp_name ||' from '|| TABLE_NAME;

    EXECUTE IMMEDIATE sqlstmt INTO o ;

    END;
     
  13. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Dynamic sql is writing sql statements using variables for things like table names and column names. Unfortunately errors in the code won't be apparent until you try to execute the dynamic statement. Let's change the order of the values passed to your procedure and see what happens:


    Code (SQL):

    SQL> EXEC p1('EMP2','JAMES',7900, :x)
    SELECT 'EMP2' FROM JAMES WHERE emp_id=7900
    BEGIN p1('EMP2','JAMES',7900, :x); END;
    *
    ERROR at line 1:
    ORA-00942: TABLE OR VIEW does NOT exist
    ORA-06512: at "GRIBNAUT.P1", line 6
    ORA-06512: at line 1

    SQL>
     

    The dynamic sql statement couldn't execute because the emp name and table name were reversed in the parameter list. Note that this did NOT generate an error at compile time; this is a disadvantage of dynamic sql.


    Let's re-write your procedure a bit:


    Code (SQL):

    CREATE OR REPLACE PROCEDURE p1( emp_name IN varchar2, TABLE_NAME IN varchar2, emp_id IN NUMBER, o OUT varchar2)
     AS
     sqlstr varchar2(4000):='select '''|| emp_name ||''' from '|| TABLE_NAME ||' where emp_id='|| emp_id ;
     BEGIN
     dbms_output.put_line(sqlstr);
     EXECUTE immediate sqlstr INTO o;
     dbms_output.put_line(o);
     END;
    /
     

    A string variable was created to build the dynamic SQL string so it could be printed to check for errors. Let's run this again with the parameters in the proper order:


    Code (SQL):

    SQL> EXEC p1('JAMES','EMP2',7900, :x)
    SELECT 'JAMES' FROM EMP2 WHERE emp_id=7900
    JAMES


    PL/SQL PROCEDURE successfully completed.


    SQL>

     

    The completed SQL statement, including passed in values, is printed to the screen prior to execution; the value returned by the statement is then printed using the dbms_output.put_line(o); command. In my opinion it's better to build your statement as a string variable so you can see what you wrote and find errors in your syntax.
     
  14. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Now I can easily understand what Dynamic SQl is!!! Thank you very much David Fitzjarrell !!!
     
  15. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Can any 1 tell me, how to execute a procedure, where I used sys_refcursor to display the output.,.?!
     
  16. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Check This, It may useful for you.
     
  17. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Thanks Jagadekara Reddy. But I've to use sys_refcursor to display the output, while I'm getting table_name as input.,.
     
  18. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Show us the code, please.
     
  19. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Sure., It's the procedure below.,. How can I execute the procedure to display the output.,.???!!!

    CREATE OR REPLACE PROCEDURE p1(TABLE_NAME IN varchar2, o OUT sys_refcursor)
    AS
    sqlstmt VARCHAR2(300);
    BEGIN
    sqlstmt := ' select * from '|| TABLE_NAME;
    EXECUTE IMMEDIATE sqlstmt INTO o ;
    END;
     
  20. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This has been answered in another thread.