Home » Open Source » MySQL » procedure makes mysql script and return it as CLOB
procedure makes mysql script and return it as CLOB [message #160818] Tue, 28 February 2006 08:29 Go to next message
fabi
Messages: 26
Registered: February 2006
Junior Member
Hi my name is lukas im from poland. i dont speak english very well so
sorry if you dont understand me. i dont find anything about my problem in
polish disciussion groups.
i must create procedure. this procedure has 1 parameter and return CLOB with mysql script(text script who create the same table as parameter in procedure with the same field and data but in mysql command). this parameter is table name.
for example create procedure aaa(table_name varchar2). then this
procedure must receive name of fields,type of fields and data from this fields and create mysql commands with this information.(create tables
and a lot of inserts) to CLOB and return this clob.


i dont have any idea to solve this problem. Sad( thanks for any information.


i try explain problem so clearly as i can .

[Updated on: Tue, 28 February 2006 08:46]

Report message to a moderator

Re: procedure makes mysql script and return it as CLOB [message #160845 is a reply to message #160818] Tue, 28 February 2006 12:11 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Hi Lukas,

Are you trying to create an Oracle procedure or a mySQL procedure?

Since this is an Oracle forum, I'm not sure if anyone will be able to help you with a mySQL question here or not.

-Todd
Re: procedure makes mysql script and return it as CLOB [message #160849 is a reply to message #160818] Tue, 28 February 2006 12:40 Go to previous messageGo to next message
fabi
Messages: 26
Registered: February 2006
Junior Member
this is procedure in pl/sql which return CLOB with mysqlcommand. i know mysql but i cant write procedur in pl/sql Smile

[Updated on: Tue, 28 February 2006 12:41]

Report message to a moderator

Re: procedure makes mysql script and return it as CLOB [message #160865 is a reply to message #160849] Tue, 28 February 2006 18:00 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
I don't know what needs to change in order to get Oracle DDL to execute in mySQL, but here is a procedure example that use the DBMS_METADATA package to pull in the DDL automatically. Alternatively, you could loop through the user_tab_cols view to pull out just column names and datatypes.

sql>create or replace procedure getDDL
  2    (p_table_name in user_tables.table_name%type,
  3     p_ddl        out clob)
  4  is
  5  begin
  6    p_ddl := dbms_metadata.get_ddl('TABLE', p_table_name);
  7  end;
  8  /

Procedure created.

sql>var ddl clob
sql>set long 200
sql>exec getDDL('EMP', :ddl)

PL/SQL procedure successfully completed.

sql>print ddl

DDL
------------------------------------------------------------------------------

  CREATE TABLE "BARRYT"."EMP" 
   (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE, 
        "E
NAME" VARCHAR2(10), 
        "JOB" VARCHAR2(9), 
        "MGR" NUMBER(4,0), 
        "HIREDATE" DAT
E, 
        "SAL" NUMBER(7,2), 
        "COMM" NUMBER(7,2)


I'm only showing the first 200 characters of the DDL here but you could see the whole thing by bumping up the value in the SET LONG SQL*Plus command.
Re: procedure makes mysql script and return it as CLOB [message #160952 is a reply to message #160865] Wed, 01 March 2006 02:24 Go to previous messageGo to next message
fabi
Messages: 26
Registered: February 2006
Junior Member
Todd Barry wrote on Tue, 28 February 2006 18:00

Alternatively, you could loop through the user_tab_cols view to pull out just column names and datatypes.



thank you. could you give me a short example how i can through the user_tab_cols view to pull out just column names and datatypes????

Re: procedure makes mysql script and return it as CLOB [message #160957 is a reply to message #160952] Wed, 01 March 2006 03:14 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
fabi wrote on Wed, 01 March 2006 09:24

could you give me a short example how i can through the user_tab_cols view to pull out just column names and datatypes????


SQL> col data_type format a20
SQL> col column_name format a20
SQL> SELECT column_name
  2       , data_type
  3       , data_length
  4       , data_precision
  5       , data_scale
  6    FROM user_tab_columns
  7   WHERE table_name = 'DEPARTMENTS'
  8  /

COLUMN_NAME          DATA_TYPE            DATA_LENGTH DATA_PRECISION DATA_SCALE
-------------------- -------------------- ----------- -------------- ----------
DEPARTMENT_ID        NUMBER                        22              4          0
DEPARTMENT_NAME      VARCHAR2                      30
MANAGER_ID           NUMBER                        22              6          0
LOCATION_ID          NUMBER                        22              4          0

SQL> desc departments
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPARTMENT_ID                             NOT NULL NUMBER(4)
 DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)
 MANAGER_ID                                         NUMBER(6)
 LOCATION_ID                                        NUMBER(4)

DESC[RIBE] is a SQL*Plus command describing the table so you can verify the datatypes.

MHE
Re: procedure makes mysql script and return it as CLOB [message #160967 is a reply to message #160957] Wed, 01 March 2006 03:39 Go to previous messageGo to next message
fabi
Messages: 26
Registered: February 2006
Junior Member
thank you
i have also another problem.
create or replace procedure proc(table_name varchar2)
is
cursor kur is
select * from table_name;
...
...

and system return me mistake in line //select * from table_name;
table or view does not exist something like that.

table_name from procedure must be the same in cursor.
how can i use parameter from procedure (table_name) in cursor???

thanks
Re: procedure makes mysql script and return it as CLOB [message #160997 is a reply to message #160967] Wed, 01 March 2006 05:53 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
fabi wrote on Wed, 01 March 2006 10:39

and system return me mistake in line //select * from table_name;
table or view does not exist something like that.

Red: post an exact error message next time. Show us what you did and use CODE tags like this:
SQL> select * from table_name;
select * from table_name
              *
ERROR at line 1:
ORA-00942: table or view does not exist
table_name is not a valid table. I assume you want to query the columns for a given table, so you could do this:
SQL> CREATE PROCEDURE foo(p_tab_name IN VARCHAR2)
  2  IS
  3    CURSOR c_cols(c_tname VARCHAR2)
  4        IS
  5    SELECT column_name
  6         , data_type
  7         , data_length
  8         , data_precision
  9         , data_scale
 10      FROM user_tab_columns
 11     WHERE table_name = c_tname;
 12  BEGIN
 13    FOR the_column IN c_cols(p_tab_name)
 14    LOOP
 15      -- do your processing here
 16      dbms_output.put_line(the_column.column_name||' is a '
 17                         ||the_column.data_type||' COLUMN.');
 18    END LOOP;
 19  END;
 20  /

Procedure created.

SQL> set serverout on
SQL> exec foo('EMPLOYEES')
EMPLOYEE_ID is a NUMBER COLUMN.
FIRST_NAME is a VARCHAR2 COLUMN.
LAST_NAME is a VARCHAR2 COLUMN.
EMAIL is a VARCHAR2 COLUMN.
PHONE_NUMBER is a VARCHAR2 COLUMN.
HIRE_DATE is a DATE COLUMN.
JOB_ID is a VARCHAR2 COLUMN.
SALARY is a NUMBER COLUMN.
COMMISSION_PCT is a NUMBER COLUMN.
MANAGER_ID is a NUMBER COLUMN.
DEPARTMENT_ID is a NUMBER COLUMN.

PL/SQL procedure successfully completed.

SQL> exec foo('JOBS')
JOB_ID is a VARCHAR2 COLUMN.
JOB_TITLE is a VARCHAR2 COLUMN.
MIN_SALARY is a NUMBER COLUMN.
MAX_SALARY is a NUMBER COLUMN.

PL/SQL procedure successfully completed.

SQL> 


MHE
Re: procedure makes mysql script and return it as CLOB [message #161004 is a reply to message #160818] Wed, 01 March 2006 06:41 Go to previous messageGo to next message
fabi
Messages: 26
Registered: February 2006
Junior Member
thank you very much for yours answers. this is a great forum Smile Razz
Re: procedure makes mysql script and return it as CLOB [message #161034 is a reply to message #160997] Wed, 01 March 2006 08:47 Go to previous messageGo to next message
fabi
Messages: 26
Registered: February 2006
Junior Member
Maaher wrote on Wed, 01 March 2006 05:53


[code]SQL> CREATE PROCEDURE foo(p_tab_name IN VARCHAR2)
2 IS
3 CURSOR c_cols(c_tname VARCHAR2)
4 IS
5 SELECT column_name
6 , data_type
7 , data_length
8 , data_precision
9 , data_scale
10 FROM user_tab_columns
11 WHERE table_name = c_tname;
12 BEGIN
13 FOR the_column IN c_cols(p_tab_name)
14 LOOP
15 -- do your processing here
16 dbms_output.put_line(the_column.column_name||' is a '
17 ||the_column.data_type||' COLUMN.');
18 END LOOP;
19 END;
20 /

/code]



ok i used your code but i want second cursor next to cursor c_cols so i write
cursor kur(c_tname varchar2)
is select * from c_tname;
and there is a mistake : table or view does not exist
Re: procedure makes mysql script and return it as CLOB [message #161041 is a reply to message #161034] Wed, 01 March 2006 10:04 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
fabi wrote on Wed, 01 March 2006 15:47

ok i used your code but i want second cursor next to cursor c_cols so i write
cursor kur(c_tname varchar2)
is select * from c_tname;
and there is a mistake : table or view does not exist
What are you trying to select from? c_tname is a cursor variable you locally declared, it is apparently no table in your schema.

What do you mean with 'second cursor next to cursor c_cols'. What do you want as output?

MHE
Re: procedure makes mysql script and return it as CLOB [message #161051 is a reply to message #160818] Wed, 01 March 2006 12:39 Go to previous message
fabi
Messages: 26
Registered: February 2006
Junior Member
i want second cursor in procedure foo. i want that cursor kur select data from table which name is as parameter in procedure foo.

how i can show that cursor's variable c_name is the same as procedure's variable p_tab_name
how i can do this????
thanks for any answer

pozdro

[Updated on: Wed, 01 March 2006 12:44]

Report message to a moderator

Previous Topic: Oracle Announces the Acquisition of Innobase (part of the MySQL Database)
Next Topic: MYSQL to Oracle SQL conversion
Goto Forum:
  


Current Time: Fri Mar 29 09:59:43 CDT 2024