Why won't this string go to oracle db

I have a php/mysql app that sends data to an oracle db.  The cliient got this error.  Can anyone see the problem with the string that is being sent?

Warning: ociexecute() [function.ociexecute]: OCIStmtExecute: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "terryM.MANAGER", line 358 ORA-06512: at line 1 in /usr/www/url/charlieHorse.techsolution.com/include/class.project.php on line 339
Couldn't Execute begin Manager.CreateProject ('119202','25505','Flyer - Marschal Overview','32','Charlie Group','Deliver To: print in-house; 120 color copies; deliver by 3/20 to: Attn: larry low 2682 thomas Drive, Suite 123 P.O. Box 1307 San berdino, CA 94000 Path To Text: Draft Due: 03/02/2009','Flyer',NULL,'23-FEB-09','02-MAR-09',NULL,NULL,'09-MAR-09',NULL,NULL,NULL,NULL,'mitchell Green',NULL,NULL,NULL,'print in-house; 120 color copies; deliver by 3/20 to: Attn: roger moore 2682 michell Drive, Suite 123 P.O. Box 1807 San bernadino, CA 94000 ',NULL,NULL,NULL,NULL,NULL,:var_out); end;
LVL 1
justmelatAsked:
Who is Participating?
 
DavidSingletonCommented:
Is this happening everytime you run it, or just for that one record.  Also, what is the field set up for wherever you are trying to insert terryM.Manager

The thing is, that error is saying your either:
a.  Trying to insert text into a int field
b.  Trying to insert (for example) 100 characters into a field that only allows 40
0
 
DavidSingletonCommented:
something is missing from your code.  No where in there do I see an entry with terryM.MANAGER.  My guess is your not getting the full statement your trying to execute in oracle.  Instead of waiting for the error, can you just print out the actual statement and paste it here for us?
0
 
justmelatAuthor Commented:
david i had to change some things so i won't get in trouble.  so the terryM.Manager is something different
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
DavidSingletonCommented:
I see, well the first thing I would check is that your not trying to write terryM.Manager to a numeric field and that the length allowed in that field is enough room for your string there.
0
 
justmelatAuthor Commented:
i did an echo of the sql before the oracle script is run - is this what you meant?

begin ProjectManager.CreateProject ('119202','25505','Flyer - GF Overview','35','GR Group','Deliver To: print inHouse, 120 color copies, deliver by 03-20-2009 to Attn Lee Lee 2682 Bhop Drive, Suite 123 P.O. Box 1447 San Ramon, CA 94777 Path To Text: Draft Due: 03/02/2009','Flyer',NULL,'23-FEB-09','02-MAR-09',NULL,NULL,'09-MAR-09',NULL,NULL,NULL,NULL,'Nige Gray',NULL,'03/09/2009','03/19/2009','print inHouse, 120 color copies, deliver by 03-20-2009 to Attn Lee Lee 2682 Bhop Drive, Suite 123 P.O. Box 1777 San Ramon, CA 9477',NULL,NULL,NULL,NULL,NULL,:var_out); end;
0
 
justmelatAuthor Commented:
just this one record.  I have submitted others and they work just fine.
0
 
DavidSingletonCommented:
justmelat its going to be pretty hard to tell you what the problem is then if you can't post the actual statement that's giving you the error.  Check out options a and b that i listed above with the "terryM.Manager" line and see if you can get anywhere there.
0
 
justmelatAuthor Commented:
Hi David

With a fresher brain, i understand what you were asking for.  The code is attached.
function putOracle()
    {
        global $db, $db_conn, $accountId, $requestorId, $projectBrief;
 
        if ($this->myDebug)
        {
            $requestId='12345';
        } else
        {
            $requestId='';      //This will become the OR ID
            $OR['accountId']=$accountId;
            if(!empty($_SESSION['ORID']))
              $OR['requestorId']=$_SESSION['ORID'];
            else
               $OR['requestorId']=$requestorId;
               $OR['title']=$this->formData['ORACLE_TITLE'];
 
            
            $OR['templateProjectId']=$this->formData['ORACLE_TEMPLATE'];
            //$OR['description']= "Deliver To: \n\t". $this->answer['Q_12'] . "\nPath To Text:\n\t" . $this->answer['Q_13']. "\nDraft Due:  " . $this->answer['Q_8']. "\n\nKey Messages:\n" . $this->answer['Q_13']; //Goals
            $OR['description']= "Deliver To: \n\t". $this->answer['Q_12'] . "\nPath To Text:\n\t" . $this->answer['Q_13']. "\nDraft Due:  " . $this->answer['Q_8'];
            $OR['groupName']=$this->answer['Q_7']; //Project Type
            $OR['companyName']=$this->answer['Q_6']; //Product Type
            $OR['endDate']=getOracleDate($this->answer['Q_8']);  //Draft Due  date
            $OR['jobNumber']=$this->requestNumber;
            $OR['startDate']=getOracleDate($this->answer['Q_1']); //Date submitted
            $OR['accountCode']=getOracleDate($this->answer['Q_9']);//Final Due Date
            //$OR['costCenter']=$this->answer['Q_4'];
 
            
            $OR['vCustRefNum1']=$this->answer['Q_4']; //requestor
            //$OR['vCustRefNum2']=$this->answer['Q_5'];  //requestor email
            $OR['vCustRefNum3']=$this->answer['Q_10'];  //To Print Date
            $OR['vCustRefNum4']=$this->answer['Q_11'];  //Delivery Date
            $OR['vCustRefNum5']=$this->answer['Q_12'];  //Deliver To
            //$OR['vCustRefNum7']=$this->answer['Q_9'];  //Completion Date
               
              
 
            $PROCFIELDS=array(  'requestorId','templateProjectId','accountId','title','jobNumber','companyName',
                                'description','groupName','statusId','startDate','endDate','budget','actual',
                                'accountCode','costCenter','purchaseOrder','templateFlag','chargebackFlag',
                                'vCustRefNum1','vCustRefNum2','vCustRefNum3','vCustRefNum4','vCustRefNum5',
                                'vCustRefNum6','vCustRefNum7','vCustRefNum8','vCustRefNum9','vCustRefNum10');
 
            $FMT=array();
            for ($i=0;$i<count($PROCFIELDS);$i++)
            {
                if ( isset($OR[$PROCFIELDS[$i]]) && strlen($OR[$PROCFIELDS[$i]]) > 0)
                {
                    array_push($FMT,"'".preg_replace("/'/","''",$OR[$PROCFIELDS[$i]])."'");
                } else
                {
                    if ($PROCFIELDS[$i] != 'templateProjectId')    //Don't put a NULL value if there is no template
                    {
                        array_push($FMT,'NULL');
                    }
                }
 
            }
 
            if (isset($OR['templateProjectId']) && strlen($OR['templateProjectId']) > 0)
            {
                $sql = "begin ProjectManager.CreateProjectFromTemplate (".implode(",",$FMT).",:var_out); end;";
            } else
            {
                $sql = "begin ProjectManager.CreateProject (".implode(",",$FMT).",:var_out); end;";
            }
 
            if ($this->myDebug)
            {
                echo $sql;
                $requestId=1000;
            } else
            {
 
                //echo $sql."<br>";
            	$s = OCIParse($db_conn, $sql) or die("Couldn't");
                OCIBindByName($s, ":var_out", $requestId, 32);
                OCIExecute($s, OCI_DEFAULT) or die("Couldn't Execute ".$sql);
            }
 
            $binProjectBrief=preg_replace('/@@R_NUMBER@@/',$this->requestNumber,$projectBrief);
 
            $sql  = "insert into C_PROJECT_BRIEF_FILE (ID,PROJECT_FOLDER_ID,DESCRIPTION,";
            $sql .= "FILE_PATH,FILE_MIME_TYPE,FILE_EXTENSION,FILE_SIZE,FILE_CONTENTS) ";
            $sql .= "VALUES (C_PROJECT_BRIEF_FILE_SEQ.NEXTVAL,$requestId,'projForm_$rn','ProjectRequest.html','text/html','html','".strlen($binProjectBrief)."',empty_blob()) ";
            $sql .= "returning FILE_CONTENTS into :FILE_CONTENTS";
 
            if ($this->myDebug)
            {
                echo $sql;
            } else
            {
                $stmt = OCIParse($db_conn, $sql) or die("Couldn't Parse");
                $lob = OCINewDescriptor($db_conn);
                OCIBindByName ($stmt, ":FILE_CONTENTS", $lob, -1, OCI_B_BLOB);
                OCIExecute($stmt, OCI_DEFAULT);
                $lob->save($binProjectBrief);
                OCICommit($db_conn);
            }
 
        }
 
        //copy files to OR
        copyFilesToOR($requestId,$this->requestNumber,$this->answer['Q_30']);
 
        //Update Intake, too
        $sql  = "INSERT INTO ANSWER VALUES ('{$this->requestNumber}','Q_100','$requestId')";
        mysql_query($sql,$db);
 
        return $requestId;
    }

Open in new window

0
 
DavidSingletonCommented:
I'm still not sure that the issue is with your code, the error seems to point to the issue being with the single record and not anything in your code.

What you've told me so far is that there isn't an issue with your code except for with that single record.  So your code works 99% of the time, but fails on this one record.

the String "terryM.Manager", can you tell me what field they were trying to insert that into?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.