logs archiveIRC Archive / Freenode / #oracle / 2010 / April / 30 / 1
insanidade
hi all. would someone please help me on a pl/sql issue ?
please take a look at http://pastebin.org/193402
I have a syntax problem here. the following code works: in a nutshell, this works: select distinct data_type into Result from ALL_TAB_COLUMNS where owner = 'GCIR_STG' and column_name = 'HARDCODED_COLUMN_NAME';
this one doesn't: select distinct data_type into Result from ALL_TAB_COLUMNS where owner = 'GCIR_STG' and column_name = upper(PSTRINGCOLUMN);
and I'm sure that the PSTRINGCOLUMN variable contains the value that I want. I have printed it with Dbms_Output.Put_Line
anybody ?
Xgc
insanidade: Ouch. I bet you didn't know this...
insanidade: '' IS NULL in Oracle ! Take out the '' || fluff.
insanidade: Just refer to temp directly.
insanidade: or refer directly to the parameter.
insanidade: But never try to concatenate null '' with anything.
insanidade
ops
Xgc, I have also tryied that way. same result. no success :(
Xgc
select distinct data_type into Result from ALL_TAB_COLUMNS where owner = 'STG_OWNER' and column_name = temp; is all you need.
Just be sure temp does not conflict with any real column names.
insanidade
Xgc, it does not work. not sure why.
Xgc
Try again. The other approach is to EXECUTE IMMEDIATE
insanidade
what if I use: execute immediate 'select distinct data_type from ALL_TAB_COLUMNS where owner = ''GCIR_STG'' and column_name = upper('||PARAMETER||')'
into Res;
Xgc
But that probably shouldn't be needed.
What??
insanidade
sorry. it jumped to another line
         

Xgc
Oh. Yes. that's an option.
that's what I meant above.
insanidade
are que quotes and || correct in that second option ?
Xgc
insanidade: 1) The execute immediate is not needed. 2) Yes, but only if you need to modify the variable value before using in the statement.
insanidade: No.
insanidade: Sorry. You missed some quotes.
insanidade: But you needn't do this in this case.
trust me. UPPER(p_name) is all you need, without the execute fluff.
insanidade
Xgc, isn't execute immediate necessary?
Xgc
insanidade: No, since you can pass the parameter value directly.
insanidade: We're talking about Oracle's pl/sql in a procedure or function, correct?
insanidade
right
Xgc
(Action) nods
insanidade
it still does not work
Xgc
I do this every day.
The only possibly problem I see is conflicting namespace, which hides the real temp variable with a table column name.
That can be tested by renaming the variable to something you know can't possibly be in one of these tables.
(Action) notes that a complete testcase wouldn't require guessing.
Maybe there's something I'm missing, but I don't think so.
A testcase would resolve that open issue.
Absolutely always the fastest approach to a solution.
I bet this would have been solved hours ago, within a few minutes of asking the first question.
I usually create testcases, but I'd rather leave that work to the questioner.
insanidade
take a look at http://pastebin.org/193465
that's how it is now. still not working.
Xgc
Consider standard debugging techniques. return UPPER(PSTRINGBLAH);
Then return(PSTRINGBLAH); There's got to be some hint of the reason.
insanidade
Dbms_Output.Put_Line(upper(PSTRINGCOLUMN)); shows the value that should be used in the query.
Xgc
Sure, but within the SELECT that could mean something different.
So figure out what that difference it.
is
I can't tell from here.
insanidade
I'm printting every step. looks like the querey fails if used with parameters.
Xgc
Fails, without an error?
That doesn't happen in Oracle.
Unless you are hiding that in your application.
Which API are you using, JDBC?
insanidade
take a look at http://pastebin.org/193480. I've added comments in it. that is the way the code is now. I'm using oracle Sql developer tool.
Xgc, that might help a little: the following query is what is being created: select distinct data_type into Res from ALL_TAB_COLUMNS where owner = 'GCIR_STG' and column_name = upper('CHARGE_MV_MIN');
CHARGE_MV_MIN is the real name of a column. that should be a valid query.
but a "missing keyword" error raises.
what am I missing ?
Xgc
Strange.
So you were catching/hiding an error/exception in the application.
insanidade
looks like.
Xgc
insanidade: I just ran a test without problem. The SELECT returns the expected value from that table.
So there we have it. Stalemate.
Works here. Doesn't work there. Something is different.
Using SQL Developer as well for testing.
insanidade: Just like this: SELECT data_type INTO res FROM ALL_TAB_COLUMNS where owner = prm3 AND column_name = upper(prm) AND table_name = upper(prm2);
insanidade
very few alternatives now.
         

Xgc
Of course, I'm not using a function. I'm just using a simple anonymous procedure.
Should be pretty much the same, apart from minor type compatibility issues.
insanidade
are you using that query without quotes in the "owner = prm3" piece of code ?
Xgc
I pasted the exact SELECT I used.
prm, prm2, prm3 are all local variables.
Just to show that the problem isn't associated with simple variable expansion.
or binding.
insanidade
Xgc, take a look: http://pastebin.org/193557
Xgc, I sometimes get those error messages
Xgc
Probably need to clear the output buffer.
That's probably the current limit.
insanidade: Try dbms_output.disable; dbms_output.enable; at the top of your function.
That might clear the current buffer.
insanidade
I'm trying it.
Xgc
I don't use that package very often.
insanidade
Xgc, one question: the "into res" clause implies that I want to store the statement result into that "res" variable, right ?
Xgc
Correct.
insanidade
for God's sake, what's wrong ? http://pastebin.org/193624
that really sucks
ha
can't believe it.
it works.
Paulius
nice
insanidade
there's an input file. the parameter values I need for the query come from that file.
turns out that the file was in a weird format. lots of hidden characters in it, like ^M and others.
dos2unix on it fixed the whole thing. it clearly wasn't any pl/sql issue.
Paulius
oh, nice.
Xgc
insanidade: That's why a testcase is so important. There's no way I would have guessed or duplicated that. Only you could have provided that information.
The problem would have been solved almost immediately, except you assumed the problem was in the function.
Without the complete testcase, the channel couldn't do anything more than "it looks ok" or "it workd for me".
mick_laptop
hi everyone, i'm trying to setup a jdbc connection in tomcat to an oracle database. this is going to seem a bit crazy, but I have no idea how to find the database name. I'm starting to think that the concept of a database name doesn't even exist in oracle (I've primarily used other databases like MySQL / SQLite / BerkleyDB etc).
i have my tables in my database (btw, i'm using 10g XE)
Xgc
mick_laptop: schema name.
mick_laptop
ah found: select * from v$database;
Xgc
Oh. You really didn't know the name? How do you connect outside of JDBC?
mick_laptop
via port :8080
the web interface
seems like the name was XE
probably some default name or something
Xgc
That's the instance name. There's only one for XE.
mick_laptop
so XE can only have one database
Xgc
Yes. But from your perspective, you should be able to create some number of schemas within that instance.
mick_laptop
ah ok
Xgc
mick_laptop: You probably wouldn't want more than one instance for testing per machine.
Not for personal use. You could, but probably wouldn't ever try.
There are other limitations in XE.
mick_laptop
thanks for the help Xgc
MTecknology
How can I get the user id that's executing a query?
RJarett
user
but be careful of proxy user conditions
MTecknology
RJarett: thanks
dreamil
Hi All, Iam encountering error while calling one of my function http://pastebin.com/ZCw8VnEz
the error I am geting is ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1231 ORA-12570: TNS:packet reader failure
Can someone tell me what could be wrong with our function?
Note that the namespace url http://sms.indictrans.org does not exist, and we have made a hosts entry to point it to DB server machine
DB server is running in a local LAN machine on IP 192.168.5.11
RJarett
have you bothered to packet trace this in wireshark or pass it through a proxy so you can capture the request its generating?
dreamil
RJarett: no, but as per your last suggestion, Iam deploying it on oc4j
RJarett: i am installing wireshark now, will check and let you know
RJarett
soap_api? isnt that the one from oracle-base?
dreamil
I got this from their examples
RJarett
ugh
and not only that but you are connecting to a server and not even using a proper namespace
good luck with that
dreamil
RJarett: my server is a local machine , what ns should i use for that?
or what alternative would you suggest?
insanidade
how to check that a select has brought no results ?
select distinct data_type into res from ALL_TAB_COLUMNS where owner = vowner and column_name = vcolumn; So, I'd like to check that the "res" received no results after the statement execution.
anybody ?
usn_work
good CEDT morning
can anyone help me on a confusion about undo retention? I have always been under the impression that undo_retention parameter on autoextesible UNDO tablespaces makes the tablespace grow before overwriting undo blocks
cofeineSunshine
good EEST morning;)
usn_work
but in threads like http://forums.oracle.com/forums/thread.jspa?threadID=680847 they are talking as if it would overwrite undo block if retention is not large enough, despite the tbs can still grow
(several GB in my case)
What's true?
insanidade
anybody ?
usn_work
http://www.oracle.com/technology/oramag/oracle/05-jul/o45tuning.html
twb
$customer has asked me to set up a SLES10 server with Sybase and "SQR". I've done the first part, and now I'm trying to install SQR.
Apparently SQR is a component of Oracle's "Hyperion" product. I found an install manual for it, but the "download" links are 404ing.
e.g. http://www.oracle.com/technology/products/bi/foundation-services/index.html
Would you lot know anything about that?
usn_work
did you try via edelivery.oracle.com?
twb
I did.
It had two Hyperion options, but they didn't *look* like the thing I *think* I want
usn_work
maybe you want to open a non-technical SR then
twb
SR?
usn_work
service request
fka "iTAR"
because download links for supported products should work
on edelivery, there often are big bundles to download, maybe you have to download something big and extract and fillet it locally
twb
Nod.
$boss downloaded *something*, which was about 8GiB of .dat and .xml files zipped up.
usn_work
rofl
twb
Hmm, file(1) reports that the .dat files are actually zips
usn_work
I think some non-tech SR should really help
I just don't know which support portal is suitable for hyperion at the moment
twb
I confess I'm pretty lost; I'm used to the FOSS space where I just say "apt-get install foo" and it's installed and mostly configured.
usn_work
I missed to keep up with support changes
FOSS used to be configure/make/make install ;)
twb
Oh, I know about that, too.
Navigating baroque .com websites, not so much.
usn_work
then, Oracle won't kill you. Just stay open for a different approach
twb
Nod.
usn_work
(means mostly debugging sh*tty java installers)
once installed, oracle products are pretty
twb
It is unlikely to be worse than the twenty-year-old sybase/solaris stack that $customer is currently running
usn_work
agree
but don't hesistate to ask Oracle for a known gap in the jungle, ask OSS
(don't know if there are Hyperion folks here, I'm none)
twb
I tried some random clicking on edelivery, and I now realize there's about three levels of hierarchy before you get to the actual downloads -- the "bottom" layer has what looks more like what I'm after
usn_work
:)
you should write a report as career changer for the IOUG magazine. "Using oracle.com websites without optimism"
admin0
is it safe to change timezone of a linux server running oracle ?
dsdel
ahh how could you retrieve the scn in oracle9? :)
usn_work
from v$database maybe?
dsdel
got it, yes
just different naming
select checkpoint_change# from v$database
ORA-00600: internal error code, arguments: [2662], [0], [999749036], [0], [1000101985]
...
usn_work
wuah
dsdel
but I can't find the scn 1000101985
every datafile/redofile has got the correct scn
v$database also
dreamil
I have made progress in calling soap service for oracle, the earlier namespace error is gone
Now i am getiing this: ORA-20000: axis2ns21:Client - The endpoint reference (EPR) for the Operation
not found is http://localhost:8080/SMSGateway/services/SMSService and the WSA
« prev 1 2 next »