Visit Our UNIX and Linux User Community


v$sql not display correct sql_text


 
Thread Tools Search this Thread
Top Forums UNIX for Advanced & Expert Users v$sql not display correct sql_text
# 1  
Old 08-12-2011
v$sql not display correct sql_text

Hi folks,

I am facing one problem with v$sql, i need to store updating sql query in temp table when one trigger get fired on some update sql statement.

but with "sql_text" , i am not getting correct update statement in temp table.

I am getting sql_text with this cursor statement.

Code:
select ltrim(sq.sql_text)
from v$sql sq,
v$session se,
v$open_cursor oc
where sq.address = oc.address and
se.saddr = oc.saddr and
se.sid = oc.sid and
se.audsid = SYS_CONTEXT('userenv','sessionid');

while i am firing update statement like this
Code:
update so_schdg_call set SCHDG_CALL_STATUS__CODE='01' where so_sub_item__id='2021S00552.1.1'; - through trigger this statement should stored in temp table.

while looking the temp table content
Code:
select * from TMPSOSCHDGCALLLOG where so_sub_item__id='2021S00552.1.1';

i am getting statement like this -

Code:
2021S00552.1.1   SICOMDBA  amitps  update so_schdg_call set SCHDG_CALL_STATUS__CODE='01' where so_sub_item__id='1018202235.249.1

while statement should print like -
Code:
update so_schdg_call set SCHDG_CALL_STATUS__CODE='01' where so_sub_item__id='2021S00552.1.1';

Please advise why this type of descripency occurs with v$sql.

Thanks
Amit

Last edited by pludi; 08-12-2011 at 05:59 AM..

Previous Thread | Next Thread
Test Your Knowledge in Computers #608
Difficulty: Easy
Python does not need compilation to binary to run.
True or False?

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to... (3 Replies)
Discussion started by: Rokkesh
3 Replies

2. Programming

SQL*PLUS How to display a count of 0

Hi, I have been frantically googling and checking some sqlplus forums, but can't find the correct syntax. Basically within sqlplus I want to do a count on a table and if the count is 0 it displays 0 instead of "no rows found". For eample: select count(*) from tableA where... (3 Replies)
Discussion started by: chris01010
3 Replies

3. Shell Programming and Scripting

Display calendar in correct format using shell script

Hi All, I'm trying to print calendar using shell script and i'm able to print it. But the format is not good. Here is the script. #!/bin/bash echo $(date) echo "Hello $USER" echo Hostname $(hostname) echo Working in $(pwd) echo Here is this month calender echo $(cal) $ sh first.sh... (7 Replies)
Discussion started by: chandrakanth
7 Replies

4. Shell Programming and Scripting

Cannot get literal ampersand to display in SQL output

Gurus, Thanks so much for your help, in advance. I'm using ksh and outputting a literal string value to an output file, however, Unix isn't playing by SQL's rules. The ampersand character which I'm trying to disply as a knowledge base link is screwing up the output. Typically, the "&&" is... (1 Reply)
Discussion started by: WhoDatWhoDer
1 Replies

5. UNIX for Dummies Questions & Answers

Email display not correct on Solaris 10

Hi Everyone, Can someone possibly point me in the direction? When I execute command echo "hello" | sendmail -v testacct@wyx.com I recived an email as expected except the From: NAME is incorrect. The server name is correct. It should read "From: TESTBOX1 " actual email: From: app... (3 Replies)
Discussion started by: smckech1972
3 Replies

6. UNIX for Advanced & Expert Users

Call parallel sql scripts from shell and return status when both sql are done

Hi Experts: I have a shell script that's kicked off by cron. Inside this shell script, I need to kick off two or more oracle sql scripts to process different groups of tables. And when both sql scripts are done, I will continue in the shell script to do other things like checking processing... (3 Replies)
Discussion started by: huasheng8
3 Replies

7. Shell Programming and Scripting

How to display fields and values in sql+ for Oracle DB

Hello, I'm trying to do a select for an Oracle table but the output gives me only filelds values without fields name as in Informix. Is there anyway to display both in output ? For instance, the output will be : Name Rico Age 30 Position Engineer Thx, (5 Replies)
Discussion started by: rany1
5 Replies

8. UNIX for Dummies Questions & Answers

Execute PL/SQL function from Unix script (.sql file)

Hi guys, I am new on here, I have a function in oracle that returns a specific value: create or replace PACKAGE BODY "CTC_ASDGET_SCHED" AS FUNCTION FN_ASDSCHEDULE_GET RETURN VARCHAR2 AS BEGIN DECLARE ASDSchedule varchar2(6); ASDComplete... (1 Reply)
Discussion started by: reptile
1 Replies

9. Shell Programming and Scripting

Order text display not correct.

My shell script below for import data to Oracle it run okay. but the text display not correct follow order command executed. =========================Shell Script code================= #!/bin/sh #directory = ${1-'pwd'} #run import data with SQLLoader runSQLLoader() { ... (2 Replies)
Discussion started by: raccsdl
2 Replies

Featured Tech Videos