[Oracle] -- How to execute package-functions that insert records?

So, good day, eh!

I've got a package in my Oracle database called "customer". Inside of the package there is a function for attaching customers to larger/parent entities and then creates some more child/detail records. The function is called "AssignToParentEntity".

There's quite a bit of logic that goes on in the function (as well as the other functions that AssignToParentEntity calls). I started to replicate the function but quickly realized I was going down a bad road.

So, I'm trying to figure out how to execute a package function. I'm having no luck. Closest I came to success was getting a DML exception (which meant that the package ran!).

What I'm trying to do now is to create a statement which calls the function and bind the result to a variable. Anyone ever achieve this? It seems so simple... I might just need to step away from the problem to get that 'Aha!' moment.

I'm using the Yajra Oracle connector/package. - uses OCI8.

Thanks for having a gander, eh! Enjoy yer day!

AndrewMack

10 years ago

Level 3

I think I made some progress. At least, I'm getting different error messages now.

Looks like I just might be attaching/binding my variables wrong? -- anyone see anything wrong?

DB::select( DB::raw("begin :result := customer.AssignToParentEntity(:customerId, :newParentId, :relationshipSummary, :detailsId, :referencesId); end;"), [
                'result' => $result,
                ':customerId' => $customer->id,
                'newParentId' => $company->id,
                'relationshipSummary' => $companyMember->relationshipSummary,
                'detailsId' => $request->detailsId,
                'referencesId' => $request->referencesId
            ]);

Error Message : ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1
Position : 0
Statement : begin :result := customer.AssignToParentEntity(:customerId, :newParentId, :relationshipSummary, :detailsId, :referencesId); end;
Bindings : [0,367,23392,relationship summary goes here,4281, 771820]
(SQL: begin :result := customer.AssignToParentEntity(:customerId, :newParentId, :relationshipSummary, :detailsId, :referencesId); end;)

AndrewMack

10 years ago

Best Answer

Level 3

Well it's actually pretty simple -- I found the answer by looking through closed issues in the Yajra package GitHub page.

If anyone checks out this issue -- here's what I did...

use DB;
use PDO;
...
...
$pdo = DB::getPdo();
$stmt = $pdo->prepare('sql-statement-here');
// bind your parameters with bindParam()
$stmt->execute();

1 like

kaikelfalcao

2 years ago

Level 5

@AndrewMack You saved my work, I'm maintaining a Laravel 5 application and it left me very late on deadline, I managed to do everything this way. Thank you very much

Please or to participate in this conversation.