Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2004
    Posts
    60

    Unanswered: Creating Table inside a Stored Procedure and Using it

    Hi

    I want to create a Table dynamically inside a stored procedure and use the values in the table for many other SQLs inside the stored procedure. I was able to create the table using execute immediate but that table cannot be referenced in the SP. I am getting the following error.

    Code:
    Error(74,120): PL/SQL: ORA-00942: table or view does not exist
    Can any one suggest how to go about doing it in Oracle.

    Regards

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    A table made using dynamic SQL can only be utilized via dynamic SQL.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You are most probably wrong using that approach; in Oracle, tables are created once (from SQL*Plus), and used many times (when necessary, including your PL/SQL script). Just in case, if you didn't hear about that feature, you might even use a global temporary table (maybe it'll be interesting for you):
    Quote Originally Posted by Oracle
    ... Oracle can create temporary tables to hold session-private data that exists only for the duration of a transaction or session.

  4. #4
    Join Date
    Oct 2004
    Posts
    60
    yes, I had thought about Global temp tables also, But that needs to be created outside the Stored Procedure for using it in the SQL inside the SP. It would be nice to have CTE sort of functionality inside SP which can be used multiple times.
    Last edited by edwin_fredrick; 02-01-12 at 03:08.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    That's the point.

    Why do you insist on creating tables in stored procedures? Oracle isn't designed to work like that, it is not MS SQL Server (which, as far as I read / heard) works that way.

  6. #6
    Join Date
    Oct 2004
    Posts
    60
    As long as i am able to store result set of a query and use it in my SQLs inside the SP instead of running the same query multiple times.

    Any how, I guess we just have to go with Global Temp Tables in Oracle. Just wanted keep the code as common as possible between databases.

    Thanks for your valuable comments.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm not sure I understood what you said.

    Presume that you create that table only once (regardless it is a global temporary table or not; maybe you don't need it at all, I just told you that it exists).

    Yes, you can store result set of your query into a table that was pre-created. You can use it in your SQLs in your stored procedures any time. Kind of a "regular" process in a stored procedure would be:
    • you DO NOT create a table in a stored procedure
    • insert data into a pre-created table
    • do whatever you want to do with it
    • if you don't need it any more, delete data from a table (or even truncate the table)
    • you DO NOT drop a table

    Next time the procedure is executed, it re-uses the same table again.

    What query is it that you don't want to run "multiple times"?

  8. #8
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by edwin_fredrick View Post
    I want to create a Table dynamically inside a stored procedure and use the values in the table for many other SQLs inside the stored procedure. I was able to create the table using execute immediate but that table cannot be referenced in the SP.
    Please show us the full code. How should we know what you are doing in the SP without seeing the code?

    It is most certainly possible to create a table in a stored procedure and use it.

    But - just as the others have pointed out - it's definitely not a good idea.

    Why don't you tell us the reall problem? Maybe there is a better solution to the problem.

  9. #9
    Join Date
    Oct 2004
    Posts
    60
    Yes I Agree, In Oracle it is not possible / not good idea. My Point is, Why would any one want to create a table to store temporary result set who's life cycle starts and ends with in the Stored Procedure.

    Any how as i have mentioned, We are now using GTT.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by edwin_fredrick View Post
    Why would any one want to create a table to store temporary result set who's life cycle starts and ends with in the Stored Procedure.
    Well that's exactly what GTTs are for.
    The fact that the table definition is always available does not do any harm, nor does it have any negative impact.

Posting Permissions

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