logo
down
shadow

Referring a to generic cursor in an anonymous block in oracle


Referring a to generic cursor in an anonymous block in oracle

By : user2949576
Date : November 17 2020, 01:00 AM
hope this fix your issue You're trying to fetch your ref cursor into three columns, when the query has eight from the object type. I don't think there's any way to fetch into an object directly but you can define a local record type with the same structure as the object:
code :
DECLARE
    TYPE l_gen_cost_record IS RECORD
    (
      gen_cost_code      VARCHAR2(30),
      gen_cost_code_desc VARCHAR2(100),
      amount             NUMBER,
      cost_code          VARCHAR2(30),
      acct_category_code VARCHAR2(30),
      category_desc      VARCHAR2(100),
      category_code      VARCHAR2(30),
      oracle_task_code   VARCHAR2(3)
    );

    v_gen_cost_rec              l_gen_cost_record;

    c_transfer_results          pkg_cost_api.t_ref_generic;

...
            LOOP
            FETCH c_transfer_results INTO v_gen_cost_rec;
                v_comments := v_comments || CHR(10)
                        || 'Plant Transfer from ' ||  v_nopc_isbn
                        || ' to ' || v_move_to_isbn
                        || ' Amounts: ' || v_gen_cost_rec.gen_cost_code_desc
                        || ': ' || v_gen_cost_rec.amount || CHR(10);


Share : facebook icon twitter icon
How to return a resultset / cursor from a Oracle PL/SQL anonymous block that executes Dynamic SQL?

How to return a resultset / cursor from a Oracle PL/SQL anonymous block that executes Dynamic SQL?


By : user1719625
Date : March 29 2020, 07:55 AM
wish of those help You can write a PL/SQL function to return that cursor (or you could put that function in a package if you have more code related to this):
code :
CREATE OR REPLACE FUNCTION get_allitems
  RETURN SYS_REFCURSOR
AS
  my_cursor SYS_REFCURSOR;
BEGIN
  OPEN my_cursor FOR SELECT * FROM allitems;
  RETURN my_cursor;
END get_allitems;
  OPEN my_cursor FOR 'SELECT * FROM allitems';
  OPEN my_cursor FOR 'SELECT * FROM allitems WHERE id = :id' USING my_id;
Referring to a cursor in an anonymous block

Referring to a cursor in an anonymous block


By : BAM
Date : March 29 2020, 07:55 AM
wish help you to fix your issue Why would you want to? Presumably, if you are selecting three columns in your cursor declaration, you need all three columns in your code, so you would need to fetch all three columns into three separate local variables, i.e.
code :
DECLARE
  x      integer;
  y      integer;
  z      integer;
  CURSOR c1
      IS SELECT column1, column2, column3
           FROM some_table;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 
     INTO x, y, z;
    EXIT WHEN c1%NOTFOUND;
  END LOOP;
  CLOSE c1;
END;
DECLARE
  CURSOR c1
      IS SELECT column1, column2, column3
           FROM some_table;
  c1_rec c1%ROWTYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 
     INTO c1_rec;
    dbms_output.put_line( c1_rec.column2 );
    EXIT WHEN c1%NOTFOUND;
  END LOOP;
  CLOSE c1;
END;
DECLARE
  CURSOR c1
      IS SELECT column1, column2, column3
           FROM some_table;
BEGIN
  FOR c1_rec IN c1
  LOOP
    dbms_output.put_line( c1_rec.column2 );
  END LOOP;
END;
Returning cursor from PL/SQL anonymous block in C#

Returning cursor from PL/SQL anonymous block in C#


By : Tom Woo
Date : March 29 2020, 07:55 AM
hope this fix your issue for the feedback everyone. It turns out that while debugging this issue, at some point I switched out the variable that defined my query, so that changes I was making to the query weren't actually being applied when running the application. As a result, I'm not sure exactly which fix ended up being the answer. But just for completeness, here's what I ended up using that worked.
First, run:
code :
create type id_array as table of number;
DECLARE t_Ids id_array;
BEGIN
UPDATE CSM_RECORDS SET MIGRATION_STATE = 1, LAST_UPDATE = CURRENT_DATE
    WHERE OBJECT_UID IN 
    (SELECT OBJECT_UID 
    FROM CSM_RECORDS obj 
    WHERE MIGRATION_STATE = 0
    AND ROWNUM <= :BatchSize
    AND (:BatchName IS NULL OR obj.BATCH_NAME = :BatchName)
RETURNING OBJECT_UID BULK COLLECT INTO t_Ids;

OPEN :rcursor FOR SELECT * FROM CSM_RECORDS WHERE OBJECT_UID IN (SELECT * FROM TABLE(cast(t_Ids as id_array)));
END;
OracleCommand getNextNodesC = new OracleCommand(QUERY_DEFINED_ABOVE.Replace("\r\n", "\n"), conn);

getNextNodesC.BindByName = true;

OracleParameter batchSizeP = new OracleParameter("BatchSize", OracleDbType.Int32);
batchSizeP.Value = batchSize;
getNextNodesC.Parameters.Add(batchSizeP);

OracleParameter batchNameP = new OracleParameter("BatchName", OracleDbType.Varchar2);
batchNameP.Value = batchName;
getNextNodesC.Parameters.Add(batchNameP);

OracleParameter returnCursor = new OracleParameter("rcursor", OracleDbType.RefCursor);
returnCursor.Direction = ParameterDirection.ReturnValue;
getNextNodesC.Parameters.Add(returnCursor);

return getNextNodesC.ExecuteReader();
How to execute an oracle procedure with an out cursor parameter in an anonymous PL/SQL block?

How to execute an oracle procedure with an out cursor parameter in an anonymous PL/SQL block?


By : prettyme.lea
Date : March 29 2020, 07:55 AM
wish help you to fix your issue I think you are misunderstanding the use of %ROWTYPE. You should just use %ROWTYPE when you are storing all rows from a table. Instead of using %ROWTYPE, make your own type(record) that fits the datatype of the columns that you are fetching. Try this:
code :
DECLARE
  C_EMP SYS_REFCURSOR;
  TYPE new_type IS RECORD(FIRST_NAME VARCHAR2(100), LAST_NAME VARCHAR2(200), DEPARTMENT_ID NUMBER, DEPARTMENT_NAME VARCHAR2(200), CITY VARCHAR2(200), STATE_PROVINCE VARCHAR2(200));
  L_REC new_type; --instead of using %ROWTYPE, use the declared type
BEGIN
  GET_EMPLOYEES('US', C_EMP);
  LOOP
 FETCH c_emp INTO l_rec;
 EXIT WHEN c_emp%NOTFOUND;

     dbms_output.put_line(l_rec.first_name||'_'||
                          l_rec.last_name||'_'||
                          l_rec.department_id||'_'||
                          l_rec.department_name||'_'||
                          l_rec.city||'_'|| 
                          l_rec.state_province);
 END LOOP;

CLOSE c_emp;
END;
generate excel file using cursor in anonymous block in oracle

generate excel file using cursor in anonymous block in oracle


By : Faruk Faruk
Date : March 29 2020, 07:55 AM
Hope this helps As OldProgrammer mentioned, you cannot call SPOOL within PL/SQL. But you can can print to a spooled file from within PL/SQL, and can pre-compile/flatten the loop into a delegating intermediate worker-script that repeatedly calls the csv-generator.
Update. In response to the specifics you provided, the following should work for you.
code :
INSERT INTO CSS_BOUTIQUE VALUES ('EUR', 'Belgium', 'a0Hb0000006LLdQ');
INSERT INTO CSS_BOUTIQUE VALUES ('EUR', 'Portugal', 'a0HB0000006LLOG');
INSERT INTO CSS_BOUTIQUE VALUES ('EUR', 'Portugal', 'a0HB0000006LLYu');

INSERT INTO CSS_BOUTIQUE VALUES ('ASIA', 'Korea', 'e0HB0000016MEIi');
INSERT INTO CSS_BOUTIQUE VALUES ('ASIA', 'China', 'e0HB0000026MEIi');
INSERT INTO CSS_BOUTIQUE VALUES ('ASIA', 'Japan', 'e0HB0000036MEIi');

INSERT INTO CSS_BOUTIQUE VALUES ('SA', 'Chile', 's0HB0000016MEIi');
INSERT INTO CSS_BOUTIQUE VALUES ('SA', 'Argentina', 's0HB0000026MEIi');
INSERT INTO CSS_BOUTIQUE VALUES ('SA', 'Equador', 's0HB0000036MEIi');
SET ECHO OFF;
SET FEEDBACK OFF;
SET HEAD OFF;
SET LIN 256;
SET TRIMSPOOL ON;
SET WRAP OFF;
SET PAGES 0;
SET TERM OFF;
SET SERVEROUTPUT ON;

SPOOL Loop-Flattener.sql;

PROMPT VAR V_ZONE_NAME VARCHAR2(64);


BEGIN
  FOR TARGET_POINTER IN (SELECT DISTINCT ZONE FROM CSS_BOUTIQUE)
    LOOP
    DBMS_OUTPUT.PUT_LINE('DEFINE TARGET = '''||TARGET_POINTER.ZONE||''';');
    DBMS_OUTPUT.PUT_LINE('EXEC :V_ZONE_NAME := '''||TARGET_POINTER.ZONE||'''; ');
    DBMS_OUTPUT.PUT_LINE('@@Target-Csv-Generator.sql;');
  END LOOP;
END;
/

SPOOL OFF;

@@Loop-Flattener.sql;
SPOOL REPORT_FOR_&&TARGET..csv;

PROMPT zone,market, boutique_id;

select zone||','||
       market||','||
       boutique_id      
from CSS_BOUTIQUE
where rownum <5 and ZONE = :V_ZONE_NAME;

SPOOL OFF;
SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 10 14:38:13 2017
SQL> @@Generate-And-Run
REPORT_FOR_EUR.csv
REPORT_FOR_SA.csv
REPORT_FOR_ASIA.csv
cat REPORT_FOR_ASIA.csv 
zone,market, boutique_id
ASIA,Korea,e0HB0000016MEIi
ASIA,China,e0HB0000026MEIi
ASIA,Japan,e0HB0000036MEIi
Related Posts Related Posts :
  • Postgres Calculate Difference Using Window Functions
  • SQL Server : unsure how to retrieve selected records
  • SQL unique field: concurrency bugs?
  • Query to get the next identity?
  • SQL Server Adding summing values based on the month
  • Join on month if available else join on latest month
  • How to solve error: ORA-01406 fetched column value was truncated?
  • Selecting Field Multiple Times using OR
  • Which SQL datatype can be used to store mobile numbers in numeric forms, without including characters like brackets and
  • Oracle SQL - "Not a group by expression" after adding CASE statements
  • Runnning total of unique string values in column over days
  • VB.Net to AccessDB by SQL - INSERT INTO with parameters error
  • SQL/Excel/VBA - UPDATE query: 'Syntax Error (missing operator) in query expression'
  • Code to combine two sql queries
  • Setting SQL Date field value based off Week Number and Day Number Field
  • SQL query to find matching records but with differences in case sensitivity?
  • How to sort record inside views in SQL Server 2008
  • How to link the output of teradata sql query to an excel sheet?
  • Matching sets in SQL when cross-referencing
  • error even after following Microsoft format in SQL
  • Increase price by percentage, results rounded
  • Can we Use "Case" in a ColdFusion Query-of-Query
  • displaying 'Active' for a row which has 'A' as value
  • Using pattern search in SQL Server Query
  • SQL - how to group transactions for a certain date pattern?
  • How to Index SQL with multiple AND conditions nested inside OR
  • SQL rank with priority
  • Does BigQuery support SubQueries?
  • Any ideas for persisting H2 Database In-Memory mode transaction?
  • How to improve SQL query performance (correlated subqueries)?
  • Filter repeated columns in Oracle
  • SQL Server join on whichever column has value populated
  • use sql stored procedure to insert data which is returned from a query stored in a table
  • Query not working1
  • SQL - using an alias in a where clause in a subquery
  • How to search for a whole word with special char using oracle contains?
  • Production Perfomance of using SELECT * statements?
  • String concatenation not working as expected in SELECT statement
  • How to detect which stored procedures UPDATE or INSERT into a certain TABLE?
  • Select statement inside CASE - SQL
  • .Net Float error 4 - float(4) = 4.44089209850063E-16
  • How to show as column to recursive rows in a table With SQL Server
  • SQL Server error: Column name or number of supplied values does not match table definition
  • How to find a table name which's column name consist cl_
  • Cannot access existing table from WCF Code
  • SQL CREATE VIEW Error
  • Many Statement in the same transaction PLSQL
  • Tables are not joining properly in sql server 2008
  • DB2 how to sum two column from two different table
  • SQL msAccess: list unfinished courses of a student
  • SQL Server delete multiple rows
  • Finding max(date) from the resultant query output
  • updating multiple columns using case statement in sql server
  • Nested SQL query having select subquery in update statement
  • "putting" a certain object to the screen from inside an array
  • Retrieve segment from value
  • Error while creating a table in oracle 11g
  • Transaction with Sequelize doesn't work
  • Search time complexity of this sql query
  • How to return unique records between two tables without using distinct and union?
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org