[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Why won't this string go to oracle db

Posted on 2009-02-23
9
Medium Priority
?
499 Views
Last Modified: 2012-05-06
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;
0
Comment
Question by:justmelat
  • 5
  • 4
9 Comments
 
LVL 3

Expert Comment

by:DavidSingleton
ID: 23714478
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
 
LVL 1

Author Comment

by:justmelat
ID: 23714714
david i had to change some things so i won't get in trouble.  so the terryM.Manager is something different
0
 
LVL 3

Expert Comment

by:DavidSingleton
ID: 23714734
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 1

Author Comment

by:justmelat
ID: 23714757
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
 
LVL 3

Accepted Solution

by:
DavidSingleton earned 1500 total points
ID: 23716149
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
 
LVL 1

Author Comment

by:justmelat
ID: 23716173
just this one record.  I have submitted others and they work just fine.
0
 
LVL 3

Expert Comment

by:DavidSingleton
ID: 23716206
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
 
LVL 1

Author Comment

by:justmelat
ID: 23720613
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
 
LVL 3

Expert Comment

by:DavidSingleton
ID: 23721623
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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month18 days, 8 hours left to enroll

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question