PL/SQL Procs arguments 1

[jjob]

Below is a simple PL/SQL proc, it is limited, in that the argument is fixed as a row type of Processed_Data_Cursor, ideally adding a new parameter for a table name then having the rowtype as dynamic would provide a general proc,

I have seen possible ways of producing dynamic SQL, but they seem rather convoluted, is there a way of making this simple proc behave as a 3GL type of procedure, with the rowtype argument being a pointer type and thus a variable structure?
TIA

John

PROCEDURE writeRow(outPutRow IN Processed_Data_Cursor%ROWTYPE) IS

BEGIN

    INSERT INTO CDF_REPORT VALUES outPutRow;
    COMMIT;

END;

 

[Thargy]

jjob,

If I have understood you correctly, you want to pass a parameter which is a pointer to a variable cursor.

To achieve this in PL/SQL use a refcursor, which as its name suggests, references a cursor, and is therefore a pointer. Do NOT strongly type the cursor, otherwise it is just as inflexible as a %ROWTYPE declaration. This obviously has the disadvantage that the compiler can't detect any type errors, but you can't have it both ways.

You should declare the ref cursor in a package specification.
Open the ref cursor for select blah blah blah in the package body, and then you can pass it to procedures and functions.

Regards

Tharg



Grinding away at things Oracular

 

[jjob]

Thanks for that, I'll award you a star, and apologise to SantaMufasa because I just realised I didn't give him a star for some help he gave me a few weeks ago!

 

[Thargy]

jjob,

thanks for the purple pointy thingy (to quote willif).

One of these days I'll catch up with SantaMufasa, but he's a hard act to follow.

Thanks

Tharg

Grinding away at things Oracular