/    Sign up×
Community /Pin to ProfileBookmark

Problem in Retrieving data from oracle database

Copy linkTweet thisAlerts:
May 04.2010

HI

i have a table in oracle databases. my table has a column of type varchar2(255). when i query data from table using php, it gets hung and doesnot display the result due to large number of charachers in the column.

if i query only those rows which has less number of characters in the mentioned column than it works.

please help me.

thanks

to post a comment
PHP

12 Comments(s) ↴

Copy linkTweet thisAlerts:
@NogDogMay 04.2010 — Do you get any specific error message either from PHP or Oracle? Have you checked the respective error logs?

I ask because I don't use Oracle, but often the best way to search on such things is via specific text from such error messages.
Copy linkTweet thisAlerts:
@SrWebDeveloperMay 04.2010 — Need to know which version of Oracle and are you using OCI8 or PDO_OCI or ODBC?

General thoughts:

I doubt the database would allow an insert of data in that column greater than its size limitation. But if it becomes an issue, maybe you could convert to a clob.

If you used OCI8 for example then the function OCIError can be used to return the debug info you need, most importantly the error code (i.e. Ora-xxxxx) which you use to find out what's wrong.

-jim
Copy linkTweet thisAlerts:
@momerauthorMay 05.2010 — actually i am also new to oracle but the problem seems to be in the php not in oracle.

i am using oracle 10g and oci8

it is my php query:

$q=oci_parse($c,"select t.mmonth,

T.cust_name,T.acct_no,t.msisdn,T.subs_status,

t.deliver_method_name, t.email

from CUST_EMAIL@dwh_link t

where to_char(to_date('1-'||t.mmonth,'DD-MON-YY'),'MM/YYYY')='$from'");

oci_execute($q);

now my column cut_name has

"Sim Box Detection Tool Test Line c/o Fraud & RA" value in one row and

"Ayazullah" in other row

i can not get the result when query for the first row

i can get result with same query for the second row

no error displayed just webpage hang and show no result for looong time.

there is no such problem when i query same data in the backend (oracle)
Copy linkTweet thisAlerts:
@SrWebDeveloperMay 05.2010 — Well, your query has more than one column. In your original response you mentioned the problem with one column, but never stated the column name, so are you sure it's the T.cust_name which is at fault? Especially since the data you said is in two rows for that column each of which clearly is far less than 255 chars. If there indeed is a problem with ONE column (whatever it may be) you can find out by ruling out the others one at a time. This means only return one column at a time to prove which one is bombing. I'd not be asking if you didn't contradict yourself in this way.

You also say no errors show, but I don't see any code that traps an error after parsing so I'll take your word for it that you simply didn't post it here yet (and no need to do so if true) and it bombs in the manner you describe.

Based on all this, my advice is first solidly identify which column is at fault, or if you've done that already and know it's T.cust_name other things to check include:

  • * If the Oracle instance is not on the same server, and it turns out a large binary field like clob or lob is being used, could it be bandwidth related?

  • * Is there a trigger or sequence on the Oracle side that is causing this when you run the query (noting grants and permissions vary from the remote user vs. local user testing a query on the back end) - had this happen to me once, an insert wouldn't work via PHP but did locally due to a sequence not being permissed properly.


    • -jim
    Copy linkTweet thisAlerts:
    @momerauthorMay 05.2010 — Yeah i have checked many times problem is in t.cust_name when it has value for e.g "Sim Box Detection Tool Test Line c/o Fraud & RA"

    but it works when it has value for e.g "Ayazullah"

    other fields are working problem and i am not using any sequence.

    how to get error because it doesnt show any error.

    if it is bandwidth issue do u have any solution for it. but i think it is not that much of big size that would effect bandwidth.

    thanks
    Copy linkTweet thisAlerts:
    @SrWebDeveloperMay 05.2010 — What does "other fields are working problem" mean?

    The bandwidth which is the least important thing I said and can only be rectified with your network admin. I only said it because none of the actual data in question is even close in length to the varchar2 limit. Plus all the rest I talked about apparently has been ruled out, including "no errors", so what can I fix?

    Please wait for some other replies, maybe someone else has other suggestions, I'm sure we both missed something obvious and it'll turn out to be something silly.
    Copy linkTweet thisAlerts:
    @NogDogMay 05.2010 — I might take a closer look at the where condition in the query, in case it is not matching the problematic record the way you think it should be.
    Copy linkTweet thisAlerts:
    @SrWebDeveloperMay 05.2010 — I agree, as the only other thing I can possibly think of at this point is this --> I noticed just now the two rows that "fail" which the OP spoke of thus far include "/" and "&" characters not found in those that don't fail. Possible encoding issue? Man, I dunno. I assume the OP copy/pasted the same query when testing locally.
    Copy linkTweet thisAlerts:
    @momerauthorMay 06.2010 — what i mean is that all other fields are working except those which has "Sim Box Detection Tool Test Line c/o Fraud & RA" or same length

    i remove / and & , still the problem exists.


    thanks
    Copy linkTweet thisAlerts:
    @criterion9May 06.2010 — Have you turned on verbose logging on the Oracle side? If so, do you get anything of interest in the log files?
    Copy linkTweet thisAlerts:
    @momerauthorMay 09.2010 — i dont know about verbose, how to turn it on and does it solve my problem

    Thanks
    Copy linkTweet thisAlerts:
    @criterion9May 09.2010 — Does the database produce an error log? If so locate it to check if you get further information that might assist your problem. If the database does not produce enough content on the logs try turning up the verbose level of logging so you can get more information.
    ×

    Success!

    Help @momer spread the word by sharing this article on Twitter...

    Tweet This
    Sign in
    Forgot password?
    Sign in with TwitchSign in with GithubCreate Account
    about: ({
    version: 0.1.9 — BETA 6.2,
    whats_new: community page,
    up_next: more Davinci•003 tasks,
    coming_soon: events calendar,
    social: @webDeveloperHQ
    });

    legal: ({
    terms: of use,
    privacy: policy
    });
    changelog: (
    version: 0.1.9,
    notes: added community page

    version: 0.1.8,
    notes: added Davinci•003

    version: 0.1.7,
    notes: upvote answers to bounties

    version: 0.1.6,
    notes: article editor refresh
    )...
    recent_tips: (
    tipper: @meenaratha,
    tipped: article
    amount: 1000 SATS,

    tipper: @meenaratha,
    tipped: article
    amount: 1000 SATS,

    tipper: @AriseFacilitySolutions09,
    tipped: article
    amount: 1000 SATS,
    )...