How to call a pl sql procedure from script

[Former Member]

I want to call a pl sql procedure "usp_fetchTPSIRows"

i have the oracle datastore already available within my local object library.

Can someone please provide step to call procedure. with and without parameters.

Thank you.

[Former Member]

Hi Rogers,

I think the below steps will clearly help you.

1. Got to your target data store and import by name (Function) - This will import your PL-SQL procedure into the repository.

2. Call the stored procedure using the syntax i gave you above. Here is an example of how you can call in your custom function / script.

# Calling Stored Procedure to Load the BASE Table

print ('Calling Stored Procedure to Load the BASE Table');

  $Ret = sql('DS_TD', 'CALL STORED_PROCS.SP_CDC_LOAD_TABLE_BASE( {$V_BASE_VIEW_DB_NAME}, {$V_BASE_TABLE_DB_NAME}, {$P_I_BASE_TABLE_NAME}, {$V_STG_DB_NAME}, {$V_STG_TABLE_NAME}, {$V_PRIMARY_KEY_COL},

                                                      :out_DML_CDC, :out_DML_INS, :out_DML_UPD, :out_DML_AUDIT)');

print( 'Function Fn_BASE_DELTA Completed');

Return ($Ret);

3. Then use this function in your script to pass values to the variables. below is an example.

# Calling Function to Load the ACQ Table for FULL Extractors based on the data that was loaded into Stage

Fn_BASE_Delta($V_TGT_TABLE_NAME);

Let me know if you are struck within or if you need any additional information.

[Former Member]

Hi Rogers,

To call a procedure or function, we need to import it to the datastore. After that we can call it using a single statement. The syntax is

Datastore_name.dsowner.procedure_name ();

If the procedure or function accepts any argument, the list can be given inside parenthesis.

In most DWs, the details of all jobs being run will be stored in an audit table. From the script, we can call a stored procedure which will insert the job details to the audit table.

[Former Member]

Hello Vamsi,

Question: How can i import pl sql procedure, i only see Functions, tables & views below oracle Datastore.

Does Something like this, will it work?

SQL('name_of_datastore', 'call name_of_sp()');

Thanks.

[ahalya_inturi]

Hi Rogers,

I got your question

Under the BODS oracle datastore you can find function node , right click on functions, go to option some thing like 'import by name' .

you can also call this function in query transformation by option "New Function Call"

hope it helps you

Thanks

Ahalya