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!

How to pass schema name as parameter in stored procedure

Discussion in 'General' started by bis, Aug 24, 2012.

  1. bis

    bis Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi
    I have need to pass schema name as parameter to a stored procedure.
    But I end up with error ORA00942: table or view does not exist.
    I goggled a lot but dint find any solution. Please help.
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Which table you are using ???
     
  3. bis

    bis Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Thanks for the reply Kiran
    Actually in our application we are writing a Store procedure (SP) in One schema and referring same SP for all other schema.
    Consider i have to find the stock of a item in different schema( 1 schema for 1 client)
    Then
    select * from abc.stock_table where itemid=xxx;

    In this querry i want to replace abc with different schema names.
    I hope you got my point.

    Regards
    Shamanth
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Shamanth,

    Can you please explain your requirement clearly.
     
  5. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi Bis,

    Base tables are created in respective module (say Inventory , Eam etc.) and synonyms are created in Apps schema.
    but to one instance we can use only Apps schema i.e synonyms.

    Are you using any other schema other than Apps ??
    Total from how many instances are available ??

    Let me know.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You will need to write dynamic sql to do what you want in this procedure as direct substitution will not work. A working example is shown below for passing the schema name to a procedure and returning the count from a given table:

    Code (SQL):
    SQL> CREATE OR REPLACE PROCEDURE get_other_schema_ct(p_schema IN varchar2)
      2  IS
      3
      4          v_rct NUMBER;
      5          v_sqltxt varchar2(4000);
      6
      7  BEGIN
      8          v_sqltxt:='select count(*) from '||p_schema||'.yamno';
      9
     10          EXECUTE immediate v_sqltxt INTO v_rct;
     11
     12          dbms_output.put_line('Row count from '||p_schema||'.yamno is '||v_rct);
     13  END;
     14  /
     
    PROCEDURE created.
     
    SQL>
    SQL> SET serveroutput ON SIZE 1000000
    SQL>
    SQL> EXEC get_other_schema_ct('BONG');
    ROW COUNT FROM BONG.yamno IS 1700
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>