|
Hi,
Can any one tell me how to run a job in oracle which take all the data from one table(for ex: name,address,lastlogintime,etc) and generate output in excel format and save in one common folder everyday.
If you have a sample code, could you share it please, it would be helpfull in understanding the concept better.
|
|
|
|
|
What part of the concept is troubling you?
Write a console-app that executes a query on your Oracle-database. There's quite some examples on the internet that show you how to achieve that part. Next, iterate through the results, and write them out to a file. Excel will accept almost everything, from CSV to XML. We got some nice articles on CP that explain how to write to Excel in various ways.
Not much to concept there. If you have the first part of the assignment, we'll talk about writing those results to Excel.
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
Basic Steps
1. Create/find scheduler
2. Create SQL that gets the data
3. Create code the outputs the data
4. Put 1-3 together.
Steps 1-3 are independent. They do NOT have anything to do with each other.
Step 3 is the only one specific to Excel.
|
|
|
|
|
Hi I have a problem writing an query with select parameter as input paramter in the stored procedure.
CREATE PROCEDURE [dbo].[SelectItemList]
@ColName string,
@ID int
AS
BEGIN TRANSACTION
SELECT @ColName FROM ItemList where ID = @ID
COMMIT TRANSACTION
Is it possible something like this with select paramter also included in input paramter to the stored procedure?
Thanks in advance,
Dhyanga
|
|
|
|
|
Why don't you try along these lines:
CREATE PROCEDURE [dbo].[SelectItemList]
@ColName string,
@FieldName string,
@FieldValue int
AS
BEGIN TRANSACTION
SELECT @ColName FROM ItemList where @FieldName = @FieldValue
COMMIT TRANSACTION
[EDIT: this doesn't solve it. The message by Michael Potter holds the answer.[/EDIT]
modified 29-Jun-12 9:41am.
|
|
|
|
|
Thanks for the reply but it didn't work out. I am restating my problem with following table.
I have one table named ItemList like this:
ID Name Price($)
1 Bag 20
2 Fabrics 35
3 Tools 100
now my query was
CREATE PROCEDURE [dbo].[SelectItemList]
@ColName string,
@ID int
AS
BEGIN TRANSACTION
SELECT @ColName FROM ItemList where ID = @ID
COMMIT TRANSACTION
I need output something like this. If I have @ID = 1 and @ColName = 'Name', then my output should be
Name
-----
Bag
I tried my way that i posted earlier and your way, but it didn't work out. It gave output like this instead.
Column1
--------
Name
Name
Is there any other way or am I doing any wrong in my query?
Please help.
suchita
|
|
|
|
|
You'd need to introduce the output-keyword to one of the parameters, as described in the documentation[^].
Your sproc is a wrapper around a simple statement, adding complexity without any benefits. The design, the approach, it's wrong. There's nothing to "commit" to the database, and a simple select-statement (with parameters) would be sufficient.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks Eddy, I will take that commit out from my query. I mistakenly forgot to take that out. But my question is still the same. I don't know whelther we can send fieldname as input parameter to get the value of that field.I know it looks very weird for that small table but I have to use the same concept for my huge database system. I thought that table would be easy for me to explain what my output should look like. I am going through the documentation you sent me but at glance, i couldn't see what I am looking for but I am going through it line by line. Thanks for your time .
suchita
|
|
|
|
|
Dhyanga wrote: I don't know whelther we can send fieldname as input parameter to get the value of that field.
You could join on the system-tables, but to what use? You'll be concatenating constants in a way that can hardly be considered helpfull. If writing Sql is that much of a problem, consider a ORM-framework.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
I had got the required output simply writing the sql query but I wanted it on stored procedure. I know i can do it something like this in my query.
string colname;
int ID;
string query;
.
.
.
query = "select " + colname + " from ItemList where ID = " + ID ;
SqlCommand cmd = new SqlCommand(query, sqlconn);
.
.
.
.
This had solved my problem but I wanted it on stored procedure. and I was stuck giving the column name itself as input parameter.
suchita
|
|
|
|
|
I can only point to jschells' comment 
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks Eddy for your time and views.
suchita
|
|
|
|
|
Dhyanga wrote: I don't know whelther we can send fieldname as input parameter
You can't.
Your choices are
1. Create the SQL dynamically in C# and then execute it.
2. Create the SQL dynamically in SQL and then execute it in SQL.
3. Use a fixed set of fixed SQL statements and select one based on the data passed in.
|
|
|
|
|
Thanks Jschell.. Just curious if that can be possible. If not, i can just execute it without using stored procedure. I was trying to use better way than that if I could...
suchita
|
|
|
|
|
Dhyanga wrote: i can just execute it without using stored procedure
My solutions work with or without stored procs.
|
|
|
|
|
Hi,
try like this..
CREATE PROCEDURE [dbo].[SelectItemList]
@ColName string,
@ID int
AS
BEGIN TRANSACTION
SELECT @ColName = ColumnName FROM ItemList where ID = @ID
COMMIT TRANSACTION
Karthik Harve
|
|
|
|
|
Am I missing something, you need dynamic SQL to meet this requirement, you solution does not work!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You have to use sp_executesql and construct the query string yourself. Do realize that you can open yourself up to SQL injection attacks if you don't properly test the variables.
CREATE PROCEDURE SelectItemList
(
@ColName string,
@ID int
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql = N'SELECT ' + @ColName + N' FROM ItemList where ID = ' + CAST(@ID AS VARCHAR(10))
EXEC sp_executesql @sql
|
|
|
|
|
Thank you Micheal. That's what I wanted but we need to give the @ColName nvarchar size too. It worked fantastic.
suchita
|
|
|
|
|
|
Keep in mind that as formatted that solution is open to SQL injection attacks.
|
|
|
|
|
Hi All,
How we make the .mdf file of a database ?
I want a script or C# code that could be used in a utility in C#.Net.
Please help !!
Regards,
Lalit
modified 27-Jun-12 4:58am.
|
|
|
|
|
lalitN wrote:
How we make the .mdf file of a database ?
That extension is used both for Sql Server as well as Microsoft Access. Which are you targetting?
Bastard Programmer from Hell
|
|
|
|
|
I am using SQL Server 2008 R2.
|
|
|
|
|
The command to create a database is documented here[^], examples are at the bottom of the page.
Bastard Programmer from Hell
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
