Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2005
    Posts
    5

    Question Unanswered: Help on temporary tables, rowcount and exec equivalent from SQL to Oracle

    Hi Oracle gurus,

    I am a newbie in Oracle, I have worked previously with SQL Server 2000. I have some queries on how to do specific tasks in Oracle.

    1. Is there an equivalent for "INSERT * INTO #tmpEmployee FROM Employee" in Oracle? If none, how can I create a temporary table and can I create it inside a stored procedure?

    2. How can I set the rows to be retrieved? In SQL, I use SET rowcount [n]

    3. What's the equivalent for Exec(string) in Oracle?

    You see, I am actually trying to create a stored procedure that will concatenate a string based on the parameters passed and then execute it in the end.

    Thanks very much for your help.

    Regards,

    Joey Gutierrez
    Last edited by jpgutierrez18; 01-12-05 at 03:54. Reason: Changed title

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello jpgutierrez18,

    I haven't used SQL Server a lot, but I think I can help you a little...

    1. If you want to populate #tmpEmployee with data from the employee table, then it is :
    Code:
    INSERT INTO #tmpEmployee SELECT * FROM Employee;
    2. You want to return only the first n rows retrieved, right ? Then :
    Code:
    select * from Employee
    where rownum <= n;
    3. Here, a little advice. You should NEVER concatenate a string and execute it (you risk SQL Injection). USE BIND VARIABLES. Have a look here , and BTW I highly recommend you read several articles on AskTom, EXCELLENT site.

    If you have any other question, don't hesitate.

    Regards,

    RBARAER

  3. #3
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Although it is possible to create tables within a procedure using dynamic SQL, it is almost never a good idea.

    If you need temp table logic in Oracle, you can create a Global Temporary Table (GTT) - see under CREATE TABLE in the SQL reference. This is a table you create ONCE (the definition is permanent) but the data you place in it is temporary and private to your session.

  4. #4
    Join Date
    Jan 2005
    Posts
    5
    Thanks for the responses guys

    @RBARAER - I'm sorry but this doesn't work in Oracle, I'm using 9i

    INSERT INTO #tmpEmployee SELECT * FROM Employee;
    Also, I am concatenating the statement server side through the parameter-driven stored procedure. Will there still be a possibility of injection? Thanks.

    @WilliamR - Yeah I've seen the GTT but I am wondering if there's a quicker way like in SQL Because in GTT I have to define each temporary table that I plan to use unlike in SQL you just issue INTO #tmpTableName and there you have it. Thanks, at least there's a way.

    How about my third question? I think I can use Execute Immediate but I'm wondering what's the limit in terms of character count it can execute. And how do you call another stored procedure from inside another stored procedure?

    Your help is surely appreciated

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As of table creation ... perhaps you could do this:

    1. create an empty global temporary table which has the same structure as your original table:

    CREATE GLOBAL TEMPORARY TABLE gtt_emp
    AS SELECT * FROM emp
    WHERE 1 = 2;

    Omit the WHERE clause if you want to have it populated during creation.

    2. later, in your stored procedure, simply populate it using INSERT statement:

    INSERT INTO gtt_emp SELECT * FROM emp;

    (Of course, this is a stupid example ... you can insert any data you want / need.)

  6. #6
    Join Date
    Jan 2005
    Posts
    5
    Thanks Littlefoot, this is just what I'm looking for

    CREATE GLOBAL TEMPORARY TABLE gtt_emp
    AS SELECT * FROM emp


    How about this

    How about my third question? I think I can use Execute Immediate but I'm wondering what's the limit in terms of character count it can execute. And how do you call another stored procedure from inside another stored procedure?
    TIA

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As of your third question: part of it is simple - you call another stored procedure with its name (along with the parameters, if it has any). If this procedure is included into a package, you have to include package's name as well.

    For example, if you have a package called "pkg_example" and a procedure within called "prc_my_procedure", the code you'd use would be:
    Code:
    CREATE PROCEDURE prc_let_me_try
    IS
    BEGIN
       /* some code here */
    
       /* calling another stored procedure now */
       pkg_example.prc_my_procedure; 
    
       /* yet another code here */
    END;
    /
    As of length within EXECUTE IMMEDIATE, it can contain any PL/SQL block. Here are a few examples:
    Code:
    DECLARE
       sql_stmt    VARCHAR2(100);
       plsql_block VARCHAR2(200);
       my_deptno   NUMBER(2) := 50;
       my_dname    VARCHAR2(15) := 'PERSONNEL';
       my_loc      VARCHAR2(15) := 'DALLAS';
       emp_rec     emp%ROWTYPE;
    BEGIN
       sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
       EXECUTE IMMEDIATE sql_stmt USING my_deptno, my_dname, my_loc;
    
       sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
       EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING 7788;
    
       EXECUTE IMMEDIATE 'DELETE FROM dept 
          WHERE deptno = :n' USING my_deptno;
    
       plsql_block := 'BEGIN emp_stuff.raise_salary(:id, :amt); END;';
       EXECUTE IMMEDIATE plsql_block USING 7788, 500;
    
       EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
    
       sql_stmt := 'ALTER SESSION SET SQL_TRACE TRUE';
       EXECUTE IMMEDIATE sql_stmt;
    END;
    As you can see, variable "plsql_block" is type of a VARCHAR2 which can be up to 32767 bytes in a PL/SQL block (while in SQL it can be up to 4000 bytes).
    What if you need more than 32767 bytes? I wouldn't know ... I never needed it.

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Concerning SQL Injection, have a look at the link I gave you, see the post from September 14, 2004 :
    this:
    begin
    ...
    select emp_name into l_var
    from emp where emp_no = var1;

    ....


    is perfection. this:

    create or replace procedure p( p_where_clause in varchar2 )
    is
    l_var varchar2(250);
    begin

    execute immediate 'select emp_name from emp ' || p_where_clause
    into l_var;


    That, that is dangerous if p_where_clause comes from OUTSIDE YOUR SPHERE of control.
    So, it depends how you wrote it : first way EXCELLENT, second way VERY BAD.

    Regards,

    RBARAER

  9. #9
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    CREATE GLOBAL TEMPORARY TABLE gtt_emp
    AS SELECT * FROM emp
    Just so we're clear, you do this in advance when you build your application, NOT using EXECUTE IMMEDIATE.

    Apart from the performance overhead with dynamic DDL, the table name would need to be uniquely generated to avoid ORA-00955: Name is already used by an existing object.

  10. #10
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    One minor point seeing that your coming over from sqlserver is that you dont need stored procedures and temp tables as much as in sqlserver. You may find due to way Oracle does locking and multiversioning, which is very different from sqlserver, you can do the same in straight sql and with better performance.

    Alan

  11. #11
    Join Date
    Jan 2005
    Posts
    5
    Thanks Littlefoot, RBARAER, WilliamR, AlanP for the help.

    @Littlefoot - appreciated the examples, they are very useful

    @RBARAER - yes, I am thinking of implementing it the first way

    @WilliamR - I was thinking of creating it dynamically in the stored procedure, so you mean it won't work? If not, when can I create the GTT and what is its lifespan? I am beginning to worry how to handle this.

    @AlanP - yes, I worry in the fact that Oracle might handle temporary tables differently against SQL Server where in SQL even if the procedure that creates a temp table is called more than once simultaneously, it is handled well. Do you have any suggestion in mind to cater creation of dynamic SQL statement in Oracle? I am actually trying to create a stored procedure that will run a specific SQL statement based on the parameters passed. The statement would then be based on a number of conditions based on table records ...

    Can I use cursors instead of GTTs? What do you think guys, do you know of any performance difference?

    Thanks a lot!

  12. #12
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    William was pointing to a fact that you can not create two tables having the same name. That is, you should create a GTT (or "ordinary" table, whatever) only once and forget about CREATE TABLE statement.
    If you insist on creating the same table in, for example, every session, you should first DROP TABLE, and then re-create it. However, I'd suggest you not to do it - create your table only once (whether it is a GTT or not) and then insert data into it, delete it, truncate etc.

    Using cursors instead of tables? I'd say that - if you populate the table using a SELECT statement that fetches data from another table(s), you can do it with cursor as well. But: when dealing with the cursor, you can fetch one record at a time. To "scan" the whole cursor, you'll need to do it in a loop (for example, to find out how many employees have a salary > 2000, you'll have to fetch employee by employee and count them. If you want the same from the table, you'd use a simple SELECT COUNT statement which will certainly be faster).

    If you need to dinamically create SQL statements, I'd say that using EXECUTE IMMEDIATE would be OK.

  13. #13
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I would only really recommend temp tables (GTTs) if you need to do multiple (complex?) operations which simply arent possible with a single sql statement (dont forget you can do really complex summarisation just by doing nested selects in a single statement). Otherwise simply pass the sql query dynamically created back to your app for execution or create something like a ref cursor which your app selects from. Dont forget to use something equivalent to bulk collect if you are selecting lots of rows as this will retrieve the rows in large batches (depends what language your using).

    If your using GTTs, as the other contributors have already pointed out, just create them once as part of your schema creation and then let any session use them how it likes. Dont forget in GTTs only the session which creates the data can see its data (the data can be automatically deleted when your transaction/session ends.

    Alan

  14. #14
    Join Date
    Jan 2005
    Posts
    5
    Thank you guys your help has been really helpful and I appreciate it.

Posting Permissions

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