Home » SQL & PL/SQL » SQL & PL/SQL » how to display the columnname with values from the table (merged)
how to display the columnname with values from the table (merged) [message #276446] Thu, 25 October 2007 03:20 Go to next message
msafana
Messages: 31
Registered: July 2007
Member
i need to display result which is varchar variable which contains

result =((TABLENAME='TESATBOND')(COLUMNNAME1)=COLUMNVALUE1,(COLUMNNAME2)=COLUMNVALUE2...);

dbms_output.put_line(result);

please me the answer for this
Re: how to display the columnname with values from the table [message #276449 is a reply to message #276446] Thu, 25 October 2007 03:31 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:

i need to display result which is varchar variable which contains
result =((TABLENAME='TESATBOND')(COLUMNNAME1)=COLUMNVALUE1,(COLUMNNAME2)=COLUMNVALUE2...);
Assigning a char literal with '=' and without having single quote at begin and end??

Do you think it will compile? If you are too lazy to post something, which can be understandable, you won't get an answer.

By the way did you ask any question above? Seeking an answer without asking a question!! Embarassed
Edit: I think your subject contains the question.
Quote:

how to display the columnname with values from the table


By
Vamsi

[Updated on: Thu, 25 October 2007 03:42]

Report message to a moderator

Re: how to display the columnname with values from the table [message #276453 is a reply to message #276449] Thu, 25 October 2007 03:41 Go to previous messageGo to next message
msafana
Messages: 31
Registered: July 2007
Member

i need to write stored procedure which is having two in parameters and one out parameter like these

CREATE OR REPLACE PROCEDURE testkesh(tablename IN VARCHAR, primarykeyvalue IN VARCHAR)

my out parameter should return this result:

((TABLENAME=TESATBOND) (BONDID)=1, (LOGIN)=SA, (BONDNAME)=Bond1, (BONDDESC)=Bond Details1)
which contains


table name: TESATBOND
Columns: BONDID,LOGIN,BONDNAME..
Values: 1,sa,bond1....

Re: how to display the columnname with values from the table [message #276455 is a reply to message #276453] Thu, 25 October 2007 03:46 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You need to write dynamic sql.
Get the column names from user_tab_columns.
Get the data using dynamic sql.
Concatenate them and assign to the return variable.

By
Vamsi
Re: how to display the columnname with values from the table [message #276460 is a reply to message #276446] Thu, 25 October 2007 04:04 Go to previous messageGo to next message
msafana
Messages: 31
Registered: July 2007
Member
please can you send one example query or otherwise
actually my problem isonce i will update any table i new log
that in to some other table which contains two column new and old .thos column need to update with this string reprasentation of value.

for example ur updating table T1(c1,c2,c3..) with some value in c2. once you will updated that uyou need to log that in Table T2(oldvalue,newvalue).

here oldvalue should contains
((TABLENAME=T1) (C2)=1)


newvalue should contains
((TABLENAME=T1) (C2)=2)

for getting this string format i need to write SP.
Thanks
Re: how to display the columnname with values from the table [message #276469 is a reply to message #276446] Thu, 25 October 2007 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at print_table on AskTom to know how to do this kind of things.

Regards
Michel
Re: how to display the columnname with values from the table [message #276477 is a reply to message #276469] Thu, 25 October 2007 04:50 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Oracle has created AUDIT functionality for that. Why don't you have a look at that.

MHE
please help me out for running this SP..in SQL [message #276674 is a reply to message #276446] Thu, 25 October 2007 23:11 Go to previous messageGo to next message
msafana
Messages: 31
Registered: July 2007
Member
CREATE OR REPLACE PROCEDURE testkesh(
   tablename         IN       VARCHAR2
 , primarykey        IN       VARCHAR2
 , primarykeyvalue   IN       NUMBER
 , columnname        IN       VARCHAR2
 , columnoldvalue    IN       VARCHAR2
 , action            IN       NUMBER
 , procedresult      OUT      VARCHAR2
)
IS
   columnvalue   VARCHAR2(2000);
   querystr      VARCHAR2(2000);
BEGIN
   IF (action > 0)
   THEN
      procedresult    := '(tablename =' || tablename || ')';
   END IF;

   querystr    := 'SELECT  ' || columnname || '  FROM  ' || tablename || 'WHERE  '
                             || primarykey || '=' || primarykeyvalue;

   EXECUTE IMMEDIATE querystr
   INTO              columnvalue;

   IF (columnvalue <> columnoldvalue)
   THEN
      procedresult    := '(' || columnname || '=' || columnvalue || ')';
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
END testkesh;


MOD EDIT: Added code tags - do so yourself next time (!).



[Updated on: Fri, 26 October 2007 01:20] by Moderator

Report message to a moderator

Re: please help me out for running this SP..in SQL [message #276675 is a reply to message #276674] Thu, 25 October 2007 23:14 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
I don't see any error, question or problem.
Please read & FOLLOW posting guidelines as found in the #1 STICKY post at the very top of this forum.
Re: please help me out for running this SP..in SQL [message #276676 is a reply to message #276675] Thu, 25 October 2007 23:19 Go to previous messageGo to next message
msafana
Messages: 31
Registered: July 2007
Member
Hi,
I need example for Userauditing in sql SP meance for loging the new value and oldvalues in the following format.


OLDVALUES -->

((TABLENAME=TESATBOND) (BONDID)=1, (LOGIN)=SA, (BONDNAME)=Bond, (BONDDESC)=Bond Details)



NEWVALUES -->
((TABLENAME=TESATBOND) (BONDID)=1, (LOGIN)=SA, (BONDNAME)=Bond1, (BONDDESC)=Bond Details1)


please send me some exaples that should be generic.
Re: how to display the columnname with values from the table [message #276677 is a reply to message #276477] Thu, 25 October 2007 23:22 Go to previous messageGo to next message
msafana
Messages: 31
Registered: July 2007
Member
can you send me some ezxaples for oracle auditing those should be generic.
Re: please help me out for running this SP..in SQL [message #276684 is a reply to message #276674] Fri, 26 October 2007 00:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
msafana wrote on Fri, 26 October 2007 06:11


EXCEPTION
WHEN others THEN
RAISE;
END testkesh;


Now this is a cool feature!
Do you know what this does? It tells Oracle to do the following: "If an error occurs, no matter what error, do exactly what you normally do, display the error, BUT don't show me what line the error occurred."
It is the dumbest thing you can do. (ok, but it is in the top ten)

Now for your question: I somehow cannot relate your second post to your original post, no idea what you want.
Re: please help me out for running this SP..in SQL [message #276686 is a reply to message #276674] Fri, 26 October 2007 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this not the same question that the one you posted yesterday in "how to display the columnname with values from the table "?

The answer is the same but you don't want to make the effort to search what I said, you just want us to put the code.

Regards
Michel

[Updated on: Fri, 26 October 2007 00:24]

Report message to a moderator

Re: how to display the columnname with values from the table [message #276711 is a reply to message #276677] Fri, 26 October 2007 01:23 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
msafana wrote on Fri, 26 October 2007 06:22

can you send me some ezxaples for oracle auditing those should be generic.
Auditing is generic, but it is also version dependent. Oracle also has manuals about this. Start at tahiti.oracle.com and select your database version and go on from there. All information is there.

MHE
Previous Topic: HAVING Clause
Next Topic: query is taking 1 hour to execute
Goto Forum:
  


Current Time: Thu Dec 08 00:35:15 CST 2016

Total time taken to generate the page: 0.14226 seconds