logo
down
shadow

Access out parameters of a procedure from another procedure in oracle


Access out parameters of a procedure from another procedure in oracle

Content Index :

Access out parameters of a procedure from another procedure in oracle
Tag : oracle , By : Matt
Date : November 28 2020, 09:01 AM

I think the issue was by ths following , You're almost there. What you want to do is:
Obtain (FETCH) the result set of the query (a SYS_REFCURSOR is a pointer to a query). Get the columns you need from the result set by placing results into a record (row-by-row) or a collection.
SQL> declare
  2    TYPE t_clientID_nt IS TABLE OF dual%rowtype;  --TYPE <type_name> IS TABLE OF <record_name>
  3    clientID_nt t_clientID_nt;  --Create a variable for the table type
  4  
  5    l_cur sys_refcursor;
  6  
  7    procedure OpenAndPopulateCursor(p_cur in out sys_refcursor) is
  8    begin
  9      open p_cur for
 10        select *
 11         from dual;
 12    end;
 13  
 14  begin
 15    OpenAndPopulateCursor(l_cur);  --Returns your SYS_REFCURSOR via IN OUT parameter
 16  
 17    if l_cur%isopen
 18    then
 19      fetch l_cur bulk collect into clientID_nt;  --Bulk collecting into the table variable (collection) we created above
 20    end if;
 21  
 22    dbms_output.put_line(concat( to_char(clientID_nt.count) 
 23                               , ' record(s) has/have been fetched.'));
 24  end;
 25  /
DECLARE
  l_cursor  SYS_REFCURSOR;
  l_ename   emp.ename%TYPE;
  l_empno   emp.empno%TYPE;
  l_deptno  emp.deptno%TYPE;
BEGIN
  get_emp_rs (p_deptno    => 30,
              p_recordset => l_cursor);

  LOOP 
    FETCH l_cursor
    INTO  l_ename, l_empno, l_deptno;
    EXIT WHEN l_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(l_ename || ' | ' || l_empno || ' | ' || l_deptno);
  END LOOP;
  CLOSE l_cursor;
END;
/

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Will oracle stored procedure support strcspn and strncpy inside the procedure?


Tag : oracle , By : matthew
Date : March 29 2020, 07:55 AM
Does that help they are c functions not pl/sql. to get the IP + port from the string you've supplied its like this anonymous block sample
(sample code assumes there's a "-" in the string, you may want to validate this up front!)
declare
  v_str  varchar2(1000) := '4000-10.1.1.1';
  v_port pls_integer;
  v_ip   varchar2(50);
begin
  v_port := substr(v_str, 1, instr(v_str, '-')-1);
  v_ip   := substr(v_str, instr(v_str, '-') + 1);
  dbms_output.put_line(v_port);
  dbms_output.put_line(v_ip);
end;
/

Read Oracle stored procedure interface by procedure name using Oracle's .NET client


Tag : .net , By : silvervino
Date : March 29 2020, 07:55 AM
I wish this help you All relevant information about procedure/function parameters is located in the metadata view ALL_ARGUMENTS.
For example:
SQL> CREATE PACKAGE somePackage AS
  2     PROCEDURE someProc (p_arg1 NUMBER, p_arg2 OUT VARCHAR2);
  3  END;
  4  /

Package created

SQL> SELECT package_name, object_name, position, argument_name, data_type, in_out
  2    FROM user_arguments
  3   WHERE package_name='SOMEPACKAGE';

PACKAGE_NAME  OBJECT_NAME   POSITION ARGUMENT_NAME  DATA_TYPE  IN_OUT
------------- ------------ --------- -------------- ---------- ---------
SOMEPACKAGE   SOMEPROC             1 P_ARG1         NUMBER     IN
SOMEPACKAGE   SOMEPROC             2 P_ARG2         VARCHAR2   OUT

Convert SQL Server stored procedure to Oracle procedure to query from tables


Tag : sql-server , By : inquiringmind
Date : March 29 2020, 07:55 AM
I wish did fix the issue. Here is the code that worked for me with Sys_Cursor. @TS for the help that guide me to this answer.
create or replace PROCEDURE QUERYALL 
(
o_Cursor OUT SYS_REFCURSOR
) 
AS 
BEGIN
O_Cursor := NULL;

OPEN O_Cursor FOR
    SELECT * FROM FUNIJ1
    INNER JOIN CAPI99 on CAPI99.APPLICATION_ID = FUNIJ1 .APPLICATION_ID
    INNER JOIN CAPI41 on CAPI41.APPLICATION_ID = FUNIJ1 .APPLICATION_ID
    INNER JOIN CAPI10 on CAPI10.APPLICATION_ID = FUNIJ1 .APPLICATION_ID
    WHERE 
    ----
    ----
END QUERYALL;
String sp= "{CALL QUERYALL(?)}"; // Procedure calling with Sys_Cursor
cst = con.prepareCall(sql);
cst.registerOutParameter(1, OracleTypes.CURSOR);
cst.executeUpdate();
rs = (ResultSet) cst.getObject(1);              
while(rs.next()) {
----
----
}

Get results of (Oracle) stored procedure output parameters in Access


Tag : oracle , By : ChristianM
Date : March 29 2020, 07:55 AM
this one helps.
How do I get this to return the results just like normal SELECT query?
Option Compare Database
Option Explicit

Public Function Call_sp_name_010(v_srch_sw As String, v_srch_crit As String, v_ssn_last_four As String) As Variant
    Const linkedTableName = "dbo_Clients"  ' an existing ODBC linked table

    Dim cdb As DAO.Database
    Set cdb = CurrentDb

    Dim con As New ADODB.Connection
    con.Open "DSN=" & Mid(cdb.TableDefs(linkedTableName).Connect, 5)  ' omit "ODBC;" prefix

    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = con
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "schemaname.sp_name_010"
    cmd.Parameters.Append cmd.CreateParameter("@v_srch_sw", adVarChar, adParamInput, 1)
    cmd.Parameters.Append cmd.CreateParameter("@v_srch_crit", adVarChar, adParamInput, 20)
    cmd.Parameters.Append cmd.CreateParameter("@v_ssn_last_four", adVarChar, adParamInput, 4)
    cmd.Parameters.Append cmd.CreateParameter("@v_ivr_data", adVarChar, adParamOutput, 4000)

    cmd.Parameters("@v_srch_sw").Value = v_srch_sw
    cmd.Parameters("@v_srch_crit").Value = v_srch_crit
    cmd.Parameters("@v_ssn_last_four").Value = v_ssn_last_four
    cmd.Execute

    ' function returns output parameter value
    Call_sp_name_010 = cmd.Parameters("@v_ivr_data").Value

    Set cmd = Nothing
    con.Close
    Set con = Nothing
    Set cmd = Nothing
End Function

Oracle PL SQL Procedure takes long time to complete, after the very last statement in the procedure is processed


Tag : oracle , By : Lucas Thompson
Date : March 29 2020, 07:55 AM
hop of those help? We are using Oracle 11.2.0.4.0. We created a procedure to do update a CLOB column in a table for all available rows. , From the comments, the fix was to add the line
    DBMS_LOB.freetemporary(l_masked_clob);
Related Posts Related QUESTIONS :
  • How can I create a "dynamic" WHERE clause?
  • index for `where wakeup_time < sysdate`
  • select only new row in oracle
  • alias all column in a query with a prefix
  • Oracle 10g Express / APEX
  • How to force oracle to use index range scan?
  • Peoplesoft queries - performance
  • Select and Insert across dblink
  • Oracle table fragmentation
  • Running a Query in SQL Plus
  • Where can I get SQL for default Oracle's "HR" schema?
  • best way of oracle database partitions?
  • How do I know the number of keywords coming from a database?
  • Retrieving Stored procedures, Views, Functions, Triggers using Toad for Oracle
  • "FRM-40602: Cannot insert into or update data in a view" error
  • PL/SQL Continue exection after error handled
  • Combination of add_months and to_date gone wrong if date is 28-Feb
  • Oracle - How to know the load volume by times of a table?
  • Data Migration - Verify Data loaded where Primary Key can change
  • encountered the symbol FROM when expecting one of the following pl sql
  • Dynamically create tables based on the total counts of another table
  • Stored procedure is taking too much time to update the table columns
  • ORA-00984: column not allowed here error while loading data
  • Using a %ROWTYPE in a WHERE IN clause
  • Extract single repeating XML node through whole XML result
  • When the timezone is adjusted to user's timezone when retrieving data from TIMESTAMP WITH LOCAL TIMEZONE column?
  • Oracle synonym alternatives in H2?
  • ORA-00920: Invalid relational operator (no missing/extra parenthesis)
  • How to update data by select random row value from another table
  • Convert Coldfusion script in Oracle view with CASE and iteration
  • How to calculate the amount of ID monthly with the condition?
  • Ho to get UDT members by SQL?
  • How to set Oracle connection string for dotNET core console app?
  • Getting MySQL error stack trace on Oracle JDBC Connection
  • Why a long running pl/sql code that shows ACTIVE in v$session doesn't show up in v$active_session_history
  • Oracle SQL changes in sqldeveloper are not reflected in EFCore
  • Oracle query to get counts from multiple tables and display them side by side
  • Want to change NLS_DATE_FORMAT in oracle DB for a particular Schema only
  • How to convert char to number with mask
  • Oracle 11g Database NUMBER value with a zero at end, a bug?
  • How to convert this code from oracle to redshift?
  • How does the Oracle (+) join work in this scenario
  • External tables and directory objects
  • Edit RAW column in Oracle SQL Developer
  • Multiple values for the same URI parameter
  • Need Help To Resolve ORA-12154
  • How to unhide page item in oracle apex
  • How can I get this syntax to work in Oracle?
  • oralce apex_json to extract fields
  • get all records without fulfilling a condition
  • Last 5 months records dynamically in Oracle without sysdate
  • Serializing SDO_GEOMETRY type to text really slow
  • What's wrong with this UPDATE SQL JOIN syntax?
  • Oracle command works in database but not in SQLPlus
  • Checking collection data in where clause oracle
  • how to define multiple (WITH AS FUNCTIONS) in a single query?
  • Could I use plsql variable constant or variable with Flyway
  • Query joined partitioned tables
  • Compare time in a non-date format in Oracle
  • runtime plsql throwing table not exist error
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com