| Dynamic SQL [message #369965] |
Tue, 21 November 2000 02:43  |
Kiki
Messages: 13
Registered: November 2000
|
Junior Member |
|
|
I heard that Dynamic SQL can give the simplicity when we want to select many column names from many table names. Does anyone give me the example or maybe the walkthrough.
Thanks,
|
|
|
|
|
|
| Re: Dynamic SQL [message #369974 is a reply to message #369967] |
Tue, 21 November 2000 08:22  |
Kiki
Messages: 13
Registered: November 2000
|
Junior Member |
|
|
What I am trying to do is instead of I repeat select column_name from table_name, maybe I can try to pass the parameter. So that I can save my works. Because I am working out with around 100 columns from 40 tables. Not only that, but also I need to specify a where statement for every one of them.
I have tried to use ref cursor, but I was unsuccesful, but my friend told me that using dynamic sql is much simpler.
thanks for your helps!
|
|
|
|
| Re: Dynamic SQL [message #369983 is a reply to message #369967] |
Wed, 22 November 2000 05:06 |
Babu Paul
Messages: 38
Registered: November 2000
|
Member |
|
|
Hi,
I think if you can let me know what exactly your query conditions are, then probably we will be in a position to achieve the results without using Dynamic SQL. I still feel that it is not better to create a procedure for this and call it forty times for 40 different tables. What do you say?
Babu
|
|
|
|
| Re: Dynamic SQL [message #369985 is a reply to message #369967] |
Wed, 22 November 2000 10:32 |
Madhav Kasojjala
Messages: 42
Registered: November 2000
|
Member |
|
|
If u are using Oracle8i,
get a feel of what u can do using Execute immediate....
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;
|
|
|
|
| Re: Dynamic SQL [message #369988 is a reply to message #369967] |
Wed, 22 November 2000 21:02 |
Kiki
Messages: 13
Registered: November 2000
|
Junior Member |
|
|
Thanks!
example:
I have 2 tables:customer and company.
Inside customer table, there is cust_id, cust_name, cust_addr. while company table has company_id, company_name.
In my query, Instead of repeating some many boring lines, I thought that it is better to have only one select statement which I can call it and pass the table name and column name.
so what I did:
sql_statement :='select :column_name from :table_name';
then if I want to get result for cust_id from customer table, this is what I did.
table_name='customer';
column_name='cust_id';
Execute Immediate sql_statement using column_name,table_name;
However, I got syntax error. does anyone can help to fix it up. and also after we run execute immediate, there is anyway we can store that results on the variable, so that I can use that for other purposes.
Thanks very much for your helps :)
|
|
|
|
| Re: Dynamic SQL [message #369993 is a reply to message #369967] |
Thu, 23 November 2000 05:20 |
Babu Paul
Messages: 38
Registered: November 2000
|
Member |
|
|
Hi
This looks quite a simple thing to me, I think the following code will help you do this... try this on sqlplus.
ACCEPT table_name CHAR PROMPT 'Enter the table name? '
ACCEPT column_name CHAR PROMPT 'Enter the column name? '
ACCEPT file_name CHAR PROMPT 'Enter the file name? '
SPOOL &&file_name
SELECT &&column_name FROM &&table_name ;
SPOOL OFF
Save this in a file and execute the file....
@file
Good Luck!
Babu
|
|
|
|
| Re: Dynamic SQL [message #369997 is a reply to message #369967] |
Thu, 23 November 2000 18:24 |
Kiki
Messages: 13
Registered: November 2000
|
Junior Member |
|
|
thx for your help. It is very helpful.
However, I found out that your technique is only effective by using prompt only. While in my case, I don't want to use prompt because This is suppose to look automatically. What I mean is that I want to store the table name, for example, in the variable inside my script.
Example,
table_name:='customer';
Anywayz, Thanks for your helps!:P
If you can, can you send me email privately to thegiggsy@mailcity.com. thx!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
