Home » SQL & PL/SQL » SQL & PL/SQL » procedures to create tables
procedures to create tables [message #274007] Fri, 12 October 2007 12:29 Go to next message
colorado_girl
Messages: 15
Registered: October 2007
Location: CT
Junior Member
I was trying to create a procedure to create tables and insert values in them.

oracle release 9.2.0.1.0

create or replace proc1
is
begin
create tab1(....)

insert into tab1(..)
select...from tab2;

end;

but I keep getting the error message:
LINE/COL ERROR
-------- -----------------------------------------------------------------
9/9 PLS-00103: Encountered the symbol "CREATE" when expecting one of
the following:
( - + mod not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>

please help.

Re: procedures to create tables [message #274009 is a reply to message #274007] Fri, 12 October 2007 12:36 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
you can only do DDL inside PL/SQL by using EXECUTE IMMEDIATE.

FYI - Privs acquired via ROLEs do NOT apply within PL/SQL procedures.
Re: procedures to create tables [message #274010 is a reply to message #274009] Fri, 12 October 2007 12:48 Go to previous messageGo to next message
colorado_girl
Messages: 15
Registered: October 2007
Location: CT
Junior Member
Thanks for the help, but can you tell me a lil more in detail about using execute immediate inside the prodedure.
Re: procedures to create tables [message #274011 is a reply to message #274007] Fri, 12 October 2007 12:52 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Are both the SEARCH button on this forum & GOOGLE broken for you?
If so, I'll be glad Read The Fine Manuals to you.
Re: procedures to create tables [message #274020 is a reply to message #274010] Fri, 12 October 2007 14:45 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, but why would you want to create a table in a PL/SQL procedure? What advantage do you see in it? What's wrong with
REM This is a pure SQL script!

CREATE TABLE test_1 
  (id    NUMBER,
   name  VARCHAR2(20));

INSERT INTO test_1 (id, name) VALUES (1, 'Little');
INSERT INTO test_1 (id, name) VALUES (2, 'Foot');

CREATE TABLE test_2
  (id        NUMBER,
   date_time DATE
  );

INSERT INTO test_2 (id, date_time) VALUES (1, sysdate);

REM End of the script

As Anacedent told you, it is possible to do it using the EXECUTE IMMEDIATE, but why???
Re: procedures to create tables [message #274023 is a reply to message #274020] Fri, 12 October 2007 15:15 Go to previous messageGo to next message
colorado_girl
Messages: 15
Registered: October 2007
Location: CT
Junior Member
I was passed on the requirements. even i though i could simply use SQL to do it.
Re: procedures to create tables [message #274024 is a reply to message #274023] Fri, 12 October 2007 15:20 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is it yet another "how to count records in a table without using the COUNT function" type of question? If so, well, start reading the documentation about EXECUTE IMMEDIATE. If not, explain to the one who required you to use PL/SQL for such a job that it is not the right tool to do that.
Re: procedures to create tables [message #274025 is a reply to message #274023] Fri, 12 October 2007 15:22 Go to previous messageGo to next message
colorado_girl
Messages: 15
Registered: October 2007
Location: CT
Junior Member
I did compile the create table, but now insert isnt working in it.

create or replace procedure EXTRACT_CPCC_CONT_PROD1211
is
begin

execute immediate 'create tab1(...)'
/*compiles till here but if i include the insert part it throws errors.*/

insert into tab1..
select ...from tab2

end;


error says declare tab1

how do i resolve this.


Re: procedures to create tables [message #274026 is a reply to message #274007] Fri, 12 October 2007 15:29 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Do not explain to us what you think you are doing.

Use CUT & PASTE along with <code tags> to SHOW us what is really happening!
Re: procedures to create tables [message #274027 is a reply to message #274025] Fri, 12 October 2007 15:30 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
It is useless to post partial code and partial error messages. Cut and paste your entire session with code tags.

Pay attention to what anacedent mentioned and to paraphrase what Littlefoot said, tell whoever it is that told you to do this in a procedure that you won't give them bad advice about how to do their job so they shouldn't do the same with you.

Re: procedures to create tables [message #274028 is a reply to message #274027] Fri, 12 October 2007 15:39 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
At them time your procedure gets compiled, table 'tab1' doesn't exist yet; that's why INSERT statement "fails".

See this example: first, create a test table:
SQL> begin
  2    execute immediate 'create table test (a number)';
  3  end;
  4  /

PL/SQL procedure successfully completed.
Now, let's create another test table and try to insert something into it - as you've already seen in your case, it will fail:
SQL> begin
  2    execute immediate 'create table test_2 (b number)';
  3
  4    insert into test_2 (b) values (123);
  5  end;
  6  /
  insert into test_2 (b) values (123);
              *
ERROR at line 4:
ORA-06550: line 4, column 15:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored
But if we create a table and insert into an existing table, no problem:
SQL> begin
  2    execute immediate 'create table test_2 (b number)';
  3
  4    insert into test (a) values (123);  --> created in the first example!
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>
Re: procedures to create tables [message #274030 is a reply to message #274028] Fri, 12 October 2007 15:42 Go to previous messageGo to next message
colorado_girl
Messages: 15
Registered: October 2007
Location: CT
Junior Member
Yes i already tried that, if I create the table before the porc compiles. But there is no other way to do it at the same time..
create and insert using execute immediate?
Re: procedures to create tables [message #274032 is a reply to message #274030] Fri, 12 October 2007 16:19 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, there is: write two PL/SQL scripts and run them sequentially (I can't believe I said that!).
Re: procedures to create tables [message #274033 is a reply to message #274030] Fri, 12 October 2007 16:19 Go to previous messageGo to next message
hmallick
Messages: 1
Registered: October 2007
Location: FL-USA
Junior Member
Try the following way :

SQL> create or replace procedure test_p2 AUTHID CURRENT_USER AS
2 begin
3 execute immediate 'create table test_t2 (b number)';
4 execute immediate 'insert into test_t2 (b) values (22)';
5 end;
6 /

Procedure created.

SQL> exec test_p2;

PL/SQL procedure successfully completed.

SQL> select * from test_t2
2 /

B
----------
22
Re: procedures to create tables [message #274128 is a reply to message #274023] Sun, 14 October 2007 04:11 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
colorado_girl wrote on Fri, 12 October 2007 22:15

I was passed on the requirements.
And the DBA's agreed? It would be a nightmare maintaining such a data model. I hope you're not trying to make "MS SQL Server-like" temporary tables. This kind of thing would most certainly NOT pass at our shop.

MHE
Re: procedures to create tables [message #274212 is a reply to message #274128] Mon, 15 October 2007 01:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Maaher raises a good point - are you planning to drop and recreate these tables frequently, and are you using them as temporary staging tables.

If so I reckon that you will find that 90% of the things you think you need them for can be done in Oracle without them, and the remaining 10% could be done with Global Temporary Tables.
Re: procedures to create tables [message #274353 is a reply to message #274212] Mon, 15 October 2007 13:53 Go to previous messageGo to next message
colorado_girl
Messages: 15
Registered: October 2007
Location: CT
Junior Member
Yes they are staging tables.
Re: procedures to create tables [message #274437 is a reply to message #274353] Tue, 16 October 2007 01:25 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
colorado_girl wrote on Mon, 15 October 2007 20:53

Yes they are staging tables.
There you go: a staging area can be created using Global Temporary Tables. A couple of years ago, when I was on a data warehousing project, that was the way it was set up. No need to create/drop tables on the fly.

A common misconception is that a temporary table is a table that exists only for the during of the session. No, a (global) temporary table is a persistent table that has data that is only visible to the session (or even the transaction) and that is purged once the session (or transaction) has ended. And I believe that that is exactly what you need for a staging area.

In the Oracle Datawarehousing Guide you'll find this passage:
8 Basic Materialized views

In a two-phase loading process:
  • Data is first loaded into a temporary table in the warehouse.
  • Quality assurance procedures are applied to the data.
  • Referential integrity constraints on the target table are disabled, and the local index in the target partition is marked unusable.
  • The data is copied from the temporary area into the appropriate partition of the target table using INSERT AS SELECT with the PARALLEL or APPEND hint. The temporary table is then dropped. Alternatively, if the target table is partitioned, you can create a new (empty) partition in the target table and use ALTER TABLE ... EXCHANGE PARTITION to incorporate the temporary table into the target table. See Oracle Database SQL Reference for more information.
  • The constraints are enabled, usually with the NOVALIDATE option.


And when I searched for "Temporary Table" I came across this line:
14 Loading and Transformation

When using Oracle Database as a transformation engine, a common strategy is to implement each transformation as a separate SQL operation and to create a separate, temporary staging table (such as the tables new_sales_step1 and new_sales_step2 in Figure 14-1) to store the incremental results for each step.

MHE
Previous Topic: User scripts
Next Topic: use of 'prior' function
Goto Forum:
  


Current Time: Fri Dec 09 21:11:22 CST 2016

Total time taken to generate the page: 0.18294 seconds